stan111 Posted May 12, 2020 Share Posted May 12, 2020 Gents, when I evaluate ExecuteSQL in DataViewer - it is working, but when I attach this calculation to field - it doesn’t! I receive a “?” instead. Where am I wrong? Link to comment Share on other sites More sharing options...
comment Posted May 12, 2020 Share Posted May 12, 2020 Instead of asking for guesses, why don't you post a demo file showing the problem (and preferably only the problem). Link to comment Share on other sites More sharing options...
stan111 Posted May 12, 2020 Author Share Posted May 12, 2020 I’m on iphone right now. Would you be happy with the screenshot? Link to comment Share on other sites More sharing options...
comment Posted May 12, 2020 Share Posted May 12, 2020 I am in no hurry... The screenshot confirms what you told us - that your SQL expression works in the Data Viewer. It provides no clue to the reason why it fails in a calculation field. Unrelated to your question, but I would be reluctant to use ExecuteSQL in a calculation field, mainly (but not only) for performance reasons. Link to comment Share on other sites More sharing options...
stan111 Posted May 12, 2020 Author Share Posted May 12, 2020 well, would you please offer an alternative to this formula? Link to comment Share on other sites More sharing options...
comment Posted May 12, 2020 Share Posted May 12, 2020 Not without knowing the context and purpose. Link to comment Share on other sites More sharing options...
stan111 Posted May 12, 2020 Author Share Posted May 12, 2020 I need to count all the apartments with status “available”. Link to comment Share on other sites More sharing options...
bcooney Posted May 13, 2020 Share Posted May 13, 2020 Do a find. Link to comment Share on other sites More sharing options...
stan111 Posted May 13, 2020 Author Share Posted May 13, 2020 I need to construct a report, I guess “find” is not an option Link to comment Share on other sites More sharing options...
Wim Decorte Posted May 13, 2020 Share Posted May 13, 2020 The "?" always means a syntax error. And given that you can use the same syntax in the data viewer, it probably means a context error. I'll reinforce what @comment said earlier: I would not use this kind of executeSQL in a field. If you want to construct a report, run a script that does all your summarizing (and ideally pre-aggregate where you can based on time periods etc). There are many techniques to make FM show the exact error, do a google for "filemaker sql debug" and you should find a few good pointers. Link to comment Share on other sites More sharing options...
stan111 Posted May 13, 2020 Author Share Posted May 13, 2020 Thanks, Wim. I'll follow your advice. Link to comment Share on other sites More sharing options...
stan111 Posted May 13, 2020 Author Share Posted May 13, 2020 Wim, I'd like to create a script and set the global variable $$countUnits. The question is: what function should I use to calculate this variable? Sum (Units::RentalUnitsStatus0or1) will count only one record. Link to comment Share on other sites More sharing options...
comment Posted May 13, 2020 Share Posted May 13, 2020 5 minutes ago, stan111 said: Sum (Units::RentalUnitsStatus0or1) will count only one record. That depends on the context (which I asked about earlier, but did not receive an answer): if you are in the context of Units, then it will sum only the current record. From the context of a related table, it will sum all related records. I think it's time to stop playing 20 questions. If you want to get useful answers, then explain in detail what do you have in terms of structure (tables, fields and relationships) and what kind of report you're trying to produce. These laconic one-liners are not helping you to get help. 1 Link to comment Share on other sites More sharing options...
stan111 Posted May 13, 2020 Author Share Posted May 13, 2020 Attached is my app. I'd like to make a report with: Total units = 3 Occupied = 2 Free = 1 Thanks for your patience. units.fmp12 Link to comment Share on other sites More sharing options...
comment Posted May 13, 2020 Share Posted May 13, 2020 I would suggest you look at it this way: units+report.fmp12 1 Link to comment Share on other sites More sharing options...
stan111 Posted May 13, 2020 Author Share Posted May 13, 2020 Thanks! I should've done it before! Link to comment Share on other sites More sharing options...
Recommended Posts