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

Topic Labels: Formulas
1023 3
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

3 Replies 3

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.

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.

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