Filter Based on Range of Values in Two Fields

Question

Hi, I am new to using Experience Builder and not sure if what I want to do is possible. I am creating an app that shows rental locations with a list of renters in a related table. The related table contains the start and end years of the rental period in separate fields as integers (Start_Year and End_Year).

I would like the user to be able to select a single year and filter a list to display all renters whose rental period contains that year. Essentially, if the user input year is within the range of Start Year to End Year, filter the list to those records.

It seems like if I want to filter the data this way, I have to set up the filter to require selecting the year twice as input for both year fields. Which works in this case, but is a more cumbersome experience.

I want thisinstead of this

Is it possible to do this?

TonghuiMing · Answer

@Meredith_Keraga

The data structure you have contains two fields, while the request is to filter two fields based on one value.

If the logic is equal, then you may achieve this by using the group filter feature; however, in this case, one is >= and the other is <=, which the group filter couldn't achieve either.

For the OOTB case, unfortunately, it might not have a better solution. You may want to go with the custom widget route on this for now.

NicoleJohnson · Answer

How many years out do the rental terms end, and is anyone willing to add years, or does it need to be entirely hands-off once you make the experience?

If it's not too many years and someone's willing to update the experience, one idea is to set up individual filters for each start/end year. This would look something like this (ignore the values not being exclusive):

Basically, you have one filter statement for each same-year combination. For the sample data I'm working with, I'm just using one field, but you can just as easily look at two fields by adding a condition.

I'm using "is between" as the operator because there are lots of potential values that could fall in this range. Since you're looking at years, you can use the "is" operator with the "unique" source type.

By using the configuration above, you can give the user buttons to click on, rather than making them select from a list (this is VERY quick and not meant to look nice):

The user clicks, and the list filters:

Happy to help with any questions you might have!