Need help with the Formula

[Yerko_Begic]

I am trying to make a simple inventory of 50+ items
I have one table called Inventory which has columns “Name” and “Quantity”. I would let other people borrow my inventory, so there are a lot of ins and outs. Anyway, On occasion, I need to do an Audit of my inventory, so I want to make sure that my personal inventory matches with the global inventory system. So what I would do is download the global inventory and put that into the Airtable as a separate table called Global Inventory. This table would also have columns “Name” and “Quantity”. I was thinking to create a “Global Inventory” column under the Inventory table and use the formula that would compare the two tables “Name” and output the Global Inventory Number so I can easily see if it matches or not. I guess this would be like reconciling a bank transaction in a way. Any help would be much appreciated. I know how to do this in Excel with SUMIF but again, this is not the same here.

[TheTimeSavingCo]

Hm, would it be possible to get screenshots of the Inventory and Global Inventory table? I feel like I need more information on your setup to be able to attempt a suggestion

[Yerko_Begic]

I am not sure if my approach is the best, but I do need to be able to compare the two to find descrapency and take action on it if there is one.

Here are the screenshots:
Global Inventory

Inventory

[Ben_Young1 · Accepted solution]

Hey @Yerko_Begic!

So, here’s my takeaway from your post.

You have two tables.

1. Local inventory, and…
2. Global inventory.

You want to be able to compare the difference between the local and global inventory to identify discrepancies.

---

This is certainly possible.
However… I’m curious about how you’re designing your base.

I want to footnote that I’m not trying to be patronizing, I’m just tracing thoughts!

Ideally, in a database, you want to keep all records for a certain ‘thing’ in a single table.

• e.g. There are customer records, product records, and employee records.
  • Products need to have data like their SKU, their price, your inventory amount, etc.
  • Customer records need to have contact information, shipping addresses, and notes.
  • Employee records need to have things like their position, their locale, their notes, and their employee contact information.

Naturally, we can link products to the customers that order them. And we can link the customers to the employees that sold products to them. And we can link employees to products they sell and get a sense of how many of each item they sold to a given customer.

So, the first thing I thought about in reading your post is your records.
What information do they hold?
Both tables hold records for inventory items.

I’m curious about what differentiates them.
You never want to have two records for the same thing in two (or more) different tables.

---

With that in mind, I recommend you create a table with the entire global inventory.
Then create something like a number field to hold your inventory counts.
Then you can create another number field to hold the global inventory count.

From there, you can create a formula to provide an audit status to provide something like this:

This allows you to create a view, for example, that is filtered to show you what’s in your inventory, and a view to see the entire global inventory.

You can build an automation that looks for audit failures and notifies you about it and/or appends it to a spreadsheet, etc.

---

In terms of downloading and updating the global inventory counts via what I assume is a CSV, you can update counts using the CSV upload app or you can use the table import data tool to push the CSV in.
This will simply allow you to override the values for the global inventory count without creating duplicate records or changing data accidentally in the upload.

---

Idk.
Just something to think about.
Lemme know if I’ve just completely misunderstood something about your use case or if you have any questions about implementing something like this.

If you're curious about the formula I used in that screenshot...

IF(
    AND(
        {Global Inventory},
        {Local Inventory}
    ),
    IF(
        {Global Inventory} != {Local Inventory},
        "❌ Discrepancy Found"
        & "\n" &
        IF(
            {Global Inventory} > {Local Inventory},
            "- " & ({Global Inventory} - {Local Inventory}) & " deviation in local inventory",
            IF(
                {Local Inventory} > {Global Inventory},
                "-" & ({Local Inventory} - {Global Inventory}) & " deviation in global inventory"
            )
        ),
        IF(
            {Local Inventory} = {Global Inventory},
            "✅ Audit Pass"
        )
    ),
    IF(
        NOT(
            OR(
                {Global Inventory},
                {Local Inventory}
            )
        ),
        "No Inventory"
    )
)

[Yerko_Begic]

Thank you very much for the suggestion. I am still in the planning stages, and I do have some experience with SQL. I was trying to avoid duplicate tables, and you are absolutely right about " never want to have two records for the same thing in two (or more) different tables.". I just didn’t know how to approach this to match/ find discrepancies between Global inventory and my local tracking. With your help, I am on track. I am VERY green when it comes to Airtable and didn’t know about the CSV. I just tried it and it works :grinning_face_with_big_eyes: . I have merged the existing records based on Product and mapped to my Global inventory column and it worked like a charm. I owe you one for sure :slightly_smiling_face: