Too many calculation fields?
Hello. I am new to the forum but have been reading it for some time now. I have not used FileMaker in almost a decade, but I just inherited a database that needs 'improvement' so I am re-learning a lot of things while I brush away all the cobwebs... I have a pretty extensive Access background however. I am using FileMaker Pro 9 on XP. Anyway on to my question:
I have an Attendance table which stores attendance infraction records. The notable fields in this table (among others) are date and type. A scoring system has been implemented based off of the type. type values are text and the scoring is numeric. I am creating Calculation and Summary fields for display on a list-view layout, which is sorted by employee. The system I have set up to get the score is a Calculation field (Case function) to translate the type into its associated score, and then a Summary field to Total the Calculation field. The Summary field is displayed on the layout. This works fine to get the score for each employee across all dates.
My question is, I need to calculate the score for various date "blocks." e.g. block1 is the last 35 days, block2 is the 35 days prior to block1, block3 is the 35 days prior to block2, etc. I need to do this for about 14 blocks, which means 28 fields added to the table. Am I going about this wrong? With my Access background I am used to being able to create "unbound" calculations on layouts in order to maintain a clean tables. I know FileMaker doesn't work this way, and have created Calculation fields in this and other tables to suit my needs just fine -- so I am over my "clean table" mentality. I am just hesitant to create 14 calculation fields and 14 summary fields to accomplish essentially one thing. I have already created the fields in a copy of the db and I get the results I want, but it seems "messy"... Am I approaching this wrong?
Thank you for your input.