5 Replies Latest reply on Mar 21, 2012 7:33 PM by MattLeach

    Set field to multiple values based on another field

    MattLeach

      Im working on refining my existing email/messaging system in one of my databases and i'm a bit stuck.

       

      We have 2 simple tables:

      1. Messaging

      2. Users

       

      On the messaging table, there is a To field which is a popup list of all users with a status of available (Value list based on Users table, username and status fields). This works great and will send a message to the selected user.

       

      What i would like to do is take things one step further and instead of sending the message to one user, send the message to all users that have a status of available.

       

      I'm stuck on how to calculate this. Ideally (if possible) i would like to have the Messaging::To field automatically have all "available" users in it at any given time and update automatically if a users status changes to or from "available"

       

      Any assistance would be greatly appreciated.

        • 1. Re: Set field to multiple values based on another field
          timwhisenant

          Matt,

           

          How does the status field get set? Set up a MessageTo field if you do not already have one and don’t use the raw data field in your users table.

           

          On status change to / from “available”  set the message to field to User & pilecrow char & “All”

           

          Then message “All”

           

           

           

          That would be the idea that your MessageTo Field would contain a List of two items User & All

           

           

           

          HTH,

           

          Tim

          • 2. Re: Set field to multiple values based on another field
            MattLeach

            Currently when a user logins the status is set to "available". When they logout it is changed to "offline". Additionally they have the ability to change the status to "away"

             

             

            timwhisenant wrote:

             

            Matt,

             

            How does the status field get set? Set up a MessageTo field if you do not already have one and don’t use the raw data field in your users table.

             

            On status change to / from “available”  set the message to field to User & pilecrow char & “All”

             

            Then message “All”

             

             

             

            That would be the idea that your MessageTo Field would contain a List of two items User & All

             

             

             

            HTH,

             

            Tim

             

            I do have a field in the Message table called "To" which is what we use now to send to a single user.

             

            Not sure what you mean by:

            On status change to / from “available” set the message to field to User & pilecrow char & “All”

             

            Then message “All”

            • 3. Re: Set field to multiple values based on another field
              ouidamac

              How I would actually solve this would depend on how many email addresses 50 or 50,000 might be in the To field and what the server's limitations on this activity might be. But maybe try this for now ~ On the Messaging table create a field calculation similar to this: Substitute ( List ( Users::Email) ; "¶" ; ", " ) and place this result in the To: field unstead. It produces a list (email1@me.com, email2#you.com). I hope you find this helpful.

              • 4. Re: Set field to multiple values based on another field
                ouidamac

                You can script it so that when you want to send to all "available" users, your script switches to the Users layout and performs a Find for "available" users, then use the Send Mail script step with the Collect addresses across found set checked in the To: of the Send Mail Options dialog box.

                SendMailOptions_small.png

                • 5. Re: Set field to multiple values based on another field
                  MattLeach

                  Thanks for the info but these are not email messages. This solution is for in-app messaging so the To field is actually a name and not an email address.