fmp17 ExecuteSQL() ORDER BY FIELD function

[dwdata]

Hi Gang,

I am reaching out to see if anyone can assist (or maybe point me in the right direction) with some ExecuteSQL() issues I am having in my my next version of my product.

 

I want to execute a query like the following:

 

SELECT code FROM Appts WHERE appt_id IN (’14’,’10’,’8’,’9') ORDER BY FIELD(appt_id, ‘9', ‘10', ‘8', ‘14’)

 

I have tried several times for this to return a working result with NO LUCK. Do you know if FMP allows this sort of query or does it fall short.

 

Appreciate any help. Thanks!

[comment]

ORDER BY FIELD is not a valid expression in Filemaker SQL syntax. I can't figure out what exactly are you trying to achieve here, so I don't know what to suggest as an alternative.

 

 

Edited December 28, 2018 by comment

[comment]

Ah. I was reading something else that made me realize what this is about. 

There is no FIELD function in Filemaker SQL syntax. To sort the records by custom sort order, try:

SELECT code 
FROM Appts 
WHERE appt_id IN (’14’,’10’,’8’,’9') 
ORDER BY CASE appt_id 
   WHEN '9' THEN '1'
   WHEN '10' THEN '2'
   WHEN '8' THEN '3'
   ELSE '4' END 

 

[dwdata]

Thanks COMMENT! With a little fudging I got it working with your suggestion. Happy New Year!!!