8 Replies Latest reply on Jan 5, 2009 10:11 AM by TSGal

    Calculation 2 fields in 2 different table - How

    mayor

      Title

      Calculation 2 fields in 2 different table - How

      Post

      I got 2 table, vot & expenses.

       

      vot (vot_no, current_amount, vot_balance)

      expenses (invoice_amount, invoice_date)

       

      The formula is :

      vot_balance = current_amount - invoice_amount

       

      When I make the calculation, question mark (?) is displayed, not the amount expected.

      Please, guide me.

       

      TQ

       

        • 1. Re: Calculation 2 fields in 2 different table - How
          TSGal

          mayor:

           

          Thank you for your post.

           

          As long as the two tables are linked by a key field(s), this should work properly.  If there is no link, then you will receive a question mark.

           

          On your layout, for testing purposes, put the current_amount and invoice_amount fields so you can see the values.  If no value is showing up in the invoice_amount field, then the current "vot" record has no related "expenses" record.

           

          TSGal

          FileMaker, Inc. 

            

          • 2. Re: Calculation 2 fields in 2 different table - How
            mayor
              

            I have try to calculate 2 fields in different tables.

             

            vot (vot_no, current_amount, vot_balance)

            expenses (invoice_amount, invoice_date)

             

            vot_balance = current_amount - invoice_amount

             

            But the answer is show the current value of invoice_amount field.

             

            It means that these 2 tables has no related.

             

            Question : How to ensure that these 2 tables are related? Because I got 1 current table, 1 related table and no unrelated table. Is it means my tables already related or what?

             

            vot(vot_no, year, current_amount, vot_balance.....

            expenses(vot_no, year, invoice_amount, invoice_date...

             

            I use vot_no and year as key fields to make relationship. Is it ok?

             

            TQ

             

             

            • 3. Re: Calculation 2 fields in 2 different table - How
              TSGal

              mayor:

               

              Yes, it is okay to have two fields as the "key" field.  Just make sure in your relationships graph that you have both fields attached.

               

              Is this a one-to-one relationship or one-to-many?  If one-to-one, then your calculation is much easier.  That is,

               

              vot::current_amount - expenses::invoice_amount

               

              If the relationship is one-to-many, then you can use the Sum() function with the related table.  That is,

               

              vot::current_amount - Sum (expenses::Invoice_amount)

               

              Let me know what results you receive.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: Calculation 2 fields in 2 different table - How
                mayor
                  

                I have tried your formula, but the answer show the amout of current_value. It not deducts the total_invoice_amount.
                 
                Ok, I explain what is my case actually,
                 
                I create 1 layout for vot & 1 layout for expenses that consist of fields as above.
                 
                vot
                vot_no
                current_amount
                vot_balance
                 
                expenses
                invoice_amount
                invoice_date
                total_invoice_amount

                vot_balance = current_amount - total_invoice_amount

                My relationship is 1 to many and I set the formula  for vot_balance like this,
                 
                vot::current_amount - Sum(expenses::total_invoice_amount)
                 
                For your info, the total_invoice_amount is correctly calculated and display in expenses layout but didn't appear in vot layout. Do we can calculate the value that’s not appear in layout?

                 

                The problems are :
                a) the value of total_invoice_amount is not appear in vot layout
                b) the answer of this calculation show the amount of current_value (no deduction)

                 

                TQ

                 








                • 5. Re: Calculation 2 fields in 2 different table - How
                  TSGal

                  mayor:

                   

                  Thank you for your explanation.

                   

                  My biggest concern is that your "total_invoice_amount is correctly calculated and display in expenses layout but didn't appear in vot layout."  This probably means that you don't have the relationship set up properly.

                   

                  Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab and make sure the two tables are connected via a key field.  I see "vot_no" in the vot table, but I don't see a corresponding key field in the expenses table.

                   

                  Once this relationship is set up properly, make a portal in your vot table into your expenses table.  Be sure to include the "total_invoice_amount" field along with some others.  This may give you a clue to why the calculation is not working.

                   

                  If the information is showing, and the calculation is still not displaying the information, try creating a new calculation field in the tot table with the formula:

                   

                  Sum (expenses::total_invoice_amount)

                   

                  There should be a value, assuming there are records in the portal being displayed.

                   

                  Please continue to keep me updated.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Calculation 2 fields in 2 different table - How
                    mayor
                      

                    Actually expenses table also include vot_no anda year like below;

                     

                    vot
                    vot_no

                    year
                    current_amount
                    vot_balance
                     
                    expenses
                    vot_no

                    year

                    invoice_amount
                    invoice_date
                    total_invoice_amount

                     

                    I have linked vot_no in vot table to vot_no in expences table, as well as year field. I think my

                    relationship is right but the total_invoice_amount is still didn't appear in vot layout. I did what

                    you ask me to do but still cannot solve the problem. I have no idea now and don't know what else to do.

                    If you have another alternative, please let me know how to settle this.

                     

                    TQ

                     

                     


                    • 7. Re: Calculation 2 fields in 2 different table - How
                      mayor
                        

                      I have try to create a new calculation field in the vot table with the formula you give:

                       

                      Sum (expenses::total_invoice_amount)

                       

                      But, the result is nothing, (no zero, no questionmark), didn't show anything.

                       

                      I have no idea, please guide me.

                       

                      TQ

                       

                      • 8. Re: Calculation 2 fields in 2 different table - How
                        TSGal

                        mayor:

                         

                        Sorry for the late reply.  I just returned from a long absence.

                         

                        In the portal, are there values for Invoice amount?  If so, then the Sum should work properly.  If not, then you won't see anything.  Make sure there is a value for that record.  If not, go to another record and make sure there are records in the portal.  Then, you will see values for the Sum calculation.

                         

                        TSGal

                        FileMaker, Inc.