Fields that contain data unique to each year and retained for future use

[wfcperrine12]

I'm pretty new to the FMP community so I apologize if this is not the right place to post this question. I am in the school fundraising business and I work with about 100 schools that do annual fundraisers (magazine, chocolate, gift, etc). Although my parent company does all the customer invoicing- I still wish to track year to year sales for each school.

My goal in FMP is to view each customer record in a layout that shows:

1.) the info that rarely changes (school name, phone, etc) and

2.) information that is unique to the current school year (which includes comparison to some of the previous years data).

I have over 105 fields that are unique to each school year. These fields then repeat for the new year. I wish to preserve the data for each past year so that it become retrievable for calculations.

My question is how do I do this - or how would you approach this with scripts or layouts. Here is what I have so far (see pic) but I don't think I'm on the right track with this. Any suggestions?

http://www.flickr.co...425/7830386606/

[eos]

How many tables does your solution include?

[comment]

1.) the info that rarely changes (school name, phone, etc) and

2.) information that is unique to the current school year (which includes comparison to some of the previous years data).

You should have 2 tables (at least) - Schools and Sales. In the Sales table, there will be a record for each unique school/year combination.

This is subject to the note below.

I have over 105 fields that are unique to each school year.

That seems rather excessive. I don't see this in your screenshot, but it sounds like you have many "like" fields that should properly be records in (yet) another table.