How do you go about doing a SQL SELECT on a FMP table with a calculated field? I'm getting all zeros. No way to have FMP evaluate the expression so SQL will work?
Thanks.
Any chance you are outputting text where your calculation is defined as a number?
1 Like
No, it's defined as a number, but the problem could be that the calculated field references a summary field like this:
GetSummary ( Total Amount) - time_billed_over_forty_hours_per_week
I don't think FileMaker can handle this calculated field via SQL.
I can export the calculated field and get the values (say, to Excel), but the problem there is that every field between date changes has the same calculated field value. The report only shows this calculated field in the sub-summary. So, exporting the data has many duplicated calculated field values...
Thus, I need to get a "DISTINCT" listing of dates and the calculated field before I could sum the calculated field to get the total. I don't see where FileMaker makes it easy (please, let me have missed this!) to get a distinct listing of maybe a date field and another field on a layout.
So, the easiest way forward is to just forget it and let the user add up the six or seven calculated values themselves.
Sigh..
cases like this I replicate the summary in SQL or minimize the SQL expression just to retrieve the relevant values and then apply some outer processing of the returned values from the SELECT QUERY kind of an embedded SELECT.
If you are in FM≥18 with WHILE loop function a lot is possible to process the result ..
1 Like
I can't retrieve any non-zero values using SQL. If I could, I'd be in great shape!
Thanks
Is it not possible to use NOT ... IS NULL predicate?
1 Like
I think the problem is that FileMaker hasn't calculated the values when I do the SQL so they're zeros. If I go to the layout, the values are there, no empty ones.
Thanks
sorry I wasn't clear // this is not what I meant. You can replicate the summary with SQL using its aggregate functions so you won't need to refer to the calc.
2 Likes
That's a good idea. I'll try that and report back. Thanks!
1 Like
It looks like the FMP SQL is too basic. I can get the basic SUMs, but trying to use a SUM in a "CASE...WHERE..." gives me a [08007][27034] [FileMaker][FileMaker JDBC] FQL0005/(0:1): Expressions involving aggregations are not supported." error.
I'll try exporting the data to MySQL. I'm hoping we start to get these data in CSV form so that would eliminate needing FileMaker for this client need.
Summary fields are based on context / found set, something that SQL pretty much ignores (perhaps with some exceptions).
Perhaps you can get closer to your goal by using GROUP BY...
Exactly. The Summary Field behavior is the issue. I've gotten most of the way there with SQL (and GROUP BY), but the FMP reporting tool is still better.
Resolution:
The problem I had with FileMaker is that it's SQL is too limited: it can't do calculations with summarized data like "SUM()". That's a huge limitation not shared by other database programs. Moving on...
So, I had three possible workarounds:
-
Do nothing. Tell the client to add up the numbers by himself. (kidding.)
-
Get the data I could from FileMaker using SQL & SUM() + GROUP BY and do the rest with another program.
-
Export all the data to MySQL and do the entire calculation in SQL.
I opted for #1 above since even though MySQL 8's SQL is totally mind blowing, I didn't want to spend hours doing data type conversions between the two databases for option 2. Plus, until we can get the invoice data in CSV, FileMaker is good for data entry and I can get some of the data I need from it at least.
So, all done now.
Thanks for all the great replies.
@OliverBarrett How many records do you have fetch before aggregating the data?
Perhaps you could run the execudeDataAPI script step to get what you need and parse the resulting JSON to build something you would export with a virtual list.
Looking at your list of workarounds, you seem to be already familiar with a 0 based index, so perhaps it could be an interesting avenue to explore.
All that said, if you are targetting a large number of records, processing that could take a fair bit of time. PSOS may alleviate that to some degree, but the transformation is going to show signs of fatigue as your record set scales up.
2 Likes
Cool.... Not that many records, really, but I'll probably end up in MySQL when/if FileMaker's GROUP BY bogs down. I've done GROUP BYs in MySQL with millions of records with sub-second response. Also, if you haven't looked at MySQL 8's "WINDOW FUNCTIONS" (like a GROUP BY without collapsing the data), this is a cool new addition to MySQL 8 (been in SQL Server and Oracle for a long time).
Thanks to this little FMP app, we've already discovered over $5,000 of over billing to this client!
Thanks again for all the insightful and thoughtful replies.
Sounds like you are already anticipating a break-down of performance of GROUP BY with FMP.
I'm not entirely clear if the means for running the SQL is the ExecuteSQL function, or if perhaps this endeavor is via an xDBC query into FMS. If it is ExecuteSQL, then I would say that you are smart to anticipate GROUP BY not scaling.
If I am going to use ExecuteSQL for this type of task, my own preference is to eschew the use of GROUP BY, and instead, use a sequence of SQL queries, each of which performs the aggregation that would have appeared on a single line of the GROUP BY output.
This, of course, means that my code requires some looping, and probably an initial query to determine parameters that will be used to define the queries within the loop, and so it loses the elegance of a single well-written SQL query. But I do it, because my experience is that that single elegant query just does not scale well with FMP's ExecuteSQL function. (I can't speak to xDBC behavior).
1 Like
Well, xDBC still relies on FMP for the basic query so if GROUP BY is going to break down in FMP (be too slow or never complete), then xDBC won't help. 
Right now, I'm just doing the GROUP BY query using xDBC and then I have all the results in a "ResultSet" that's very easy to process to get the final results I couldn't get easily in FMP.
It's really interesting to hear how you work around issues like this. FMP has always given me a way to work around whatever problem I run into. The GROUP BY performance is an exception to that rule that I hope FMI addresses at some point.
Thanks Steve!
If GROUP BY is the bottleneck here can't you circumvent this maybe by nested selects or other means? Refactor FM-functions in SQL questioning a FM database could be the wrong direction. A let statement with step by step building the foundset first and aggregating values second could be another way.
just throwing in 2 more cents 
2 Likes
No bottlenecks yet. I think it will be OK since there shouldn't be that many records.
Great ideas.
Thanks for your comments.
I finally got it all working in FileMaker. A bit complicated to do, but with a couple nested loops, it's working great. FileMaker is great for this.
However, I was totally surprised that once the client understood she would have to pay $900 per year to "host" the application (FMS minimum 1-5 user license) so she could access it in the browser, that was a deal breaker (again!).
I would have hosted the app for her, but, FMS' extremely restricted license now prohibits that, too.
So, we just put the data on MySQL with an HTML front end. Not as pretty as FileMaker, but totally free. One user or ten thousand. Still free.
Here is another case where I would pay to host apps on FMS to support clients, but that's "not allowed"....another lost sale for FMI. I don't get FMI with FMS.
My two cents: I think that for FMS to stop being a niche product (albeit an excellent one -- a truly beautiful product!), FMI needs to get with the real word and relax their license with a reasonable cost structure (I would happily pay $5,000 for unlimited connections, no subscription).
The bottom line in my case was this FMP development time spent was ultimately totally wasted, though it could be argued that it's my fault for not first checking the client's cost restrictions.
In any case .... so disappointing.