CalcBuilder Forum
Syntax for SQL Query on option list?
Hi,
this configuration field allows any valid query and the only rule is that you need to recover a couple of fields from it in order to fill the each option text and value.
You will find information and an example on how to fill the sql query configuration here:
https://www.moonsoft.es/tutorial/entry/14-calc-builder-for-joomla-4/calculator-configuration-form-fields
(section option List)
Please let us know if you need further help.
Kind regards
Silvia Martín
Moonsoft Team
Hi,
Thanks for the link, but I am still struggling with this.
I have a field called project_name in a table called jobsiteforms_projects
I want to be able to select the project name from that table using the option list field type.
I've tried to use the example you linked to, but I am getting a message that says there's an error in the SQL syntax.
What would I put in the SQL query line to retrieve that information from that field in that table?
Hi,
you don't share the query you're trying, so we can't tell you which is the problem with it, but remember you need to get 2 fields from the table in order to fill the combobox option value, and text.
If you have an id you can try to use that one, or get the text field as value as well:
select project_name as id,project_name from #__jobsiteforms_projects
should do the work.
Before using the query at the configuration, you can try it first at your preferred db manager so you will confirm it has a valid syntax and it retrieves the couple of fields required.
Hope this helps, regards
Silvia Martín
Moonsoft Team
Thanks! That worked.
Hi,
Thanks, that worked.
However, I am trying to limit it to entries only made by the logged in user, and I can't figure out what to put after WHERE
Could you let me know what that should be too?
Hi,
when you are building the query, you can use this text :
##id_user##
inside it and it will be replaced by the current logged in user id. You'll need to check your table in order to confirm to which field you'd need to apply the condition to, so you could build
where YOUR_DB_FIELD=##id_user##
Kind regards
Silvia Martín
Moonsoft Team
Hi again,
I am using this query SELECT project_name as id,project_name from #__jobsiteforms_projects WHERE state=1 {{created_by}} and it is working.
HOWEVER, when the text displays on the exit layout, all spaces are stripped out.
Why is that happening, and how do I fix it?
Hi,
sorry can you give more details/example about this issue? At the output layout the option text should be displayed, without further process, so not sure which are the spaces that are being stripped out, if they were inside the option into the text, or if the field is a multioption and you try to show the different options selected....please share at the private data the page where we can see this issue or an example on how to reproduce and we'll take a look.
Thanks, regards
Silvia Martín
Moonsoft Team
Hi,
I made a video about this. https://1drv.ms/v/c/acb8060b47bfc49d/IQCpmVg11mP7TIvM3T-h0FYtAcb0iK44gA4onW1mVChvgic?e=tBdORq
It is happening on all of the calclators I have built using SQL queries to access data from the component mentioned here, but as mentioned in the video, I have confirmed that the data is saved in that table WITH the spaces.
Some of the fields that specifically use SQL Queries are Project Name, Clent and a few others, depending on which calculator it is.
I have included login information below for the admin area and a frontend account, so you can test it yourself.
This is stopping me from launching this platform, so the sooner I can figure out what is happening, the better.
Hi,
we have checked and the issue is caused by a default filter applied to the variables. This is solved for the latest version of the extension, but we also applied the fix to your actual code so the values should be showing properly now.
Kind regards
Guillermo V.
Moonsoft Team
Hi there,
I want to use a SQL query to pull information from a database table.
Is there a how to or tutorial that explains the syntax etc?
Thanks!