dwdata Posted December 28, 2018 Posted December 28, 2018 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 Posted December 28, 2018 Posted December 28, 2018 (edited) 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 Posted December 29, 2018 Posted December 29, 2018 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 Posted January 1, 2019 Author Posted January 1, 2019 Thanks COMMENT! With a little fudging I got it working with your suggestion. Happy New Year!!!
Recommended Posts
This topic is 2405 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now