Dynamically building select box 1

[Argus]

I want to build an application where users can select a datasource/table and query it. They will be able to select what fields they want to be displayed, and also what criteria they are looking for (ie. dynamically build a query). So, for each table I have a list of fields, and whether users should enter criteria in a text box or choose from a select box for that field. If they choose from a select box I want to build one (obviously) and populate it with data from that field.

Firstly I have a query called GetFields which returns the FieldName, DisplayName, and what I've called ConstructType (Select, Text, and hopefully later Date, Between, GtrThan etc) for the table they have selected.

Typical values might be 'FirstName', 'First Name', 'Select' or 'City', 'City', 'Text' etc.

I also have a variable called DataTable which holds the table name.

After this I cfloop over the GetFields query, and have a CFSWITCH/CFCASE to deal with each 'construct'. The CFCASE statement for the select box is as follows:-

<CFCASE value=&quot;Select&quot;>
<BR><BR>
<CFOUTPUT>


<CFQUERY name=&quot;qSelect&quot; datasource=&quot;MyDB&quot;>
SELECT #FieldName# FROM #DataTable#
GROUP BY #FieldName#
ORDER BY #FieldName#
</cfquery>

</cfoutput>

<CFOUTPUT>#DisplayName#:
<SELECT name=&quot;#FieldName#&quot;></cfoutput>
<CFLOOP query=&quot;qSelect&quot;>
<CFOUTPUT>
<OPTION value=&quot;#FieldName#&quot;>#FieldName#
</cfoutput>
</cfloop>
</SELECT>

</cfcase>


The problem is with the OPTION tag. I don't want it to be the value of #FieldName# but rather the values from the field in the query 'qSelect', which is being held in #FieldName# !!

For instance if I select 'FirstName' then the first query would be:

<CFQUERY name=&quot;qSelect&quot; datasource=&quot;MyDB&quot;>
SELECT FirstName FROM MyDB
GROUP BY FirstName
ORDER BY FirstName
</cfquery>

But the select bit would just show 'FirstName' (literally)for every option, instead of the actual FirstNames derived from the database.

So, I guess I want CF to work out what #FieldName# is first, and then use that value as a fieldname rather than an absolute value.


Got there in the end but did anyone understand me??

 

[CFDude]

I think I understand -- you want the value of the GetFields query fieldname there -- since it looks like it would be inside that GetFields loop or output, can you use:

<OPTION value=&quot;#GetFields.FieldName#&quot;>#FieldName#

 

[Argus]

I've tried that already and it just returns the actual fieldname, not the values stored in that field.

I've just tried another approach, which is to write a custom tag that accepts the qSelect query as an object and recursively loops through it extracting each column value. Seems to have worked but I'm sure there's an easier way!

Thanks for your quick response.

 

[SidianConsulting]

You can use Evaluate() in this instance:

Evaluate(FieldName), which will give you the value of the variable within FieldName.

-Tek

 

[Argus]

Brilliant! Thanks for that. Exactly what I needed.