Automatically Link Based on Date range

[vshammah]

Can I create an automation (or formula field) to do the following?

When a record is created in the "Deals" table,  if the Move-in date of that record is within the range of the start & end dates of a record in the "Payments" table,  link the new Deals record to the particular record in the Payments table.

[Joseph_Roza]

1. The record in the ‘Deals’ tables would need to be linked to the record in the ‘Payments’ table. 

2. You would then use two lookup fields to look up the start and end date of the date range from the linked record of the ‘Payments’ table. 

3. Then you would add a formula field to calculate if the date of the ‘Deal’ record is within the range with a formula like this:

IF(
AND(
IS_AFTER({Target Date}, {Start Date}),
IS_BEFORE({Target Date}, {End Date})
),
"Within Range",
"Out of Range"
)

4. Set up an automation with a trigger of ‘When a record matches conditions’, and make the condition be the result of that formula field: ie. ‘Within Range’. And update the record’s linked record field with the ID of the record you want it to get linked to. 

[vshammah]

thank you.  I am not sure I get it.

in step 1, "The record in the ‘Deals’ tables would need to be linked to the record in the ‘Payments’ table. " do you link all records to a specific record in the payments table?  Does it matter which?  The purpose of the automation is to automatically link the record in the Deals table to the correct record in the payments table based on date range.  

I'm not following how to get the automation to do that.