10 Replies Latest reply on Jan 20, 2012 10:38 AM by MichaelVoccola

    Creating a report from multiple tables

    MichaelVoccola

      Title

      Creating a report from multiple tables

      Post

      The layout in question handles information for a number of "requirements" for a job. The requirements are currently stored in different tables (ie: Audio, Video, Lighting). Currently, when printed, the layout uses seperate portals to show this information. I am having issues with portals breaking across page boundaries, and have decided it would be better to approach this layout in a similar manner to an invoice, where each requirement record is sorted appropriately in rows. This would mean the layout is showing records from a table named "requirements", which will store information in a single table vs separate tables.

      I am ok with setting this up; however, the issue is that each type of requirement record (Audio, Video, Lighting etc...) has a variety of fields that need to be filled out. For example:

      - Lighting:

      - Fixture

      - Subject

      - Rigging

      - Video:

      - Screen size

      - Projection type

      - Location

      - Audio:

      - Input style

      - Mono / stereo

      - Purpose

       

      What would be the best approach to displaying these records in a view as described above? Ideally, there would be a header above each section displaying the column titles.

        • 1. Re: Creating a report from multiple tables
          philmodjunk

          I agree that a single table makes more sense here.

          There are several approaches you might experiiment with:

          1) Create pairs of text fields. The first field in the pair stores label text, Fixture, screen size, input style, etc. The second field in the pair stores the value.

          2) Define a lot of fields, with only a few used for any given requirement. Stack transparent fields on top of each other in the report layout such that only one field holds data for a given requirement in each such stack of fields.

          3) Do the same as 2) but use slide up /resize enclosing part to elminate the white space produced by empty fields

          In both 2 and 3 you may still find the need to use "label" fielda, often calculation fields that display label text or are empty depending on the requirement type.

          It's often easier to manage your fields if you use related "detail" tables for each requirement type that link by Requirment ID to the requirements table on which you would still base your report layout. Since this is a one to one relationship, you can add fields to the layout from the related tables just as though they are all fields in the same table.

          • 2. Re: Creating a report from multiple tables
            MichaelVoccola

            Phil,

            The current UI uses a portal to create new records in the (currently) separate tables. I can visualize your suggested approach using scripts and potentiall seperate layouts/custom dialog boxes to create a new record, and setting "Requirements::ReqType" to whatever type of requirement it was (audio, video etc...) so that they can be sorted appropriately in portals and groupings on the print layout.

            The issue I am running across here is that the data is currently typed directly into a portal allowing a user to briskly type in multiple records using the tab/return keys. needing to trigger a script with a button is a step backwards for the UI experience and efficiency.

            Is there a method I could use to continue entering these new records as I currently have it setup, while creating a mirror record in the master "requirements" table, which will house all the fields needed to cover every requirement type?

            • 3. Re: Creating a report from multiple tables
              philmodjunk

              You could, but I don't recommend setting up such redundant tables of data. You should be able to keep the same look and feel that you have now for data entry, though you may find it necessary to add a few script triggered scripts to smooth the process along a bit.

              Since you already have (separate I assume?) portals for entering the data, you should be able to keep that look but build behind the scenes scripts (or possibly a relationship) into the interface that fill in the additional "type" data so that the user does not do anything different from what they currently do to enter this data.

              Please note that your current separate tables could be set up as the "detail" tables I described earlier and then you just need to automate a process that creates a matching Requirements record that joins your current job record to this new "detail" record.

              • 4. Re: Creating a report from multiple tables
                MichaelVoccola

                Thank you, I will be experimenting with your approach tomorrow.

                 

                I have reviewed a little further and stumbled across another similar related problem. I currently have the following relationships in place:

                 

                Events--<Components--<LineItems (equipment)

                and

                Events--<Labor

                 

                Think of an event as a job as a whole, and a components as different part of an event, usually in different rooms. Labor is currently billed to an event record, and not to a component while line items (equipment) is billed to components. I would like to continue associating labor with an event as a whole, but at the same time, it needs to now be stored in LineItems. Of course, doing this isnt a problem. The problem arises with, once again, the printed report, which is the entire reason I need to change this (the table "Labor" is currently displayed as a resizing portal on an invoice layout showing records from LineItems).

                So I have the current parts setup:

                • Title Header
                • Sub-summary by LineItems::ComponentID (leading)
                • Body (showing records from LineItems)
                • Sub-summary by LineItems::Category (trailing)

                I would like the section "Labor" to show up as if it were a Component with the same formatting as the "Sub-summary by LineItems::Components (leading)".

                This could be accomplished by automatically creating a Component associated with each new Event record where Components::ComponentName = "Labor", which would give it a unique ComponentID. The portals and UI could also be set to filter out this component by means of the name or some other field that dsignates it "invisible" (ie: Components::Visibility); this could be used in statements to filter portals more reliably than using Components::ComponentName ex:

                Only show records when:Components::Visibility = "1"


                Other layouts etc... would also need to be modified so that, when printed, this other Component known as "Labor" wouldn't be visible. For example, some scripts in the system produce .PDF's that outline the requirements for each component (which I mentioned earlier in this thread). In this instance, I wouldn't want Labor to show up as a component, as it wouldn't have any of the information filled out, nor would that information be applicable, so it shouldn't show up at all as a component.

                 

                Essentially, I understand that (at least in theory) the above approach I outlined seems like it would work, but I want to be sure I am not overcomplicating future revisions to this system by needing to filter out labor as a component in a script step future reports in functionality. Basically, I don't want to create a nightmare, which this seems like it could easily become.

                Is there an alternative method that could be implemented to produce the desired end results while maintaining a simpler working environment (on the programming end) going forward?

                • 5. Re: Creating a report from multiple tables
                  philmodjunk

                  You seem to have an excellent grasp of the issue, though I'm not sure that your visibility field will help you much and may complicate the process. (If you have a networked database, one user may need visibility = 1 and the other may not need visibility = 1 for the same record.)

                  Theireare two alternative methods you can consider given the current report limitations of FileMaker:

                  1) Generate a PDF of the report instead of just printing or previewing it. This can be done with an "append" option in Save as PDF that appends the report from layout B to a PDF generated from Layout A. The drawback here is that each appended PDF will start on a new page.

                  2) Desing a "report table" that combines fields from multiple tables in it's design. Base your report on this layout, then import sets of records from different tables into this "merged report" table just for reporting purposes. This keeps your current data structure intact, but requires what is often a very complex combination of scripts and report layout design to pull off.

                  • 6. Re: Creating a report from multiple tables
                    MichaelVoccola

                    Phil,

                    I did experiment with adding Components::Visibility as a "true/false" field and it seems to work ok.

                    Essentially, I edited the "Create new event record" script in such a way that when a new event record is created, a component is also created that is related to the event record:

                    • Components::ComponentName = Labor / Other
                    • Components::EventID = $EventID
                    • Components::Visibility = "true"

                    This "Labor / Other" component will show up in the Component's Portal on the Event Record. This portal features "view related record" and "delete related record" buttons. The scripts on the buttons were rebuilt to check Components::Visibility. When a related record is viewed, the script will now point to a separate layout optimized for looking at the "Labor / Other" item charges. When deleting a record, it will return a dialog stating the item can't be deleted if it is a "Labor / Other" component, and will confirm a delete if it is a regular component.

                    Most importantly, it does accomplish the goal on the printed Invoice / Estimate. The formatting is consistent throughout.

                    The only issue is summaries on the "Financials" tab of the Event Record. Previously, there were fields summarizing the totals for "Equipment" and "Labor / Other" separately; with this modification, the Equipment table stores all the data and Labor is non-existant table.

                    For example: The field EventRecord::Total_Labor will need to give me a summary of records only when Components::Visibility = "true". I can probably figure this out, but before I lost track of what I wanted to accomplish tonight, I wanted to write all that down. Still, any suggestions for the revised (separate) summary fields is welcome!

                    • 7. Re: Creating a report from multiple tables
                      philmodjunk

                      If you need sub totals of different groups of records in your report, you can put the same summary field in a sub summary layout part. The report's found set of records must then be sorted by a field that groups the records into the needed groups and this field must be specified as the "sorted by" (break) field in the part setup for that sub summary part.

                      If the format of your report does not support that option, there are ways to set up self  joins that match to specific groups of records so that a sum, count or other aggregate value can be computed for that group of related records.

                      • 8. Re: Creating a report from multiple tables
                        MichaelVoccola

                        Phil,

                        I do have the report functioning in that manner successfully; I was inquiring about how to achieve this separation of "Labor / Other" (or future categories) in a summary field that shows on the Event-Record. I am pondering using "If" statements, which I experimented with last night. I don't remember exactly what I tried, seeing that it was around 3am by the time I got to this point, but it went something like this (and didn't work):

                        If ( LineItems::Visibility = "True" ; Sum ( cExtendedBilledPrice ) )

                         

                        • 9. Re: Creating a report from multiple tables
                          philmodjunk

                          Nevertheless, a summary report with sub summary parts is always the first method I consider as it is simple and flexible.

                          taking the Sum of a single field is the same as saying "take the sum of 5". Since the answer is still 5, we can just refer to 5 instead of using sum. Sum has to refer to a list of fields to sum up before you can get a total of two or more values.

                          I don't have a sufficiently clear understanding of how your tables and relationships are set up here. You have a visibility field in Lineitems, but are totalling values in Events. What is the relationship between the two tables? Is this a one to one or one to many relationship?

                          Sum can total up a set of related records so it might be an option here via the appropriate relationship. But it can't sum selectively. It will sum up all related records so you have to define a relationship that matches only to the records you want to sum. Another option is to use a summary field from a related table--either by itself or inside a one row filtered portal where the filter limits the records to be summed up by the summary field.

                          • 10. Re: Creating a report from multiple tables
                            MichaelVoccola

                            Phil,

                            I was able to create script (and set of sub-scripts) to import the records from the various tables I am merging into the "Requirements" table.

                            Under normal usage, I have maintained the multiple portals (which previously referenced multiple tables) and used a "+" button that triggers a script. In this example, it is an AudioInput requirement.

                             

                            • Set Variable [$compID; Value:Components::ComponentID]
                            • Go to Layout ["Requirements"]
                            • New Record/Request
                            • Set Field [Requirements::ComponentID; $compID]
                            • Set Field [Requirements::RequirementCategory; "Audio"]
                            • Set Field [Requirements::RequirementSubCategory; "Inputs"]
                            • Set Field [Requirements::RequirementColumnHeader; "QTY Input Type         Style         Comment"
                            • Go to Layout [original layout]
                            • Go to Object [Object Name: "AudioInputsButton"
                            • Go to Portal Row [Select; Last]
                            • Go to Field [Select/perform; Requirements::QTY]
                            Requirements::RequirementColumnHeader is a text field that holds the enter row of column headers in a single field. This data was created for each requirement type by laying out the fields on the layout as desired, creating a text box above them, and typing in the column titles with the appropriate spacing for that particular type of requirement. I then copied and pasted the contents of the text box into the "Set Field [Requirements::RequirementColumnHeader] "specify value"
                             
                            These steps were repeated for each record type.
                             
                            This was coupled with your initial post on this thread. I used the second option you provided.
                             
                            Thanks again Phil!!!
                             

                            2) Define a lot of fields, with only a few used for any given requirement. Stack transparent fields on top of each other in the report layout such that only one field holds data for a given requirement in each such stack of fields.