9 Replies Latest reply on Feb 10, 2011 9:05 AM by ColdSpring

    Filter multiple summary fields from across mult  tables in one layout

    ColdSpring

      Title

      Filter multiple summary fields from across mult  tables in one layout

      Post

      Hello,

      I am trying to apply a date range filter across one main table and have all appropriate summary fields from multiple tables

      also have the same filter applied.

      Pretty simple schema

      - Jobs: table that has invoice data

      - Direct Purchases: discretely chargeable items to JOBS (join to JOBS on InvoiceNumber)

      - Indirect Purchases: not direct charges to client, but part of JOBS expense (labor, etc) (join to JOBS on InvoiceNumber)

      - Company expenses

      I Understand how to link tables using the Cartesian join operater (X) from this posting:

      http://forums.filemaker.com/posts/805b390f9a

      This works fine to present summaries across all data in each table.

      I want to apply a date range filter to the JOBS table layout, and have all the other summaries likewise filtered for the same date range.

      Any thoughts???

      There is no issue applying a filter to the discrete tables above and getting the desired summary, but I'd like to have one summary layout that has high level data in it. Essentially I am trying to capture all data to support tax preparation.

      Many thanks!

      Rob

        • 1. Re: Filter multiple summary fields from across mult  tables in one layout
          philmodjunk

          You might consider merging Direct and Indirect purchases into the same table with a PurcahaseType field used to distinguish one type of purchase from the other. That might simplify a number of issues here for you. (And relationships and portal filters can include data to only link to one purchase type or the other to keep them separate.)

          Don't think that a cartesian join will be the best option here for you.

          The main problem, though is that only Jobs are labeled by date. That suggests that you should base your layout on Jobs and use relationships to the other tables to access the relatd data. Fields defined in Jobs can use aggregate functions such as Sum, count, average... to compute totals or you can define summary fields in the related tables that compute the same totals. Either approach can be made to work for this.

          You'd perform first a find for Invoices by a date range and then your report could include data from all related records that link to that found set of records.

          • 2. Re: Filter multiple summary fields from across mult  tables in one layout
            ColdSpring

            PhilModJunk,

            Thanks for the response....

            I appreciate the idea to merge direct and Indirect expenses into one table.  I split them out since I have more InDirect expenses than Direct.  Your idea will make the db simpler for sure.

            I am using Summary fields in the related tables as you point out and the results are not what I expect.  When I change the date range filter on the main JOBS table, the summary fields do indeed change on the summary layout but are not correct.

            I am thinking I need to add additional filters on the layout, a date range for each of the related tables to force the filter on the corresponding summary.  End result: Date range filter for JOBS table, date range filter for Direct Expenses table, ditto for Indirect Expenses and Company Expenses.  Not pretty, but I think it will get the job done.  Would be great to use one date entry across all, but I dont see how I can do it.

            Where the cartesian join is allowing an open join to related tables, the related table summary fields are not being likewise filtered properly.

            As you suggested, I tried a direct relationship linking by InvoiceNumber from JOBS and the related tables.  The summary fields computed for a single Invoice only, not across the date range.

            I'm not with my Mac now, but determined to get this to work.  I'm a relative newbie to FM, but an old salt with relational db's.

            If I'm missing something here, let me know...

            Many thanks,

            Rob

            • 3. Re: Filter multiple summary fields from across mult  tables in one layout
              philmodjunk

              This indicates that you are close:

              As you suggested, I tried a direct relationship linking by InvoiceNumber from JOBS and the related tables.  The summary fields computed for a single Invoice only, not across the date range.

              You can use a calculation field in Invoices that computes the invoice total and a summary field defined in the jobs table to compute the total across multiple invoices to give you both sub totals and grand totals.

              To get a sub total (for all invoices for a specific customer, for example), define a sub summary part and place the summary field there. To get a grand total over all the records found, put the records in a footer, heater or grand summary layout part. (see parts setup in the layout menu for adding/changing layout parts for your layout.)

              When using sub summary parts, be careful to also sort your found set of records by the same field that you specify as "when sorted by" in the part's setup, or the sub summary part will be invisible when you browse, preview or print the report.

              • 4. Re: Filter multiple summary fields from across mult  tables in one layout
                ColdSpring

                Thanks PhilModJunk.

                More experiementing ahead.  I'll revert with the outcome.

                • 5. Re: Filter multiple summary fields from across mult  tables in one layout
                  ColdSpring

                  Phil,

                  The Invoices table is the same as the Jobs table, sorry for the confusion.

                  Still haven't reached Nirvana.

                  I changed the relationship back to a direct (Inv# = Inv#) for both Jobs->DirectExp and Jobs->IndirectExp, both were a Cartesian before.

                  I do have defined Summary fields for Jobs, DirectExp and InDirectExp tables.

                  Results are not what I'm expecting when applying a date range filter on Jobs table

                  - summary fields for Jobs table ARE CORRECT

                  - summary fields for DirectExp and InDirectExp are NOT CORRECT

                  For example, applying a 2010 date range on Jobs will result in selection of 70 of 100 records.

                  tabbing thru selected records will result in:

                  - summary fields for Jobs table ARE CORRECT (stay the same for every record, displaying correct GrandTotal for selected 70 records)

                  - summary fields for DirectExp and InDirectExp are NOT CORRECT (show the value for a given single record, not the summary across the set).

                   

                  As suggested above, I moved the above summary fields from the BODY to the FOOTER.  No joy.  I created a GRAND SUMMARY layout part, still no joy.  With the GRAND SUMMARY, I do not have the ability to select my fields in the picklist....that I believe could be the issue...

                  More experimenting ahead tonite.

                  • 6. Re: Filter multiple summary fields from across mult  tables in one layout
                    philmodjunk

                    I understood that invoices and jobs were the same record.

                    Here is a demo file you can check over:  http://www.4shared.com/file/Kd9BolfS/SummaryWithRelatedChildren.html

                    There's one layout for data entry and a second for reporting. It only has one related table, but that should be enough to illustrate the technique.

                    • 7. Re: Filter multiple summary fields from across mult  tables in one layout
                      ColdSpring

                      Many thanks Phil.  Big Brother has blocked the site you provide.  I will check it out this evening.  I'm sure your example will highlight something very obvious....!

                      • 8. Re: Filter multiple summary fields from across mult  tables in one layout
                        ColdSpring

                        Phil,

                        My FIOS connection was down last night, so I was unable to view the demo you had offered.

                        I've been changing the layout as per the many suggestions you have offered on this forum.

                        Still stymied to get the children summary grand totals for DirectExp & InDirectExp to compute correctly when the parent is filtered.  I notice in the table view of the data that the related summary fields are not computing properly: although a summary field, only shows total for that row.

                        Two things to try on the commute home:

                        - change children summary fields to calculation (sum) fields.

                        - move the child summary calcs to the parent table (JOBS)

                        More to come.

                         

                         

                        • 9. Re: Filter multiple summary fields from across mult  tables in one layout
                          ColdSpring

                          PhilModJunk,

                          Your example (URL link) above made the solution quite clear.

                          With GrandTotal infomation coming from a CHILD relationship, your example shows that the summary field used for the GrandTotal data needed to be sourced from the PARENT table. 

                          You had stated above essentially the same, but I didnt digest it..."You can use a calculation field in Invoices that computes the invoice total and a summary field defined in the jobs table to compute the total across multiple invoices to give you both sub totals and grand totals."

                          The website also has a number of other great examples.

                          Many thanks for your patience and assistance.

                          Rob