Rick Miller

Thanks so much for the advice. For now, I cannot abandon the structure for several reasons I cannot discuss. I wholly agree with the opinions and am very aware of what I should be doing going forward (ie: rebuilding into a relational database), but the hands are kinda tied. (I inherited this project...just trying to help out...) I'm working with FmPro 14/Windows 7, and cannot upgrade either at this time. I've built some workarounds for other functions, but this one issue has been perplexing. I think I will create a separate, truly relational database and then figure out a way to extract the one bit of info I need, (ie: the use on any given day of a piece of gear) from that repeating field in our current file. Then I should be able to manage the inventory properly. I am grateful for the help and suggestions...

Thank you for the quick reply. Allow me to expand on my goal...and (Yes, the data in the repeating fields are values entered in separate reps.) This database is very large, and live (125K+ records) It's for gear needed for meetings. Each record is an "invoice" of gear. I would like to be able to "mine" the data in two existing, repeating fields (10 reps; "ID", which happens to be the primary key, and "Equip Description") so I can track amount of gear in use, and gear needed for future events.(Related table is just an inventory list, related by the ID code) I am assuming I need to separate the repeating fields in order to get a running count, or is there a way to do this with the existing structure? Note:This database was not set up to track the gear very well, but it's what I have to work with...All fields are in one table except the gear list. (There are many other fields for random data, but not of concern to my project) Due to the large number of existing records I don't thing migration is workable, although I like the idea. I hope I am on the right track here, and thank you for any help or guidance. Please let me know if I need to clarify...

I am trying to extract lines entered in a repeating text field (named "Equip" w/10 reps) into separate fields (Equip1...Equip 2...etc) I am trying to modify (add to) a large existing database with alternate layouts so I can manage all the data better. I would assume a calculation or script to populate the alternate layout fields with the extracted data... Or perhaps a better idea? Any help is greatly appreciated!

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

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