Importing Data into Related Files

[Carl Smith]

I am a relative newcomer to Filemaker, using Version 5.5, and either I've missed something or I am trying to achieve something that is not possible.

How can I, when importing completely new data using the FileImport Records function, establish a relationship to another file for the new data?

Scenario:

File A (e.g. Book Titles) includes a field called ID Number with the Auto Enter option

File B (e.g. Barcode Numbers) includes a field called ID Number as a regular Text field. This would be the match field.

I now want to import completely new data from either a FP file or an other file (e.g. Excel) that contains data for both files

Having created a unique number for the imported records by using the Auto Enter option on the import routine how can I pass the related data (e.g. Barcode Numbers) into File B?

[dbruggmann]

You need a new field in file B

[MoonShadow]

There are several ways to approach this, Carl. The simplest would be to use your relationship. If you have the 'Allow Creation of Related' in Define Relationships checked (in your main), you will automatically create your related record (and insert the mainID into the related ID field) when you enter data into this related BarCode field - if an ID currently doesn't exist in related that matches your MainID.

So you would import into your main db, including the BarCode field (into a tempBarCode holding field in main). Then simply loop through your newly found imported set (that have already been assigned their UniqueID during the import process) with:

Go To Record/Request/Page [First]

Loop

Set Field [Related::BarCode, tempBarCode]

Go To Record/Request/Page [Next, Exit if Last]

End Loop

You will end up with records in your related that contain the ID plus your BarCode. And your structure sounds fine to me. Always back up before applying any tests that are suggested. And welcome to the Forum!

[Carl Smith]

Many thanks for your reply.

Would I be correct to understand that using this routine I would have to create "temp" fields for every different type of Barcode field(e.g. ISBN and UPC Barcode Numbers)?

The "related" data is the traditional many-to-one scenario.

Do you have any suggestions?

[MoonShadow]

Carl said:

Would I be correct to understand that using this routine I would have to create "temp" fields for every different type of Barcode field(e.g. ISBN and UPC Barcode Numbers)?

Hmmm, I assumed the majority of the import data was for your main file and that the majority of your related file would be filled by other means. It is possible you are importing into the wrong file.

Maybe it would be helpful if you described the fields in your two files and explain their relationship and why you have them separate. Bar Codes are unique are they not? Maybe the join should be on your Bar Codes. And maybe Detlev is right. Why do you have the Book Titles and their Bar Codes in different files?

[Carl Smith]

Thanks for the reply.

I guess what I am trying to achieve is a strong use of the one-to-many structural concept whereby one book can have several unique barcode numbers (e.g ISBN, EAN, UPC) - especially here in the U.K.

Similarly, the book could have several authors or editors and I wish to keep this data as child records separately, but somehow create them when importing new data.

To give you a breakdown of my files and their perceived relationships may I use the following example,

File A (Parent) fields

Record ID No

Book Title

Publisher

Publisher's Book ID No

File B (Child) fields "Barcode Nos"

Record ID No (foreign key)

Barcode Number

File C (Child) fields "Authors/Editors"

Record ID No (foreign key)

Author/Editor

Also, I guess I am trying to find a way where I don't have to add new "temp" fields as I envisage the size of my database to be a working catalogue of books which will regularly increase in size and defining new fields in a database of hundreds of thousands of records could be a problem.

Perhaps this is impossible?

One thought I had was once the Main ID number has been created on import in File A to then loop these records and copy the ID Number to another FP (where the new data has been imported from) and then loop the found set in this file and paste the Main ID number into this file which would give me the key for the relationship. But I fear that getting the correct Main ID number into the other file could be compromised by any subsequent change in either file before the script is run. Also this could not happen if importing from other file formats.

[MoonShadow]

Imports happen in one whack. If you wish to assign the ID to the related records, they must be imported into your Main file first for that assignment; otherwise, there would be no connection when your records are created in your related file. As Detlev suggested, you can then script their movement to their related files. But I understand not wanting those temp fields in main.

You could consider importing into a holding file first, with auto-enter upon import, assigning a tempID to connect them, and then pulling (importing) into your Main (including the tempID). Then use auto-entry during *that* import to assign your MainID and also write the tempID to a field in Main at the same time (one temp field in main is much better); and then (based upon a relationship from Main to this holding file on tempID), create your related records, assigning the MainID to your related records through the temp relationship.

I can think of no other way. That doesn't mean it isn't possible, but the connection needs to be established (MainID) one way or the other.

[Carl Smith]

Thank you very much for the time taken to help me.

It really is much appreciated and hopefully I can re-think a few things and put your ideas to good use. Thanks also to Detlev for your response.

Best Wishes

Carl

[dbruggmann]

Hello Carl

I think MoonShadows proposition of an additional holding file is more elegant and practical than my