Little help please?
Is it possible to calculate the difference between two summay fields in two tables?
Are you working with grand totals--the total of all items out or sub totals--the totals of all items out for a particular user-product name group of records?
Yes. You must have a relationship between the two tables. The difference will be based on the table occurance (relationship). You may need a relationship using the cartesian (X),which will include all records. Then you would have a calculation field summaryfield1-summaryfield2.
Please note that the relationship you define will control the value returned by a summary field defined in a related table. In that context, the summary field's value will be computed from the set of related records rather than a found set or sorted group of records like it would in other contexts.
Thank you both for your reply. I do have a relationship between the two tables based on "UserID". But the numbers do not match.
The calculation is going to be based on this relationship. The only records counted will be the items that match your relationship. If you want all records counted you will need a relationship with the (X) cartesian symbol. When you display the calculation field you will have to use the table occurance that your relationship is based on. tableoccurrance::calculationfield. Your records will be filter by your relationship and your total will be based on this relationship.
I'm sorry I'm very new to filemaker and don't know exactly how to do that. I'll explain my setup and maybe you can help me further. I have a transaction table where I input Items in and Items out. In this table I have a summay field that calculates the total items out. I created a summary report that displays totals grouped by UserID then by ProductName. I have another table where I import all completed work orders to. I have a similar summay report for this table that isplays totals grouped by UserID then by ProductName. So I have my two totals. Total issue, total consuemed. However they are two seperate tables and reports. I'm having a very hard time linking the two. Thanks you.
I am working with sub totals.
Then your relationship needs to match records by the same criteria that you are using in your original summary report that groups your data by user and product.
Transactions::ProductID = WorkOrders::ProductID AND
Transactions::UserID = WorkOrders::UserID
Then a calculation field defined in work orders that refers to your summary field in Transactions will access the desired sub total.
Note that I am guession on field and table names here and assuming that you have the needed fields in WorkOrders to make this relationship work.
Retrieving data ...