Filter all dates from linked field

[Nils_Westhoff]

I want to count all linked dates per specific users, but can’t figure out how to do it.
My use case is different from the feature request to filter visible fields when selecting (I think).

I have several users with a specific (to them) date. All users have multiple linked dates. I would like to count all dates that are after the specified date.

visual representation (currently Count also contains dates before paymentDate)

I’m able to filter fields that have a single linked date using the following formula: IS_AFTER(dates, paymentDate), but it throws #ERROR when there’s multiple linked fields.

In my application I am able to filter specific records using a similar formula, but this is applied to each of the entries, not the rollup as a whole (which is what I’m looking for).

Is this possible? thanks in advance!

[Jeremy_Oglesby]

Hi Nils,

This is possible, but you have to do a little shuffling of data. I’m going to assume that the table you shared int he screenshot is called {People}, and the table the linked dates come from is called {Dates}.

---

Step 1:
Create a “Lookup” field in {Dates} that Points at the {People} table and the “paymentDate” field. Now each Date in the {Dates} table will know the “paymentDate” of the Person to which it is linked.

---

Step 2:
Create a “Formula” field in {Dates} called “isAfter” with something like this:

IF(
   IS_AFTER({Date}, {paymentDate Lookup}),
   1
)

Now each Date in the {Dates} table labels itself as either Before the “paymentDate” (0), or After the “paymentDate” (1).

---

Step 3:
Create a “Rollup” field in {People} that points at the {Dates} table and the “isAfter” field, using the SUM(values) rollup function. Now you have the total number of linked dates that fall after the specified date for each Person in your {People} table.

[Nils_Westhoff]

Hi Jeremy, thanks a lot for your quick response!

Unfortunately, this does not seem to work in my case, as each date could have multiple attendees (linked from people table) as well, which throws an #ERROR again :frowning:

If you’ve got a solution for this as well, I’d love to hear it!

[Jeremy_Oglesby]

I might have a solution, but it’s pretty convoluted… Do you have a reasonable expectation of the maximum number of people you might ever, in the future, link in the attendees field?

[Nils_Westhoff]

Currently I’m at 55, it might go up a little, but won’t go over a hundred any time soon. Does that sound reasonable? I’m interested in your approach, though I also feel like this should be easier… :grinning_face_with_sweat:

[Jeremy_Oglesby]

Ya, with that many linked records my initially imagined approach would be entirely too cumbersome.

Another, more reasonable, approach would be to use a third table in-between – a join table. I assume the dates are representing some sort of meeting or event. You have a table for People already. So let’s make the third table called “Attendance Records”.

Table 1 = People (1 record for each Person)
Table 2 = Events (1 record for each Event, with its “Date” as a field)
Table 3 = Attendance Records (1 record for each instance of a Person attending an Event)

Now you can use Lookups to pull the Event Date and the Payment Date into the Attendance record and make the comparison within the Attendance record. Then you can use Lookups/Rollups in either the People table or the Events table (or both) to determine/report payment status.

In this case, your People table will be pretty static (add or remove people as they come or go).
Your Events table will only have 1 record added per Event.
You could work FROM WITHIN your Events table to add Attendance Records (hit enter in the linked record field, then select “+ Add new record”) and add a Person to each new Attendance Record you make. It may sound like a lot of extra work, but it’s really like 2 extra key presses per person you want to add to an Event, so it’s not that bad once you get the workflow down.