Conditionally Linking Records

[Drew_Nemer]

Hello,

I am trying to assign the correct Payment Standard to our unit rent increase tracker.

CR Approvals is one table.

Payment Standard is my other table

I am trying to figure out if there is a way to automatically updating the PS in the CR table with the correct Payment Standard listed on the other table based on the city, effective date and the BR fields.

So a 1 BR, effective for 7/1/2022 in Boston would be assigned a PS of $2383.00.

I feel like this is an exercise that would work in Excel with a XLookup type of function or something but I am not sure how to make this work on Airtable.

Any thoughts?

[ScottWorld]

Unfortunately, that feature is not available in Airtable, but all of us have been requesting it for 5+ years. You can try to get your voice heard by sending an email to support@airtable.com. Good luck with that. May you succeed where the rest of us have failed….

[Kamille_Parks]

You could probably do this with an Automation assisted by a formula or two. Assuming your Standards table has an {Effective Start Date} and {Effective End Date} …

The Find Records step will let you search for records from your Standards table that have an effective date range covering the Approval record’s creation date, and is the correct city. I’m assuming you don’t have concurrent standards, so only one record should be found. Automations let you use dynamic values, however date fields can’t be inserted as dynamic values yet. The workaround for this is to create formula fields that convert each relevant date into a number than can be compared with simple math for “greater than/less than” instead of “after/before”. That formula could be DATETIME_FORMAT({Date Field}, 'YYYYDDDD').

This could be slightly. simpler if instead of an Effective End Date you just had a field that said “expired” or something. Then you’d only have to check for conditions where the City matches and the field does not say “expired”.

From there, I assume you have a field in Approvals that shows how many bedrooms there are. For each possible number (appears to be 6?) create a conditional action step in your automation that inserts the 1bed price if its 1 bedroom, the 2bedroom price if its 2beds, etc.

[ScottWorld]

Thanks for posting the automation solution, @Kamille_Parks! I wasn’t even thinking about automations, because I was too focused on taking the original question too literally: I wish that Airtable offered native VLOOKUP or XLOOKUP functionality, but it doesn’t.