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!