Interactive date-range filtering of lookup column

1

Hi all! I’m trying to come up with an easy way for a non-profit client to pull a list of donors from a particular date range (i.e., their fiscal year, which almost never follows the calendar year). Here’s the rundown:

  • I have two tables: 1) Donors and 2) Donations.
  • I have a reference column in the donor table called “Donations” which pulls in each person’s donations.
  • I want to see only people who’ve given donations between two specific dates.
  • I want to use Interactive Date Pickers to do this so my client can change the filters dates without having to go into the filter panel.

I know I can easily run a filter on the “donations” table but the purpose of this is to pull a clean list of names for their marketing materials. Because many people give donations multiple times a year, pulling the list from the “donations” table does not produce a clean list. They’d have to go through a second step of removing duplicates from their list when Suzie or Joe shows up 10 times.

Seems simple but I can’t figure out how to do it. Here’s my sample doc, which is editable. If you know the trick, please help!

2

Hello @Kelly_Claus!
I managed to get it working by checking each date with the help of a FormulaMap()

If you use daterange:

 thisRow.Donations.Date.FormulaMap(
   CurrentValue.matches([daterangepicker 1])
 ).Contains(true)

If you use start and end dates:

thisRow.Donations.Date.FormulaMap(
  CurrentValue>=[Start Date] and CurrentValue<=[End Date]
).Contains(true)

If you want to make it easy when selecting dates you could add a button so the End Date gets calculated with the Start Date.

I made the changes directly to your doc showing the changes.

Cheers!

2 Likes
3

@Saul_Garcia Amazing!!! Thank you so much, you legend, this is exactly what I needed!

1 Like