search multiple fields

[twcman]

I have an employee information form with about 25 fields. The fields are all in one table.

I would like to use the same form to search for employees that meet certain criteria from that form. Everything is drop down boxes except for the obvious ones (name, address etc.). How could I use perform a search based only on the fields populated with data submitted from this form?

 

[dallasweb]

This will loop over the field names passed in your search. If you are passing fields that are not related to the search, you'll need to deal with those.

You will need to name the form fields the same as the columns in your database table.

I haven't run this code but it should work(maybe with some minor tweaking).
Place this in your WHERE clause of your SQL statement.

<!--- Check that fieldnames exists --->
<CFIF IsDefined(&quot;FORM.fieldnames&quot>
<!--- Create empty list of processed variables --->
<CFSET fieldnames_processed = &quot;&quot;>
<!--- Loop through fieldnames --->
<CFLOOP INDEX=&quot;form_element&quot; LIST=&quot;#FORM.fieldnames#&quot;>
<!--- Try to find current element in list --->
<CFIF #ListFind(#fieldnames_processed#, #form_element#)# IS 0>
<!--- Make fully qualified copy of it (to prevent accessing the wrong field type) --->
<CFSET form_element_qualified = &quot;FORM.&quot; & #form_element#>
<!--- Output it as part of WHERE statement --->
<CFOUTPUT>
#form_element# LIKE '#Evaluate(form_element_qualified)#'
</CFOUTPUT>
<!--- And add it to the processed list --->
<CFSET fieldnames_processed = #ListAppend(#fieldnames_processed#, #form_element#)#>
<!--- determine if this is the last item in list, if not add AND to statement --->
<CFIF ListLen(FORM.fieldnames) EQ fieldnames_processed>
<cfelse>
AND
</cfif>
</CFIF>
</CFLOOP>
</CFIF>

 

[twcman]

Thanks for the code. I did some minor tweaking to put just the fields with a value in the where statement.

The where part of this is generating an error. First I will show the query it is based on. Don't get too hung up in the select and from section, this code works with the first cfif condition.

<cfquery datasource='#datasource#' name='GetRecords'>
SELECT tblMain.LN, tblMain.FN, tblAddress.Mail, tlkpDesc_Add.DescAdd, tblAddress.Address1, tblMain.Selected, tblMain.PT, tblMain.SWN, tblMain.TPS, tblMain.UnContacted, tblMain.Metroplex, tlkpCounty.County, tlkpRegion.Region, tlkpCity.City, tlkpState.ST, tblMain.Church, tblMain.MainID, tblAddress.Zip
FROM tlkpState RIGHT JOIN (tlkpDesc_Add RIGHT JOIN (tlkpCounty RIGHT JOIN (tlkpCity RIGHT JOIN ((tblMain LEFT JOIN tlkpRegion ON tblMain.RegionID = tlkpRegion.RegionID) LEFT JOIN tblAddress ON tblMain.MainID = tblAddress.MainID) ON tlkpCity.CityID = tblAddress.CityID) ON tlkpCounty.CountyID = tblMain.CountyID) ON tlkpDesc_Add.DescAddID = tblAddress.DescAddID) ON tlkpState.StateID = tblAddress.StateID
<cfif #len(URL.letter)# EQ 1>
where Left(tblMain.LN,1) = '#url.letter#'
</cfif>
<cfif '#URL.letter#' EQ 'filtered'>
WHERE
<!--- Check that fieldnames exists --->
<CFIF IsDefined(&quot;FORM.fieldnames&quot>
<!--- Create empty list of processed variables --->
<CFSET fieldnames_processed = &quot;&quot;>
<CFSET isfirst = &quot;yes&quot;>
<!--- Loop through fieldnames --->
<CFLOOP INDEX=&quot;form_element&quot; LIST=&quot;#FORM.fieldnames#&quot;>
<!--- Try to find current element in list --->
<CFIF #ListFind(#fieldnames_processed#, #form_element#)# IS 0>
<!--- Make fully qualified copy of it (to prevent accessing the wrong field type) --->
<CFSET form_element_qualified = &quot;FORM.&quot; & #form_element#>
<cfset formelementvalue = #Evaluate(form_element_qualified)#>
<cfif #len(formelementvalue)# GT 0 AND #formelementvalue# NEQ '0' AND #IsDefined(&quot;formelementvalue&quot#>
<!--- Output it as part of WHERE statement --->
<CFOUTPUT>
<CFIF #isfirst# EQ 'yes'><cfelse>AND</cfif>
#form_element# LIKE '#Evaluate(form_element_qualified)#'
</CFOUTPUT>
<cfset #isfirst# = &quot;no&quot;>
<!--- And add it to the processed list --->
<CFSET fieldnames_processed = #ListAppend(#fieldnames_processed#, #form_element#)#>
<!--- <!--- determine if this is the last item in list, if not add AND to statement --->
<CFIF ListLen(FORM.fieldnames) EQ fieldnames_processed>
<cfelse>
AND
</cfif> --->
</cfif><!-- element has value -->
</CFIF> <!-- current element list -->
</CFLOOP> <!-- fieldname loop -->
</CFIF><!-- fielname exists -->
</cfif>
ORDER BY tblMain.LN, tblMain.FN;
</cfquery>




THis is the error that is generated. Do you see anything wrong with the WHERE statement:


ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' '.


SQL = &quot;SELECT tblMain.LN, tblMain.FN, tblAddress.Mail, tlkpDesc_Add.DescAdd, tblAddress.Address1, tblMain.Selected, tblMain.PT, tblMain.SWN, tblMain.TPS, tblMain.UnContacted, tblMain.Metroplex, tlkpCounty.County, tlkpRegion.Region, tlkpCity.City, tlkpState.ST, tblMain.Church, tblMain.MainID, tblAddress.Zip FROM tlkpState RIGHT JOIN (tlkpDesc_Add RIGHT JOIN (tlkpCounty RIGHT JOIN (tlkpCity RIGHT JOIN ((tblMain LEFT JOIN tlkpRegion ON tblMain.RegionID = tlkpRegion.RegionID) LEFT JOIN tblAddress ON tblMain.MainID = tblAddress.MainID) ON tlkpCity.CityID = tblAddress.CityID) ON tlkpCounty.CountyID = tblMain.CountyID) ON tlkpDesc_Add.DescAddID = tblAddress.DescAddID) ON tlkpState.StateID = tblAddress.StateID WHERE LN LIKE 'scott' ORDER BY tblMain.LN, tblMain.FN;&quot;

 

[dallasweb]

Try adding the &quot;%&quot; to the LIKE clause

This will return wild card: sc returns scott, etc
#form_element# LIKE '%#Evaluate(form_element_qualified)#%'

This will return exact: sc returns sc only
#form_element# LIKE '%#Evaluate(form_element_qualified)#'

 

[twcman]

Sorry, still returns the same error.

It seems like it would be obvious but I have spent over an hour troublshooting this part of the code and I feel like I am overlooking something.

 

[twcman]

Well after another hour or so I found the problem. For some reason CF did not like the comments at the end:

<cfelse>
AND
</cfif>
</cfif> <!-- element has value -->
</CFIF> <!-- current element list-->
</CFLOOP><!-- fieldname loop -->
</CFIF><!-- fielname exists -->
</cfif>
ORDER BY tblMain.LN, tblMain.FN;
</cfquery>

I took them out and all is well...