Courses::_Constant1 = Searches::Shared AND
Courses::UserID = Searches::CreatorID
Would work for your conditional value list. The trick is in getting a value into Courses::UserID--which can be a global field but must be defined in the Courses table. If Users::UserID is a global field, you can define Courses::UserID as a calculation field that refers to Users::UserID. This produces an unstored calculation field, but it will work for this relationship. The value could also be updated by a script.
Ah, I see. Makes sense. I could set a script trigger on the drop down box to populate a new global in the Courses to the user's ID.
This works great...except, that I want to see 'Yours' OR Shared Searches. Not Yours AND Shared. Is there a way to do an OR test in a relationship? Or to combine to result sets into one list? :)
An "OR" can be done bit it takes a different approach.
Define a calculation field in each table.
In courses, define cKeyList as:
List ( _Constant1 ; UserID )
in Searches, define cKeyList as:
List (Shared ; CreatorID )
Courses::cKeyList = Searches::cKeyList
And make sure that the value used to mark a searches record as "shared" is never a possible value for a UserID.
Return separated values used in a relationship will match to any record where any one of the values in cKeyList in the courses table match to any one of the values in cKeyList of a Searches table.
I think I get it. Using List() creates a return separated value list, the first having "1" and "UserID" as the list elements. (For mine I used the "gUserID" field that we talked about before, where I am setting the User's ID in a global field via script trigger when entering this layout.) The second one has "share_flag" and "creatorID" as its elements. This is for each record in the table (i.e. not global). Then the relationship will match on any of the two elements from the first list to any of the two elements on the second list.
My implementation is failing, though. I set up the fields, changed the relationship between Courses and Searches to use the new calc field. But I get a "index missing" error when I use the value list that uses this relationship. Any ideas on that?
The list calculation field used in Searches must be a stored, indexed field. You'll need to figure out why it is not and what to do to correct that.
Ah, that did the trick! Thanks, Phil! Seems to be working well.