Help

How to change a field based on time passed since a record was created

636 3
cancel
Showing results for 
Search instead for 
Did you mean: 
ncparker
4 - Data Explorer
4 - Data Explorer

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:

  • "Transaction Date" for the exact date the purchase was made
  • "Cardholder Name" for the purchaser's name displayed on their credit card.

Table 2: Each record corresponds to a purchaser, with the following fields:

  • "Full Name" is the purchasers name (exact same name as the "Cardholder Name" from Table 1).
  • "Status" is a single-select dropdown that has the three options: New, Inactive, Active.

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


3 Replies 3

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

ncparker
4 - Data Explorer
4 - Data Explorer

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.

 

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