I need to adjust the Let function to specifically count the meeting rooms, as shown in the snapshot. I have two meeting rooms, but the dashboard shows three instead of two because it is counting the asset rows.
Let (
[
selectedDate = Get ( CurrentDate );
meetingroomCount = ExecuteSQL ( "
SELECT COUNT(*)
FROM Inventory_Asset_List
WHERE "Date" = ?
AND Department = 'Meeting Room'
" ; "" ; "" ; Get ( CurrentDate ))
];
"MR. ¶" & TextSize ( meetingroomCount; 20)
)
Rather than SQL, I would approach this using normal FileMaker relationships and calculations, where you have a relationship between this table and the Inventory_Asset_List table, using calculated fields for today's date and the room Department "meeting rooms" (or use global fields, if you want to change the dates and room types).
Then, If each row of the asset contains the foreign key for the Meeting room, you could simply do this:
meetingRoomCount = ValueCount(UniqueValues(List(Inventory_asset_list_by_Date_And_Department::roomID)))
instead of using COUNT( * ), count the field with your meeting room name and use distinct on that.
See a reference here: Aggregate functions
You could also do this with a while calculation
One of the key issues with sql in FM that should preclude using it extensively; if there is an open record in the found set, eSQL will wait until all records are not locked.
Results in high variability in performance.
I believe Date is a reserved word in FileMaker SQL syntax.
I'd recommend renaming the field and using a third-party SQL tool like DataGrip (or similar) to fine-tune your SQL.
I prefer SQL in cases like this or anywhere when possible in FileMaker, since SQL is an industry standard.
Right, see Reserved words in FileMaker Pro
Agreed that you should be careful with how/when you use ExecuteSQL. But, I have not heard of an issue where it will just wait (forever?) until all records are not locked. Kirk, if you've seen that, I'd be interested in hearing more details.
The "gotcha" I'm aware of is similar, but is this:
If the client running the ExecuteSQL query has uncommitted records in that target table, the server will say "you have potential edits in that table, so you (the client) must run the query yourself. Here is ALL the data for ALL the records in the table: good luck."
Now, if the table is fairly small, that's not so bad. But, if the table has many thousands (or millions!) of records, the server has to transfer all that data to the client, and then the client does the query.
ExecuteSQL is built that way so that its results match the way FileMaker has always worked: the client knows about and can include its own uncommitted changes. But, the big downside is that queries normally done BY the server, and thus often very fast, might instead take a VERY long time while the entire contents of the table are transferred to the client over the network.
Right - that article is describing in more detail what I described above: the query becomes slower when the client trying to run the query has an open record in the table targeted by the query, for the reason I described above.
I thought perhaps you had heard of some other potential issue where the eSQL query would actually never complete until the records were committed. And, your comment implied that anyone having open records could cause this problem, when it is much more specific than that.
Also, the query does not "wait until all records are not locked". It takes longer, but it does not pause/wait. It will run, but potentially very slowly, if the specific circumstances described occur.
Try running a GROUP BY query with only 50,000 records if you want to see FileMaker really hung! Yes, I know, I know, .... "FileMaker is not a SQL Database!". LOL.
(I just did a GROUP BY query on MariaDB (free) with 8 Million records in 4.5 seconds.)
I would like to express my gratitude to everyone for sharing their suggestions and for their ongoing efforts in resolving this issue. Below are my solution based on the requirements.
Let (
[
today = Get ( CurrentDate ) ;
result = ExecuteSQL (
"SELECT COUNT(DISTINCT "Employee_Name")
FROM Inventory_Asset_List
WHERE Department = ?
AND "Date"= ?" ;
"" ; "" ;
"Meeting Room" ; today
)
] ;
"MR. ¶" & TextSize ( result; 20)
)
