Sorting by custom fields

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

Is there a way to sort by a customfield's data.  I'm setting up some links as predefined searches and I would like the order to go according to the customfield data.

I feel like creating another case in this snippet from showsearchresults.asp would work...  but... what would the syntax be?

if strORD<>"" then
    Select Case StrORD
        Case "1": strORD1="products.idproduct ASC"
        Case "2": strORD1="products.sku ASC"
        Case "3": strORD1="products.description ASC"
        Case "4": strORD1="products.price ASC"
        Case "5": strORD1="products.price DESC"
        Case "6": strORD1="products.custom1 DESC"
    End Select
Else
    strORD="1"
    strORD1="products.idproduct ASC"
End If

--------
Any guidance would be appreciated.

thanks, 

sean

Edited by sdesantis - 21-December-2006 at 5:54pm

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
sdesantis Members Profile Send Private Message Find Members Posts Add to Buddy List Newbie Joined: 21-December-2006 Location: United States Status: Offline Points: 0	Post Options Post Reply Quote sdesantis Report Post Thanks(0) Quote Reply Topic: Sorting by custom fields Posted: 21-December-2006 at 5:53pm
	Is there a way to sort by a customfield's data. I'm setting up some links as predefined searches and I would like the order to go according to the customfield data. I feel like creating another case in this snippet from showsearchresults.asp would work... but... what would the syntax be? if strORD<>"" then Select Case StrORD Case "1": strORD1="products.idproduct ASC" Case "2": strORD1="products.sku ASC" Case "3": strORD1="products.description ASC" Case "4": strORD1="products.price ASC" Case "5": strORD1="products.price DESC" Case "6": strORD1="products.custom1 DESC" End Select Else strORD="1" strORD1="products.idproduct ASC" End If -------- Any guidance would be appreciated. thanks, sean Edited by sdesantis - 21-December-2006 at 5:54pm

netprofits Members Profile Send Private Message Find Members Posts Add to Buddy List Certified ProductCart Developers Joined: 05-January-2006 Location: United States Status: Offline Points: 22	Post Options Post Reply Quote netprofits Report Post Thanks(0) Quote Reply Posted: 21-December-2006 at 9:46pm
	This will be tough because each product can actually have up to 3 custom fields. The custom1, custom2 and custom3 fields only hold the record # pointing back to the customfields table. The content1, content2 and content3 fields in the products table actually hold the values that you probably want to sort on. If you only have one custom field per product then you could try sorting on: "products.custom1, products.content1". But this will sort by the custom field record # then the custom field values. If you only have one custom altogether then you can just do the sort on "products.content1". If you actually want to sort by the real custom field names you would need to modify the whole SQL statement to join the customfields table. That could cause your searches to slow down. I would recommend an outer join so you don't lose records that have no custom field values.
	NetProfits Internet Consulting Certified ProductCart Developer Our Site