11 Replies Latest reply on Jul 23, 2010 11:36 AM by FentonJones

    One Report Pulling from Multiple Tables



      One Report Pulling from Multiple Tables



      Well I've run into a blockade that I cannot seem to find a way around and am hoping that someone out there with more experience knows the answer.

      What I have is a project management/time tracking/inventory/invoicing database that has quite a few tables for the multiple entities I'm dealing with.  What I want to do is click a button and print and invoice similar to what is in the Invoicing Template for FM11.  But, I don't just have inventory that needs to be on the invoice I also have labor which is in two linked tables. Labor and Labor_Line_Items.  The reason for the dual tables here is that I am dealing with multiple labor rates on multiple days attached to one project that may or may not have more than one invoice.  

      I know how to create report layouts that will have the fields for one table in the body layout part if that information is directly from the Material_Line_Items but I have two tables with line item info, Labor_Line_Items and Material_Line_Items and these are both related to the Invoices table back through a chain of related tables.  

      I read some old posts that referred to making an additional table but I am not seeing how that is going to work since the fields are quite different between Materials and Labor with only a Project_ID that links them together and there could be many more material records for a project than labor or vice-versa.  

      To make things more complicated, on the invoice, the labor and the materials are in different sections, not all across the same line so putting the field names in the header and the fields in the body will only make sense for one or the other, unless there is a trick someone knows.  

      I hope someone can help.  If my explanation isn't clear enough, ask and I'll try and clarify. 

        • 1. Re: One Report Pulling from Multiple Tables

          Personally, I'd treat labor charges as simply another type of item the customer has purchased and put both labor and material items in the same line item table. Both materials and labor will have an ID, a Description, a unit cost and a quantity so you may get that to work quite effectively. You can sort the portal so that materials list at the top and labor at the bottom or vice versa if you wish.

          Merging your material and labor line items into a report table is an option that can be made to work, but it takes some extra fiddling to get it to come out right. Your report table must have a field for every needed field from both tables. Then you add calculation fields that serve as field labels for one type of record, but which are blank for the other type. Here's an example:

          If ( RecType = "Labor" ; "Labor Rate" ; "" )

          This gives you two sets of field labels you can stack on top of each other in a sub-summary to get completely different field labels for your material and labor items. You can then stack your data fields on top of each other in the body in the same way.

          • 2. Re: One Report Pulling from Multiple Tables

            I'm not sure I want to combine labor and materials into the same table.  You are right that they both have an ID and description of sorts and a quantity but for the labor that quantity is a non-stored calculated field based on values in start_time and stop_time fields.  It would be some work to combine the two tables at this point since they are both populated with records.  So, extra fiddling with a report may be less work in the long run.

            Do you know of an example report that uses the approach you are suggesting?  Maybe one of the FM starter solutions?  It would be great to actually see what it is you are describing.  I'm not fully conceptualizing exactly what you are suggesting. 

            • 3. Re: One Report Pulling from Multiple Tables

              Actually, merging the two tables into one may be less work than setting up the report table.

              Here's an extremely simple demo file of the concept. It doesn't deal with the mechanics of importing records from the separate tables--just how to set up the report layout. You can select the data layout to see how the data was entered and the field definitions to see how the dynamic labels were defined. You may want to duplicate the report layout and then take it apart by moving fields around to see how it was set up.


              • 4. Re: One Report Pulling from Multiple Tables

                Okay I see when you said "stacked" you meant in literally one directly on top of the other.   Cool trick.

                But this demo does bring up several questions about the tables that I am not sure was just for the simplicity of the demo or necessary for the approach.  If I am understanding correctly, I need to create a new table, call it Invoice_LineItems for example, and in Invoice_LineItems I need fields for each of the report's field labels, AND I MUST also into Invoice_LineItems merge the pertinent data from Materials_LineITems and Labor_LineItems and create a new field to track what type of record it is?

                • 5. Re: One Report Pulling from Multiple Tables

                  Yes, which is why I said that merging your two original tables into one may be less work than setting this up with a report table. The report table requires you to import data from the relevant tables just before you print your report each time you need the report.

                  • 6. Re: One Report Pulling from Multiple Tables

                    Well this means some work then.  Too bad there isn't an easier way.  Thank you for all your help!

                    • 7. Re: One Report Pulling from Multiple Tables

                      There is another way; but I don't know that it's the easist way. It works fairly well, especially if the # of fields, and their size, are not huge. They are 2 Custom Functions (use one or the other), both written by Bruce Robertson. So you would need to have FileMaker Pro Advanced to implement them.

                      Basically they gather the fields of related fields (portal rows for example), or named fields, placing a delimiter between fields (tab most common); producing a single text result. You could also add a "header" line at top, with field names. This would all be done in a Calculation of the field (storage, do not store).

                      You can put it on the layout (in Layout Mode) using the menu command Format/Insert/Merge Field.... Which can then be formatted with Tabs, using tabs in the Text Ruler (menu View/Text Ruler) to make things like up nicely. 

                      Merge fields tend to break as they should when printing over multiple pages. Portals break right in half (horizontally), which makes them pretty much unusable for printing over page breaks.

                      These are the custom functions:

                      1. GetRows ( fieldList, N , delim )http://www.briandunning.com/cf/309

                      [ I'll just add a comment here, from a post by Bruce. Which is that later versions of FileMaker can use the new function, GetFieldName ( Field ), in the Logical section. It is safer to use this instead of typed text, you're required to refer to a field by its text name. Because it won't break if you change the actual fields' names later; it will always evaluate to the existing name of the field, in an unstored calculation.]

                      2. GetRowsObjectList( ObjectList; N; delim1; delim2 )http://www.briandunning.com/cf/843

                      Haven't used this newer one yet. It's a bit more flexible I guess.

                      You would create these calculation fields in table which was the parent of 2 related tables you wanted the data from; say Invoices. If you target the 2 different types of costs, Labor and Materials, from Invoices, you can use the existing relationships to gather all the lines of each into a their own text field (all records matching the relationship).

                      If you put them one beneath the other on the layout, as Merge Fields, stretched to the maximum expected size (ever), then set them to "slide up", then you can get a decent report on 2 (or more) tables, without much structural changes.

                      • 8. Re: One Report Pulling from Multiple Tables

                        The most "correct" method (to my way of thinking) would be to use the David Grahamn method, of having 3 tables. Which sounds like a "reports" file, but it is not like that at all. Most of the data is entered into the central table at the time of CREATION, not pushed there by a scripted routine later; nor there (much) redundancy. 

                        Data would be entered into the central table FROM the child tables, using a special power of the relational engine to populate an ID field on both sides of a relationship at once, if you target any other field in the related table, when [x] Allow creation of related records is checked. That is how the central table's brand new ID is passed BACK to the child table's corresponding field. Or you could use a script trigger; but the above is elegant (in my opinion).

                        The child tables are the data entry and viewing tables. But anything that need to look at the items as one entity would look at the fields of the central table (common fields), or thru it to the appropriate child table.

                        Oh, you can layer fields on a report layout, in cases where it would be either one child or the other who had date in that space. So some fields could be quited different. So there is still that difficultly. 

                        • 9. Re: One Report Pulling from Multiple Tables

                          Fenton, I don't have the FMP Advanced version, just the FMP11 so the option with the custom functions is out, at least for now.  I really need a more compelling reason than this to get the Advanced version - I am not looking into developing for others, just in-house for my small company.  The "correct" way that you mentioned is intriguing.  I've done some googling to find what the "David Grahamn" method is and found a few of your posts at fmforums and even a long winded argument with a David Graham and a Comment regarding the "PARTY" model.  Is this model what you are referring too?  I did a little googling on it and it seems to be a particular ideology for modeling a database.  Should I be looking more into this model?  I don't mind creating another table that could be this central table perhaps.  I certainly like the idea more than trying to merge my labor and materials line item tables and labor and materials billing tables together.  

                          Could you go into more detail about what you are talking about?  Examples maybe?  I found something you did called Org_people and quickly looked through the relationship graph.  Is conPeople and conOrganizations examples of what you mean with the Central table?  They only hold ID fields which makes think they are more like join tables.  Is that what you are describing, join tables used for resolving many-to-many relationships?   I'll toy around with this a bit tonight and see if I can build a sample of what you are describing.  

                          • 10. Re: One Report Pulling from Multiple Tables

                            Compelling reasons for buying filemaker advanced:

                            1. Running a problem script with the debugger enabled often saves hours in trying to figure out why the script does not perform as expected.
                            2. Creating a database design report and using the browser find tool to find and examine each reference to a given table occurrence, field, script or other identifier can help you avoid getting unintended consequences when you modify the design/definition of that item.
                            • 11. Re: One Report Pulling from Multiple Tables

                              Yes, my Org_people file is an example of the David Graham separation method. I'll put it up on my web site (for a while), so others can look. The subtables only have IDs as it's the simplest structure for an example. The "Name" field is however in the central table (Contacts in this case), and is on the "satellite" tables layouts as a related field. Since either an organization or a person has a name, that is the field which causes the creation of the central Contacts record. 


                              It does not really matter what the entities are, and there can be more than 2. We did another example (on fmforums) of a trucks and trailers; but that was in someone else's file, and kind of specialized. But Labor and Materials fit the model also. 

                              Though, in production databases where I've had to do similar for a printed report, I just used the GetRows Custom Function, as it's much easier and faster to add to an existing structure. The Graham method is more suited to situations where there's quite a lot going on with the satellite tables, quite a few fields unique to the entity, separate entry and list layouts, and also quite a few fields common (for the central table). It's kind of overkill just for a report. But it does give a very flexible relational structure.