How to create inventory Count in the system?

Topic Labels: Formulas
2249 2
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

i am using the main Table (client List) where I add my client then I use lookup (in client list) to find the Invoice number in another Table Called “Master Inventory”

My question is How to find the count? with the following conditions

  • Count the number of people in “SOLD TO” TAB
  1. number of people in “SOLD TO” - “Store Inventory”

if nothing is available in “SOLD TO” return the original “Store Inventory”

Can you guys help me


2 Replies 2
10 - Mercury
10 - Mercury

You’ll first want to add a Count Field (one of the options in the field type dropdown) that counts the number of records in {SOLD TO}, then use that in formula instead. Let’s say you call it {Sold To Count}. Use the following formula:

IF({SOLD TO}, {Store Inventory} - {Sold To Count}, {Store Inventory})

You didn’t ask, but for some unsolicited advice… :slightly_smiling_face: I would consider adding at least one more table to your base. If you create an [Items] table, you could then link your [Invoices] table to it, and use rollups to more easily track the quantity of each item in stock. You could also use lookup fields to save time in retyping item names, descriptions, prices, etc.

Additionally, if you ever have a situation where you need to put more than one item on an invoice, I would also add a [Line Items] table to hold each line item on each invoice. This also facilitates selling multiple items with different quantities for each.

The below example base shows what I’m talking about :slightly_smiling_face:

Perfect @AlliAlosa : It’s working… thank you.

Appreciate your suggestions i already created the Different table for invoices and using lookup for invoice.

Thanks again