Automation to Link a record based on date

[ted_ellis]

Hi, 

I have 2 fields:

Transaction Date - date field

and Month (For Reports) - linked field

What I want is whenever a date is set under transaction date, it should automatically link to the corresponding Month - linked field. 

I know this is doable but I can't seem to wrap my head around how to do this. I hope someone can help me figure this

[RdMedia_srl]

Hi,

In your "transaction table" you should create an hidden formula field with this code "DATETIME_FORMAT(Date, 'MM')"

where Date is the field where you have the transaction date

You need a table with all the 12 month, like

ID - Name
01 - Jan
02 - Feb
03 - Mar
04 - Apr

then  you need to create an automation that trigger  when a new transaction is created in the "transaction table"

and then 2 action

Find records (where Name il "select month")

Update record with the ID of the month of the previous action

I hope is clear, let me know if works 🙂

[TheTimeSavingCo · Accepted solution]

If I were you I'd create a formula field that would create the name of the record I want it to be linked to, so:

"BUSINESS " & DATETIME_FORMAT(Date, "DD.MMMM YYYY")

Then I'd have an automation that would trigger if the "Transaction Date" field was not empty and the "Month (For Reports)" was empty, and its action would be to paste the value from the formula field into the `Month (For Reports)` field

This would do what you're looking for I believe

[ted_ellis]

Ahh i knew it was simple. Thank you so much for this input!