5 Replies Latest reply on Mar 20, 2015 3:16 PM by philmodjunk

    Unstored Calculation Field and Filtering Portals



      Unstored Calculation Field and Filtering Portals


      Using Filemaker 13 Pro Advanced. I set up a database to track expenses.  The following is where I have my question.  

      Relationship: Dashboard::Status Filter |--x--| Expenses::Status Order

      Other field being used - Expenses::Date

      Expenses::Status Order = unstored calculation with following calculation in it.

      Let([CM= Month(get(CurrentDate));CY = Year(get(CurrentYear)); DM= Month(date); DY = Year(date)]; Case(DM&DY = CM&CY;2; DY=CY;1; DY=CY-1;0)) //2 = Current Month, //1=Current Year, //0 = Last Year

      On the dashboard layout, I created a portal to Expenses table with buttons above it that say 'Current Month' 'Current Year' and 'Last Year'.  When clicked, the buttons 'Set Field' Dashboard::Status Filter to '2', '1', '0' respectively.  Everything is working fine and the portal filters records based on button pushed.  My question is, I understand case functions stop after the first 'true', however I didn't know if it was possible to override that without harming the stored calculation?  Expenses can be both Current Month AND Current Year, and I would like to show both, depending on which button is clicked.  I feel like the only way to do this would be to write a script which would filter all the expense records depending on which button was clicked and then Set Field to Expenses::Status Order, but I'm not too sure how to go about this (and worry this could slow down the database).  It seems like manipulating the calculation field would be better, but don't know if it's even possible.  Thanks!

        • 1. Re: Unstored Calculation Field and Filtering Portals

          Your are correct that Case functions only return the specified result value for the first Boolean expression to evaluate as True.

          Which means that you'd need to use a different calculation to return multiple values instead of a single value. Such as:

          List ( If ( DM&DY = CM&CY;2 ) ; If ( DY=CY;1 ) ; If ( DY=CY-1;0))

          This returns a list of values separated by return characters. If record is same month, same year, you'd get:


          Same year, different months:

          Last year:

          You can then use Not IsEmpty ( FilterValues ( Expenses::Status Order ; 1 ) )  to filter for all records that are the same year regardless of same month or not.

          • 2. Re: Unstored Calculation Field and Filtering Portals

            Thanks Phil!  I can always count on you when I've reached my frustration point.  The only thing I changed was the filtering.  I used your formula, but since I applied it to filter the portal itself, I changed it to:

            not IsEmpty(FilterValues(Expenses::Status Order; Dashboard::Status Filter)), which works perfectly no matter which button is chosen.  Thanks again!

            • 3. Re: Unstored Calculation Field and Filtering Portals

              OK Phil - I have a new question but it's based off this portal, so I thought it would be easier to post it here then begin a new post.  If you have time to help out, I'd appreciate it.  If not, I will start a new post if that's easier.  Thanks!

              I created a pie chart (which is completely new to me) and basically want the data that the portal filters when Current Month, Current Year or Last Year are clicked.  I created a pie chart Title: "Expenses", Category Labels: Expense Category::Name, Slice Data: Case(not IsEmpty(FilterValues(Expenses::Status Order;Dashboard::Status Filter));Expenses::Total) *Expenses::Total is a summary field that totals up Expenses::Amount.  Then in Data Source I chose 'Related Records' for chart data and Related Table:Expenses.  I selected "Summarized groups of records".  

              This partially works.  When I click on 'Current Month' it only gives me one value at 100%.  Clicking on Current Year and also clicking on Last Year does return the pie chart in working order, with the totals for each expense category showing up with the correct percentage.  I'm stumped...  

              • 4. Re: Unstored Calculation Field and Filtering Portals

                Update: I was playing around, adding in some more test records, and now I realize that it only seems like the pie chart was filtering correctly according to respective buttons.  I now find that some categories will add together, and then others will show each record.  And it seems to have something to do with the Expenses::Amount field - When I would click on the Current Month button, expenses would total and show on the pie chart, unless it was an expense named Home Office for $110.  If I changed the amount, it would show up.  Weird.  Then when I would click on the Current Year, the Home Office items that were $110, would add together and the any Home Office items with a different total would show up as a new pie wedge.  Clearly I've confused something besides myself...

                • 5. Re: Unstored Calculation Field and Filtering Portals

                  The method that I recommended was for a filtered portal, not a chart. Charting, like other calculations references the data directly in the related table, by passing any defined portal filter. So an unfiltered portal based on your current relationship will show the data being used in your chart.