Forms database planning advice?
I'm hoping that someone might be kind enough to be give me some advice.
I am creating a new Filemaker database to replace an XLS spreadsheet that our field consultants use to evaluate our stores - we call it the consultation form. The FM database will be used in the field on iPads with FM Go.
- Sheets: The form has three main sheets in the XLS.
- Sections: On each sheet there are five to ten sections.
- Questions: Within each section there are several scored questions. (Scores are selected from one of three values, but those values are subject to change with each question. Example: question one might have scores of 0, 10, 20 and question two might have scores of 0, 25, 50.)
- Items: Each scored question can have many detail items that are checked or unchecked regarding compliance.
In total, between scored questions and detail items, there are about two hundred potential fields.
The requirements are that the database would need to be able to be filled out in FM Go and the resulting evaluation exported as a PDF to be emailed to the store owner, preferably while the consultant is still on site.
My question is this. Would it be better to just create a field for each item OR would it make sense to create a table for each part above and then relate the tables to each other?
My thought is that creating related tables would avoid having hundreds of fields AND would allow for flexibility in adding/removing Items in the future (the content of this consultation form tends to be subject to a fair amount of change thru the year). However, I'm not entirely sure how I would relate the various tables successfully and the 'flat fields' approach would be simpler to create.
I'm at a bit of a crossroads and am looking for advice on the best way to proceed.
Any advice and thoughts about this planning decision would be appreciated. Thanks for your time.