Help

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

If QTY Column of Part A = 1,000, then correct

Topic Labels: Formulas
800 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Kristin_Turner1
5 - Automation Enthusiast
5 - Automation Enthusiast

Is there a way for me to Reference the Summary bar that is built into Airtable in another column with a formula?

I know AirTable doesn’t have normal sum function (which makes my job so much harder) but I didn’t know if I’m missing an option or an extension that can reference those Airtable built in summary bars?

I want the formula to be something like:
If column Part name = xxx-xxx-1315-xxx, then reference summary bar of column QTY used, then if that sum is equal to 1,000, then “Correct” if not “incorrect”.
I need to be able to do this with quantities that match 4,6,500, and 1,000. I currently use a switch function to achieve something like this with a very basic formula, but if we receive part numbers with different lots and they don’t total to one of my set numbers, it looks like it’s incorrect (but it’s not-it just has different quantities that all collectively should total to 1,000)

Furthermore, rolling up data in to a separate column will require me to have 8 different tables that rolls up 8 different sums of different part numbers and that’s just too much data for my employees to have to deal with on top of having to manually drag that SUM column I create (based on this article) every-time they create a new record. It’s just not adequate for what we need.

Any help, or suggestions would be appreciated. Trying to move away from Excel as I don’t like the fact that I can’t prevent people from editing my formulas in columns that have items they need to edit data on + it doesn’t have great traceability.

1 Reply 1

No. There is no way to reference values in the summary bar in a formula.

It sounds like you want to know if you have enough of a part across all quantity records for that part.

You are on the right track with using rollup fields and additional tables. Can you explain why you think you need 8 different tables?

I am picturing two tables.

[Quantities] table

  • name of part / link to parts table
  • quantity for this record
  • rollup stating if the QTY is correct

[Parts] table

  • name of part (editable)
  • link to [Quantities] table that allows multiple records
  • desired quantity for this part (4, 6, 500, or 1000)
  • rollup with the sum of linked quantities
  • formula that compares sum of linked quantities with desired quantity, resulting in “Correct” or “Incorrect”

Notice that both tables have a rollup field. The [Parts] table has a rollup of the {Quantity}, and the [Quantities] table has a rollup of the rollup.

You users should be able to do everything from the [Quantities] table