5 Replies Latest reply on Jan 8, 2014 9:50 AM by philmodjunk

    Multiple Table Report Generation

    scalar

      Title

      Multiple Table Report Generation

      Post

           Hi All,

           I recently updated and converted a dated database from Access into Filemaker.

           I have everything built exactly as I need it with calculations and navigation between tables, however, after numerous hours, have struggled to find a method of conducting a "find" across multiple tables, generating specific information from multiple tables onto one report.  In Access this was a "query", and let you pick and choose different fields from different tables as long as the tables were related in some way.

           Specifically, I have a contact table, 2 content tables, 2 promotional purchase tables, and 1 total billing table.  The reason for the 2 separate content and promotional purchase tables is that we have 2 types of products, and one contact may purchase components of either product.  What I would like to be able to do, as I was able to do in Access, is pull specific fields from 2 or more of these related tables and display a report with this information, while maintaining the current structure and display of my current tables

      Screen_Shot_2014-01-07_at_12.51.26_PM.png

        • 1. Re: Multiple Table Report Generation
          philmodjunk
               

                    The reason for the 2 separate content and promotional purchase tables is that we have 2 types of products, and one contact may purchase components of either product.

               This does not require separate tables and the separate tables complicates your design. (I wouldn't use separate tables in Access either).

               Combining the data into less tables can be done by adding a "category" field to distinguish between your two types of products records.

               Or are these two Tutorial: What are Table Occurrences? of the same data source table?

               Can you provide an outlined example of what your report needs to look like?

               A report combining data from contacts, promotions and products for just one product type should be fairly straight forward from what I see in your relationship graph. Combining data from both "chains" of related tables is also possible, but will require a fair amount of extra work that could be avoided if you combined tables here.

          • 2. Re: Multiple Table Report Generation
            scalar

                 The product I am dealing with is a Magazine, my company puts out 2 separate types of magazine once a year, at different times of the year.  A vendor, which is a contact in my relationships graph, can purchase listing space in one or both of our magazines, and can specify what section in that magazine they would like their listing to be in.  So basically we have 2 books, each book has 22 sections in which a vendor may purchase space and has the option to buy additional promotional products to include in either of the books.  The billing table is a combination of calculations between the promotional rates table, the listing size tables (separate for each book), and the additional promotional options tables (separate for each book).  It is necessary to keep the section size tables separate for each book for production tracking purposes, and it is necessary to keep the promotional product purchases separate for each book because the vendor may purchase something in one book but not the other.

                 One thing we would like to be able to do is show on one screen or report, for a certain vendor, how big the section is they purchased in one of the books (one table) and how much their total charges are (another table), while also showing their contact information (another table) so we can send this report to them for billing purposes.  

                 Thank you

            • 3. Re: Multiple Table Report Generation
              philmodjunk
                   

                        It is necessary to keep the section size tables separate for each book for production tracking purposes, and it is necessary to keep the promotional product purchases separate for each book because the vendor may purchase something in one book but not the other.

                   I agree that the different records need to be treated as different sets of records, but will respectfully point out that this does not require putting those sets of records into separate data source tables. There are other ways to "keep them separate" such that the user does not even know the difference. But you will know the difference when it comes time to set up the reports that you need.

                   

                        One thing we would like to be able to do is show on one screen or report, for a certain vendor, (contacts) how big the section is they purchased in one of the books (Products) and how much their total charges are (Billing), while also showing their contact information (contacts) so we can send this report to them for billing purposes.

                   That actually sound pretty easy to set up from what you have here. It's just that you'll have to do it all twice over given the separate tables for separate products and related promotions. I've guessed as to the tables for each part of your report. Please correct me where I have guessed wrong.

                   Your basic relationships for a single product break down like this If I can correctly decipher your partially obscured table occurrence names:

                   Billing>-----Contacts----<Products----<Promotions

                   If I have the correct, then Contacts, Products and Promotions data can all be presented on a single layout based on Promotions. This would be a list view layout with fields from Products placed in sub summary layout parts and Contact fields can be included in the Header, grand summary or footer layout parts. You'd use a find or Go TO Related records to pull up the desired Promotions records--which could be all Promotions records for a given contact or just those meeting certain criteria. Other layout designs might be used where you use a Portal to list related data (Think Access sub report or sub form). There can be pros and cons for either approach.

                   The billing data could be more of a challenge depending on the nature of your report as your relationships link Billing data directly to Contacts. There's no link in the current data model that lets you link specific charges to specific product or Promotion records. But a grand total of all Billing records or a total based on a date or range of dates is quite possible.

                    

              • 4. Re: Multiple Table Report Generation
                scalar

                     Thank you for your detailed responses!

                     To possibly complicate matters, things are separated further because there are different types of vendors, indicated by "vendor status" who have different billings rates.

                     The primary reason for the layout the way it is is for viewing simplicity.  I coordinate each of these tables with separate departments in my office, and each of them is only responsible for one table at a time, which would become confusing to them if the tables were to be combined in any way.  I understand the methodology you're describing, but was just hoping to avoid it and have a more straightforward process like was used in Access for these types of queries.  Basically in Access, I could tell it to show me in a single table-view document, any number of fields from any table that were linked by the Vendor Number, and I was hoping I could do the same here.

                • 5. Re: Multiple Table Report Generation
                  philmodjunk
                       

                            which would become confusing to them if the tables were to be combined in any way.

                       The fact that the tables are combined need not create any visible differences in how your different departments use your database. Scripts and other design features can kick in to conceal the fact that there's additional records in the table that are not "their" records.

                       I've also designed databases in Access and I wouldn't use separate tables for this data in Access either. It's actually more complicated a design (especially in FIleMaker) to work with than with combined tables. That's not to say that there aren't good and valid reasons for using separate tables, I just haven't seen those reasons in your posts to this point. wink

                       

                            in Access, I could tell it to show me in a single table-view document, any number of fields from any table that were linked by the Vendor Number, and I was hoping I could do the same here.

                       In FileMaker, you can produce much the same result with a Table View Layout and performing a Find to take the place of the WHERE clause that you'd use in the SQL query you'd use in Access. Finds can be scripted to produce the needed found set of records for your view--either with or without user input. They can even take place automatically when you first select the layout for view.

                       But only if you combine the tables. With two sets of separate data source tables, you'd need two very similar table view layouts. This complicates your design process, but the users need not even know that there are two different layouts if you set up your interface design to keep that from being obvious to them.

                       PS. One major design flaw to your original screen shot is that you are using both a Name and an ID field to link your records. I strongly recommend that you remove the name field from all the relationships shown. The ID field should be unique in contacts and that should be sufficient for linking records in your relationships. With multiple copies of the contact name stored in all of your tables, a name change--either because the contact changed their name or because a mistake needs correct, will be a tricky time consuming process that can be completely avoided if you don't use the name field in your other tables, but just keep as a field in Contacts.

                       And if you really want to, you could use a very large edit box with an unstored calculation field that uses ExecuteSQL to produce your table like view of the data. You can use tab characters as the field separator and can set tab stops in the inspector's appearance tab to get the columns sized appropriately. And this can be a UNION query to combine data from more than one table of identical format/type information.