Running Inventory Availability

[Rick Miller]

Running FmPro 14.

This is about trying to mine data as it is entered into a database.

I have to modify an existing database, but it has not  been set up very well.

There are only 2 Tables: Invoice, and Inventory.

Only key field is ID, which is used to identify the specific piece of gear. Approx 200 ID "codes" 

(Primary Key in Invoice table, Foreign Key in Inventory table)

Inventory rarely changes, and current stock is kept in an "on hand" field in that table.

An invoice is created for future meetings listing line by line items.

Invoice form is set up as lines...Fields are:

# of units needed,        ID,              Name of item,    # of days used,       pricing.

All on one line. Up to 10 lines (repetitions for each field) available per invoice.

There are many invoices generated over many days for different meetings.

 

I am looking to get a count of specific "ID (codes)"  so I can see if additional items need to be rented for any given day.

I would be looking about 2-3 weeks in the future.

Should I try to modify the Starter Solution "Inventory" and relate it to the existing by filed "ID" ?

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?

I would like the users to be notified if there is enough of any 1 item.  Can I can set a threshold check to warn of low counts?

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

 

Thanks,

Rick