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

# Calculation 2 fields in 2 different table - How

### 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.

TQ

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

mayor:

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

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

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

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

mayor:

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

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

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

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.