Working with a SQL database without altering the tables

Discussion created by pcuezze on Feb 16, 2017
Latest reply on Feb 16, 2017 by bertrand

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.