madmike6537

Execute SQL - Find records where field = multiple values

Discussion created by madmike6537 on Jan 20, 2015
Latest reply on Jan 21, 2015 by 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

Outcomes