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
if nothing is available in “SOLD TO” return the original “Store Inventory”
Can you guys help me
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… 
I would consider adding at least one more table to your base. If you create an eItems] table, you could then link your nInvoices] 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 oLine 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
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… I would consider adding at least one more table to your base. If you create an eItems] table, you could then link your nInvoices] 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 oLine 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
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.