Consider this setup, for a student/assignment grading database:
Student Table:
ID
gAssignmentKind
Scores Table:
StudentID
AssignmentKind
Score
Each student has 1 or more Scores in the Score table - but there's never more than one StudentID + Kind + AssignmentID combo per student in the score table, e.g. no more than one Assignment of a particular kind per student.
There's a relationship between Student and Scores tables called "Scores to Student by gAssignmentKind'
where
Student::ID = Assignment::StudentID and
Student::gAssignmentKind = Scores::AssignmentKind
gAssignmentKind is a popup global field, so you can change the popup and view/edit a specific assignment.
This all works great, except that some finds don't work properly.
From the student table, if you find for Scores to Student by gKind::Scores = "*"
The record set returned is incorrect.
However, if I set a calculated field in the Student table: Student:cHasScore = If(Scores to Student by gKind::Scores = "*", 1, 0)
Then the find works correctly.
If I understand, you want to find all scores for a given kind of assignment. The * predicate is a text predicate. It means zero or more characters. This would return empty results. You probably mean to use .* (period, followed by asterisk). This means a character followed by zero or more characters.
Interesting - If I change the Global gAssignmentKind field to a regular field (and set the value properly for each student record) then the find works as expected, and finding for "*" or ">0" in the related table no longer shows records with nothing in the child table.
After more testing, I'm pretty sure I undersand what's happening:
From within a parent table
when you find for a field in a child table
and the parent/child relationship includes
one or more normal (stored, indexed) fields (on parent and child side)
and one or more global fields (on the parent side)
then records in the Parent table will be found if they have any match in the child table, regardless of the value of the Global field. It's as the if Global field is ignored.
in my tests above, although Charlie had no Midterm score, he did have a Quiz score, which is apparently what causes his record to show, even when we are searching for "Midterm". If I remove Charlie's Quiz score (so he has no scores at all) then the find works as expected.
I've been a bit too busy to look at this thread earlier.
Finding across a relationship is a shortcut for doing the search manually. It's exactly the same, just a lot easier. If you had to write it in SQL you would be doing this:
Go to the "last" table and search for the thing you want.
You then have to go back. This time you have a list of IDs from the records you found.
You want to be able to go back up the chain to show every record that matches the relationship.
for each step in the relationship (could be multiple tables in between)
Get the foreign keys in the found set
Go to the next table and find records that match those keys
you'll finally get back to the next-to-the-original table. You are going to take the foreign keys in the relationship and use them to search the original table.
When you go to that table that search for keys in a normal field, you do a find and get the records that match. Not all records will match.
When you search the global field, by definition every single record shares the value of that global field, so every single record is a match. So the global field doesn't modify the found set, it is going to match every record. The only active filter is the other key or keys in the relationship.
Another test - if, instead of making the relationship depend on a global field, I create a calculated field in each record which is equal to the global field, and use the calc field in the relationship.
In this case, the relationship appears to work normally (I can view and edit records in the child table). However, if I try to do a find on the child table through the relationship, now I get an error:
That statement is too general. The local side of a relationship can be either a Stored or Unstored calc. (And, a stored calc can be used on the remote side of a relationship, as well - it behaves just like a static stored field in relationships). Both work in Browse/Preview mode to view related records (where the remote/related side are stored static/calc fields). However, relationships that use unstored calc fields for the local side will not work in Find mode. You can view related records, but not SEARCH on those related fields, because the unstored value is not available while not ON a specific record already (in Browse/Preview mode).
Stored calls work in Browse AND Find mode, but there are obviously some things you cannot store (calcs where you want a live Get function result, or calls based on globals or other unstored fields/calcs).
So, in the example @xochi gave, there is a calc that is based on a global (so, unstored) included in the relationship. That doesn't work in Find mode, giving the error: "This operation cannot be performed because one or more of the relationships between these tables are invalid." That error is correct: the unstored field won't work in Find mode to be a relationship criteria, and so the relationship fails in Find mode (but works in Browse/Preview mode).
this error message only shows up sometimes - in the case when the global field itself is part of the relationship, the Find operation completes, returns the wrong data, and no error.
it's only when I make the field a calc field which depends on the global field that the error shows up.
the "Works fine in Browse, breaks in Find mode" is also unexpected behavior and hard to understand.
Behavior that silently fails really seems like a bug to me - it's a pretty subtle "footgun" / "gotcha" situation.
