Finding most recent date across linked table

[Benrobot26]

Hi all,

As a new AirTable user, I am seeking assistance in creating a base to support my father's nonprofit organization in managing data. Currently, I have two relevant tables: 1) "Foundations Records" which contains information on grant agreements and applications with various foundations, and 2) "Foundations" which holds specific details about each foundation (e.g., related records, total funded amount, contact information). I would like to add a field that displays the most recent award granted by each foundation. While attempting this, I may have complicated the process, but I will explain my approach.

Firstly, I created a lookup field in the "Foundations" table to gather all records associated with that foundation from the "Foundations Records" table. Additionally, I created a lookup for the year corresponding to each record related to the foundation using the same linked table relationship. My next step was to create an IF formula field that would use the highest numbered year and display the corresponding award amount. Unfortunately, this resulted in an error, and I was unable to find a suitable workaround.

Therefore, I kindly request your assistance. Any help provided will be greatly appreciated, and I promise to buy you a beer as a gesture of gratitude.

Thank you in advance.

[ScottWorld]

Unfortunately, Airtable doesn’t natively offer the feature that you’re looking for, even though it is one of the Top 10 requests that customers have been requesting for years.

There is only one workaround to do what you’re looking to do, and I discuss it in this episode of the BuiltOnAir podcast.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with your Airtable base, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Sho]

Hi, @Benrobot26 

So you mean to extract the amount for the most recent year for each foundation?

Try these two fields

Field name: Latest year position
Type: formulas
Description: Get the position of the latest year from the Lookup field
Formula: 

FLOOR(SEARCH(MAX({Grant Date}) & "",ARRAYJOIN({Grant Date}))/5)+1

Field name: Latest year amount
Type: formulas
Description: extract amount based on year position from Award's Lookup field
Formula: 

VALUE(
  SUBSTITUTE(
    REGEX_EXTRACT(
      ARRAYJOIN({Award)}),
      "([^,]*,?){" & {Lates year position} & "}"
    ),",",""
  )
)

Combining these two fields into one will not work with an error.
Is it a bug?

[TheTimeSavingCo]

Hmm, check out this base

If this looks like what you want, the base setup was:
1. Have all the records linked to a single record called `Rollup`
2. In the `Rollup` table, create a rollup field on the `Created Date` value from `Table 1` with the formula `MAX(values)`
- This will give us the latest date
3. In `Table 1`, create a lookup field to pull over the most recent` Created Date` from the `Rollup` table
4. Use a formula field to check the `Created Date` value against the most recent date pulled in via step 3

You should be able to get the result you want from modifying the setup above.  If you DM me an invite link I can set it up for you real quick too

[pressGO_design]

I think that part of your issue is that the thing that you're calling a date (the single-select field with the year in it), isn't something that Airtable sees as a date, so it's not helping find the most recent date, so you can't use that date to help you find the amount.

I would suggest creating a Fiscal Year table with start and end dates and then watching @ScottWorld's workaround to get you where you want to be. 

Edited to add link to base that you can copy and play with.