"ListSort" does not work! Any idea?

rookie9 · Sep 12, 2003

Hello,

The following is a portion of my "Search.cfm" file in which I posed a question(breaking a Field into two, FirstName & LastName) last July and implemented Carl's response. Worked great. However the results "NAMES" is presented to the user (to pick a name for search) sorted by FirstName which is the original "OWNER" order. Although I have added the "SortList" to sort the "NAMES" but it does not have any effect on the final results. I need to be able to sort the results(NAMES) by LastName. How can I use "ListOrder"? what else can be done to put the results in Order by LastName?

Thanks a bunch for your help.
-Rookie guy

==================================
here is a portion of my code:
...
<SELECT style="width: 400;" NAME="theOwnerName" SIZE="1">
<OPTION VALUE="%">SELECT A NAME:
<CFOUTPUT QUERY="myOwnerQuery">
<cfset FirstName=Listfirst(Owner, ",&quot

>
<cfset LastName=ListRest(Owner, ",&quot

>
<cfset Names=LastName & ", " & FirstName>
<cfset sortedlist = ListSort(Names, "Text", "Asc&quot

>
<OPTION VALUE="#OWNER#">#sortedlist#
</OPTION>
</cfoutput>
</SELECT>
...

rookie9 · Sep 12, 2003

...Sorry for the typos...
I meant to write "ListSort" while describing the problem instead of "SortList" and "ListOrder".

csteinhilber · Sep 12, 2003

Everytime through the CFOUTPUT loop, you're creating a new sortedlist list. And, in fact, all you're sorting is your first name from your last name... not sorting by all last names.

There are (at least) two ways you could address this. In SQL, and in CFML.

Doing it in SQL would be the best bet, if it's possible with your database. In Oracle, for example, you could use SUBSTR to pull out the last name as an aliased column, then do an ORDER BY that column to sort by last name. However, SUBSTR is an Oracle-specific extension to SQL. If you're not using Oracle, you might be able to use a similar function, or you might not be able to do it at all (some databases have very limited extended functions).

Doing it in CFML is more convoluted and won't perform as well (speed-wise)... but you can certainly do it. You just need to break it up into two separate loops, one to create the list to be sorted, and one to build the select box.

Code:

<!--- set up an initial empty list --->
<CFSET lstNamesByLast = &quot;&quot;>
<!--- loop through the original query --->
<CFLOOP QUERY=&quot;myOwnerQuery&quot;>
  <CFSET sFirstName=ListFirst(&quot;#myOwnerQuery.Owner#&quot;, &quot;,&quot;)>
  <CFSET sLastName=ListRest(&quot;#myOwnerQuery.Owner#&quot;, &quot;,&quot;)>
  <!--- append the name to the list in LASTNAME,FIRSTNAME format, with a non-comma delimiter --->
  <CFSET lstNamesByLast = ListAppend(&quot;#lstNamesByLast#&quot;, &quot;#sLastName#,#sFirstName#&quot;,&quot;|&quot;)>
</CFLOOP>

<!--- now sort the list... make sure you specify the non-comma delimiter --->
<CFSET lstNamesByLast = ListSort(&quot;#lstNamesByLast #&quot;, &quot;TextNoCase&quot;, &quot;Asc&quot;, &quot;|&quot;)>

<select style=&quot;width: 400;&quot; NAME=&quot;theOwnerName&quot; SIZE=&quot;1&quot;>
   <option value=&quot;%&quot;>SELECT A NAME:</option>
   <!--- now loop through the sorted list --->
   <CFLOOP list=&quot;#lstNamesByLast#&quot; index=&quot;whichOwner&quot; delimiters=&quot;|&quot;>
      <!--- pull the first name and last name back out, since you need the value to be in FIRSTNAME,LASTNAME format --->
      <CFSET sLastName=ListFirst(&quot;#whichOwner#&quot;, &quot;,&quot;)>
      <CFSET sFirstName=ListRest(&quot;#whichOwner#&quot;, &quot;,&quot;)>
       
      <!--- write that name's option tag --->
      <option value=&quot;#sFirstName#,#sFirstName#&quot;>#whichOwner#</option>
   </CFLOOP>
</select>

But with all the list manipulation, it's going to suffer in performance.

-Carl

csteinhilber · Sep 12, 2003

Sorry... apparently we're both having typo-itis today.

The ListSort line should be:

Code:

<CFSET lstNamesByLast = ListSort(&quot;#lstNamesByLast#&quot;, &quot;TextNoCase&quot;, &quot;Asc&quot;, &quot;|&quot;)>

without that bothersome extra space in there.

and, of course, you need to wrap the output of the option tag in CFOUTPUT's:

Code:

<CFOUTPUT><option value=&quot;#sFirstName#,#sFirstName#&quot;>#whichOwner#</option></CFOUTPUT>

-Carl

r937 · Sep 12, 2003

If you're not using Oracle, you might be able to use a similar function, or you might not be able to do it at all (some databases have very limited extended functions).

all the databases in my experience have a substring function

definitely try to do the sort in the sql

csteinhilber · Sep 15, 2003

What I was trying to say with "you might be able to use a similar function" is "there's probably a function that's equivilent, but it might not be named 'SUBSTR' exactly".

MySQL, for example, names it's function 'SUBSTRING'... and actually even supports the old school 'MID'.

Sorry I wasn't clear.

-Carl

philhege · Sep 15, 2003

Just to add to all this confusion, here's my take on the 2D array/split list sort problem. Works for me, and the performance isn't all THAT bad:

<Cfset referencelist = "fdsioa14,hiopq345,iopqna534,aggfda234,ggt13455,bcn5438,4583ab">
<Cfset custpartlist = "fdsioa14,hiopq345,iopqna534,aggfda234,ggt13455,bcn5438,4583ab">
<cfset ourpartlist = "FRD110HM,FZB089VM,FAM089E1,FEW02221,FGA08483,FRD05330,FMA045QL">
<cfset sortedcustpartlist = listSort(custpartlist,"textnocase&quot

>
<cfoutput>#sortedcustpartlist#</cfoutput><br>
<cfset newourpartlist = "">
<cfloop index="thispart" list="#sortedcustpartlist#">

<cfset newourpartlist=newourpartlist&listgetat(ourpartlist,listfind(referencelist,thispart))&",">
<cfoutput>#newourpartlist#</cfoutput><br>
</cfloop>

<cfset sortedourpartlist=mid(newourpartlist,1,len(newourpartlist)-1)>
<cfoutput>
<table border="1" cellspacing="0">
<tr><td>original customer part number order:</td><td> #referencelist#</td></tr>
<tr><td>original our part order:</td><td>#ourpartlist#</td></tr>
<tr><td>new customer part number order:</td><td>#sortedcustpartlist#</td></tr>
<tr><td>new our part number order:</td><td>#sortedourpartlist#</td></tr>

</table>
<br>
<br>
<br>

</cfoutput>

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

DuncanKing · Jan 15, 2004

If using MS access with Cold Fusion and an SQL statement to order under one column what is the correct syntax

Lets say year is the column.

I've tried OREDER BY Year, OREDER BY #Year#, OREDER BY tblName.Year but no joy!

Thanks

r937 · Jan 15, 2004

ORDER BY tblName.Year

not OREDER BY

rudy

http://r937.com/

SQL Consulting

DuncanKing · Jan 15, 2004

rudy,
I made a typo I did mean ORDER BY

Here's what I have

<CFQUERY NAME="GetResults" DATASOURCE="DSN">
SELECT tblName.LNAME,tblName.FNAME,tblName.YEAR
FROM tblName
ORDER BY tblName.Year
WHERE 0=0

thanks

r937 · Jan 15, 2004

yeah, i thought so, but you did say it three times ;-)

hmm... could it have anything to do with "Year" being a reserved word?

i thought qualifying it with the table name would remove the ambiguity, but try this:

SELECT tblName.LNAME,tblName.FNAME,tblName.[YEAR]
FROM tblName
ORDER BY tblName.[YEAR]

rudy

http://r937.com/

SQL Consulting

DuncanKing · Jan 15, 2004

No joy...

If I try

<CFQUERY NAME="GetResults" DATASOURCE="DSN">
SELECT tblName.LNAME,tblName.FNAME,tblName.YEAR
FROM tblName
WHERE 0=0

i.e. leaving out
ORDER BY tblName.Year
i get a result..its just not ordered...
Any other suggestions?

Thanks

r937 · Jan 15, 2004

didn't you see that i put square brackets around the reserved word?????

rudy

http://r937.com/

SQL Consulting

DuncanKing · Jan 15, 2004

Yes i did see that and i did try it that way as well as without them?

r937 · Jan 15, 2004

then i'm sorry, i do not know

SELECT a,b,c FROM t WHERE 0=0 ORDER BY a

is perfectly okay syntax

rudy

http://r937.com/

SQL Consulting

DuncanKing · Jan 15, 2004

Ya thanks...it may be something to do with CF syntax???

r937 · Jan 15, 2004

run your query directly in the SQL query window in Access, not through CF, and run it with and without the ORDER BY

rudy

http://r937.com/

SQL Consulting

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

"ListSort" does not work! Any idea?

rookie9

Programmer

rookie9

Programmer

csteinhilber

Programmer

csteinhilber

Programmer

r937

Technical User

csteinhilber

Programmer

philhege

Programmer

DuncanKing

Programmer

r937

Technical User

DuncanKing

Programmer

r937

Technical User

DuncanKing

Programmer

r937

Technical User

DuncanKing

Programmer

r937

Technical User

DuncanKing

Programmer

r937

Technical User

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

&quot;ListSort&quot; does not work! Any idea?

Programmer

Programmer

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Programmer

Technical User

Similar threads

Log in

Part and Inventory Search

Sponsor

"ListSort" does not work! Any idea?