Look-Up from last related record

[BruceJ]

I have a solution that needs to auto-fill certain fields.

For example - a medical record: The first visit a patient answers YES to a question about smoking.

Then several months latter the patient returns and the same assessment is completed, the smoking field defaults (look up) and fill in YES in the smoking field, but then the patient says they stopped smoking so it needs to be changed to NO.

Then several months latter, same patient returns, same assessment, and NO is now the default (look up) for this question.

I've got lots instances of this type of issue... does anyone have a simple, straight forward solution.

[comment]

Make a selfjoin:

Form::PatientID = Form 2::PatientID

AND

Form::SerialID > Form 2::SerialID

Sort the relationship on Form 2 side by SerialID, descending. Now the first related record is the previous form filled out for this patient.

[BruceJ]

Make a selfjoin:

Form::PatientID = Form 2::PatientID

AND

Form::SerialID > Form 2::SerialID

Sort the relationship on Form 2 side by SerialID, descending. Now the first related record is the previous form filled out for this patient.

When I do this the look-up pulls form the first created related record reguardless of how I sort them. Am I doing something wrong?

[comment]

It works for me, so probably yes.

[Genx]

lol, im sorry, i just had to laugh at that

[BruceJ]

Yep - I was doing something wrong!

The sort has to be specified in the relationship definition. I was just going to the layout and doing a sort.

Man! This is a lifesaver - you can't imagine! Many thanks!