Child-parent record creation

[Speekless]

I have two tables, X and Y, the relationship is one-to-many.

X has the following fields: kp_ID_X, name (unique)

Y has a foreign key kf_ID_X to X, and some other fields .

On the Y layout I have the "name" field from the related record in X. The problem now is that when I want to enter a value that already exists into that field, it will show an error saying it needs to be unique (because "name" is defined to be unique in the X table, obviously).

The behavior that I would be looking for is that a new record is created if the value doesn't exist; if the value already exists I would like it to change to the right foreign key.

Does anybody have a simple explanation on how to achieve this?

Thanks!

Pim

[Søren Dyhr]

Since you're on FM10 wouldn't I hesitate to involve event triggers, for this purpose....

--sd

XY.zip

[Speekless]

Thanks -) That comes very close, but when I change the "name" value from the Y layout, it changes on all records that share that value. What I'd like to do is when the value is changed it just creates a new record in X.

So I'll have to tweak things a little bit, but I'm sure your example will come in very handy.

Maybe I'm wrong, but it must be possible to do this with only one TO of X (XY in your example), right?

I also was wondering, in your script, what is the "xor 1" part for after isEmpty?

Thanks for your time!

[Søren Dyhr]

Maybe I'm wrong, but it must be possible to do this with only one TO of X (XY in your example), right?

No I have not tossed it there just in vain, you need to make a selfjoin on the values already stored, to get the first occurrence of the value or rather the ID of the record. It should be said that several fields values could be matched to prevent two John Smith living in two different locations eliminate the other.

http://sixfriedrice.com/wp/deleting-duplicate-records-in-filemaker/

What I'd like to do is when the value is changed it just creates a new record in X.

Where exactly was this mentioned in the initial mission statement?

I also was wondering, in your script, what is the "xor 1" part for after isEmpty?

Similar to not IsEmpty( ...it's simply a logical switch which I find faster to hit when clicking the components to place in the equations.

--sd