Jump to content
Sign in to follow this  

fmp17 ExecuteSQL() ORDER BY FIELD function

Recommended Posts

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!

Share this post

Link to post
Share on other sites

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 by comment

Share this post

Link to post
Share on other sites

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 


Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By ChangeAgent
      Is this at all possible?
      I want to have two buttons that will each do the following. 
      Have one button to ‘Strike Thru’ text that is highlighted before.  Means not all the text in the field will be changed.
      And one button to change the text colour to Red, again highlighted before.  Means not all the text in the field will be changed.
      Any tips how to do this?  Please bare in mind I am a FMP beginner.  Thanks. 
    • By lutfishah
      Dear All,
      I am trying to put a conditional formatting validation which turns Red when the value is entered wrong. Basically all my tries have failed so I would like your help here.
      The criteria of entering a value is 
      1. Length of value is  = 19     OR      this should be a 16 digit number, in groups of 4 separated with dashes
      2. Non-Digits characters are not valid.
      Please could you help me with this and I will really appreciate. 
      Many thanks
    • By Koonce
      I have filemaker server on virtual server, I have a script that exports emails and or names i cannot get the file to be saved to the local desktop it tries to save to server with an error when i do manually it works fine so i am doing something wrong...
      Thanks in advance
    • By ThomaStudios
      I have been using FM17Adv for some time.  I am on a MacPro 12-core tower and I had been running macOS Sierra.  All the internal scripts I had in one database worked perfectly.
      I recently updated to High Sierra and now I am getting script errors I don't understand.  And this is from the same exact file I used previously.  I have one script where I want it to open a folder in Dropbox so I can access files there.  But I am getting this error:  Can’t make "Dropbox" into type integer.  Then I get a -1700 error dialog immediately after that.  Can anyone explain how I can fix this.  I have not been able to find anything online.
      The script is a native AppleScript, and I suspect that has something to do with this.  Here is the script:
      tell application "Finder"     activate     open folder " Pieces 2011" of folder "Teaching Material" of folder "Dropbox" of "  ThomaStudios 4TB"     select Finder window 1 set position of Finder window 1 to {2877, 167} end tell
      J D Thomas ThomaStudios
    • By Caio Euzébio
      Hi Guys,
      There are any logic or resource that i can use to make like a "text to columns".
      Make text from field one flow to another fields but only with in the middle of coma regarding your sequence, like a excel?
      Full text with Comas Text1 Separated Text2 Separated Text3 Separated How,Are,You? How Are  You? Could please help me, i need a lot of this resource.
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.