6 Replies Latest reply on Oct 12, 2012 9:10 AM by TurtleKoala

    Report Generation Difficulties

    TurtleKoala

      Title

      Report Generation Difficulties

      Post


           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.

      Collating Layouts

      Using multiple layouts to create a single dynamic layout

            

           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

           Projects_Name

           Projects_Description

           Feedback_Name

           Feedback_Score

           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.

            

           tl;dr

           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.

        • 1. Re: Report Generation Difficulties
          Sorbsbuster

          "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"

               You can go to the source table and find the set of records you want to import, then go to the destination table and import to there.  That will only import the subset of records in the found set.

          • 2. Re: Report Generation Difficulties
            TurtleKoala

                 Is this by using the Import Records script step and selecting my current file as the file to import from? This seems like strange behavior. Does this mean that if I ever want to import all of the records from another database, I fisrt have to open it to make sure that the current records being browsed are not only a subset of all of the records?

            • 3. Re: Report Generation Difficulties
              Sorbsbuster

                   It doesn't seem strange to me, but then I've been using it for years.  In the script you would go to the table that you want as the data source, and find the appropriate found set.  The return to a layout based on the table that you want to import into.  Then use the Import Records script step to specify that source that you just set up.

                   MaybeI have mis-understood.  Which aspect of the process seems strange?  You are selecting a source and specific records in it, then selecting a destination (and even specific reecords to update, if that is what you want).  I can't see how you would have tight control in any less steps.  It is like an SQL, only you are doing the find first, not as a bundled part of the query.

                   If you import from a closed database I believe it will automatically select all records in the source table.

              • 4. Re: Report Generation Difficulties
                TurtleKoala

                     Thanks, it seems to be working.

                     The part that seemed strange was that the Import Records script step seems to be primarily designed to import records from other files, and if you were importing records from another file, it would be more difficult to constrain the set of records that you were importing. It does seem logical that it would work this way for importing records within the same file.

                • 5. Re: Report Generation Difficulties
                  philmodjunk

                       Here's the rule for Import Records and found sets:

                       If the file is open when Import Records executes--obviously, this will be the case 100% of the time for imports from within the same file, Import Records imports the found set.

                       If the file is not open when the step executes, it imports all the records in the table.

                  • 6. Re: Report Generation Difficulties
                    TurtleKoala

                         Thanks, that helps to clear up my confusion.