Jump to content
Rick Miller

Custom Function to Count Data Entries

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.