1 of 1 people found this helpful
I am trying to get my Executesql() calculation to evaluate a count as part of a reporting structure of "active" records based on a request date and type of service from a portal. My parent table (Client_Layout) has my status record field, named STATUS. My portal, located on my parent layout, contains a Date and a category field named Services. Since my fields are in two different tables, I am unsuccessful in getting this to work.
Are you saying that you only want to consider records in the second table that are related to the layout record? The simplest way to do that is usually to add the primary key to the WHERE clause (in effect replicating the FM relationship definition):
ExecuteSQL ( "
SELECT STATUS, COUNT(Service)
STATUS = ? AND
SERVICE = ? AND
\"nameOfForeignKeyField\" = ? AND
\"DATE\" BETWEEN ? AND ?
" ; "" ; "" ;
"Active" ; "Visit" ; CLIENT_LAYOUT::primaryKeyField ; $$YTDFrom ; $$DateTo
1 of 1 people found this helpful
UNION is like multiple queries concatenated.
You need JOIN which sets up the relationship. The connectors on your graph are NOT any help with ExecuteSQL (context agnostic).
You need this (get the example files as well as the PDF)
Sent from miPhone
Trying to get a query working in the "Data Viewer" is like pulling teeth with a jackhammer!
Aside from no query assist, the brain-dead "?" result "messages", FMP's "Data Viewer" is simply not productive for SQL development or really even "data viewing" in it's teeny tiny scroll-able area (And no data "editing" in "Data Viewer").
Until FMP implements an actual "Data Viewer" in some future version, I would recommend you check out "RazorSQL" which is a "real" SQL tool that will connect to your LIVE FileMaker database (and any other JDBC-enabled database ....basically ALL OF THEM). Razor will also copy/sync data from one database type to another (that useful features isn't supported in FileMaker, but that doesn't disqualify Razor as an excellent SQL tool for FMP).
Using Razor, or a tool like it, you'll get live query assist, actual error messages, a nice large resizable area where you can edit/view your results.
Then, once you get your query working, and a two-table join is simple to do with the right tool, move the query back to FMP and add your replaceable parameters and such.
So, in summary, using a tool like Razor will:
1. Reduce your FMP SQL creation frustration by A LOT
2. Drastically increase working SQL creation productivity. (I usually save about 75% of the time using Razor)
3. See actual error messages so you quickly see what's wrong. FileMaker is very picky about GROUP BY expressions as one example
4. Have fully scroll-able windows where you can adjust everything.
5. Export and other options are built right in.
6. Have productive SQL and data work-space where you can work effectively.
And ... Razor and tools like it are just fun to use. And you can try Razor for free for 30 days I think.
I'm really not trying to sell you on Razor itself (I don't use it), but Razor or a tool like it will be like a breath of fresh air doing any SQL development in FMP. For myself, I use a Java IDE with a "data panel" that basically does the same thing as Razor, but not quite as nice as razor.
HOPE THIS HELPS.
Love the link to the SQL reference, however it gets a little blurred with all things SQL for FM. ExecuteSQL() doesn't always follow everything in that guide. Just a caveat!
understood. I was specifically pointing to these two paragraphs:
The FileMaker Pro ExecuteSQL function lets you retrieve data from table occurrences named in the relationships graph but independent of any defined relationships. You can retrieve data from multiple tables without creating table joins or any relationship between the tables. In some cases, you may be able to reduce the complexity of your relationships graph by using the ExecuteSQL function.
The fields you query with the ExecuteSQL function do not have to be on any layout, so you can use the ExecuteSQL function to retrieve data independent of any layout context. Because of this context independence, using the ExecuteSQL function in scripts may improve the portability of the scripts. You can use the ExecuteSQL function anywhere you can specify calculations, including for charting and reporting.
Thank you for the clarity!
And, as I recall due to the fact that FM SQL cannot do SQL that doesn't reference a table in the Relationship Graph, so even something simple like getting the current date requires a hack (MUST HAVE A FROM CLAUSE when other SQL environments don't require that).
(on my list.)
Oh yeah! must have a table (table occurrence on the graph) with at least one record (no data needed!), as a query to an empty table also does not return results.
Thank you everyone for all of your suggestions! Ideally, I would be looking for something like this:
From JAIL_PORTAL, CLIENT_LAYOUT
Where SERVICE = 'Intake' and STATUS = 'Active' and \"DATE\" >= ? and \"DATE\" <= ? " ; "" ; "" ; $$YTDFrom ; $$DateTo )
This does give me a resulting count, but an incorrect one. I did resolve this by creating a second status field in my related table (Jail_Portal) to read the status field from my parent table (Client_Layout) (e.g. case ( client_layout::status = "active" ; 1 ; 0 ). This brings my status field into the Jail_Portal table with my other fields so that it's all under a single table. A common practice I believe. And it works perfectly! A minor cost for adding a calculation field, and one a do not prefer to keep repeating since it should all be handled natively in FQL.
correct, it should be handled by the FQL. you did not recreate your JOIN (relationship) in the query that you had on the graph. had you done that, you would not have needed to create the calculated field to bring the related data. that's one of the basics of using the ExecuteSQL() - the need to not create extraneous relationships or "tunnel-through" calculations!
I have not seen the posts online and may have missed the information that a screen shot would provide.
Others who have answered should have provided the JOIN for you.
If you want to find records that have certain properties AND are related to the context record (and you're not referring to fields from a third table), you can simply add that relationship as an additional predicate to the WHERE clause, using your already existing primary and foreign key fields.
Please refer to my earlier post where this has been discussed - if you're asking for assistance, it's not helpful to ignore the one you receive.
This brings my status field into the Jail_Portal table with my other fields so that it's all under a single table. A common practice I believe.
It is not, since you can access and reference related fields as well as 'native' ones. (There are exceptions for certain summary and multi-hop sort requirements.)
Bringing everything "under a single table" very much defeats the purpose of having an RDBMS.
the quote from the FM SQL guide in my post #6 suggests not needing "joins". It's not required for a successful query. But you need them if you want to look at data across relationships. The rest of the quote is very informative - should be read over and over. Here's a sample of creating a join within a single statement:
"SELECT Score FROM Scores
JOIN Classes ON Scores.fk_ClassID = Classes.pk_ClassID
WHERE Classes.SortOrder = ? AND Scores.fk_StudentID = ? AND Scores.TestDate = ?";
TO::data1; TO::data2; TO::data3
1 of 1 people found this helpful
For a single query, I find a JOIN overly complicated; you need a JOIN for stuff like this:
ExecuteSQL ( "
SELECT T.type, COUNT(*)
FROM Incidents I
JOIN Types T ON Types.id = Incidents.id_type
WHERE I.id_carrier = ?
GROUP BY T.type
ORDER BY T.type
" ; "" ; "" ; Carrier::id
i.e. from the context of a first table, read (and summarize) data from a second table while resolving keys to pull in related data from a third (Made-up example, not tested ..)