Help with If.... Then Calculation?

[Asuncion LB]

Hi, I am new to FM.... I need help with a simple calculation

I have two fields one is called Account Number (will have accounts like 710004, 711000, 711002, etc.) and another field called Total (this field will hold how much an item is say a computer for $700). I have created this in a portal and I am able to sum up the Total field. I would like to know what is the calculation to get: If Account Number is 710004 then add all Total Fields that have Account Number as 710004.

Does this make sense? I am just trying to find a fast way where I don't have to add up all the fields that are say 710004 manually.

Thank you soo much for any help :)

[comment]

There are two easy ways to do this in Filemaker:

1. Produce a report sub-summarized by account number. Use a summary field to total the Total field.

2. Add a table of Accounts and define a relationship with your existing table (Items?), matching on account number. In the Accounts table, add a calculation field =

Sum ( Items::Total )

[Asuncion LB]

Hi,

Thanks for responding.... Sorry I am really new with Filemaker.

I have all my records in a portal. I am almost there but it is only adding one portal row.

I currently have a field called FY 710000_710004_2Spent_Calculation with calculation If ( ${710000 Equipment Major}::Account No. = "710004" ; ${710000 Equipment Major}::Budget )

Then a summary field called FY 710000_710004_3Spent_Summary with Total of selecting the field above....

Right now it's able to find only one 710004 in my portal, but I have three 710004.... How do I make it so it finds all 710004 and add the total?

Thanks..

[comment]

I am almost there

I am afraid you are nowhere near "there". Filemaker doesn't have a SumIf() function. If you are willing to hardcode the account number into the calculation (something I would not recommend), you could create a calculation field such as =

If ( Account No. = "710004" ; Budget )

then sum up this field. But you would need to repeat this for every account number - or select one account to view at a time.

I have all my records in a portal.

A portal from where? What is the relationship used by the portal?

[Asuncion LB]

Hi, Attached is how the database looks. I tried uploading the database but it won't let me. I am utterly confused....

fmhelp.bmp

[comment]

The picture doen't tell me much about what you're trying to accomplish. You can attach your file, if you zip it first - but I am not sure that will answer the question.

One thing, though, is clear: you are mixing data and structure. No table and no field should be named after a specific account; the account numbers are data - and data goes inside fields, not into the data structure.

I suggest you try my second suggestion above.