I’m working on an application that has at least 2 tables named “Note” and “Subject”.
Both tables have a field named “GenAcct” into which the system auto-enters the creation account name.
The Note table has a SubjectID field that may not be empty. The field Note::SubjectID is the foreign key of the related Subject table record. The SubjectID field on the Note form layout is represented by a pop-up menu control. Originally the value list for this control was the primary key field of the Subject table and the second (display) field was Subject::Name. The value list showed all records and it worked very well.
Next I took steps to ensure that each logged-in user could access only Note and Subject records created by that user. That worked very well also.
The train came off the tracks when I tried to filter the Subject value list so that it would only let the user choose a Subject (to relate to the Note) from a value list of Subjects created by the logged-in user. To accomplish this I created a new calculation field “Note::AccountName” that returns the value of the function Get (AccountName). I added an occurrence of the Subject table to the NOTE table occurrence group with the relationship NOTE::AccountName = note_SUBJECT::GenAcct and I changed the Subjects value list so that it was based upon this new table occurrence and to include only values starting from the NOTE table occurrence.
My database has two privilege sets and two accounts. One of the privilege sets is “[Full Access]” and I call the other “User”. One of the accounts is called “Admin” and uses the “[Full Access]” privilege set and the other account is called “JMcI” (my initials) and it uses the “User” privilege set.
When the user logs in as “Admin”, everything works very well. In the Note form layout the SubjectID pop-up menu shows the “Name” of the related Subject record and, when the user clicks on that control, it is populated only with Subjects created by the user “Admin”.
Unfortunately, when the user logs in as “JMcI”, the pop-up menu representing the field Note::SubjectID on the Note form layout shows the actual value of the field Note::SubjectID (the foreign key value) rather than the display field from the value list. When the user clicks on that pop-up menu it displays only the Subjects created by the “Admin” account.
When I examine all records in the Note table I see that the calculation field “Note::AccountName” is returning “Admin” in each record whether the user was logged as the “Admin” account or the “JMcI” account. Why is that? Can somebody suggest the right way to filter the Subject value list?