executeSQL returns "?"

[stan111]

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?

[comment]

Instead of asking for guesses, why don't you post a demo file showing the problem (and preferably only the problem).

 

[stan111]

I’m on iphone right now. Would you be happy with the screenshot?

[comment]

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.

 

[stan111]

well, would you please offer an alternative to this formula?

[comment]

Not without knowing the context and purpose.

 

[stan111]

I need to count all the apartments with status “available”. 

[bcooney]

Do a find. 

[stan111]

I need to construct a report, I guess “find” is not an option 

[Wim Decorte]

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.

[stan111]

Thanks, Wim. I'll follow your advice.

[stan111]

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.

[comment]

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.

 

[stan111]

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

[comment]

I would suggest you look at it this way:

 

units+report.fmp12

[stan111]

Thanks! I should've done it before!