Hi everyone,

I'm a bit a of a beginner, so bear with me -- I have two databases.
One is contact information, one is bio information, and I want to link
these databases via name. I have a button in the bio database that I'm
trying to get it to go to the contact information in the contact
database, but it won't go. I've set firstname lastname honorific and
suffix equal to each other. and the script I've used is "Go to related
record" and I've told it to go to the contact management database, and
it's set to open in a new window, using the external database's layout.


Thank you so much for your time,

- Minnie

Minnie wrote on (9/27/2005):

Without a closer look, I can't explain what is or is not happening, only
tell you that Go To Related Record should work if you do, in fact, have
related records. If the key in one is identical to the key in the other,
there is no reason it shouldn't work.

The question is, why would you want to go to the other file? You can view
related information in the file you're already in.

Place the fields from the Bio file right on a layout in the Contact file
and you're already there. Put it on a different layout if you want to
control access to it.

Set your realtionship so that you can create a Bio through the realtionship.

Back to the key: "firstname lastname honorific and suffix equal to each
other."

Does this mean you have your relationship set up so that all FOUR fields
have to match? My guess as to why nothing happens with your Go To Related
Record is that there is actually not a related record to be found. Having
just one character off in any one of those fields means there is no
related record.

If you place a related Bio field on a Contacts layout, you'll see right
away whether the relationship is working. If there is no Bio info
displayed for a Contact you know contains such info, it's broken.

Matt

Add on.

Matt Wills wrote:

It should be a good usage to make scripts to trim blanc spaces at
beginning and end, to avoid not seen problems, for better control of a
relationship.

--
Vr.groet - regards, Léon Obers

Reacties per mail, vervang "invalid" door "cc" in het adres.
Reactions by mail, exchange "invalid" by "cc" within address.

Léon Obers wrote on (9/27/2005):

Right.

Replace Field Contents:

Replace Field with Trim ( Field )

That does remove leading and trailing spaces, admittedly a major cause of
breakage. The relationship is still susceptible to other entry errors.

Matt
--

In article <1127853397.663157.233490 (AT) g44g2000cwa (DOT) googlegroups.com>,
"Minnie" <inhamay (AT) yahoo (DOT) com> wrote:

It is not good practice to use an editable field as the key field in a
relationship. Your case of using components of the name as key fields is
not good, because if some element of the name gets edited, the
relationship will be broken.

It is good practice to base relationships on automatically generated
serial numbers that have no inherent significance to the user and will
not be edited.

For this purpose, you would define a serial number field in each table,
and define the field so as to prevent modification of the number by the
user. This serial number then should be the primary key field for
relationships to that table.

In each table that you want to relate to that table, you should put a
number field to hold the key field number of the "master" table.

A good naming convention is to use the field name in the form
"kpContactID" for example as the primary key field in the Contact table,
where "kp" signifies that it is a primary key.

For the number field in the related table, use the convention
"kfContactID" for example, to show that it is a "foreign key" and holds
the number ContactID form the Contact table.

The relationship is then based on a number that in itself has no
significance to the user, and will not be edited. This means that the
relationship will not break when you modify data in an editable field.

Since you already have some number of records in each data table, and
presumably have not defined the kp serial number key field in each
table, you will need to define the kp serial number field, but for the
moment allow the user to modify the number. Then go to a layout that
shows the kp serial number field, go to the Browse mode, and do
Records>>Show All records. Then click in kp serial field, and do
Records>>Replace field Contents, select Serial Number, Initial Value 1,
Increment 1, Update Serial Number in entry options, and click Replace.
That will serialize the existing records and update the next value in
the kp serial number field definition. Go to the field definition, check
that the next value is correct, and then click the box to Prohibit
modification of value during data entry.

Do this for each of your data tables.

Also define appropriate foreign key number fields in your tables. From
your description, it sounds like you need a foreign key number field in
the Bio table, to hold the value of the appropriate kpContactID. Call
this field in the Bio table kfContactID.

Assure that the relationships are correct as presently set up, based on
names, so that the right bios are related to the each contact.

You will now set the appropriate values in the kfContactID field for
each Bio record.

Go to a layout of the Bio table, that shows the field kfContactID. Go to
the Browse mode. Do Records>>Show All records. Click in the field
kfContactID. Do Records>>Replace Field Content, select Replace with
calculated result, and specify the following formula:

Contact::kpContactID

which means you will put the value of kpContactID from the related
Contact record in the kfContactID field of each Bio record.

Click Replace.

Verify that the values in the kfContactID field are correct for a sample
of Bio records.

Now go to File>>Define Database>>Relationships, and change the
relationship between Contact and Bio to be

Contact::kpContactID = Bio::kfContactID

Delete all of the old Name parts of the relationship.

This gets the relationship defined on a sound basis, a serial number
that is unique to each contact, and that will not be subject to breaking
because of editing contact or bio data.

Set the relationship to allow creation of records in the Bio table via
this relationship. You may also want to cause deletion of related Bio
records when a Contact record is deleted.

After you have verified that all is working OK, go to the layout of the
Bio table that shows the field kfContactID, and either remove that field
from the layout, for set the field behavior to prohibit entry in the
Browse mode. That will prevent a user from inadvertently changing the
value of kfContactID in the Bio table.

In a layout of the Contact table, put a Portal to the related Bio table,
and put appropriate fields from the Bio table in the portal. This will
show existing related Bio records, and will allow you to create related
Bio records directly from the Contact record. All the related Bio
records for each contact should show up in the Portal, and you can go to
the first empty row of the portal to create a new Bio record. The field
kfContactID of the Bio table should NOT be one of the fields in the
portal. The value of kfContactID will be automatically filled in when
the new Bio record is created via the portal.

I suggest you put a navigation pushbutton in the portal to Go to Related
Record in the Bio table, and likewise a navigation pushbutton on the Bio
layout to Got to Related Record in the Contact table.

Another comment:

The foregoing assumes you may have multiple Bios for one Contact.
Perhaps this is not that case; maybe you really only have one Bio for
each contact. In that case, why use a related table at all? Why not just
put the Bio data directly in the Contact table?

Bill Collins

--
For email, remove invalid.