Auto adjust Quantity based on Multiple select field option

[Northwell_Lab]

I want to keep track of devices which are at my client sites as well as track device quantity.

I created a simple Table 1 with a Primary Key as “Client account #”, a Multiple select field called “Device Name” with a bunch of options called “HP 2000”, “Dymo 400”, etc., a barcode field too.

In Table 2, I will have a row or field called “HP 2000 Quantity” (with a value of 50) and a row or field called “Dymo 400 Quantity” (with a value of 20).

In Table 1, when a record is added and “HP 2000” is selected I would like the Quantity field in Table 2 to auto adjust to show “49”.
In Table 1, if another record is added and “HP 2000” is selected then the Quantity field should automatically show “48”.
etc.

So, Table 2 is strictly to keep track of product device quantity. Is this doable… and how?

[Chester_McLaugh]

Rather than using a Multiple Select field type, use a “Link to another record”

“Used” is a count field counting the linked records in the Clients field.
“Remaining” is simply a formula subtracting Used from Available.

As a bonus, I added another formula field in the Device table that shows an error if/when Available is less than zero. A lookup field in the Clients table will show errors right as you do the data entry.

[Northwell_Lab]

Thank you Chester that looks like it will suit my needs. Can I download/import your sample?

Thanks

[Northwell_Lab]

This was simple enough to set up :slightly_smiling_face:

Is there a way to send an email alert if “Remaining” field is 5 or less?

[Chester_McLaugh]

At the very least, you can create a view that filters based on Remaining. That way you can see the status of Devices at a glance.

You could build a custom alert system using the API or you could use Zapier.

I’m not positive how the “New Record in View” trigger works—if the trigger responds to existing records being ADDED to a view or if the trigger only responds to truly NEW records that happen to be in a view. Anyone else know? If you test it out, let us know what you find. If the trigger fires whenever a record is ADDED to the view that could be pretty powerful.

Zapier

Airtable Integrations | Connect Your Apps with Zapier

Instantly connect Airtable with the apps you use everyday. Airtable integrates with 1,500 other apps on Zapier - it's the easiest way to automate your work.

[Northwell_Lab]

I will check out Zapier… thanks for that.

Going back to the two tables I created… So, I used “Link to another record” as you suggested. The first table (Clients), when I export to CSV if I have a 4 digit number it shows just fine. On the 2nd table (Device), when I export to CSV, client # shows as 4,253 when I’m expecting 4253. In Airtable, both tables show fine without the comma.

How can I get the 2nd table (Devices) to show the number without the comma? As you can see in my screenshot client # 1243 shows as 1,243 as well as 1265 shows as 1,265. As I mentioned it shows fine in Airtable. I tried making the ID field/name field as Single Line Text and also tried Number, but both show comma in the 4 digit number when exported to CSV.

[Chester_McLaugh]

The CSV file format doesn’t contain field type instructions, so Airtable can’t tell the CSV how to treat the data. So it’s all up to the application you use to open the file.

In MS Excel, you can format the column once you open it so that it displays as Text or as a number with no thousands separator.

Also in MS Excel, if you import the file (as opposed to opening it directly) you can specify field types (and preview the results) as you import.

[Northwell_Lab]

All good! Ty… I gave you some easy ones :winking_face:

Awesome app! Appreciate the help.