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