Field Lookup Failing, Form saves the wrong record.

[MyBrainIsFull]

Hi all, I have a problem with a lookup field on a join table
That is, I have a table that joins students to courses called StudentCourseJoin obviously
it holds an id for both course and student

The dictionary has RI for these, the SCJ:Student_Key has a single field SCJ:StudentID
it links to the student primary key which has a single field the STU:ID   nothing shonky here!

So in the form Update_StudentCourseJoin the id field has a lookup on the student table which it orders by student last name

The student table has thousands of records, my end users has found that if they select a last name of Thorpe the lookup list shows 5 people with that name.   

If you select the third person named Thorpe - in this case "G Thorpe" then a trace statement after lookup shows id=20946,  as does postUpdate  BUT the id that is saved is ALWAYS the first Thorpe,  Sarah ID=20944

I have tried creating this form from scratch using the Wizard template so there is no code interfering with the save, and it does the same behavior

attached is the dictionary RI and a view of the students and the settings for the lookup



[attachment deleted by admin]

[Bruce]

Hi Kevin,

This effect happens when you have "show description instead of value" turned on - but the description field/key is not unique.

If "thorpe" is all that is displayed (regardless of which thorpe was selected) then that's all that is submitted with the form - so it's all the server has to go on when selecting a record.

The solution to this is to create / use a unique description field - one which resolves to a single record. This can be done by createing a new description field and key which is unique.

For example - I have a list of customers. Clearly first names or last names or indeed whole names are not unique. So I have a field in the table which contains a string containing the first name, last name, and customer number.

the user never "enters" this field - it is constructed from the component parts when they add or change a record via a form. However lookups can use this field as the "description" field, so the drop shows more information, and more importantly the more information is sent when the Save button is pressed.

cheers
Bruce

[MyBrainIsFull]

Thanks for that Bruce. But I dont understand the problem.  The lookup is retrieving the ID for the correct Student (last person is 23355  Peter)  and the session queue is holding that value

- see my screen shot of my server, its session queue is showing the join table holding  SCJ:StudentId=23355

Are you saying you ignore this ID when you write the record back to the database by trying to re read the lookup on Student using "Thorpe"  ?

I would have thought that once the session queue was set to the right value then that is what is written to disk.

[attachment deleted by admin]

[Bruce]

>> Are you saying you ignore this ID when you write the record back to the database by trying to re read the lookup on Student using "Thorpe"  ?

yes.

>> I would have thought that once the session queue was set to the right value then that is what is written to disk.

It's not that simple - although making it a bit smarter is probably do'able. (It's not easy to just use the session value because the session value may not be set - or more accurately I don't know if it's set or not.) For example say the person doesn't use the lookup at all - or just edits an existing record etc.

I suspect it could be smarter, but at the moment it needs the unique key to be sure.

Cheers
Bruce