6 Replies Latest reply on Jan 21, 2015 11:55 PM by madmike6537

    Execute SQL - Find records where field = multiple values


      Hello there,


      Using SQL - I am trying to count the number of records where my field (Room_KF) equals any one of a set amount of values.


      Some googling led me to the IN statement - I am not sure if Filemaker supports this, but I cant get it to work. Here is what I have:





      roomIDs = "('" & ExecuteSQL("SELECT \"_Room_PK\"  FROM ROOM WHERE Customer_KF = ?"; ""; "','"; overview_CUSTOMER::_Customer_PK ) &"')";

      totalMaterials = ExecuteSQL ("SELECT COUNT(*) FROM MATERIAL_READING WHERE \"_Room_KF\" IN roomIDs AND Reading_Date = ?"; ""; ""; Get(CurrentDate))









      The first line roomIDs gets a list of all my room ID's that match the current customer record. It ends up looking something like this: ('123', '234')

      The reason for this formatting is according to W3 that is the format SQL wants: SQL IN Operator


      Then, my next statement tries to find Material Readings that are a child of ANY of those rooms.



      If this isnt supported, any ideas on how I might approach this using native Filemaker functions? Hopefully I explained it well enough :X