So I have a table called "Dealerships" and a table called "Services". Each record in "Services" has a date, an amount, and a link to "Dealerships". I'm creating an interface that will aggregate these, so I can know the amount that came from each "Dealership". I know I can do this with a Rollup, but the problem arises when I try to use a Filter element in the Interface.
I have a "Dashboard" page and a "List" element sourced from "Dealerships". This list contains both the Dealership name and the rolled-up amount.
I need the user to be able to select a date range via a Filter and have the Rollup be updated to only add up the services that match those dates. I haven't been able to find a way to do this. The closest I came to a solution was to use 2 levels in the List, which actually DOES filter the services in the 2nd level but does NOT change the value in the Roll-up accordingly.
Any guidance would be greatly appreciated. Thanks!
Though I am a little unclear on the specifics, what you would do to filter data in the rollup is to create new columns with the filter parameters, like start, and end date, and a checkbox, then formulaically reference those columns inside of the rollup (with an if statement or another formula). Then in the interface you can create fields for the columns you created, and the value in the rollup field would change depending on user input
Thanks for the answer but that doesn't solve the issue, as what I'm trying to do is make the filtering dynamic.
The interface already has a Filter element, and by using 2 levels in the list, I'm able to show only the appropiate records. However, the Rollup total does not update accordingly, and continues to show the total Rollup.
Perhaps an image will help:
As you can see in the image, the Filter works for showing only the desired Services associated to a Dealership, but the Amount (which is a Rollup) is not updating accordingly. It is still showing the sum of all of the services associated.
What you would need to do is create a custom filter and not use the filter component. You would have a separate “Service Date” field where your user inputs his filter parameters and display it as a single text or date field in the interface. Then you would set conditions in your roll up based on the inputs in this field.
in order to have user specific options you would have a user table and have the service date field live in there then use a look up to bring it to the other table. In the interface you would create a record picker of the user, then connect other components to it, making them unique to the user