Return value in field for record that matches value in linked table

Not sure if this is a formula issue but here goes.
I have a table with contract info that is linked to a table with contract billing info (i.e.billing period start date, end date and due date and payment status (invoiced, paid, overdue).
In the Contract table I have a rollup field that returns for each contract - the start date of the most recent billing period. I would like to now display the payment status of that most recent billing period. Is there a way to do this? I’ve tried the lookup value but I cannot set it to lookup status only for the max billing start date.

Unfortunately, Airtable makes this unnecessarily complex & cumbersome.

You have to go back into your billing info table and create a new lookup field that looks up the rollup date field that you just created in your Contracts table.

Then, while still in your billing info table, create a new formula field that compares the start date of that particular record to the lookup field that you just created. If the 2 values match, then the formula should result in the payment status of that record. (If the 2 values don’t match, it should result in nothing.)

Then, back in your Contracts table, create a new lookup field that looks up the new formula field that you just created.

1 Like

Agree -very clunky - BUT it Worked, so thank you very very much for this solution!. Not sure I would have figured this out on my own.

1 Like

You’re welcome! Glad I could help! :slight_smile:

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.