Find through global fields giving odd results

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'

    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.

Finding on the related Scores table:


Finding on calc field in the Student table:

Results are as expected.

This feels similar to Confusing 1 to Many Find behavior with relationship sorted by Date except in this case, the predicates do not seem to be matching properly. Is this an issue with doing a Find through a relationship defined by a Global field?

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.

Hope this helps.

In FileMaker, finding for "*" only, only finds fields that are non-empty.

In any case, the field in question is a numeric field, and the behavior is the same if I search for ">0" : I still get the wrong set of found records.

1 Like

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.

Here's a test file (about 200KB zipped)

username/password = admin/admin


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.
1 Like

@Malcolm Thanks for the ideas, but I'm pretty sure that's not what's going on here:

  • the global field is part of the relationship
  • most behaviors work as expected across the relationship, such as viewing, editing, creating
  • if I swap out the global field with a stored field (and set the value per-record) eveything works normally
  • it's only when (A) Finding and (B) the key field in the Parent table has Global storage that I see the bug.
1 Like

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:


Relationships cannot be based on calculated fields. That's one of the constraints calc fields have.

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).


That fits exactly with what I am saying.

The problems as I see it:

  1. 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.
  2. it's only when I make the field a calc field which depends on the global field that the error shows up.
  3. 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.