Count does not count all records. Like any aggregate function it has three contexts in which it can be used:
Count ( RepeatingField ) -- the original method counts each repetition of the field that is not empty.
Count ( Field1 ; field2 ; field3), counts the number of fields in the list that are not empty
Count ( RelatedTable::Field ) count the number of related records where field is not empty.
and there is a corresponding "count of" summary field that can count the records where the specified field is not empty across the current found set.
So either method can count records where the field is 1, not records where the field is empty. To do that, you could count all the records and subtract the count of records where the field is set to 1.
ExecuteSQL might also be used to count the records where this field is Null.
ExecuteSQL ( "SELECT COUNT ( the_field ) FROM the_table WHERE the_field IS NULL" ; "" ; "" )
I tend to do this periodically as well...
I simply do a find for the flagged records, then "show omitted only".
(You could script it by showing all and omitting the flagged ones via find)
This not only gives you the count (via Get(FoundCount), it also puts you in the found set of stuff you have to address.
if your flag is 0 (or blank) or 1, then you can use something like this:
get(foundcount) - sum(flaggedField) or
count(relatedtable::primarykey) - sum(relatedtable::flaggedField)
sum(flaggedField) won't work - you'd need a summary field that is the sum of flaggedEvent (or a self-relationship).
But sum(related flaggedField) will work.
What if I do a filtered portal and filter for the flagged value and then add only a summary field that counts number of records, with the portal being only one row?
I could even make that summary field a count of record serial IDs.
First of all, disregard what I said about a self-relationship, above. That would only work in a special situation.
Secondly, what is your perspective on the flagged records? Are you in a related table and can use Count and Sum? Or are you seeing them locally?
Crap, you're right. I have a Dashboard for each User (I have an admin Dashboard where some fields are hidden to all other users other than me).
Technically, the flagged table records are in a table that is down the pipeline of related records.
So the portal idea won't work as I can't show related records to get there since the flagged table items will be created by different users.
Can I do a variation of what you said earlier: count(relatedtable::primarykey) - sum(relatedtable::flaggedField)
and just set no auto-fill data and checkbox for flagged = "1"? And somhow use a Summary Field for the sum of flaggedField? I know how to use the summary field in a portal, but not necessarily not in a portal.
What if I made a portal in the table linked to the flagged records table and put a summary field there, and then created another field in the main User table and just that field as a calculation equal to the summary field in the directly related table. Could that work?
Why not autoenter "1", and have the flag checkmark change it to zero?
Then all you need is a Sum()
How do I set that up? I have the auto-enter and the checkmark change it to zero. I set up a global field and put in the calculation Sum(table::flaggedField) and placed that global field on my main page, but nothing shows up. Do I need to create it not as a global field?
I think that what you need is a new two-condition relationship to the table with the flagged records. One of the conditions would be the one you have set up now. For the second,
- in your "master" table (layout based upon), add a global calculation number field (g_static_1) that is equal to 1
- in your new relationship, make the second condition flag < g_static_1
Now you have a "filtered" relationship. (You could use flag<1 in a portal filter, but you couldn't use the Count or Sum functions.)
If the record to be flagged is on a related table...
Set the flag field in the child as a number type.
Create a custom value list with values 0 and 1 (in that order)
Put the flag field as a checkbox on your layout, and size the field down so only the "0" checkbox shows
Add whatever title you want to show by the checkbox.
Now when you haven't flagged it, it has the value of 1...but the check mark doesn't show on your layout.
Now when you check the checkbox...you are checking it to "0"...but the zero also doesn't show on your layout.
Sum(ChildTable::FlagField) from the parent will add up all of the "unflagged" related records...
For your existing records, you'll have to cycle through and change flags to "0" and unflagged to "1" to handle the already existing stuff.
I'm trying what you listed but running into a snag.
How the tables are set up:
Users related to Contacts by ::MainContactID
UpdateCorrectContact table related to Contacts by ::MainContactID and also has a UserID entered into it by creator ID.
I have the checkmarks and the autoenter in the UpdateCorrectContact table and put the Sum() field in the Contacts table. Technically, the Contacts table does is not the parent, is sort of a parallel table. I'm not sure if the UpdateCorrectContact table has a parent table fully.
Right now, the Sum() just gives me back "1" constantly. I am testing it with 3 records all marked "1" so should read "3".
David M -- I am reading your responses as well, though they are more complicated (so I will have to read up more on them) so thought trying the simpler one, if possible, would be a logical first approach.
Make a new table occurrence of UpdateCorrectContact table and cartesian join it so all records are related.
This will get all of the relationships and subsequent filtering out of your way...assuming that's what you want.
Doing the Sum() through relationships will affect your values according to the related records schema.