Child-parent record creation

April 27, 200916 yr

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

April 28, 200916 yr

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

--sd

XY.zip

April 28, 200916 yr

Author

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!

April 28, 200916 yr

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/