Hi folks - I get the feeling this is staring me in the face, but I just can't seem to get it. I've been spending hours reading, but I can't get that missing conceptual link.

I'm an optometrist. I have many patients. Each patient has many consultations. When I see a patient, I want to start in that patient's details screen, update them, and then click a button that will open a new consultation screen, which will be automatically associated with that patient, showing name and age, etc.

(I guess it's no different to having a group of Customers, all of whom have many Orders).

But how do I get the new consult to 'know' it is associated with that patient?

I've learnt a bit about relationships, primary keys and foreign keys, and unique identifiers. I've set it up so that each patient on the Patient table has a unique auto-enter serial number, each consult has a unique consult number. The relationship is one patient has many consults. The relationship is defined by linking PatientID.pk with PatientID.fk on the consult table.

But when I open the new consult screen, the information doesn't automatically enter. If I actually type in the PatientID.pk value into the PatientID.fk value... ta da! all the information I wanted is suddenly there. But I can't seem to figure out how to make FMP do it for me.

Do I use a script? A calculation? Do I need to work it as multiple windows? I get the feeling this is just so obvious, but FMP has so many options I don't know which way to go...

Please help! I know I'll feel silly, but success is more important to me than pride!

Thanks in advance

As one of the first steps of the script to open the new consultation window set a variable, $patientID, with the value of the primary key for that patient.

Create the new record in the consultation table when the window is opened and set the new record's foreign key field with the value of the variable, $patientID

Thanks Efen! I'll give it a go. I knew it'd be simple... :(

...and set the new record's foreign key field with the value of the variable, $patientID

Okay, now I feel really dumb! I can set the variable, but I still can't see how I set the new record's foreign key field with the variable. Here's the script I have so far:

Set Variable[$PatientID; Value:Patient::PatientID.pk]

Enter Browse Mode {}

Go to Layout ["Interaction"(Interaction)]

New Record/Request

xxxx

Commit Records/Requests[]

That probably shows up that I'm winging it! Anyway, it works in opening a new consultation (Interaction) window, but nothing more.

xxx indicates where I figure the step to set the foreign key should be - but what command? Set Field? When I use Set Field I can see how it can use a calculation or another field, but I can't see how to get it to use the variable.

Eureka! I did it! I knew if I just fiddled enough it would come good - million monkeys and all that.

For anyone who is wanting to do this, here's the solution I found (there might be other, better solutions, but this works so far).

Set Variable (as above)

Enter Browse Mode

Go to Layout

New Record

Go to Field [interaction::PatientID.fk]

Set Field [$PatientID] (this is done by choosing Calculated Result and just typing $PatientID in the calculation box)

Commit Records

If there's a more elegant or robust way, I'm still interested to hear it - this is going to be mighty important in my patient records system!

Thanks for your help Efan.