I need a layout to act as a spread sheet for data that would be imported into a form. if that doesn't make sense let me try to break it down. at my work we have these incident reports we have to fill out when there is an injury to a customer. every week those reports get tabulated and put into a report. what i would like to do is have the incident report in file maker along with a spread sheet that would keep a running total of what injuries are seen. so let's say that we have 3 wrist injuries, 2 upper leg injuries, and a neck injury; each injury would get its own report but then there would be a main page that would tabulate how many injuries we saw that week. i hope this all makes sense. any help would be greatly appreciated.

Yes, you can do this in FileMaker. But a database is somewhat different than a spreadsheet. While both store the data in "rows and columns", which in FileMaker would be "records and fields", databases have another dimension, which is tables. A well-structured spreadsheet would use linked worksheets to simulate such a relational hierarchy. But a spreadsheet relies heavily on "location," ie., where the data is on the sheet, whereas FileMaker is less rigid. You have layouts, which belong to a particular table (actually table occurrence), but on a layout there is no fixed location arrangement for the fields; indeed a field may not even be on the (or any) layout, but still function.

When one is building a FileMaker database one has to carefully consider the relative relationships of the data. Then one has to think how the data can be shown on the layout so that data entry is easiest. You cannot sacrifice one for the other.

For example, the injuries in yours. Obviously an Incident is an "entity" (thing) which requires its own table. It happens to a particular person at a particular time. There will be many things which belong to an Incident; some of them are "attributes" of an Incident, such as the person and the date-time. But others may be enitities themselves, and require their own tables, linked to Incidents.

Injuries for example. The big question: Is there more than 1 injury per Incident? If so then you will need another table for IncidentInjuries.* It would have 1 entry for each injury belonging to an Incident, tied to the Incident with an IncidentID. You would enter the data into a portal, usually with "Allow creation of related records" checked in the Relationship box (in the middle of the line in the Relationship Graph).

You might be tempted to enter the multiple injuries into a single checkbox field in Incidents, as it's easy. But then you'd have real problems trying to get your Injuries reports. The trick is therefore to use correct relational structure, but make data entry easy. This is often accomplished using "filtering" of value lists, so that an intelligently filtered list of possibilities appears. Also FileMaker 8's auto-completion (quickfill) can help with some fields, as well as its date-picker calendar. There's lots of little tricks.

So, that's the theory. But it depends on what you've got. I'm off, catching a plane, but I check back later. Tell us more details, especially about how many injuries, their details, etc..

* I say IncidentInjuries because you may want a standard Injuries table, which lists all possible injuries, with other fields saying something about each. This would be used as a value list table. Or possibly this would just be in a globally available table with all value lists (if there's nothing else to say about a standard injury).

Hey Fenton

Great answer. May I copy it to use on my users?

John