Jump to content

Recommended Posts

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites
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.

 

  • Thanks 1
Link to post
Share on other sites

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.