I was an ace on FMP 1 thru 5 - way back, so-so on FMP7 and now I find myself a beginner on FMP13 Adv. This is my first shot at the forum.
I am trying to build a database, which is a checklist with 84 parameters. The 84 parameters are divided into 7 categories. The target is to replace a MS Excel spreadsheet operation where values are manually copied/pasted. I want to be able to expand the number of parameters as well as the number of categories.
The check is performed on weekdays, setting values 1 or 0 for each of the 84 parameters.
On some days, the check is not performed.
The values are summed, per the 7 categories/day.
The daily sums are then summed per week and the average is displayed. It looks like this.
My approach: I have opted for a simple data model with just a few fields, with one record for each of the 84 parameters:
I have a prep table. For each day that the check is performed the 84 records are imported to a second table, which also includes a date field, set to Today.
In the second table I have a Value field set to either 1 or 0 by pushing buttons.
In the second table I also have 7 unstored calculation fields that presents the values for the 7 categories. (Case Cat_No = 1;Value), (Case Cat_No = 2; Value), etc.
A third table has one record per day, with a Date field and 7 calc fields; Sum_Cat_1, Sum_Cat_2, etc. The date field lets me calculate Year, Week_Of_Year and Day_Of_Week.
How can I present the data in a layout like in the spreadsheet?