Populate Primary and Foreign keys after importing data.

[jjfcpa]

I am importing data into two tables.

Customers

Customer's Products

This is a one (customers) to many (customer's products) relationship. Interesting, I created the tables in FM and have the following fields in each table:

_kp_customers_id (customers table)

_kf_customers_id (customer's table)

and it creates a many-to-many relationship in the graph. Not sure if I can manually change that?

Anyhow, my real issue is I need to update the primary key and foreign key in these tables. The old key fields were:

custno (customers primary key)

custno (customer product foreign key)

These fields still exist in the tables, as well as the new fields after I do my import. Now I need to get the primary keys and foreign keys (the new fields) populated. How do I go about doing that so the parent table will be linked to the child table? My plan is to calculate the fields using Ray Colglon's base36 uID function.

Edited August 24, 2010 by Guest

[jjfcpa]

Figured it out and it really wasn't as hard as I thought it was going to be. Here is a summary of what I did.

1. Populate new primary keys for all tables using base36 uID custom function. Custom function for base36 uID is available here:

Link ...

I simply created a layout for each of my tables and put the new primary key field on the layout, then used the "Replace Field Contents" option to get the uID in it.

2. With that done, I needed to get the foreign key in the child tables so that the related tables would be properly linked. To do this, I went to the child table's layout (Customer's Products layout) and added the new foreign key field to it. It's blank right now, but I will populate with the parent's uID primary key in a second.

I then went to the relationship graph and linked the parent and child table using the old key fields that I had imported in. For example, the primary key in the old Customer table is "custno", and this is the foreign key in the old "Customer's Products" table. I drew a line from the custno field in the Customer's table to the custno field in the Customer's Products table. This effectively linked the tables, but of course, it was using the old keys rather than the new key fields containing the uID keys.

We'll fix that next.

Back to the layout for the Customer's Products table. It currently has only two fields on it. The primary key of the Customer's Products table and the foreign key field of the Customer's table. Remember, the goal is to get the foreign key field populated with the new uID field contents from the Customer's table.

At this point, since the tables are linked by the old "custno" key, I used the "Replace Field Contents" option to populate the foreign key field with a calculated value - the primary key field in the Customer's table. Since they are linked, it pulls in the correct primary key.

With that done, I go back to Manage Database and change the relationship graph to use the new key fields.

Worked like a charm.

Comments?