Iterating Through Data Within "Link" Field

[Hunter_Casillas]

Hey there,

I have a list of records that include a “Link” field that contains multiple records from a separate table. Each one of these records within the “Link” field contains a field with a date. I have used a rollup field to determine all of the dates of those records and display them.

My question is, how can I iterate through all of those dates and get a count of how many == the criteria I will be calculating?

Thank you so much for your responses.

[Julian_Kirkness]

Hi @Hunter_Casillas

If I understand you correctly, I think the solution would involve calculating whether the records on the many side of the link meet the criteria within their own table with a formula which returns a 1 if they do and a 0 if not. You may need to use a lookup field in this table to get that value of the criteria into the ‘sub table’ records.

You would then be able to use a rollup field in the ‘parent’ table to sum the value of your calculated field which would give you a count of those matching the criteria.

Hope this helps - come back with more details it not!

[Hunter_Casillas]

I think you understand the question, I probably just don’t understand the answer. Here is a visual picture of what I’m trying to do: gyazo. com/90fb63c1d474b081eae47c03809dcb24 (Sorry if I’m not allowed to post links, I just couldn’t post a picture either)

I have all of the times, but I’m confused as to if I should be doing some sort of formula within the rollup field shown in the picture, or if I should be doing a separate formula. If so, what should this formula even look like?

[Julian_Kirkness]

Hi

Where does the criteria ‘x’ come from - and is the result you want a count of records or a list of matching donations?

Julian

[Hunter_Casillas]

So I want to get the DateTimeDiff between the “Link Date” and “Opt-In Date” (another date field I have).

If DateTimeDiff < 1 do X
If DateTimeDiff > 1 do X

I understand how to do the IF statements, but I am unsure as to how I would get a count of the amount that fit Criteria A vs Criteria B.

Generally in Java I would just increment a variable based on which condition was true, is there some type of alternative to do it within AirTable?

[Julian_Kirkness]

I am assuming the dates are both in the Child table - if one is in the parent then you can use a lookup field to get it…

You could then have a formula field int he child record with the if test based on the two dates and return a 1 or 0. You can then use a rollup field in the parent record to add up the value to give you the count of those matching the criteria.

Next, with the records linked to a parent (I linked it to a Person), create a rollup to sum the formula field in the child records:

[Hunter_Casillas]

Awesome, this is really helpful thank you! I’m really close to making this thing work.

So how would I go about doing this calculation for all of the values at once? In your example you show Date A and Date B, but my function would be more like IF(DATETIME_DIFF([Array Of Dates], {Date B}, ‘days’) > 1 “blah blah blah”).

[Julian_Kirkness]

I had assumed your dates were coming from another linked table and then you do the calculations there

[Hunter_Casillas]

Well this was the formula I tried within that rollup of dates:

if(DATETIME_DIFF(values, {Opt-in Timestamp}, 'days') < 1, "0", "1")

I was only given one output, instead of the expected multiple outputs, considering I have multiple days.