Hello all,

I have two quick questions:

1) If making a relationship between two tables, is it necessary to make
the match field a field that the user is not going to change? Here's
the problem I'm running into: if I have a match field called "name" and
change its value in table 1, the value of "name" in table 2 will not
automatically change at the same time, and this breaks the match
relationship. I realize I can associate the tables using a unique
identifier field that the user won't need to touch, but I'm wondering
if that's really necessary or whether it's possible to tell Filemaker,
"if the match field value in table 1 changes, change the match field
value in table 2 to the same value."

2) If I import a bunch of records into table 2, those records will line
up with records in table 1 provided that the match field value in the
import file is the same as a value in table 1. But what if there are
records in the import file whose names don't match anything in table 1?
Is there a way to tell Filemaker to create associated records in table
1 if there's no match?

Thanks all, and sorry for starting the other confused thread. I've
been able to sort through my confusion to the point where I can post
these somewhat clearer questions.

jbpollock (AT) gmail (DOT) com wrote:

Sure, as long as you can figure out how to associate the new key to the
old one, so FileMaker can search out occurrences of the old one and
change it to the new. Once the name in Table1 changes, how is FileMaker
supposed to know what the old key was so it can be searched out and
changed?

You can create a calc field in Table 2 that determnines if there are
matching records in Table1 and create records where there are none.
COnsiderably easier than the first scenario.

Matt



--

Matt Wills wrote:

Upon further consideration:

While I am sure the overwhelming majority would highly recommend
against changing the key in any relationship, it would still be
possible.

My idea:

Create a second ID field for each record. Let's call them Table1::Key1
and Table1::Key2. Create two relationships: Table1:Key1 = Table2::Key2,
and Table1:Key2 = Table2::Key1.

Use EventScript (free plugin) to trigger a script if the value of
Table1::Key1 changes. Since the primary relationship no longer exists,
this script would use the second relationship to change the value of
the child records Table2::Key1 to the new value from Table1:Key1.

When all that is done, make the Table1::Key2 equal Table1::Key1.

You would need a mechanism to assure that the initial values of the two
ID fields in Table1 are the same. This could be built in to the
beginning of the script triggered by EventScript:

If isEmpty [ Table1::Key2 ]
Set Field [ Table1::Key2 ; Table1::Key1 ]
Exit Script

Like I said, you're not liable to garner much support for the idea from
very many people, including me. The potential for lost child records
through the break in relationship is enough to stay away from it.

The best method is to have an ID Key that will never be changed
automatically set upon creation of the record. With a unique,
unchanging ID key in place, you change what you want in the records as
often as you want, and they stay related.

Out of curiosity, why would you want to go to all this trouble in the
first place? Why is the simplicity of a unique unchanging ID Key not
suitable?

Matt

Matt,

Thanks much! Your answer is pretty much what I expected. And in
answer to your question:

It's probably not worth trying to explain the convoluted scheme I had
in my head. And part of it was that I had already done a bunch of
match fields without using a unqiue id and was hoping i might avoid
having to change all the relationships. But obviously that's not a big
deal, and doing anything else would be ugly.

I'll also look into doing a calc field to check for missing records;
that'll be helpful.

Thanks again!

In article <1134088474.914545.232330 (AT) g14g2000cwa (DOT) googlegroups.com>,
"jbpollock (AT) gmail (DOT) com" <jbpollock (AT) gmail (DOT) com> wrote:

Some thoughts:

1. In almost all circumstances, it is best to use as match fields (or
"Key fields" in more standard Filemaker usage), fields whose content
does not change and is not editable by the user. The automatically
assigned serial number in the "parent" table and a corresponding number
field in the child table is the usual method.

2. It is possible to change key fields from one scheme to another in a
straightforward and reliable way. In the following discussion, I assume
the existing relationship is based on key fields named OrgName in both
tables.

a. Set up the new key fields in both tables. For the sake of discussion,
call them kpParentID in the parent table, and kfParentID in the child
table. Both are number fields.

b. Define kpParentID in the parent table to be a serial number, next
value 1, increment 1.

c. In the parent table, show all records, then go to the kpParentID
field and do Replace Field Contents with Serial number, starting value
1, increment 1, with Update serial number in entry options checked.

d. In the parent table, go to the field definition for kpParentID,
verify that the next value of the serial number has been updated to be
one more than the numbers automatically entered in step c, then check
the box Prohibit modification of value during data entry.

e. In the child table, Show all records, then go to the field
kfParentID, and do Replace field contents, with a calculation, the
formula being
kfParentID = Parent::kpParentID.

This will put the value of the kpParentID in the field kfParentID of
each record of the child table, based on the existing relationship
between them.

f. Redefine the relationship between the two tables to be based on the
ParentID field instead of the Org Name field.

Now you can edit the OrgName field and it will not break the
relationship. You can even delete the OrgName field from the Child
table, and simply put the related OrgName field from the parent table in
layouts of the Child table. That way, they will always be the same.

The field definition prevents a user from editing kpParentID in the
parent table. To prevent a user from editing kfParentID in the child
table, leave that field out of most layouts of the Child table, and for
those layouts contain that field, format the field so that the user can
not enter the field in the browse mode. The user does not need to see or
use that field, so it can be left out of practically all layouts.

3. To find orphan records, put a related field from one table (Table A
for discussion) into a layout of the other table (Table B). Pick a field
that you know always has a value in Table A. In Table B, do a Find for
records in which that related field is empty. All the empties are orphan
records in Table B.

Bill Collins

--
For email, remove invalid.

Thanks Bill! This is extremely helpful info, particularly the tip
about locating the orphans.

Bill wrote:
I agree with everything Bill says except the part about locating orphan
records. You can not find orphan records by searching related data
because they don't have a related record. Even searching for an empty
related field will not find orphan records. What you can do instead is
search for all records that have data in a related field and then find
omitted records; these are the orphans.

In article <1134186412.087774.296940 (AT) g47g2000cwa (DOT) googlegroups.com>,
"FP" <ab (AT) pottnerconsulting (DOT) ca> wrote:

That is true. I was perhaps too cryptic in my explanation. Do a Find
that omits records than have data in the related field. The remaining
records are the orphans.

Bill Collins

--
For email, remove invalid.