Your question makes me wonder if you have your structure set up correctly.
How many tables do you have?
Does each table have 100 different fields or are some of the fields the same across the tables?
Even if the questions are different are the answer types all different with different quantities of date, time, number, text and container fields?
Are some of the fields the same based on who completes the form like name, address and phone numbers?
All of these questions will help determine what is the best way for you to create your layouts.
I'll start with the result in mind. The output of this report would be multiple pages that has the specifications for different parts of a building project, mainly about the design functions.
One page would be interior specifications, one would be electrical fixtures, styles. one would be kitchen, and so forth.
My thought was that each page could be one identical table with a total of 25 lines. One of the fields would be the merge field on the report that would contain descriptions such as Interior Door, Door Hardware, so forth.
The next column would be a sub- description such as "Finish" or "Textured or Smooth". The report output to the right of these would have the description such as "Brushed Nickel", "Pocket Door", etc. So in the case of the merge fields, they would constitute the template to be filled out and the editable fields for the user would allow the type and descriptions.
So with this design, which might be clunky, there's a layout for creating the template which has the 100 fields. There's a layout for using as the worksheet that could be pretty enough for the client to see filled out, then there's the report which will printout whatever is filled in on the worksheet.
On thinking more about this, this one table could work as all the pages vs. a table for each page, however, I'm not sure if this would be effective unless each client had their own database file, which I suppose is possible.
If I just name each record one of the page numbers, I guess each page could still be tied to the customer record. I suppose there could be a master copy that serves as the template and then when a new client is created, a script pulls the master copy pages and creates all the pages for the new client?
For the most part, once the first 30 page form is designed, the merge fields won't change that often but the description fields would differ from client to client.
Hopefully, I've described it clearly enough. I think by trying to write this out, I may try the single table and multi-page version. If you have any other input, I would appreciate it.
I think that you will find the FileMaker Training Series: Basics very helpful to review before you proceed to much more, particularly Lessons 1-6 & 8, Lesson 5 is the lesson on designing a solution. It is a free download at http://info2.filemaker.com/FileMaker_FTS14_Basics.html.
You probably still need to define your solution better even to yourself. The training series uses a one-sentence problem statement to help you define your goals. Your goals will help you design your solution and what you have for goals can change the structure of the solution greatly. You will have multiple goals.
You could have customers who have projects who need to complete the questions for the project. But you could also have customers who have projects which are made up of various areas (rooms & outdoor spaces). Each area has its own set of questions that vary based on the type of area.
You more than likely don't need separate database files for each client. Each client will end up with a separate record for each project and each project may have separate related areas.
Thanks for the help. I agree this was probably backed into rather than designed and then implemented - it changed as the design came along to instead of a fixed form with fields, being able to rename the fields (for customer view) via a template layout.
I think I over-thought how I was going to keep the function of one field acting as a field title in another layout across many different pages.
As far as each client ending up with a separate record, the problem I was really trying to solve was how to make so many pages for each client without making each page a bunch of different fields, even if they have the same function - the title of each field being changeable being the design obstacle.
I think you pointed me in the right direction. Each record, one page, each different page will assemble as part of the report by scripting and sorting by page (record) number, with only those related records to the client/customer ID being the pages included in the report.
Sorry for the confusion. This is one of those where it's easier to point and show than to describe.
Remember that you can still use multiple Layouts to show different parts of the same record and that your report layout doesn't have to be and probably shouldn't be the same layout as your data entry layout.