Customizable Report Design question

[Kent Searight]

I'm trying to create a lengthy and complex report that combines research data from about 10 tables and places it into user-defined text templates stored in their own separate table. The report would look like the following (sans blue text):

[color:"blue"] template text Category 1.0

[color:"blue"] research data Two boxes and three crates show damage.

[color:"blue"] template text Category 2.0

[color:"blue"] research data One lamp was broken in box #44786

etc., etc., etc.

My very general question is this - what would be the best approach to assembling/generating the reports? Via each child table that corresponds to the research data found in a given section of the report OR create a new table (w/all globals, maybe?) to bring the data into a table dedicated to reporting? My main concern is finding the best way of combining the user-definable "canned" text with the research data. I'm finding pros and cons in either of my methods and I'm a little concerned about painting myself into a corner. I'm open to any suggestions.

Any input is greatly appreciated.

[transpower]

You may want to use summary fields on their own layout and print from that.

[Fenton]

I would think of importing the primary ID of each of the child tables' records into a dedicated ID field in the Reports table; and whatever foreign keys makes sense, so you can tie things together. In other words the reports file is a big flat join table.

The one question I'd have, which you didn't mention, is what kind of synchronization is needed; whether the data is going to open to later edits; hopefully not.

The Reports table would have its own Table Occurrence Group, with Reports in the center, and a concentric web of connections to new Table Occurrences (TO) of the other tables.

Almost all data showing in the reports would be related data, with Subsummaries, as Transpower said, based on the IDs.

There are basically 2 ways to reconcile this with "user-editable templates." The first, easiest for you, is to allow Layout access, and teach them how to use Merge fields (all related; but pretty straight-forward, as only a few TO's would be in their "related" list). But they will be seeing all the fields in the parent tables.

The alternative is to import whatever fields you need into the reports table; redundant data, but a lot more user-friendly. You are beginning to bloat the file, so hopefully these "user-reports" are important.

[The following is not possible in Subsummary parts, only parts visible in Browse mode. So it may not work for what you want. It's more for custom letters. Developer-controlled, user-configurable reports have limits in FileMaker. Unless they can properly set up a Subsummary, including the script to sort it, they aren't going to get very far.]

The 2nd way would be to create a (massive) Substitute, in a calculation field, to swap "custom markers" with the real related fields. That requires you to assemble a list of every possible field they'd want and make markers available for them. You can make them look like merge fields, ie., <<First_Name>>, etc..

You might want a little table to handle these "name-value" pairs. And a mechanism, on the user layout, to insert them at the insertion point. This has the advantage of presenting a limited list of the fields they want. It has the disadvantage that the result is a calculation field, which may or may not pass every little bit of formatting (though 7 does well).

[Kent Searight]

Thanks to both transpower and Fenton!

I'm still mulling over your inputs, and pros and cons associated with them. I'll be sure to post my choice and the outcome when I get there. Thanks again!