Sum of selected records of a child table
I have a money control database, with a movement table (with 1 record per transaction) and another tables that groups each transaction.
I have a table called "items", with a calculated field called "Quantity in stock". This database is small, and I wish to sum all the "quantity" field in movement table to get the total in stock.
In other words, I need to "translate" the SQL statement bellow to File Maker understands it:
from movement a, items b
where b.id_items = a.id_items -- primary key and foreign key
and a.transaction_type in ("sell","buy","borrow")