Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

FIFO Script help

Topic Labels: Scripting extentions
717 2
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello,

I’m hoping someone can help me figure out what’s wrong with my script. I’m new to JavaScript so I’m hoping it’s something simple.

This is to track inventory first-in-first-out.

I have 3 tables. First is a list of inventory received. Second is a list of inventory sold. Third is mostly lookup fields that track the current inventory levels.

I have a script that works when I iterate through the array by manually updating the index. i.e. I put in “0” as the index, run the script, edit it to “1”, run the script, and it works fine. But when I try to loop it, something’s wrong. I’m doing this on the line that I’ve starred.

Here it is:

let salesTable = base.getTable("Sales");
let salesRecords = await salesTable.selectRecordsAsync({
    sorts: [
       // sort by "SKU" in ascending order
       {field: "SKU"},
       // then by "Week (End)" in ascending order.
       {field: "Week (End)"},
    ]
});

let inventoryTable = base.getTable("Inventory Levels");
let inventoryRecords = await inventoryTable.selectRecordsAsync({
    sorts: [
       // sort by "SKU" in ascending order
       {field: "SKU"},
       // then by "Delivery Date" in ascending order.
       {field: "Delivery Date"},
    ]
});


**let sale = salesRecords.records[1];**

    //track amount of cases sold, since this will need to increment down
    let casesSold = sale.getCellValue("Cases Sold")

    //for each sale, we start with a list of records that match the SKU:
    let skuInventory = inventoryRecords.records.filter(record => {
        return ((record.getCellValueAsString("SKU") === sale.getCellValueAsString("SKU")) && record.getCellValue("Cases Remaining") > 0);
    })

    //Now we loop through the list of records that match the SKU and subtract the cases sold from the cases remaining
 
    for (let inventoryRecord of skuInventory) {
        let casesRemaining = inventoryRecord.getCellValue("Cases Remaining");

        if(casesRemaining > casesSold) {
            await inventoryTable.updateRecordAsync(inventoryRecord, {
                "Cases Remaining": casesRemaining-casesSold,
            })
            break;
        } else if(casesRemaining < casesSold) {
            await inventoryTable.updateRecordAsync(inventoryRecord, {
                "Cases Remaining": 0,
            })
            casesSold -= casesRemaining;
        } 
    }
2 Replies 2

Hello,

Trust you’re keeping good.

Surely, we can assist you on your requirements.

Please Reach me on my Email- seth(at)cisinlabs(dot)com, Skype - cis.seth

Regards,
Seth

Hi!

Was just wondering if you got this to work? I’m looking for a similar solutions.