9 Replies Latest reply on Jan 12, 2015 5:28 AM by philmodjunk

    Filtering a calculation

    capt_bean

      Title

      Filtering a calculation

      Post

      This is my first post on this forum! I am trialling FMP as a better solution to MS Access because it runs on Macs.

      For the last week I have been trying to overcome what appears to be a simple problem, but for the life of me I cannot find how to solve this in FMP. There are some conceptual differences between FMP and Access some of which still clearly elude me!

      I have put together a very simple DB with 2 tables with a one-to-many relationship. Basically I need to display information from the first table grouped by Category and show the total amount from Table 2 next to each category. This is not difficult to achieve by having a calculated field in Table 2. The problem is that I need the data to be filtered. How do you filter a calculation in a field? The layout is filtered by writing a script, but this is ignored by the field.

      I have created a simple sample file to illustrate 'the problem' but I am not sure if I can upload it here!

      Any guidance would be very much appreciated. Thank you.

       

        • 1. Re: Filtering a calculation
          philmodjunk

          You cannot filter the calculation, but you can build that "filter" into the relationship so that it only matches to the records you want to use in an aggregate calculation such as sum.

          You can also use ExecuteSQL() to compute and produce this subtotal.

          But what you describe sounds like something that is far easier to produce as a summary report based on the related table instead of the parent table using summary fields and sub summary layout parts. This can be set up such that you get one row of data for each category with a subtotal associated with it. This report can report on all records in this table or you can perform a find to base it on a subset of the records--such as all records created in a particular time interval.

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Filtering a calculation
            capt_bean

            I just can't see the wood for the trees anymore! When you say "you can build that filter into the relationship" how do you do that? I thought I had by doing a Perform Find [Restore] in the layout script. This line simply filters out the records by date being greater than 2014 (i.e. 2015 or later). This does indeed filter out the records in the parent table. BUT it does not apply this to the related records in the second table. Why does the related field disregard the found set. How can that ever be relevant? So how exactly can I build this into the relationship?

            ExecuteSQL() might be an option, I guess as a field in Table 2? But to be honest, what I am trying to achieve is so fundamentally basic, that I am staggered the answer can be this complicated.

            I have tried as you suggest reversing the tables but this gives the same issue because I need data from both tables to display on the same layout.

            From Table 1, I need to display:

            CategoryGroup

            Category

            YearlyBudgetAmount

            WeeklyBudgetAmount (by calculation)

            From Table 2:

            The sum of each category

            But the layout must filtered by date (which it is via script) but the 'sum of each category' from Table 2 does not filter. It produces the sum of all records per Category in Table 2.

            I've also had a look at the link you posted, and the only thing that caught my eye was "Unlike the sub-total you see when you place a summary field in a Sub-Summary part, references to a summary field in a calcualtion will return the grand total of the current found set." I have tried this but without success, probably because I am doing something wrong!

             

            • 3. Re: Filtering a calculation
              capt_bean
              /files/e09125c132/Script.png 964x90
              • 4. Re: Filtering a calculation
                capt_bean
                /files/c407f83070/ExpenditureTypes.png 692x246
                • 5. Re: Filtering a calculation
                  capt_bean
                  /files/47d45592cd/Expenses.png 842x406
                  • 6. Re: Filtering a calculation
                    capt_bean

                    Table 1 is ExpenditureTypes

                    Table 2 is Expenses

                    Even though Layout filtered by Date as per script, the totals from Table 2 are that of ALL records in that table. Why oh why are they not related? (and yes a relationship exists linking Category in each table.

                    Please help...

                    • 7. Re: Filtering a calculation
                      philmodjunk

                      When you say "you can build that filter into the relationship" how do you do that? I thought I had by doing a Perform Find [Restore] in the layout script.

                      Performing a find has nothing (directly) to do with building a "filter" into a database relationship. Performing a find changes what records make up your current layout's found set. To get subtotals from related data, this is something that you control with what fields you use in the relationship and what values are present in those fields.

                      For example, you can define a pair of global date fields in one table and include them as match fields in the relationship matching records to a second table. You can then select different dates in these global fields to match to different sets of records in the other table by date. (And you can use inequality operators (<.>) in these relationships rather than the default = operator.)

                      I have tried as you suggest reversing the tables but this gives the same issue because I need data from both tables to display on the same layout.

                      If you take a look at the tutorial that I recommended, you'll find that the example used there does exactly that. Just because you base your report on the related table this does not prevent you from including fields from the parent table on your layout. Since, from the context of this layout, there's a many to one relationship, there should be no ambiguity as to which related record supplies the needed data for your report.

                      What I see in your example layouts looks very, very similar to the example found in the tutorial.

                      • 8. Re: Filtering a calculation
                        capt_bean

                        THANK YOU!

                         

                         

                         

                        You put me on the right track. I went with the match field idea because by adding a date field in Table 1 not only does it solve the problem but it increases functionality for a future purpose I have in mind too!

                        I still have an awful lot to learn about FMP. Looking at the various replies you have given on so many threads shows me how little I know :-( I wonder if there is a course available that formally develops one's knowledge of FMP rather than learning by just battling through?

                        Anyway, thanks again for putting me on the right track!

                        • 9. Re: Filtering a calculation
                          philmodjunk

                          There are many training resources available for FileMaker. Some organizations do offer FileMaker training if memory serves to be accurate.