I have established a live connection to our SQL database and am able to work with the tables in FM. My ultimate goal is to use FM to create reports, dashboards, etc. I would like the connection to remain live (i.e., I don't want to download and sync tables if I don't have to).
My problem is that every calculation I have tried to do has become unnecessarily complicated because I want to avoid creating additional fields in the SQL table. Here's an example: I have two date fields that I want to calculate the difference between. I was able to use a <<$calculated>> field in a single record layout to obtain what I wanted. But when I try to create a list report that averages the <<$calculated>>field (by using another calculated field), I am unable to use the average function on a calculation.
This would be so easy if it was locally stored and I could create summary fields, global fields, etc.
So I guess my question as a complete newbie, is what is my best solution for manipulating the SQL data with calculations, summaries, etc? Is it possible with a live connection and NOT adding fields to the SQL tables? Am I better off just duplicating the SQL data and syncing it periodically? The SQL database is "missing critical" for our business so I really don't want to risk destroying its functionality...Any other ideas would be very helpful. Thanks in advance.