A strange primary key/foreign key question

[reflous]

I have two tables:

-------contact.db----------

contact_id (primary key)

note_id (foreign key)

---------------------------

-------notes.db------------

note_id (primary key)

note_text

---------------------------

So what I would like to do is have a unique note record created and somehow get that primary key into the contact table. This is probably an easy to do routine question but I've not been able to figure it out or find any related posts. I could make note_id = contact_id but that will cause other problems for me, I need a unique note_id key.

Thanks for any help.

[Kurt Knippel]

Well you have things a little backwards as far as I would do it. The Note.db is really a child of the Contact.db and not the other way around. I would make the Note.db contain the contact_id as a foreign key. You can them make a relationship from the Contact.db to the Note.db by that key. Just put the related field from the Note.db on the contact layout and when the user types something into that field Filemaker will automatically create the record and insert the contact_id.

[reflous]

How exactly does filemaker automatically create the record and insert the contact_id into the note.db?

Also, I can't use the contact_id because I also have tables like:

invoices.db

quotes.db

purchase_orders.db

that all need note references. So it would make sense to have contact.db contain an id for note_id and not have note.db contain contact_id

[CobaltSky]

Hi Reflous,

Part of the way FileMaker works is that if you have the option to 'Allow creation of related records' enabled for a given relationship, if you try to write to a field via that relationship when there isn't a record to write to, the target file acquires a new record and performs a procedure which is akin to a 'lookup' (except in 'reverse', so to speak) to acquire the correct key value from the record you are currently working from.

I agree with Kurt that the logical way to set it up is for Notes to be the child.

There is nothing stopping you from making Notes a child of more than one file at the same time. That way you can have users writing to the notes file from invoices, quotes, purchasing etc. Each file will only 'see' those notes which relate to its own records, so the multiple relationships to notes will not impinge on each other - but your notes will then all be in one place, with an ID/key field which allows you to identify where they each came from and what record in whch parent db they are each associated with.