8 Replies Latest reply on Nov 7, 2012 10:13 AM by philmodjunk

    Little help please?

    FrancisPena

      Title

      Little help please?

      Post

           Is it possible to calculate the difference between two summay fields in two tables?

        • 1. Re: Little help please?
          schamblee

               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.

          • 2. Re: Little help please?
            philmodjunk

                 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.

            • 3. Re: Little help please?
              FrancisPena

                   Thank you both for your reply. I do have a relationship between the two tables based on "UserID". But the numbers do not match.

              • 4. Re: Little help please?
                schamblee

                     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.

                • 5. Re: Little help please?
                  FrancisPena

                       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.

                  • 6. Re: Little help please?
                    philmodjunk

                         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?

                    • 7. Re: Little help please?
                      FrancisPena

                           I am working with sub totals.

                      • 8. Re: Little help please?
                        philmodjunk

                             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.