Count records after a date

[Jeff_Hay]

I have a base with two tables:

“People” table -> Contains a manually entered “last special date” field
“Matches” table -> Contains records for matches. Each record has a date the match took place and a link field linking to multiple people (typically 2-8 per match)

What I would like to do is to have another field in “People” that tracks the number of matches the person took place in since “last special date”. Essentially, I’d like a SUM(values) Rollup of match dates, but only for matches with dates after the “last special date” for each person.

I can’t find a way to do this… any suggestions?

Thanks!

[W_Vann_Hall]

I can’t think of any way other than to brute force a comparison of {LastSpecialDate} with the date of every match up through the maximum number of matches in which an individual would be expected to participate.If this maximum is, oh, 5 or 10, it’s a workable solution. If the maximum is 500, or if there is no maximum, brute force won’t work.

The problem is Airtable’s current lack of support for processing loops: There’s no way to write code to do something like

FOR(
    {LoopCount,}
    1,
    {NbrOfMatches},
    IF(
        IS_AFTER(
            {MatchDate},
            {LastSpecialDate}
            ),
        +=1
        )
    )

[Again, the preceding is not a valid Airtable formula.]

As a result, to compare a thing with each item in an array¹ of other things, you have to retrieve the array as a string and then step through the string. Each individual item must be referenced explicitly; that is, if you need to support up to 5 matches, your formula must have a section that addresses the first match date in the string, followed by a section that checks to see if there are at least two match dates in the string and, if so, addresses the second match date, followed by a section that checks to see if there are at least three match dates in the string — and so on. Since you want to compare dates, each extracted match date (which will be in the form of a string) will have to be converted to a date using DATETIME_PARSE() before the call to IS_AFTER().

If {MaxMatches} is a reasonable number, but you have trouble implementing the [not-really-a-]loop, let me know and I’ll see what I can toss together.

---

1. For instance, the list of linked records in a linked-record field is returned as an array.

[Jeff_Hay]

Thanks. That makes sense… and got me thinking about what I really want and if there’s another way to get to what I want. The number of matches can get pretty huge, so a brute force implementation is probably impractical (and not something I’d want to write :winking_face: ). However, if I can guarantee that LastSpecialDate is always the date of a match (would it should be), then I think I can get there:

I made a Rollup of the match dates with ARRAYJOIN(); this gives me a field that is an array of UTC date/time strings of every match the person has been in, and I know every date/time in that array is 24 characters long (25 with the comma). Then I have a field with this formula:

LEN(REPLACE({MatchDates}, 0, FIND({LastSpeicalDate}, {MatchDates}), ""))/25)

This finds the location of LastSpecialDate in the array, takes the substring from there to the end of the array, and divides by 25 to see how many elements we’re left with. This relies on LastSpecialDate being the date of a match, and on the rollup field being sorted in increasing date order (which it always seems to be?). But seems to be working well enough for what I need now.

Thanks for the help!

[W_Vann_Hall]

That’s a pretty damn ingenious solution to the problem. Even had I known that {LSD} could be assumed to have been a {MatchDate}, I doubt I would have twigged to that method. I’m going to bookmark this to steal later.! :winking_face: