6 Replies Latest reply on Dec 2, 2016 7:24 AM by erolst

    Count blank fields values with a calculation

    user28397

      Hi

      I'm trying to create a message system and i would like to show a badge that show the number of "unread messages". What i've done until now is:

       

      - Create a message table with: ID, Title, Date, Message text and 15 field (that are the number of users) that hold "read" or "unread" values.

       

      How can I count the number of "unread" messages from message table (and this for all users)?

      How can I hold different values for different users?

       

      Thank you.

        • 1. Re: Count blank fields values with a calculation
          philmodjunk

          For starters, instead of 15 different fields in the same record, add a related table with one related record for each intended recipient. That way, changing the number of users is a matter of adding more or fewer records--a data entry task rather than adding or removing fields--a design change.

           

          Your message title speaks of counting "blank" fields but your post describes fields with "read" and "unread" values. If the field has a value of "unread", Which is it? Using your current design, you could add a calculation field with one of the following expressions:

           

          To count fields with "unread" in them:

          (Field1 = "unread" ) + (Field2 = "unread) + ( field3 = "Unread" ) ... and so forth

           

          To count fields that are empty:

          IsEmpty ( field1 ) + Isempty ( field2 ) + ... and so forth

           

          Then define a summary field as the sum of this calculation field. If you show all record on this table, this summary field will provide the total number over all your messages.

           

          Using the design I am recommending you'd use either:

           

          Field = "unread"

           

          or

           

          IsEmpty ( "field" )

           

          and set your summary field to total it.

           

          Other methods using relationships or ExecuteSQL could also return the total count.

          • 2. Re: Count blank fields values with a calculation
            erolst

            user28397 wrote:

            - Create a message table with: ID, Title, Date, Message text and 15 field (that are the number of users) that hold "read" or "unread" values.

            That's not a good idea; you'd have to create a new field for every new user.

             

            Either

             

            - create a join table - User --< UserMessageStatus >-- Message, or

             

            - create one field in the Message to hold a list of IDs of those users who've read the message, and create a relationship User::id = Message_read::readByUserID; then the count of unread messages is all messages minus Count ( Message_read::readByUserID )

             

            - create two fields in the Message table - a list of IDs of those users who have not read the message, and another field with a list of those who have. That second field starts out empty, and the first field has an auto-enter calculation of a list of all current userIDs; whenever a user reads the message the first time, delete the ID in the unread field and move it to the read field.

             

            Now you can use two dedicated relationships:

            User::id = Message_read::notReadByUserID

            User::id = Message_read::readByUserID

             

            and eg Count() them individually.

             

            The join table approach takes more effort to set up, but is the way to go if you want to store more than just a yes/no fact (like eg meta-data like a time-stamp).

            • 3. Re: Count blank fields values with a calculation
              user28397

              Thank you,

               

              ok I try to do as you said.

              @create one field in the Message to hold a list of IDs of those users who've read the message,

              How can I take a list of IDs in the message table, with a calculation? and if yes how?

               

              Thanks

              • 4. Re: Count blank fields values with a calculation
                erolst

                user28397 wrote:

                How can I take a list of IDs in the message table, with a calculation? and if yes how?

                Upon reading your other post, I think that in your system the initial list of users not having read a message is simply the list of recipients; since a message is not for everyone to read, but only for the recipients, you don't have to care about all the other users.

                 

                If you store the recipients in a single field, then you could set the unread field simply to an auto-enter calculation that copies the contents of that field.

                 

                In the end the management of read vs unread would be informed by how you manage the recipients. If you create a join table (which would allow you to distinguish e.g. recipient, cc and bcc, if you want that), it would make sense to store a read flag there.

                • 5. Re: Count blank fields values with a calculation
                  user28397

                  erolst ha scritto:

                   

                  If you store the recipients in a single field, then you could set the unread field simply to an auto-enter calculation that copies the contents of that field.

                  I'm a starter so how can I store recipients in a single field and how to copy contents of that field with an auto-enter calculation?

                   

                  erolst ha scritto:

                   

                  In the end the management of read vs unread would be informed by how you manage the recipients. If you create a join table (which would allow you to distinguish e.g. recipient, cc and bcc, if you want that), it would make sense to store a read flag there.

                  What do you mean?

                  Thanks

                  • 6. Re: Count blank fields values with a calculation
                    erolst

                    user28397 wrote:

                    I'm a starter so how can I store recipients in a single field

                    Store them as a list, delimited by carriage-returns. That is the usual method for storing multiple values in a single field (for the relatively few occasions where this is a good approach). Whenever you format a field as a checkbox with a value list attached, the checked values are actually stored as a list (you can see that if you display the same field as an edit box).

                     

                    user28397 wrote:

                    If you create a join table (which would allow you to distinguish e.g. recipient, cc and bcc, if you want that), it would make sense to store a read flag there.

                    What do you mean?

                    Thanks

                     

                    That you could (and this might be a better solution) create a join table of recipients, as illustrated before:

                     

                    User --< Recipient (type, hasNotReadMessage) >-- Message

                     

                    Now you could set the hasNotReadMessage field to a default value of 1 and use a relationship

                    User -< Recipient_unread

                     

                    where

                     

                    User::id = Recipient_unread::id_user

                    User::constantOne = Recipient_unread::hasNotReadMessage

                     

                    Initial opening/reading of the message would set the field to empty/0, and it would no longer count as unread.

                     

                    As to how you can auto-enter values:

                     

                    FileMaker Pro 15 Help (it's a direct link to a topic, not a generic one)

                     

                    where the calculation can be simply a field reference.

                     

                    user28397 wrote:.

                    I'm a starter

                    Right, but please read the documentation anyway