9 Replies Latest reply on Mar 19, 2013 11:38 AM by Mike_Mitchell

    Print one long report from multiple tables

    Sukisu

      Hi, I'm trying to print a report that contains multiple information from multiple tables. For example, I have a list of sectors, and for each sector, I want to be able to print the team members allocated to that sector, and below that i want to print various lists, such as client contacts, events and marketing. Each one of these are currently in a different table, and shown on the screen within portals.

       

      An example of what I want to see:

       

      Sector

      Team

      Team Member 1

      Team Member 2

      Team Membert n

      Areas of opportunity

      Area of opportunity 1 (could be multiple lines)

      Area of opportunity 2

      Area of opportunity n

      Strategic Vision

      Strategic Vision 1 (could be multiple lines)

      Strategic Vision 2

      Strategic Vision n

      Client & Contacts

      Client Contact Lead Action Outcome 1 (could be multiple lines)

      Client Contact Lead Action Outcome 2

      Client Contact Lead Action Outcome n

      Events and Marketing

      Month Targeted events Seminars & Conferences Marketing Materials 1 (could be multiple lines)

      etc

       

      I hope I've explained myself clearly, I'd really appreciate any help you can give.

        • 1. Re: Print one long report from multiple tables
          StephenWonfor

          Sukiso

           

          Sounds like a job for Virtual Lists - http://www.mightydata.com/blog/taming-the-virtual-list-part-i/

           

          ...and thanks again to Bruce Robertson for the technique...

           

          Stephen

           

          "Did you ever notice that the first piece of luggage on the carousel never belongs to anyone?" --- Erma Bombeck

          • 2. Re: Print one long report from multiple tables
            Mike_Mitchell

            Stephen -

             

            I'm curious. I'm reasonably familiar with using the Virtual List technique for crosstabs and typical list view reports, but ... can you elaborate a bit on how you would use it for a case like this, where you are merging data from multiple different tables with a variable number of rows into a single list? Thanks.

             

            Mike

            • 3. Re: Print one long report from multiple tables
              StephenWonfor

              Mike

               

              I must admit it was an idea that occured to me, without testing or validation, as I use variables in arrays and GetValue() and ValueCount() so much that it seemed like a logical fit.

              I imagine I'd jump in and find I need a ringmaster of some sort to tame the variables but it sure "seems" feasible.

               

              I realize now this creates an obligation of sorts to prove it.  I'll be "right back".

               

              Stephen

               

              "A successful man is one who makes more money than his wife can spend. A successful woman is one who can find such a man." --- Lana Turner

              • 4. Re: Print one long report from multiple tables
                Sukisu

                Hi Stephen and Mike,

                 

                Thank you so much for your responses.

                 

                I've had a quick skim through your link to Virtual Lists, and it kind of makes a bit of sense, but I'm afraid that is beyond my understanding at present.  I was rather hoping that there would be an easy answer.

                 

                Any further 'detailed' help would be much appreciated.

                 

                Su

                • 5. Re: Print one long report from multiple tables
                  LabsRock

                  If your tables are heirarchically arranged, and appropriately linked, you should be able to use a sub-sum report.  I generally setup the report using a layout for one of the TOs of a table at the lower end of the heirarchy (so to speak).  For example, I work with a system that has four tables, Clients, Calls, Topics, and Referrals.  I keep foreign keys for each of the higher level tables in subsequent tables (e.g. Referrals have foreign keys for Client, Call, and Topic).  I can subsum from Referrals and get a report that would look something like this:

                  Client A

                       Call 1

                            Topic 1

                                 Referral 1

                                 Referral 2

                                 Referral 3

                            Topic 2

                                 Referral 1

                                 Referral 2

                       Call 2

                  Etc., etc.

                   

                  You can subsum from higher levels using calculation fields to aggregate/total data from a lower level if that is what you want.  That might look something like this (from a Topic TO layout):

                  Client A

                       Call 1

                            Topic 1

                                 Total Referrals = 3

                            Topic 2

                                 Total Referrals =2

                       Call 2

                   

                  Hopefully this helps.  If your tables aren't associated as described you will need something a little more involved and input from someone with a little more knowledge about pulling and aggregating data than myself.

                   

                  Craig

                  • 6. Re: Print one long report from multiple tables
                    usbc

                    Sukiso,

                    You don't say how often this report needs to be complied or if one version of the reported data will suit everyone who will read it. You do indicate that your understanding and presumably your skills are still developing.

                     

                    You might consider using a separate report file which would be a "catch and release" tool used only for constructing reports. That way you can leave your main system unburdened by extra calculations, relationships and scripts. You would do the appropriate searches in the pertinent tables and import then into the report file.

                     

                    It has been my experience that any useful report will spawn a request for yet another useful report.

                    • 7. Re: Print one long report from multiple tables
                      BruceRobertson

                      Mike: that is the original purpose and example of the technique.

                       

                      The basic idea is that you have a collector script that accumulates the return-delimited data into a variable or global field.

                       

                      The script has to be designed to collect the data in the correct order; or to parse various chunks of collected data into a final list in the correct order.

                      • 8. Re: Print one long report from multiple tables
                        Sukisu

                        Usbc,

                         

                        This report will need to be compiled many times, and separated by each sector. 

                         

                        I am relatively new, having taken over the running, maintaining and imminent upgrading of a rather messy unstructured database.  I've been adding further little database files on an adhoc basis, and am now contemplating giving it a major overhaul.  Many users are on FileMaker 9 but some have got FileMaker 11.  We can't move up to 12 because some of the macs can't run that.  We have about 100 users.

                         

                        I've been using Crystal Reports from our accounting software, and getting quite pro on that.

                        • 9. Re: Print one long report from multiple tables
                          Mike_Mitchell

                          Bruce -

                           

                          Thanks. So, if I understand correctly, you end up with what - an array of the data you want in something like this form?

                           

                                    Team Member 2

                                    Team Membert n

                                    Area of opportunity 1

                                    Area of opportunity 2

                                    Area of opportunity n

                                    Strategic Vision 1

                                    Strategic Vision 2

                                    Strategic Vision n

                                    Client|Contact|Lead|Action|Outcome

                                    Client|Contact|Lead|Action|Outcome

                                    Client|Contact|Lead|Action|Outcome

                                    Month|Targeted events|Seminars & Conferences|Marketing Materials

                           

                          Do you then have, say, 4 fields on your layout (because that's the maximum number of columns) and parse out the rows from there? Or am I missing the idea?

                           

                          Mike