Qty of 1 10X80 Link filters ( ProductID 1),
You should really have the filter information in a seperate related table each with its own record like a line item table.
Lets say that you have a table for the unit information, a table for the parts, and a table that stores what filters each unit is assigned.
You would store the record ID number for whichever unit and then the part ID in each seperate record.
1 Airhandler 1
2 Airhandler Whatever
3 Airhandler Blah
1 10X80 Link filters
2 20X80 Link filters
3 30X50 Link filters
4 45X45 Link filters
fkUnitID fkProductID Qty
1 1 2
2 1 1
2 2 1
2 4 2
So for this example, Airhandler 1 ( UnitID 1 ) has a Qty of 2 10X80 Link filters ( ProductID 1) and Airhandler 2 ( UnitID 2 ) has three filters with a Qty of 1 10X80 Link filters ( ProductID 1), Qty of 1 20X80 Link filters ( ProductID 2), Qty of 2 45X45 Link filters ( ProductID 4).
You can use a portal to display the line items and even enter items in ( turn on allow creation or record in the relationship ).
Finally, if you still get lost, look for an invoicing demo referred on these forums produced by the user: comment
Oh, how I wish that were so. Unfortunately I 'inherited' this database and such relationships don't exist, in fact, I think it started life as a FileMaker Pro 4 or 5 file. I may decide to spend some time on it and lay it out as you have suggested... I just have to weigh whether it's worth the time or not.
Thank you very much for your advice!
To bad you can't climb into time machine and give your system's creator a dozen lashes with a wet noodle! :smileywink:
Even back in FMP 4 or 5 days Mr. Vodka's suggested solution would have been the best way to go.
Although I concur regarding the "wet noodle" approach and with Mr Vodka's solution it must be said that if the data is intact in some fashion, it can be manipulated, relationships reassigned, imports to tables executed . . . etc. That said, I'm not the guy staying up all night to make this happen.
"...if the data is intact in some fashion, it can be manipulated, relationships reassigned, imports to tables executed . . . etc. That said, I'm not the guy staying up all night to make this happen."
You certainly can manipulate the data to get what you want, but doing it without a significant investment in a complex combination of scripts and a new table or two? Probably not, and if you're going to have to put that much effort into it, a importing your existing data into a new table structure might not require that much more time/effort anyway and leaves you with a much better database structure.
Using the existing structure, I think you're looking at a script that walks through each field and computes counts of each type of filter. That suggests using the script to load a table like Mr. Vodka has described and that both gives you your counts and completes a key part of restructuring your tables.