4 Replies Latest reply on Oct 7, 2013 3:58 PM by calebmiranda

    one report, multiple tables



      one report, multiple tables


           Hi. I'm new to filemaker and what I really want is to know if it is capable of doing what I need.


           I'd like to have multiple table for storing different kinds of information but, the point is, all this information is, at the same time, equal in some part. Let me explain with a Star Wars example:

           I'd like to have one table for, let's say, all the races from the Star Wars universe. In this table, I'd have each race listed with its corresponding fields, one of which is planet;

           In another table, I'd have all star wars weaponry. In this table, each weapon would have its fields, one of which is also planet.

           My problem is, I do not want to print a weapon table and a separate races table. Because I have tens of tables, and what I need is all the info, and I'd like to have it grouped by planets. So, when I look at Tatooine, I'd like to have all the info about Tatooine, without having to look all tables (eventually I'll print it in a book-like way).

           I know two solutions, each so far unsatisfactory:

           1. I could do one table, with multiple empty fields, and each record is a "unit of knowledge". That seems to suck

           2. As the idea is to have the report every now and then, I could do a script to run in each table which would, then, create a new record, in a table made just for the "final" report, with field being calculated and store in as few fields as possible (in a text-like way).


           I know this two things would, sort of, work. But what I really would love is to be able to print a report that sorted all my info from a field I choose, all this info being from various tables.


        • 1. Re: one report, multiple tables

               It really depends on the data and the specific relationships.

               What you describe in your example would appear to have these tables and relationships:

               Races>-----Planets------<Weapons        (But you might also have a many to many relationship between these tables and that would require more tables.)

               With such relationships in place, you could base your layout on Planets and use portals to Races and Weapons to list those for each planet record.

               You could also base the report layout on either Races or Weapons with data from Planets listed in a header, grand summary or sub summary layout part. With a report based on Races, a portal to Weapons can be used to list the weapons. And if you base the layout on Weapons, you can use a portal for Races. Often, this version is a way to display data in one of the tables on the "many" side of the relaitonship that doesn't work well in a portal.

               PS. and you can size such portals to be many rows in size and use sliding and visibility settings to reduce the size of the actual portal to just those needed to display related records....

               But do keep in mind that with other relationships, layout options that do not use any portals are also possible. Example:

               If you had these tables/relationships: table1---<table2----<Table3>-----Table4, you can construct a list view report based on Table 3 that lists all needed data from tables 1, 2 and 4 without the use of a single portal.

          • 2. Re: one report, multiple tables

                 The last option seems very suitable. Actually, I think I need to provide more info.

                 Instead of planets, let's list by topics, sub-topics, etc (and for the moment, let's think that's the only way I'd ever sort my report). And let's change from weapons and planet to canon material and non-cannonical material. Then I could:

                 1 - make 3 tables (canon, non-canon and topics), being the first 2 tables (canon, non-canon) related to topics;

                 2 - make a report based on topics, with all the info I wanted from canon/non-canon, and not necessarily in blocks. Like:

                 >> Fire weapons:

                     > - weapon A (from canon material)

                     > - weapon B (from non-canon material)

                 >> >> Fire weapons from aliens (it being a subtopic of fire weapons)

                          > - weapon C (from non-canon material)

                          > - weapon D (from canon material)

                 >> lightsabers:

                  >> >> dark side (suptopic):

                           > - red lightsaber (canon material)

                           > - double red lightsaber (canon material)

                  >> >> light dark (subtopic of lightsabers):

                           > green saber



                 The idea is:

                 - canon and non-canon material are different in their structure: if just movies are cannon, I'd just quote it with just the movie number (1-6) and time (00:00:01), and non-canon may have more details: author from the book/comic, date release, page, transcription, etc

                 - canon and non-canon are both related to topics, and the order of the topics should show them both


                 So, File Maker allows me to do this topics reports, am I right?

            • 3. Re: one report, multiple tables

                   My last suggestion is actually much the same as the earlier suggestions, but based on a different table/relationship structure.

                   From here, I see no significant difference between "canon" and "non-canon" data and would put records for both in the same table.

                   Successful data modeling is part art, part science and figuring out the proper table/relationship structure is crucial to sound database design for many more reasons than just reporting. The two most common "newbie" data modeling errors are 1) putting data that should be in the same table in separate tables and 2) Putting data in the same table that should be in separate tables. Hitting the right balance between the two is a factor of having a good grasp of database essentials and the strengths/weaknesses of FileMaker Reporting.

                   Fortunately, one the strengths of FileMaker is that you have the tools needed to make incremental improvements to the basic data model without having to throw everything out and start over so you can start with what you think is the best model and then revise it as your knowledge of the project needs and your understanding of FileMaker changes.

                   And keep in mind that often, it's the inclusion of a common "join" table of one type or another that is key. You might, for example have a "Weapons" table that links to two related tables, one for "canon" an done for "non-canon" data. That gives you a common table with one record for each weapon of any type on which to base your report layout, but you can selectively pull in data from the "detail" tables that are specific to a given type to provide additional detail about that weapon in your report.

                   In a previous Job, we had a "Specifications database" that contained purchase and manufacturing specifications for items manufactured or purchased by the company. A common table of "SKUs" had one record for every inventory item but with related tables that were dedicated to a specific subset of all the items in that table. Thus, we had specs for natural wine corks in one table and specs for metal screw caps in a second and specs for pvc shrink on plastic capsules in a third, yet had a common table listing all of them plus many more to use for reporting (and other) purposes.

              • 4. Re: one report, multiple tables

                     fmp seems amazing and I thank you much for your answer. I could use some help with my database, specially on understanding how it would/should work (and all the people I know use just sql, which seems to take too long for anything). If you answer my questions out of good will, it wouldn't be insane to think you could be interested in helping me in a professional (and by e-mail/skype) way. Would you like to? How could we exchange contacts?