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

    Execute SQL - Find records where field = multiple values

    madmike6537

      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:

       

      Let([

       

       

      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))

       

       

      ];

       

       

      totalMaterials

      )

       

      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