Redesigning - Easy way to transition from Company to ID

[Cryophallion]

So, after making a functional database, and then putting it to use, I showed it to a programmer I know.

I had put ID numbers on most things, but it looks like I didn't use them properly. The database is linked by text name, not the id number.

Therefore, in a projects tab, I have the customer name for the project linked to the customer name.

From what I hear, this is bad design, and I want to go back and fix it.

So, I created a field in customers for ID. I want to link this to the projects table. Can I link it to the company name field, or do I have to create a customer id field in projects so it relates to customers properly?

I can do the latter, but it will take redoing all the value lists, lookups, etc., which will take a while. I just wanted to know if there was a proper way to retroactively. I don't want to have to go back and fix it again after this, so I want to make sure it is perfect now.

I shall now go and change my skill level to "moron".

Just when I thought I had the hang of it....

[Fitch]

Yes you will need a customer ID field in projects. You might try this:

In the customers table, do a Replace to set the customer ID field to serial numbers, if you haven't already done so.

In the projects table, set the options for its customer ID field to lookup based on customer name. Then show all records, click in the customer name field and do a relookup.

You should now have the customer ID set in projects. At this point, you can change the relationship from name::name to id::id, and also turn off the lookup option in the projects cust. ID field.

By changing your existing relationship (rather than making a new one) you may not need to redo anything else.

[LaRetta]

Be sure and set that CustomerID as Auto-Enter serial. And then, when you run your Replace Contents and set the CustomerID to serial numbers, be SURE to check 'Update serial number in Entry Options' or you'll be in for some nasty surprises when you start creating new customers.

[Cryophallion]

As the beer ads say:

BRILLIANT!

It worked perfectly, and was exactly the kind of solution I was looking for. I had never come across the replace function before, and thought I would have to export and re-import with serialized. The idea of using the lookup to put it in was just the trick to do it right. Thank you so much for helping me with this - everything is back to working fine... until the next problem rears its ugly head.

Thanks again.

[Cryophallion]

And so the problem arises.

Before, I had customers linked to the projects table by customer name.

Under projects, you would use a drop down to select the customer from the customer name in the customer table.

Now that value list will not change the linked customer id that is linked to the other table. I can't lookup the id, because it is the primary key.

The customer does not want to have the customer id as the drop down (I do know I can list he customer name in the drop down, but the id is stores, and they want to see only the customer name, no the id, when they go to the layout).

This happens several places in my database, and I am just wondering how I can get it to work the way it used to (which was wrong design wise, and probably would cause future problems, but did work functionally).

I have also been looking for a good tutorial on all this, but no one seems to have one that uses proper ids, but only shows the customer name.

Any help would be appreciated. (I repied because I believe this still applies to relationships as the underlying problem, not to value lists).

Thanks