Report Generation Difficulties
I am trying to generate a report that pulls in data from multuiple records from multiple tables. The following links can provide some background if my following explanation is not clear enough. The following explanation is a little long, and it mainly provides the motivation for why I am using the approach that I am using, so you can skip to the last paragraph and look at the explanation if you think it might be helpful.
Essentially, what I was doing was using a list to bring in data from one of the tables and then I was bringing in the other tables' records through portals. My first issue was that people that I was not able to generate a report for people who did not have any records in the table I was using for the list, so I created another layout to handle those people. I also wanted to not display portals for people who did not have anything to display, but allowing portals to slide up was causing pagebreak issues, so I created more layouts to display different combinations of portals depending on what was necessary. Ultimately, I had 8 layouts and a script which used a bunch of if statements to determine which layout to use for each person. Unfortunately, I am now being told to add another portal, and also that there may be more tables that I need to pull in records from in the future. As a result, i think I need to pursue a new strategy, as my current approach will not scale well as I add more portals.
Now I am going to use one of PhilModJunk's suggestions in one of the linked posts. Essentially, I am planning to create a new table specifically for this report that will pull in all of the records for the report and then I will use conditional formatting and calculation fields to create a list report that can handle each type of record.
I am planning on creating completely separate fields for the records pulled from each table, named along the following lines
I realize that some of these fields may be redundant, but I want to keep it organized this way so that things don't get confusing as I start pulling things in from more tables (it might become difficult to keep track of which fields are shared by what).
My main issue now is dealing with the import records step. I would prefer to only pull in the records which pertain to the employee that I am trying to generate the report for. As far as I can tell, when I use the Import Records script step, there is no way to import a subset of the records from a filemaker table. I suppose that I could import all of the records and then filter out the ones that I don't want, but there are many records, and I would be pulling in about 100 times the number of records that I actually need, which seems inefficient. The other approach that I can think of is looping through the records and using set field steps to copy over the desired fields. I don't have much experience with this, and so I feel that there are probably issues with both methods that I am not considering. I would greatly appreciate any advice from a more experienced person about how to approach this problem.
I am trying to import a subset of records from other tables in my file and would like advice on the best way to accomplish this efficiently.