1 2 Previous Next 19 Replies Latest reply on Oct 18, 2016 1:32 PM by ezeitgeist

    Count flagged records


      I searched the archives and found some sort of similar discussions, but not exactly.


      Essentially, I have a table of records that are User generated. As admin, once I have seen and dealt with the submitted record, I have added a checkbox marking that I have dealt with it. I'd like to set something up where it counts the number of records that I have NOT dealt with yet (ie. has an empty "Admin dealt with" field rather than the "1" assigned when checked). How exactly do I do this? I'm just learning the Count function but it seems to only apply to counting ALL records. I am likely just confused on proper use.

        • 1. Re: Count flagged records

          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.

          • 2. Re: Count flagged records

            ExecuteSQL ( "SELECT COUNT ( the_field ) FROM the_table WHERE the_field IS NULL" ; "" ; "" )

            • 3. Re: Count flagged records

              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.



              • 4. Re: Count flagged records
                David Moyer


                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)

                • 5. Re: Count flagged records
                  David Moyer


                  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.

                  • 6. Re: Count flagged records

                    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.

                    • 7. Re: Count flagged records
                      David Moyer

                      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?

                      • 8. Re: Count flagged records

                        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?

                        • 9. Re: Count flagged records

                          Why not autoenter "1", and have the flag checkmark change it to zero?

                          Then all you need is a Sum()

                          • 10. Re: Count flagged records

                            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?

                            • 11. Re: Count flagged records
                              David Moyer


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

                              • 12. Re: Count flagged records

                                If the record to be flagged is on a related table...


                                Set the flag field in the child as a number type.

                                Autoenter "1"

                                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.



                                • 13. Re: Count flagged records

                                  I'm trying what you listed but running into a snag.


                                  How the tables are set up:


                                  Users table

                                  Contacts table

                                  UpdateCorrectContact table


                                  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.

                                  • 14. Re: Count flagged records

                                    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.

                                    1 2 Previous Next