Importing Text Files - Setting Foreign Key?

[Dana G]

I have three tables STORES, STORE DATA and SQUARE FOOTAGE. I have a 1:Many relationship between kP_Stores (STORES) and kF_Stores (SQUARE FOOTAGE).

I need to import 2000 text files that contain only the store number, store name, department and square footage.

What's the easiest/fastest/best way to import those records seeing how they don't contain any foreign keys? I want a portal on a department tab in the STORE DATA layout showing only the square footage of the areas in the department.

My thinking thus far has been to import the text files individually via script and ask the user to select the store to fill in the foreign key. Is there a better way?

Thanks in advance.

Edited July 29, 2010 by Guest

[comment]

Could you provide a more detailed explanation, both about your file (STORE DATA, for example, doesn't tell anything about the table's purpose) and the text files? If the files contain a unique(?) store number, where exactly is the problem?

[Dana G]

Here's the result I'm looking for. The only way I can get it is to manually type the Store PK into the Square Footage layout.

[Dana G]

The store data table contains all of the other information about the store such as the building type, construction type, whether fire proofing is required, merchandise capacity, etc. It will be the main location that a user will go to see all the data about their store.

The text files are generated out of AutoCad and they come out in a list form. There are two text files for each store. The store number and name will be the same for each line but the department and square footage will change depending on what drawing sheet data is being exported.

StoreNumber StoreName Department SqFootage

StoreNumber StoreName Department SqFootage

StoreNumber StoreName Department SqFootage

etc.

It's possible that the store number is not always unique. A store could be set up - goes to real estate approval and gets denied. Later that year a different site is located within the same city and the original store number is used. We want to keep all collected information from the original iteration of the store so we have a recors as to why it was denied.

Does that help?

[comment]

If the store number in the text files matches the one in your STORE table, why not use a relationship matching on store number to lookup the store's ID?

As for reused numbers, you could set up a calculation field =

If ( not Denied ; StoreNumber )

and use that instead of the StoreNumber directly.

This seems to be unrelated to your question, but the STORE DATA may be redundant; how many related records does a store have in this table?

[Dana G]

That seems fairly simple. I will proceed down that route.

Ahh. I have the same number as the STORES table. Thanks for pointing that out.

Thank you for your help.