Note: This was originally posted in a Legacy forum...

I need to count instances (how many) of any 1 item is requested on any invoice for a future 3 week period.

I have to add this function to an existing database, but it has not  been set up very well. (FmPro 14, cannot upgrade...)

Details of database structure: There are 2 Tables: Invoice, and Inventory. Only key field is "ID", which is used to identify the specific piece of gear. (Approx 200 ID "codes" )

Related by Primary Key (Invoice table), Foreign Key (Inventory table).  Inventory rarely changes, and current stock is kept in a field in that table.

An invoice is created for future meetings listing line by line needed items.  Invoice form: Fields are: # of units needed, "ID", Description of item,  # of days needed, pricing.

There are many invoices generated over many days for different meetings. 20 line items (repetitions) available per invoice.

I am looking to get a count of specific "ID (codes)"  so I can compare to inventory stock to see if additional items need to be rented for any given day. There may be multiples of same "ID" on 1 invoice.

What is the best way to do this...Is there a series of script steps or other way to compare how many of any "ID" are ordered vs how many in stock for any particular day?

How would I notify the users that item is not available that day.

Any help would be greatly appreciated.  I have mid-range knowledge of FMPro.

Thanks, Rick

4 minutes ago, Rick Miller said:

20 line items (repetitions) available per invoice.

For real?  With repeating fields?  Or is it just a portal that shows a max of 20 related records?

If those really are repeating fields then you'll have to loop through each invoice and each repetition of the repeating field to keep track how many of a particular ID have been assigned.

If the invoice line items are proper related records then a fairly simple ExecuteSQL() query would give you the result.