I have a product that expires in one year. If they purchase an upgrade, it extends the expiration another year. The records are added individually to Airtable. How do I say IF this email address has product A, check to see if it also has product B in another record?
Solved
How do I say IF this email address has product A, check to see if it also has product B
+4Best answer by Arthur_Tutt
Hey @Shelly_Criswell ! Alright good news, I think I finally understand what you're looking for and developed an automation solution for you. It's a little complicated, but I made a step by step Loom Video here to explain how it works. Here's my example:
*** remember to change my Table / Field / Variable names to match yours exactly or it won't work ***
Table Setup And Expected Output (circled in blue):
Field Formulas:
Automation Trigger:
Script Input Variables:
Automation Script:
// get Purchases Table
var purchaseTable = base.getTable("Purchases"); //UPDATE TO MATCH YOU TABLE NAME
var purchaseQuery = await purchaseTable.selectRecordsAsync({fields: ["Buyer Email", "Product (Hide)", "Transaction Type (Hide)", "Record ID"]}) //UPDATE TO MATCH YOU FIELD NAMES
var purchaseRecords = purchaseQuery.records;
var inputConfig = input.config();
var productName = inputConfig.productName;
var transactionType = inputConfig.transactionType;
var buyerEmail = inputConfig.buyerEmail;
var recordID = inputConfig.recordID;
var purchaseProducts = "";
if (productName == "PR Starter Pack" && (transactionType == "Sale" || transactionType == "Rebill")) { //UPDATE TO MATCH YOU VARIABLE NAMES
for (var i = 0; i < purchaseRecords.length; i++) {
if (buyerEmail == purchaseRecords[i].getCellValue("Buyer Email") && recordID != purchaseRecords[i].getCellValue("Record ID")) {
purchaseProducts = `${purchaseProducts} | ${(purchaseRecords[i].getCellValue("Product (Hide)"))}`
}
}
}
console.log(purchaseProducts)
// Updating Purchased Data
var updates = [{
"id": recordID,
fields: {
"Purchased Other Products?": purchaseProducts
}
}]
console.log(updates)
await purchaseTable.updateRecordsAsync(updates);
Let me know if this works for you!!
+15Hey @Shelly_Criswell ! can you provide a screenshot of your table(s)? Helps me to visualize your setup
+17Two ways to approach this:
1. Nested IF
IF(Condition A, IF(Condition B, True, False), False)
2. Using the AND() function. This checks to see if multiple conditions are satisfied before returning a True or False value.
Ex. AND(1 = 1, 2 = 2) would return True
AND(1 = 1, 2 = 1) would return False
+4Arthur_Tutt wrote:
Hey @Shelly_Criswell ! can you provide a screenshot of your table(s)? Helps me to visualize your setup
@Arthur_Tutt If they purchase the PR Starter Pack they have the option to purchase the Go Custom. If just the PR Starter Pack, they have access for 1 year. If they upgrade, they get an additional year.
+4Kenneth_Raghuna wrote:
Two ways to approach this:
1. Nested IF
IF(Condition A, IF(Condition B, True, False), False)
2. Using the AND() function. This checks to see if multiple conditions are satisfied before returning a True or False value.
Ex. AND(1 = 1, 2 = 2) would return True
AND(1 = 1, 2 = 1) would return False
@Kenneth_Raghuna thank you for responding. How would I write that out? Here's a screenshot if that is helpful.
+15@Shelly_Criswell a few more clarification questions.
So firstly, there's just 1 purchase per record, correct? Something like:
shelly@email.com => PR Starter Pack
shelly@email.com => Go Custom
And then you want to run a script that checks the first record, sees that shelly bought the PR Starter Pack, and then checks all other records to see if she also bought the Go Custom? And what kind of output are you looking for? Do you want a field that says "Purchased Another Product" and then just "Yes / No", or you want it to list all other products they purchased?
Formula calculations can only be done within a single record, so if the data is spread out across multiple records will need to be some kind of automation.
I'm sure your question sounded simple initially, but getting into the weeds gets a bit complicated 🙂
+4Arthur_Tutt wrote:
@Shelly_Criswell a few more clarification questions.
So firstly, there's just 1 purchase per record, correct? Something like:
shelly@email.com => PR Starter Pack
shelly@email.com => Go Custom
And then you want to run a script that checks the first record, sees that shelly bought the PR Starter Pack, and then checks all other records to see if she also bought the Go Custom? And what kind of output are you looking for? Do you want a field that says "Purchased Another Product" and then just "Yes / No", or you want it to list all other products they purchased?
Formula calculations can only be done within a single record, so if the data is spread out across multiple records will need to be some kind of automation.
I'm sure your question sounded simple initially, but getting into the weeds gets a bit complicated 🙂
Hey @Arthur_Tutt yes, you're correct it's in separate records, like your example.
And yes, some way to notate when a PR Starter Pack student purchases a Go Custom... I was afraid it was more complicated than I thought. 😩
+17Shelly_Criswell wrote:
Hey @Arthur_Tutt yes, you're correct it's in separate records, like your example.
And yes, some way to notate when a PR Starter Pack student purchases a Go Custom... I was afraid it was more complicated than I thought. 😩
I understood the problem a little differently initially.
From what it sounds like, the best solution would be an automation, and might even require a script depending on how your sales process itself is structured.
I'm pretty busy today but if you want to shoot me an email I can probably hop on a virtual call tomorrow and give you some assistance:
dillon@bootysattva.productions
+15Hey @Shelly_Criswell ! Alright good news, I think I finally understand what you're looking for and developed an automation solution for you. It's a little complicated, but I made a step by step Loom Video here to explain how it works. Here's my example:
*** remember to change my Table / Field / Variable names to match yours exactly or it won't work ***
Table Setup And Expected Output (circled in blue):
Field Formulas:
Automation Trigger:
Script Input Variables:
Automation Script:
// get Purchases Table
var purchaseTable = base.getTable("Purchases"); //UPDATE TO MATCH YOU TABLE NAME
var purchaseQuery = await purchaseTable.selectRecordsAsync({fields: ["Buyer Email", "Product (Hide)", "Transaction Type (Hide)", "Record ID"]}) //UPDATE TO MATCH YOU FIELD NAMES
var purchaseRecords = purchaseQuery.records;
var inputConfig = input.config();
var productName = inputConfig.productName;
var transactionType = inputConfig.transactionType;
var buyerEmail = inputConfig.buyerEmail;
var recordID = inputConfig.recordID;
var purchaseProducts = "";
if (productName == "PR Starter Pack" && (transactionType == "Sale" || transactionType == "Rebill")) { //UPDATE TO MATCH YOU VARIABLE NAMES
for (var i = 0; i < purchaseRecords.length; i++) {
if (buyerEmail == purchaseRecords[i].getCellValue("Buyer Email") && recordID != purchaseRecords[i].getCellValue("Record ID")) {
purchaseProducts = `${purchaseProducts} | ${(purchaseRecords[i].getCellValue("Product (Hide)"))}`
}
}
}
console.log(purchaseProducts)
// Updating Purchased Data
var updates = [{
"id": recordID,
fields: {
"Purchased Other Products?": purchaseProducts
}
}]
console.log(updates)
await purchaseTable.updateRecordsAsync(updates);
Let me know if this works for you!!
+17Arthur_Tutt wrote:
Hey @Shelly_Criswell ! Alright good news, I think I finally understand what you're looking for and developed an automation solution for you. It's a little complicated, but I made a step by step Loom Video here to explain how it works. Here's my example:
*** remember to change my Table / Field / Variable names to match yours exactly or it won't work ***
Table Setup And Expected Output (circled in blue):
Field Formulas:
Automation Trigger:
Script Input Variables:
Automation Script:
// get Purchases Table
var purchaseTable = base.getTable("Purchases"); //UPDATE TO MATCH YOU TABLE NAME
var purchaseQuery = await purchaseTable.selectRecordsAsync({fields: ["Buyer Email", "Product (Hide)", "Transaction Type (Hide)", "Record ID"]}) //UPDATE TO MATCH YOU FIELD NAMES
var purchaseRecords = purchaseQuery.records;
var inputConfig = input.config();
var productName = inputConfig.productName;
var transactionType = inputConfig.transactionType;
var buyerEmail = inputConfig.buyerEmail;
var recordID = inputConfig.recordID;
var purchaseProducts = "";
if (productName == "PR Starter Pack" && (transactionType == "Sale" || transactionType == "Rebill")) { //UPDATE TO MATCH YOU VARIABLE NAMES
for (var i = 0; i < purchaseRecords.length; i++) {
if (buyerEmail == purchaseRecords[i].getCellValue("Buyer Email") && recordID != purchaseRecords[i].getCellValue("Record ID")) {
purchaseProducts = `${purchaseProducts} | ${(purchaseRecords[i].getCellValue("Product (Hide)"))}`
}
}
}
console.log(purchaseProducts)
// Updating Purchased Data
var updates = [{
"id": recordID,
fields: {
"Purchased Other Products?": purchaseProducts
}
}]
console.log(updates)
await purchaseTable.updateRecordsAsync(updates);
Let me know if this works for you!!
Nice Solution @Arthur_Tutt .
Regarding reading data from Select fields: You can eliminate the need for translating to plain text in the table by either using the getCellValueAsString() method, or accessing the "name" property within the object.
Examples of each:
getCellValueAsString("Product")
getCellValue("Product").name
You can also get the record ID within the script by accessing the "id" property:
purchaseRecords[i].id
If the logic flow for the sales transactions themselves only allow for purchasing the "Go Custom 1" AFTER purchasing the "PR Starter Pack", you can significantly reduce the number of runs of the automation by changing the trigger to seek transactions where "Go Custom 1" was purchased. This will require the script to be updated but would pay off in base performance loads as the base grows.
@Shelly_Criswell : In order to translate Arthur's solution into extending the expiry dates, you can have the script output a check into a checkbox field titled something like "Extension Purchased" (whatever you want, just make sure it's consistent in the script).
Then you'll need to modify your "Expiration" field. I'm assuming it's just a formula that adds one year to the Purchase date. You'll want to change it to either a SWITCH() or IF() formula that checks to see if the box in "Extension Purchased" is ticked. If it is, add two years, if not, add one year.
Please note though, that if this log is used to track transactions that may repeat over time, the solution will break.
ie. A client that had a PR Starter Pack + "Go Custom 1" extension decides to purchase just the PR Starter Pack 2 years later. The solution as is would see the old purchase of the extension and add it to the expiry for the newly purchased PR Starter Pack.
+15Ah @Kenneth_Raghuna good point! I'd forgotten about dot operating into the object. Well spotted 🙂
And yeah, there's still a lot of uncertainty around the logic or which purchases count and which don't, so lot's of room to further optimize.
+4Arthur_Tutt wrote:
Hey @Shelly_Criswell ! Alright good news, I think I finally understand what you're looking for and developed an automation solution for you. It's a little complicated, but I made a step by step Loom Video here to explain how it works. Here's my example:
*** remember to change my Table / Field / Variable names to match yours exactly or it won't work ***
Table Setup And Expected Output (circled in blue):
Field Formulas:
Automation Trigger:
Script Input Variables:
Automation Script:
// get Purchases Table
var purchaseTable = base.getTable("Purchases"); //UPDATE TO MATCH YOU TABLE NAME
var purchaseQuery = await purchaseTable.selectRecordsAsync({fields: ["Buyer Email", "Product (Hide)", "Transaction Type (Hide)", "Record ID"]}) //UPDATE TO MATCH YOU FIELD NAMES
var purchaseRecords = purchaseQuery.records;
var inputConfig = input.config();
var productName = inputConfig.productName;
var transactionType = inputConfig.transactionType;
var buyerEmail = inputConfig.buyerEmail;
var recordID = inputConfig.recordID;
var purchaseProducts = "";
if (productName == "PR Starter Pack" && (transactionType == "Sale" || transactionType == "Rebill")) { //UPDATE TO MATCH YOU VARIABLE NAMES
for (var i = 0; i < purchaseRecords.length; i++) {
if (buyerEmail == purchaseRecords[i].getCellValue("Buyer Email") && recordID != purchaseRecords[i].getCellValue("Record ID")) {
purchaseProducts = `${purchaseProducts} | ${(purchaseRecords[i].getCellValue("Product (Hide)"))}`
}
}
}
console.log(purchaseProducts)
// Updating Purchased Data
var updates = [{
"id": recordID,
fields: {
"Purchased Other Products?": purchaseProducts
}
}]
console.log(updates)
await purchaseTable.updateRecordsAsync(updates);
Let me know if this works for you!!
Arthur,
I am BLOWN away at how much effort and thought you put into this! 🤯 I cannot thank you enough! Thank you for the loom, screenshots, and script! So amazing!!! Thank you thank you thank you!
1 person likes this
+4Kenneth_Raghuna wrote:
Nice Solution @Arthur_Tutt .
Regarding reading data from Select fields: You can eliminate the need for translating to plain text in the table by either using the getCellValueAsString() method, or accessing the "name" property within the object.
Examples of each:
getCellValueAsString("Product")
getCellValue("Product").name
You can also get the record ID within the script by accessing the "id" property:
purchaseRecords[i].id
If the logic flow for the sales transactions themselves only allow for purchasing the "Go Custom 1" AFTER purchasing the "PR Starter Pack", you can significantly reduce the number of runs of the automation by changing the trigger to seek transactions where "Go Custom 1" was purchased. This will require the script to be updated but would pay off in base performance loads as the base grows.
@Shelly_Criswell : In order to translate Arthur's solution into extending the expiry dates, you can have the script output a check into a checkbox field titled something like "Extension Purchased" (whatever you want, just make sure it's consistent in the script).
Then you'll need to modify your "Expiration" field. I'm assuming it's just a formula that adds one year to the Purchase date. You'll want to change it to either a SWITCH() or IF() formula that checks to see if the box in "Extension Purchased" is ticked. If it is, add two years, if not, add one year.
Please note though, that if this log is used to track transactions that may repeat over time, the solution will break.
ie. A client that had a PR Starter Pack + "Go Custom 1" extension decides to purchase just the PR Starter Pack 2 years later. The solution as is would see the old purchase of the extension and add it to the expiry for the newly purchased PR Starter Pack.
Hey Kenneth,
Wow, thank you as well! I'm going to work on this next! I really appreciate your reply!
1 person likes this
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.