Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

having a little trouble with a find statement

Posted on 2014-01-16
4
Medium Priority
?
193 Views
Last Modified: 2014-01-17
I'm converting some old code;

<cfif #find("Any US",USREGION)#>
	<cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
	<cfset USREGIONquery = "true">
<cfelse>
	<cfif #find("Carolinas",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Carolinas%')" > 
		<cfelse>
			<!--- (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Carolinas%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>
	<cfif #find("Mountain West",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Mountain West%')" > 
		<cfelse>
			<!--- (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Mountain West%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>    etc......

Open in new window



To a more dynamic table based code;

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
    <cfset USREGIONquery = "false">
    <cfset USREGION = "South">
    <cfset querystring = "(" >

    <cfoutput>
        <cfif #find("Any US",USREGION)#>
            <cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
            <cfset USREGIONquery = "true">
        <cfelse>
            <cfloop query="regions">
                #usregion#-#regions.description#<br>
                #querystring#<br>
                <cfif #find(regions.description,USREGION)#>
                    <cfif USREGIONquery EQ "true">
                        <cfset querystring = "#querystring#" & " or (USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                    <cfelse>
                        <cfset querystring = "#querystring#" & "(USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                        <cfset USREGIONquery = "true">
                    </cfif>
                </cfif>
            </cfloop>
            <cfif USREGIONquery EQ "true">
                <cfset querystring = "#querystring#" & " or (USREGION Like '%All%')" >
                #querystring#<br>
            </cfif>
            <cfif querystring eq "(">
                <cfset querystring = "" >
            <cfelse>
                <cfset querystring = "#querystring#" & ")" >
            </cfif>
            #querystring# 
        </cfif>
    </cfoutput>

Open in new window


The problem is the find function doesn't seem to be working.  I've tried many variations.  The output I get is;

South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest
(
0
Comment
Question by:lanterv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mkishline earned 2000 total points
ID: 39788863
Couple of thoughts. The first would be to switch your "Find" to "FindNoCase" which will make the search case-insensitive. You may also want to consider doing the same thing for the conditions in your query.

For example:
<cfset querystring = "#querystring# OR (USREGION LIKE '%#regions.description#%') />

Open in new window


Would become:
<cfset querystring = "#querystring# OR (LOWER(USREGION) LIKE '%#LCase(regions.description)#%') />

Open in new window


It may have been lost during your post, but it appears that you're also missing an operator in your <cfelse> block (see below)

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
<cfset USREGIONquery = "false">
<cfset USREGION = "South">
<cfset querystring = "" >

<cfoutput>
	<cfif FindNoCase("Any US",USREGION)>
		<cfset querystring = "#querystring# (USREGION IS NOT NULL)" >
		<cfset USREGIONquery = "true">
	<cfelse>
		<cfloop query="regions">
			#usregion#-#regions.description#<br>
			#querystring#<br>
			<cfif FindNoCase(regions.description,USREGION)>
				<cfif USREGIONquery EQ "true">
					<cfset querystring = "#querystring# or (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
				<cfelse>
					<!--- Should have "AND, OR, or some other operator between #querystring# and "(" --->
					<cfset querystring = "#querystring# (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
					<cfset USREGIONquery = "true">
				</cfif>
			</cfif>
		</cfloop>
		<cfif USREGIONquery EQ "true">
			<cfset querystring = "#querystring# or (USREGION Like '%All%')" >
			#querystring#<br>
		</cfif>
		<cfif querystring NEQ "">
			<cfset querystring = "(#querystring#)" />
		</cfif>
		#querystring# 
	</cfif>
</cfoutput>

Open in new window

0
 

Author Comment

by:lanterv
ID: 39788926
<cfif USREGIONquery EQ "true">
means that the query string has already been added to and an and/or is needed.

The findnocase issue is a good one, but the original code didn't use it and worked fine.  I have made the change anyway but it didn't help.
0
 
LVL 12

Expert Comment

by:mkishline
ID: 39788999
In the list of the output that you posted:
South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest

Is the idea that the desired list should only contain the records whose regions.description contains "South"?
Desired resultset:
South-Southeast, South-South, South-South Central, South-Southern California, South-Southwest

If so, you should switch the arguments you are sending to the find function:
<cfif Find(USREGION, regions.description)>

Open in new window


If I have misidentified what your expected result set should look like, can you provide more detail as to what your desired results would contain?
0
 

Author Comment

by:lanterv
ID: 39789000
I haven't figured out why yet, but the code is working now with the findnocase changes.  Thank you.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question