How to automatically subtract from quantity

[April_Steward]

I’m creating an inventory management base and I want to have a count of product per location (shop, truck 1, truck 2, etc.) and I want to be able to move quantities from one location to another (3x product moved from shop to truck 2) without subtracting from the total product inventory.

So for example, if I had 27 units of product A, 20 are in shop, 4 in truck 1, 2 in truck 2, 0 in truck 3, 1 in truck 4. I want to move 4 units to truck 3 from shop. When I add to truck 3, I want to be able to select the original location and subtract the number that I moved.

I’m not sure if what I want can really be done or if my description makes any sense but I’d love any suggestions.

[Rupert_Hoffsch1]

Hi and welcome to the Airtable community :slightly_smiling_face: If I understand correctly you want to shift stock from one location to another, so basically automatically subtracting in one place and adding that amount in another. For me it sounds like you should try to build a simple app that gives you a user interface, which looks like this:

Select location to take stock from: ___________
Select location to add stock: ___________
Amount: ___________

I can’t really give you an example of an existing script, but if that’s something you’re interested in, I might have some time in the next days and post something here. Just let me know.

[April_Steward]

That sounds like what I’m wanting. I just have no idea how to do it. If you find the time, I’d love to see an example. Thank you!

[Rupert_Hoffsch1]

I’m sure this can be written a lot better, but this works for now. If you recreate the following base by keeping the exact naming for the table and fields and create an app with the code further below, you’ll see it in action:

output.markdown('# My Inventory App!');

let table = base.getTable("Inventory");

let sourceRecord = await input.recordAsync('Select the record to take stock from', table);
let targetRecord = await input.recordAsync('Select the record to add stock to', table);

let transferAmount = await input.textAsync('Select amount to transfer');

let newSourceRecord = sourceRecord.getCellValue("Amount") - parseInt(transferAmount);
let newTargetRecord = targetRecord.getCellValue("Amount") + parseInt(transferAmount);

await table.updateRecordAsync(sourceRecord.id, {
    "Amount": newSourceRecord,
});

await table.updateRecordAsync(targetRecord.id, {
    "Amount": newTargetRecord,
});

A next step would be to select the type of stock item that shall be moved (if it’s not always the same) and create another record in case that that item doesn’t exists yet. Creating inventories is fun :slightly_smiling_face: I’m not familiar with Airtable’s new interface designer yet, but that might also be an interesting option!