search interface question

A bit hard to say but something like this may work

<cfset searchby = "id,department">
<cfset dept = department> < not sure where department value is coming from but ...

<cfquery name="checkDept"...>
select * from tbl where department =  #dept#
</cfquery>

<cfif checkDept.recordcount eq 0>
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%'>
<cfelse>
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%',UPPER(department) LIKE '%dynaicFormvalue%">
</cif>

 You can check it against a query with no records returned and make a list of valid fields...



<cfset searchby = "id,department">
<cfquery name="checkFields" datasource="#request.datasource#">
  select * from myTable where 1 = 2
</cfquery>

<cfset searchFields = "">

<cfloop index="aField" list="#searchBy#">
  <cfif listFindNoCase(checkFields.columnList,aField)>
     <cfset searchFields = listAppend(searchFields,aField)>
  </cfif>
</cfloop>

Now you can search by any column name still on the list "searchFields"...

<cfif listFindNoCase(searchFields,"Department")>

</cfif>

That's fine GD but i have to FIND this

UPPER(ID) LIKE '%dynaicFormvalue% and remove it from the searchString because searchString is build upon the SearchBy, because if the DEPartment is defined, then ionly this will appear and i have to completely eracticate from the searchstring which will be building like this

SearchString will be like this UPPER(ID) LIKE '%dynaicFormvalue% AND UPPER(DEPARTMENT) = 'MyDept' OR UPPER(ID) LIKE '%thisvalu%

so suppose i check for Dept in searchby and it is linked with AND UPPER(DEPARTMENT) = 'MyDept' , so then i have to remove this from the searchString, like this there are couple of 5/6 fields to do it like this

AND can be OR and equal to sign could be like or not equal to, all selected dynamically and the value passed against each parameter

isuppos e i have abeen able to properlyexplain what i m trying

Any Updates @gd, @sid

If I understand correctly, you are saying that you already have DEPARTMENT in the list of search criteria and you need to REMOVE it if the column doesn't exist in the table.

<cfset searchby = "id,department">
<cfset searchString = "UPPER(ID) LIKE '%dynaicFormvalue%',UPPER(department) LIKE '%dynaicFormvalue%">

<cfquery name="checkFields" datasource="#request.datasource#">
  select * from myTable where 1 = 2
</cfquery>

<cfset searchFields = "">
<cfset searchClause = "">

<cfset counter = 0>
<cfloop index="aField" list="#searchBy#">
  <cfset counter = counter + 1>
  <cfif listFindNoCase(checkFields.columnList,aField)>
     <cfset searchFields = listAppend(searchFields,aField)>
     <cfset searchClause = listAppend(searchClause,listGetAg(searchString,counter))>
  </cfif>
</cfloop>

Now searchClause contains only the criteria you are allowed to search on..

searchClause

Select allOpen in new window

the string from which to remove the data exists like this

AND (UPPER(ID) LIKE ''%2%'' AND UPPER(DEPTID) =''HELLO''so this is not need

 AND UPPER(DEPTID) =''HELLO''

in this case, i have to detect the followng:

AND symbol

Upper(Fieldtowhich to do the replacement)

Like or = operator

so like this

AND Upper(Fieldtowhich to do replacement) LIKE %'