Well, I started out thinking that this would be pretty simple, but as it turns out, I must be missing something. I am trying to setup a cfinput autosuggest that will quickly return a list of document numbers from our database.
A little backgound on our document number format: 00-00-AA-0000000
Basically there are three parts to the number. The first part, 00-00-, is the site and contract identifier. The second part, AA-, is the type of document. And the third part, 0000000, is the actual document.
CFM:
<cfset vTicketNum = gNavContractID & '-' & gNavSiteID & '-' />
<cfform name="frmAutoSuggest" action="" format="HTML">
<cfinput type="Hidden" name="asTicket" id="asTicket" value="#vTicketNum#" />
<cfinput type="text" name="asSearch" autosuggest="cfc:as.getData({cfautosuggestvalue})" autosuggestminlength="5" id="asSearch" showautosuggestloadingicon="True" maxresultsdisplayed="30" />
</cfform>
CFC:
<cfcomponent output="false">
<cfset THIS.dsn = "datasource" />
<cffunction name="getData" access="remote" returntype="array" output="No">
<cfargument name="asTicket" type="any" required="No" default="" />
<cfargument name="asSearch" type="any" required="No" default="" />
<cfset var asData = "" />
<cfset var asResult = ArrayNew(1) />
<cfquery datasource="#THIS.dsn#" name="asData">
SELECT
docNum
FROM
docs
WHERE
docNum LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ARGUMENTS.asTicket#%" />
AND RIGHT(docNum,5) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#ARGUMENTS.asSearch#" />
</cfquery>
<cfloop query="asData">
<cfset arrayAppend(asResult, docNum) />
</cfloop>
<cfreturn asResult />
</cffunction>
</cfcomponent>
Now doing a basic query like below, I get about 70000 results.
<cfquery name="qryData" datasource="#request.dsn#">
SELECT
docNum
FROM
docs
WHERE
docNum LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#vTicketNum#%" />
</cfquery>
Adding in the following drops my result set to 4.
AND RIGHT(a.ticketnumber,5) = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="00001" />
The autosuggest loading icon will activate, yet I do not get any suggestions… Any help would be greatly appreciated.