May 25, 2023 10:11 AM
I'm stuck on how to create this automation so any help is appreciated!
I have two tables:
Table 1: Each record corresponds to a purchase, with the following fields:
Table 2: Each record corresponds to a purchaser, with the following fields:
I have created a working automation that does the following:
When a record matches conditions: "Transaction Date" is on or after one month ago, find records in Table 2 where "Full Name" matches "Cardholder Name" in Table 1 AND update "Status" to Active. Essentially, If a purchase was made in the last 30 days in Table 1, this finds the record of the purchaser in Table 2, then changes their status to Active.
I also want the following automations but cannot figure out the logic behind them:
If a purchaser has not made ANY purchases in the last 30 days, set their status to Inactive.
If a new purchaser is added to Table 2, Set their status to New for 30 days EVEN if they have not yet made a purchase.
I have tried setting these desired automations up using conditional logic but always received errors pertaining to the conditions, so I scrapped them out of frustration lol
May 26, 2023 04:03 AM
Hmm, would you be interested in using linked fields, rollup fields and formula fields to handle this instead of automations? If so I can provide some suggestions. Best of luck either way
May 26, 2023 12:14 PM
Hi Adam, @TheTimeSavingCo, as long as it leads to my desired outcome, I am open to any method! I have a basic understanding of linked fields, rollups, and formula fields - Please let me know how I can use these to do so.
May 27, 2023 01:40 AM
Ah ok! I'm assuming all the "Purchase" records are linked to the correct "Purchaser"?
If so, in "Purchaser", add a rollup field that'll display the transaction date value, and use the formula "MAX(values)". This will display the date of the latest transaction
After that, add a formula field that will check whether the latest transaction date is within one month of today like you mentioned, and if it is, make it display "Active", and if not, to display "Inactive"
To handle the case of "New", we could try adding a "Created Time" field to your "Purchaser" table, and then add a formula field that would check whether the created time is within one month of today, and make it output "New" if so
The logic for "New" as well as the "Active", "Inactive" can all be consolidated into a single formula field but it'll depend on which takes precedence for display, and this would leave you with a single field that would display "Active", "Inactive" or "New" based on the criteria you've described
If you wanted it to be a single select field, you could add an automation that'll paste the value from the formula field into the single select field too