Instead of merging the files, simply use item_no to link them with a relationship. Much simpler and faster with much less modification of your database design.
With such a relationship designed, you can place fields from database 2 on a layout in database 1 and see data such as title or description for the current item.
I'd give you step by step, but don't have filemaker 7 installed on my machine and it may differ in a few respects from FMP 10.
In general terms, you'd add a new table to your relationship graph and use "add filemaker data source" to find and specify the table in database 2. Then you'll drag from item_no in one table occurrence to item_no in the other to relate them.
Then you can add fields from this new table occurrence to a layout in database 1.
I'll try that ... as you can see, I'm a novice Filemaker guy, so you lose me at:
I'll see what I can come up w/ though!
Press f1 and look up terms like table and relationship and see what you get. In filemaker 10, you'd select manage | Database from the file menu--but believe you have different options in the file menu for version 7.
I figured out pretty easily how to define the relationships between the two files. I copied the fields i needed into one layout from both files ... voila!
Then, when I went to export the filemaker database to a .txt (Tab-Delimited) so that I could copy/paste it into the required upload format/fields for Amazon.Com's marketplace, I realized I didn't even really have to drag the fields into the layout ... in the export windows, I can choose what fields I want exported from all fields in the related databases.
So, it took me like 10 minutes, and I've got it. Thanks so much for your help!
I have a related question to Fred's.
I need to update a master database with records from a secondary database. The latter will have some completely independent records and some records which contain more accurate field data than their corresponding record in the master. Right now, the only way I can figure this out is per the "Synchronizing or Merging Two Databases While Updating Records" article: creating lookup relationships for all the fields in the master database so that I can run a relookup based on a key field common to both databases and pull data into the master. The problem with this is twofold: one, the databases have a lot of fields so that's a lot of lookup-field mapping, and two I have to do this about a dozen times with a dozen different secondary databases.
I had thought that this was doable with import and not-empty validation in the fields to be updated. It almost works but just one empty field in the source file will cause the import to skip the entire record.