we need to know more about how you've set up your basic database.
Most order/invoice systems have this structure:
Is that what you had before you added this additional table where you have one record for each item?
I think you can set up what you want if you base your layout on the new table so that you can list every item and their serial number, but then use the relationships you've set up so that you can add fields from the other tables to provide the additional information about your order.
We have two systems in house BV and FileMaker 11 server . BV for Accounting and Inventory and FileMaker for Production Records.
The BV record has a Confirmation number and line items of product (model #) , quantity ( 1 to multi quantity )
There is a database for each product maintained in production they contain:
Model # , Serial # , Board Revision , Customer PO# , Customer Name , Shipping Date as well as calibration information etc..
These databases generate the Serial Numbers for each unit ( 1 to multi quantity )
I have added a new field in the production databases that has our BV Confirmation number ( document that tells production what to build and ship )
I need to generate a document in FileMaker that can list all the serial number created for each confirmation ( this could be from multiple records from the same database as well as from other databases )
Normal confirmation could have 1 controller , 10 transmitters , 2 power supplies
OK, I get why you have separate tables for each item as that is what makes it simple to produce indepente sequences of serial numbers of each one, but that really complicates your reporting as you would need to somehow merge all this data into a single report. As I understand it, using your example above, the report would list:
Transmitter, Type ABC
Power Supply, Type xyz
To do this in filemaker, you need to pull all the ordered item records from their separate tables into a single table so they all can be listed like this.
One option is to use Import Records in a script so that you search each table for records with a given confirmation number and import them into the report table from each respective table if any are found. This would take the least design work for your existing system, but can be combersome and pulling up such a report could be pretty slow.
Another option is to use your seperate tables solely for generating your serial numbers, but then use a combined table for listing all items for production. This creates the same unified table, but on an item by item basis when each order is completed, eliminating the need for a scripted import to set up each such list of items for production.
The first option looks to be the best solution for my existing databases.
To clarify I will link in the existing product databases into my new database
I will then write a script that will bring each record that has the confirmation number I am looking for - with the SN of that record.