Skip to main content

Complex inventory

H

Hello,  

I'm working on an inventory system.  Paired with AirPower, it connects to our shopify storefront to upload on the base what we have in stock on our website.  Once I get this finalized, it will also work in reverse.  We have individual items and then packages with multiple different items within it.  I have made a separate tab with Compositions for my mixed packages based on this discussion.    Thus, with sticking in the structure of Airpower, I have one tab for ALL products, one tab with variants that holds the inventory, and another tab (Compositions) that connects all the individual items (within variants) to its mixed packages (also within it's variants) and associates individual item quantity in each package.  

All this being said, I now am coming to a standstill.  I want to write a formula that is equivalent to saying "If the package (linked) is the same, and the quantity available of the individual item (also linked) = 0, than the package= 0."  In other words, if the individual item on the website is sold out, mark the packages that have that individual item as sold out too.  I have played with a bunch of different if() formulas as well as ifsame() formulas which I realize after consulting the formula reference will not work with this sort of comparison.    

Please help!  

4 replies

Arthur_Tutt
  • Arthur_Tutt
  • Inspiring
  • 118 replies
  • October 31, 2023

Hey @Helen_Harrell ! Can you please provide screenshots of your table(s) / data? Helps me to visualize your setup

H
  • Helen_Harrell
  • Author
  • Participating Frequently
  • 5 replies
  • October 31, 2023
Arthur_Tutt wrote:

Hey @Helen_Harrell ! Can you please provide screenshots of your table(s) / data? Helps me to visualize your setup


Hey @Arthur_Tutt, Thank you for responding.  Sure.  

This first photo is the all items import.  All of our items are uploaded here.  We have three different variants that are run and connected to the variant page: Prime, Choice, Other.  

Next, Here is the variants page.  It is all categorized by our sku's.  Next is the type of variant it is: Prime, choice, or sometimes something else.  Next is the item's name shown on the website and is connected to the "all items import" from above.  To the right of this is the mixed boxes we have that that item is in.  The quantity shown on this inventory is what is shown for each individual item on the website.  

Lastly, the components page.  This is what the variants is linked to for mixed boxes.  It has the name of the package with the individual item on there, the sku number which connects to the variant name (for double checking), then the item's sku number that looks up the product name and varient.  The column furthest on the right is the quantity of that item that is in the mixed box (ex: In the bunny basket, there is 1x roast, 3x top sirloin, 2 Choice NY Strips, and 2 lbs ground beef).  To the left of that, "Individual Item Available" is a lookup of the item's inventory from variant that is on the website... 

As you can see, the roast is currently at 0 for individual Item Available.  Thus, I want the mixed package in the variant to read 0.  I tried doing some different formulas to automate this, but I'm not quite sure as to how.  

Thank you!

 

 

 

 

Arthur_Tutt
1 person likes this
  • Arthur_Tutt
    Arthur_Tutt
H
  • Helen_Harrell
  • Author
  • Participating Frequently
  • 5 replies
  • October 31, 2023
Helen_Harrell wrote:

Hey @Arthur_Tutt, Thank you for responding.  Sure.  

This first photo is the all items import.  All of our items are uploaded here.  We have three different variants that are run and connected to the variant page: Prime, Choice, Other.  

Next, Here is the variants page.  It is all categorized by our sku's.  Next is the type of variant it is: Prime, choice, or sometimes something else.  Next is the item's name shown on the website and is connected to the "all items import" from above.  To the right of this is the mixed boxes we have that that item is in.  The quantity shown on this inventory is what is shown for each individual item on the website.  

Lastly, the components page.  This is what the variants is linked to for mixed boxes.  It has the name of the package with the individual item on there, the sku number which connects to the variant name (for double checking), then the item's sku number that looks up the product name and varient.  The column furthest on the right is the quantity of that item that is in the mixed box (ex: In the bunny basket, there is 1x roast, 3x top sirloin, 2 Choice NY Strips, and 2 lbs ground beef).  To the left of that, "Individual Item Available" is a lookup of the item's inventory from variant that is on the website... 

As you can see, the roast is currently at 0 for individual Item Available.  Thus, I want the mixed package in the variant to read 0.  I tried doing some different formulas to automate this, but I'm not quite sure as to how.  

Thank you!

 

 

 

 


Also, @Arthur_Tutt if you think there is an easier/better way to do this, please let me know!  I am 100% open to ideas!

Arthur_Tutt
1 person likes this
  • Arthur_Tutt
    Arthur_Tutt
Arthur_Tutt
  • Arthur_Tutt
  • Inspiring
  • 118 replies
  • November 2, 2023

Hey @Helen_Harrell - thanks for the detailed update! ðŸ™‚ 

I was able to find a working solution to your issue using an automation. I recorded a short Loom Video to show you step by step how it works. The good news is it works, the bad news is it's a little complicated to implement (many moving pieces here). But here we go:

Oops I missed something Loom Part 2

*** remember to change any of my Table / Field / Variable names to match your own or else it won't work for you***

Table Setup:

Formula For Record ID

 

 

Automation Trigger Setup (Trigger when Inventory field on record is updated):

 

Script Input Variables

 

Automation Script:

// get Package Composition Table
var packageTable = base.getTable("Package Compositions");
var packageQuery = await packageTable.selectRecordsAsync({fields: ["Individual Item Available", "PKG Record ID", "PKG Variant"]})
var packageRecords = packageQuery.records;

// get Input Config
var inputConfig = input.config();
var recordID = inputConfig.recordID;
var pkgRecordID = inputConfig.pkgRecordID;
var pkgVariant = inputConfig.pkgVariant[0];
console.log(pkgVariant)
var inventoryAvailable = inputConfig.inventoryAvailable;

// get Meat Variants Table
var variantsTable = base.getTable("Meat Variants")
var variantsQuery = await variantsTable.selectRecordsAsync({fields: ["Inventory", "Record ID"]});
var varitantsRecords = variantsQuery.records;

var minInventory = 100; // a default starting inventory amount, to be replaced in code w lowest inventory #

console.log(packageRecords[0].getCellValue("PKG Variant")[0].name)

for (var i = 0; i < packageRecords.length; i++) {
if (pkgVariant == packageRecords[i].getCellValue("PKG Variant")[0].name && minInventory > packageRecords[i].getCellValue("Individual Item Available") ) {
minInventory = packageRecords[i].getCellValue("Individual Item Available")
}
}

console.log(pkgRecordID[0])

// Updating Last Charge Date With New Date
var updates = [{
"id": pkgRecordID[0],
fields: {
"Inventory": minInventory
}
}]
console.log(updates)

await variantsTable.updateRecordsAsync(updates);

 

Let me know if this works for you!

H
1 person likes this
  • H
    Helen_Harrell

Reply


Most helpful members this week

Terms & Conditions