See if this gets you started, in a calculation field called "Missing":
Count(Table::MasterList) ≠ Count(Table::Counted);
"Missing " & Count(Table::MasterList) - Count(Table::Counted); //This will show "Missing 1" (or any amount of difference)
Valuecount ( MasterList ) - valueCount ( FilterValues (Masterlist ; completed ) )
Can be a better option if there is any possibility of getting duplicate values in the Completed field.
Thank you both - I greatly appreciate it! This gets me going in the correct direction.
After getting this working correctly, I'm having an issue filtering out specific items in my master list. Some of the items are tagged as Optional = "Yes", and I don't want them showing up in my list.
In order to create my master list, I have a formula [ List ( course_PROJECT__list::__kP_Project ) ] looking down a relationship from Course::kP_Course and Course::Subject to Project::kF_Course and Project::Subject. This creates my master list of projects that are related to a specific course and subject within that course.
Course::kP_Course and Course::Subject -< Project::kF_Course and Project::Subject
However, when I attempt to add in an additional relationship field of Course::g_Constant_Yes =! Project::Optional into the above grouping, my master list does not show any projects. I also tried a self-join with another TO of the Project table to filter out the optional projects, with the same result.
I'm sure I'm missing something basic here... either that, or my entire understanding of relationships is flawed (likely!).
What data types are g_Constant and Project::Optional?
The values and data types need to be exactly right on both sides of this relationship.
You might also consider using ExecuteSQL to generate your lists of items.
The data types are all text, but the pK and fK values are numbers (type is text, however).
I was afraid I would need ExecuteSQL to get this done efficiently. I'm just getting into Filemaker, so it's back to the books...
ExecuteSQL can take the type of list building done with the List function to the next level and simplify your relationship graph at the same time. On the negative side, you have to do it with SQL and with a developer interface that can be pretty frustrating.
Course::g_Constant_Yes =! Project::Optional
so does the "g" stand for global storage or a calculation field with global storage?
If a calculation field, remove the global storage option and leave it as a stored, indexed calculation field.
Check the exact values in these two fields. It actually sounds like one of them is empty given the ≠ operator if I understand what you meant by !=.
Of course that's why it wouldn't work...
g_Constant_Yes was actually pointing to a global calculation field in another table (resources table) which was set to always show "yes" - I changed it to a stored, indexed calculation and it works like a charm.
And yes, I meant ≠ when using !=.
Thanks so much!