I have a FM13 DB with a table "machines" and its related table "consumption" connected with the IDs. My layout is showing the machines data and a portal with the related consumption entries. Now I want to summarize the fields "amount" of all related entries where "fuelType" is "diesel" and the "year" is "2015" into one calculted field within the machines table.
Can anyone give me a clue how to do that?
thx dan
In addition to the ways Michael suggested, FM13 introduced the ExecuteSQL command, which can used in a calculated field. The calculation would look something like this:
ExecuteSQL (
"SELECT SUM(amount)" & ¶ &
"FROM consumption" & ¶ &
"WHERE FuelType = 'diesel' AND \"Year\" = 2015" ;
"" ; ""
)