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!
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!
