Out of stock product suggestion script?

[William_Sweet]

I’m using airtable as a way to keep a product catalog for my company, both out of stock, and currently available products are listed, as to help customers compare products they like but may be out of stock, against our current inventory. Is there a way to script some code to identify specific markers (Flavors, similar categories, etc.) based on user selection/input, and for airtable to automatically recommend a similar product? I’m not well versed in coding, so any assistance would be appreciated!

[TheTimeSavingCo]

Hi @William_Sweet, could you provide more details on how you would want everything to work together?

Specifically, who would be keying in this input, and what would they be using it for afterwards?

What you’re asking for is definitely doable via a scripting extension, but depending on your use case you may be able to do it via automations instead. Given you’re not too well versed in coding, I would recommend going down the automation route instead (assuming it’s possible given your workflow, of course)

[William_Sweet]

Thank you @Adam_TheTimeSavingCo

I am absolutely open to automations as well if you think that route might be easier, but let me explain the best I can how I currently have my Airtable set up.

Currently I have every product that we carry (even if it’s not in stock) as our stock changes at least somewhat every few days. I have all of the products in our catalog tied to simple checkbox, to indicate whether or not I have a product on hand. I also have 9 different attributes for each product (flavor, smell, type, vendor, ect). The task my sales team has each day, is to conduct a consultation to determine which product is best for them based on those 9 different attributes.

What I imagine is that if my sales team narrows down to a product we don’t currently have on hand, could they (either through code or an automation run) select X product, and then airtable find the closest comparable item that’s in stock, and has as many similar attributes as the one selected?

Any and all suggestions would be most appreciated!

[TheTimeSavingCo · Accepted solution]

Ah, hmm. I think it’s possible to use an automation for this actually

I would:

1. Create a new table called Search Helper or something
2. Create a link between the Products table and Search Helper and call it “Rollup” or something
3. Link all the records in Products to a single record in Search Helper
4. Create a new link field between the Products table and Search Helper and call it “Item to match against”
5. Create lookups in the Products and Search Helper tables to pull the attributes of the product we want to match against through
6. Use formulas to match the attributes and output the total number of matches in Products
7. Use a rollup field to find the highest match number in Search Helper
8. Use a lookup with a conditional based on the previous step to output the highest matching products

The workflow for the sales team would then be:

1. Go to the Search Helper table
2. In the Item to match against link field, select the item that’s out of stock that they want to find the next closest thing for
3. Look at the lookup field mentioned in step 8 above

I’ve put it together here for you to check out and you can duplicate it to see formulas, automations etc

Pros: It’s all automations, so you can make changes yourself
Cons: It’s a pretty intense setup, and involves linking all your Product records to a single record. This might slow down your base, but you generally should be fine unless you’ve got tens of thousands of products?

---

If you’re using the scripting extension for this, the workflow would pretty much be a button in the Products table that said “Find Matches” or something, and then you’d click it and it’d output a list of the highest matches possible

I can’t really provide much advice for this bit as it’s just code, really. Sorry about that

Pros: No set up (since you’d be paying someone to do it)
Cons: You’d be paying someone to do it heh. And if you ever needed changes you probably would need to pay someone too unless you learned JavaScript yourself

If you’d like to go down the script route, you could hire me to do it! (If you’d rather use the non-script solution I linked above instead and don’t want to set it up yourself you can hire me to do that for you too!)

[William_Sweet]

Okay great! Thank you so much for the advice! I’m going to speak with my team, and we’ll email you if we require your further services.

[William_Sweet]

@Adam_TheTimeSavingCo could you give access to that table so we can open up the formula to present to our superiors? It would help us make a case for this system. thanks!

[TheTimeSavingCo]

Hey @William_Sweet, to view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button

I can also invite you to the base if you’d like; send me a private message with your email and I’ll do that

[William_Sweet]

Hi again @Adam_TheTimeSavingCo

the table and functions you wrote are excellent! i am just having a problem with changing the names from “attribute 1,2,3, etc.” to the actual names within my airtable. when I change the names, some of the “match” function accepts it, but other parts dont. it keeps telling me that im missing an open parenthesis somewhere, but I have no idea where.

IF(
{Item to Match Against - Search Helper} = “”,
IF(
{Strain} = {Strain (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Classification} = {Classification (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Lineage} = {Lineage (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Effects} = {Effects (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Flavor & Aroma Profile} = {Flavor & Aroma Profile (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Helps With} = {Helps With (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Cultivated vs Curated} = {Cultivated vs Curated (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Active?} = {Active? (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{N2 Can Profile} = {N2 Can Profile (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Terpene Profile} = {Terpene Profile (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{Vendor} = {Vendor (from Item to match) (from Search Helper - Rollup)},
1,
0
) +
IF(
{THC %} = {THC % (from Item to match) (from Search Helper - Rollup)},
1,
0 +
)
)

[TheTimeSavingCo]

HI @William_Sweet , that looks fine actually. Could you invite me to your base so I can troubleshoot it from there?