Conditionally Linking Records

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….

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.

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.