Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: FIFO Script help

1486 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Amanda_Shipka
5 - Automation Enthusiast
5 - Automation Enthusiast

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
seth_cis
5 - Automation Enthusiast
5 - Automation Enthusiast

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

fkno
4 - Data Explorer
4 - Data Explorer

Hi!

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