Setting Unique Keys Question

[cmartin]

Good day all ( I hope!)-

A db I inherited is currently set up where I have my main file:Study and Related File: People. There are several relationships between study and people depending upon the roles the people serve (ie PI, Data Contact, etc) Currently for each role the files are related by the person's full name (first name & last name & title). For obvious reasons, I think this sucks, namely if any part of a person's name is changed (they get married, promoted, etc), then all the relationships to the study file break. EEK!

I am in the process of switching the relationshis to be based on unique identifier keys (Capt Kurt system) but I do not understand how to handle this for data entry. I know I don't want to show the user the key so how do I allow the user to see an identifier such as name while actually setting the field to the key?

PLEASE HELP!

Thanks-

Courtney

[Kurt Knippel]

You have to following fields:

ContactID_pkey

First Name

Last Name

Title

cFirstLastTitle

You never show the PrimaryID to the user, they will neither understand it, nor does it have any use for them.

As the developer you will first set the new ContactID_pkey (primary key) in the contact DB. Then you will use the existing relationships to set a ContactID_fkey (foreign key) in each of the related files.

Once all the IDs have been reset, then you can repoint the relationships.

[cmartin]

Not properly explaining my question...grrr. Frustrated with myself. I understand very well the concept of keys: primary, foreign, how to work it. My question is this: For a user in the master file (Study) entering a new record, I want them to *SEE* a list of names to choose from yet *SET* the Contact Foriegn key to the Primary Key that matches the perosn with that name. Currently I have it set up so that the Contact Foreign Key field is a value list that shows values from both the primary key field of conatct and the full name. So the user can select the name but store the key. However, I want to switch to the Capt Kurt key method and I will not want to show that field! I know I am missing something simple.

Am I making any sense?

[Kurt Knippel]

Your only mistake is showing the ID to the user. Imagine this: User selects a name from a list, clicks a button, scripting sets the ID. The user never sees or interracts with the ID.

[cmartin]

Your answer then seems to confirm my suspicion that a button must be attached in order for this to work. I was hoping to avoid that as landscape is precious and my boss is a bit kookoo for coco puffs when it comes to anything being changed from the user POV.

So am I correct: if the user is viewing the study record and in the Data Contact field they see Joe Smith, FMPRo (which I am displaying by Data Contact::Full Name), then the user cannot simply click on the value list and change the name to say Bob Greene, Newbie and have some automatic process change the key...the user must always pick from the list and then click on a button which calls my script that sets the key?

If so, yuck! But thanks for the help

[Kurt Knippel]

Unless this will be a continually ongoing process, I would suggest that you do this behind the scenes one time. Otherwise you will need the button.

[jasonwood]

Not sure if there are any problems with this, but you can have two relationships, one based on the name, and one based on the key.

The key field is a lookup field based on the name relationship, and the rest of the fields are lookups based on the key relationship. This way you can setup a popup list to select the name (sounds like what you're doing?), without relying on that field for the ongoing relationship.

I use this technique for UPC's on an invoice. When a UPC is entered on the invoice, the product number is looked up automatically through the UPC relationship, then everything else fills in from the product number relationship.

Can anyone see any problems with this?

[Ugo DI LUCA]

Nope. I do the same thing on my files to retrieve a product using multikeys to find the exact product (many products with same name but from different vendors...). But this could not be an exact match for contacts are there could be a lot of Jason Woods (great site)...

[cmartin]

i'll give it a try....thanks!

[jasonwood]

To expand on my example...

To use this in a clients database you'd probably want this field to include first name, last name, company name, and client number, to guarantee uniqueness. You'd make this a popup list probably (which by the way would take a long time on a network with a lot of clients!).

But again, please do not make the mistake of using this as your primary key. It's fine to use it to just lookup the primary key, but after that moment it should not have any relevence.

[cmartin]

Tried this method and it works great! I understand your latest comment that loading such a value list could take a long time on a network with many clients. So that leads me to a question: how are other people accomplishing this? I cannot see any other way than giving my users this pop-up.

[jasonwood]

I understand your latest comment that loading such a value list could take a long time on a network with many clients. So that leads me to a question: how are other people accomplishing this?

Normally you create the record from the client database.

Here's an example scenerio for creating a new invoice for a client:

1) Locate the client record

2) Click "Invoice Client"

3) A new invoice is created with the client information entered

Here's how it works:

Script in Clients, "Invoice Client"

Set globalClientNumber (a global field) to the client number (or whatever you use as your key).

Run an external script in invoices, "New invoice from clients"

Script in Invoices, "New invoice from clients"

Enter Browse Mode

New Record

Set field clientNumber to Clients::globalClientNumber

[Ugo DI LUCA]

Do not know if that can help, but I use it in one of my files and this work pretty well. It could help if your list was very huge.

Create a Type-Ahead Portal. The concept is that you create a relationship based on a calculation that filter the Client Name in your Client File and show the result in a Portal (e.g. in invoice). Then you select the raw et voila.

You can see this thread I posted some time ago. This is a solution from the Chris Moyer and Bob Bowers Advanced FMPro 5.5.

Have a look to see if it suits your need

Filtering Portal