Help with calculations on a Charity database

[meglome]

I'm helping out with a database in Filemaker 8.5

So I have a list of Charities that apply for funding from a preset list. At the moment I have two tables. The first one is the details of the charities, the second is the list of funding they can apply for and the amount of cash that goes with each one. The funds available on each option are fixed.

The charities can apply for 10 out of a list of 43 possible grants. So I have dropdown on the charities table (with 10 repeats) which you can select from the full list of the funds. What I want to do is list the funds they applied for then have a selection for the funds they are actually given. I then like to add the amount of the funds they applied for and add the ones they were given.

The only way I know to do this is to do a lot of long If statements. So I calculate (If Field1=Fund1 then Field1Amount, If Field1=Fund2 etc…)

Is there a better way?

[comment]

I believe you should have three basic tables: Charities, Grants and Applications - where Applications is a join table between the other two.

To sum the approved applications, you will need some more relationships. This is difficult to explain but easy to show - see the attached.

Grants.fp7.zip

[meglome]

Excellent thanks. I'll take a look later and let you know how I get on.

[meglome]

This is difficult to explain but easy to show - see the attached.

I can really see that. Can anyone direct me to some good reference books or online info that can help me. Thanks again for the file.