Create a PowerApps chatbox to communicate about SharePoint items

Recently, I was asked by a customer to create a process (in SharePoint) that empowers users to report disruptions. Some of the requirements (such as scanning bar-codes and reporting on an iPad) made me decide to create a PowerApp for reporting the disruptions.

They also wanted the reporter and the handler to have the ability to communicate with each other on a specific distuption so I decided to play around a bit to see if I could incorporate this into the PowerApp and a chatbox was born:

This blogpost describes how I created that chatbox into my PowerApp.

Storing chat feed

First of all, we need to create a storage container for our chat feed. Since I’m using SharePoint, I will create a new multiple lines of text field (no rich text!) in which the chat feed will be stored. Each message will be stored on a new line within that field. Each line will contain the following information, seperated by semicolons:

  • Date and time when the message was posted
  • Sender type. I distinguished the disruption reporter as Reporter and the disruption solver as Handler.
  • Sender e-mail address. This is used to differentiate the Reporter from the Handler
  • The actual chat message

An example of this format is shown below:

21-05-2019 12:54:14;Reporter;rik@contoso.com;Hi, can I ask you a question?
21-05-2019 12:54:19;Handler;admin@contoso.com;Well of course you can!
21-05-2019 12:54:26;Reporter;rik@contoso.com;Great
21-05-2019 12:54:37;Reporter;rik@contoso.com;How do I create a chatbox in my PowerApp?
21-05-2019 12:54:48;Handler;admin@contoso.com;Well, that's easy. Just follow my blog on https://about365.nl!
21-05-2019 12:54:55;Reporter;rik@contoso.com;Thanks, will do!

Configuring the chatbox in PowerApps

Now that we configured SharePoint to store our chat feed, we can proceed to PowerApps to configure our chatbox. To do this, we must complete a few steps:

  • Identifying the SharePoint item
  • Loading the chat feed into PowerApps
  • Separating the information
  • Distinguish the messages from the Reporter and from the Handler
  • Posting new chat messages
  • (Auto) refresh the chat feed

Identifying SharePoint item

When you are using a PowerApps form on your SharePoint list, you can skip this step. But if you’re using a lossless canvas app, you need to make sure your PowerApp knows which item has to be pulled from SharePoint. You can do this by using a parameter. Parameters can be used to pass values from the URL to your PowerApp by putting ?<ParameterName>=<VALUE> at the end of the Web link of your PowerApp:

https://web.powerapps.com/apps/12a34b5c-678d-90e1-f23g-456h78901hij?itemID=1

In your PowerApp, you can pick up this parameter by using the Param() function and setting it to a global variable using the Set() function in your OnStart event:

Set(
    itemID,
    Param("itemID")
)

Please note that parameters are always in String format, so we need to transform this value into an integer when using a lookup on item ID using the Value() function.

Loading chat feed

When the PowerApp is started, you need to show all the messages that have been posted already. Do do this, you must add your SharePoint list as a new DataSource to your PowerApp. After that, you can load the chat feed from the specific field in your list into a collection in your PowerApp using the ClearCollect() function on your OnStart event:

If(
    !IsBlank(
        LookUp(
            Requests,
            ID = Value(itemID),
            Communication
        )
    ),
    ClearCollect(
        communication,
        Split(
            LookUp(
                Requests,
                ID = Value(itemID),
                Communication
            ),
            Char(10)
        )
    )
)

Breaking this down into smaller portions:

  • If(!IsBlank( will check if the result from your condition is not blank. Please note that PowerApps distinguishes the IsBlank() and the IsEmpty() function. IsBlank() will check if the result is equal to an empty string. IsEmpty() will check if the result is equal to null.
  • Lookup(Requests,ID = Value(itemID),Communication) is the actual lookup to the SharePoint list. Requests is the name of the list. ID is the field we want to filter our itemID on. Since itemID is a variable that has been triggered from a parameter, it is set as a string variable. To transform it into an integer, we need to use the Value() function. Communication is the field we want to retrieve.
  • If our condition (the above lookup does not return an empty string) is met, ClearCollect(communication, will fill the communication collection with:
  • Split(LookUp(Requests,ID = Value(itemID),Communication ),Char(10)). This function will split the result from our Lookup() (see second bulletpoint) into seperate rows. The seperator for the Split() function is a newline character, which is Char(10).

If you’re testing, you must set your itemID variable manually because parameters only work in Play mode. You can easily do this by commenting out the Param() function (by using a double slash) and setting your own value. Make sure you use a string value and uncomment the Param() function again when done testing!

Set(
    itemID,
    //Param("itemID")
    "1"
)

When running your OnStart event, your collection will be filled:

Separating chat information

Now that we have each chat message stored into our collection, we need display the correct information in our PowerApp by seperating each type of information (date, sender type, sender e-mail, message).

First of all, we need to create a container that contains our chat feed. For this, I used a Gallery that shows the contents of our communication collection, sorted on date:

Sort(
    communication,
    DateTimeValue(
        First(
            Split(
                Result,
                ";"
            )
        ).Result
    ),
    Descending
)

With the DateTimeValue() function, you tell PowerApps that the value you’re using is of a DateTime format. The First() function is used to extract the first part of the result from the Split() function (since date is the first information type in our row). We need to split the Result column from our collection into pieces where the semicolon (“;”) is the separator on which we split our information.

Inside the gallery, three types of controls are used:

  • A hidden label for the message itself – lblChatMessage
  • A label for the chat information (date and sender type) – lblChatInformation
  • A button that shows the message in a ‘WhatsApp-like’ format. – btnChatMessage
    This is not really necessary, but I found it easier on the eye than a square format that you get with a label.
lblChatMessage

The message itself is the last information type in our row, so we can use the Last() function around the Split() function:

Last(
    Split(
        ThisItem.Result,
        ";"
    )
).Result

Since we don’t know the exact size of the message, I’ve set the AutoHeight property to true and since I only use this control for the AutoHeight property (the actual message will be shown in the btnChatMessage control), the Visible property is set to false.

lblChatInformation

This control shows both date and sender type.

  • Date is the first information type, so we can use the First() function around the Split() function.
  • Sender type is the second information type. There is no method to get a middle section from your Split() result so we must split this into:
    1. Getting the first two parts of information by using the FirstN() function: FirstN(Split(ThisItem.Result,”;”),2).Result
    2. Getting the last part of information from the above result using the Last() function

I want to combine both results with a piece of text in the middle: <date> by <sender type>. You can combine results by using &:

First(
    Split(
        ThisItem.Result,
        ";"
    )
).Result & " by " & Last(
    FirstN(
        Split(
            ThisItem.Result,
            ";"
        ),
        2
    ).Result
).Result
btnChatMessage

This control shows the actual message that we’ve configured into lblChatMessage, so we can set the Text property to lblChatMessage.Text. The Height property must also be related from the lblChatMessage control by setting the property to lblChatMessage.Height.

Distinguish messages

Now that we’ve configured our gallery to show the correct information in the correct controls, we must distinguish the messages from the Reporter and from the Handler. If you don’t, messages will be shown underneath each other with no clear separation:

There are different ways to to that:

  1. Providing information on who posted the message. We actually did that already by filling the lblChatInformation with sender type.
  2. Separating the messages by X location
  3. Separating the messages by colorization
Separating the messages by X location

First of all, we need to distinguish Reporter from Sender. That is where the third part of our chat information (sender e-mail) comes in: we need to check if the current logged in user is the one who wrote the current message.

If(
    Lower(
        Last(
            FirstN(
                Split(
                    ThisItem.Result,
                    ";"
                ),
                3
            ).Result
        ).Result
    ) = Lower(User().Email),
    0,
    galChatbox.Width/2 - 25
)

Sender e-mail is the third part of our chat information, so we must split this into:

  1. Getting the first three parts of information by using the FirstN() function: FirstN(Split(ThisItem.Result,”;”),3).Result
  2. Getting the last part of information from the above result using the Last() function

To compare that against the e-mail address of the current logged in user, you can use the User().Email function. Since conditions are case sensitive, I put a Lower() function around both results to make sure the results are always lowercase.

If the condition is met (current user is the one who posted the message), btnChatMessage will be placed on X coordinate 0. If not, btnChatMessage will be placed on the other half of the gallery, which is the half of the width of the gallery (galChatbox.Width/2). To ensure your scrollbar won’t be placed over your message I subtracted 25 from that X coordinate:

Separating the messages by colorization

To make it even more appealing to the eye, you can assign a different color to Reporter and Handler by setting the Fill (and HoverFill when using a button!) to different values based on the X coordinate of the button:

If(
    btnChatMessage.X = 0,
    RGBA(235,117,0,1),
    RGBA(56,96,178,1)
)

If the X coordinate is equal to 0, the fill property will be set to orange. If not, the Fill property will be set to blue:

Posting new messages

To prevent additional lookups to your list during your postback to SharePoint, I added one extra variable to the OnStart event of my PowerApp: reporter, which contains the e-mail address of the reporter.

Set(
    reporter,
    LookUp(
        Requests,
        ID = Value(itemID),
        'Created By'.Email
    )
)

To write a message back to SharePoint, you can use the Patch() function on the OnSelect property of your Send button:

Patch(
    Requests,
    {ID: Value(itemID)},
    {
        Communication: Concatenate(
            Concat(
                communication,
                Result,
                Char(10)
            ),
            If(
                !IsEmpty(communication),
                Char(10)
            ),
            Concatenate(
                Text(
                    Now(),
                    "[$-nl-NL]dd-mm-yyyy HH:mm:ss"
                ),
                ";",
                If(
                    Lower(User().Email) = Lower(reporter),
                    "Reporter",
                    "Handler"
                ),
                ";",
                User().Email,
                ";",
                txtChat.Text
            )
        )
    }
)

Breaking this down into smaller portions:

  • Requests is the SharePoint list we want to patch
  • {ID: Value(itemID)} is the ID of the list item we want to patch
  • {Communication: is the field we want to patch
  • The Concatenate() function is used to combine all of the chat information into one single row according to the format we defined in the beginning
    • The first thing we need to combine is the chat history. This is each row in our communication collection. By using the Concat() function, you can convert a collection/array/table into a string value. We combine everything from the Results column inside the communication collection with a newline character (which is Char(10)).
    • If the communication collection is empty (please note that we are using the IsEmpty() function here and not the IsBlank() function!), it means that there is no chat history so that won’t be patched into SharePoint. If the communication collection is not empty, we need to add an additional line break (Char(10)) to the chat history to make sure the new message is stored on a new line in SharePoint.
    • After that, we can add the actual chat information which needs to be put in an additional Concatenate() function.
      • First, we need to add the current date using the Now() function into a string format  according to your liking using the Text() function. Since I’m based in The Netherlands, I use the Dutch date format, which is “[$-nl-NL]dd-mm-yyyy HH:mm:ss”.
      • The semicolon (“;”) is used to seperate each part of chat information, so it also needs to be added after date.
      • The second part of chat information is the sender type. For this, we use the reporter variable we just created on our OnStart event to compare against the current logged in user (User().Email), both in lower case using the Lower() function. If they match, it means the current user is a “Reporter”. Otherwise, the current user is a “Handler”.
      • The semicolon (“;”) is used to seperate each part of chat information, so it also needs to be added after sender type.
      • The third part of chat information is the sender e-mail address which is User().Email.
      • The semicolon (“;”) is used to seperate each part of chat information, so it also needs to be added after sender email.
      • The fourth (and final) part of chat information is the actual chat message which is the outcome of your Text Input control: txtChat.Text.

After the message is stored in SharePoint, you need to clear the contents of your Text Input by using the Reset() function after the Patch() function:

Reset(txtChat)

Refreshing the feed

To make sure you always have the latest chat feed on your screen, it is necessary to refresh your feed. You can choose to do this in seperate ways (or combined):

  • After posting the message back to SharePoint
  • Manually with a refresh button
  • Automatically using a Timer control

Refreshing consists of two actions:

  1. Refreshing your DataSource
  2. Updating your collection
Refresh DataSource

Refreshing your DataSource can be done using the Refresh() function:

Refresh(Requests)
Update collection

This is done the same way you initially load your communication collection (using the ClearCollect() function on your OnStart event)

Timer control

To use this method of refreshing, set the AutoStart property to true, the Duration property to the desired refresh interval (in milliseconds), the Repeat property to true and the Visible property to false.

When using a Timer control, keep in mind that you will pull a lot of data in a short time!

80 Replies to “Create a PowerApps chatbox to communicate about SharePoint items”

  1. Hello, Rik
    I have a question regarding Posting new messages part.
    In my app Text input control is a part of a form, so I would like to know if there is a way to replace Patch function with other? Or this function also can be used for forms?
    Right now my “Send” button has following functions:
    SubmitForm(Form5)
    ClearCollect(communication, ‘SP chat’)
    Patch function that described in your post

    When i click Send button, in my Sp list 2 row appears with message that was inputted: 1 is normal message and 2 with format from your post

    0
    0
    1. It should be possible to do this in a form as well. You need to map the chat history with the newly generated chat message to your DataCard to make sure the field gets updated correctly, so I think you need to play around with the Default value of your DataCard/textbox or the Update property of your DataCard. When using a form you don’t need the Patch() function anymore

      0
      0
  2. Thanks Rik. So I added a lebel in the chat screen just to show which item is selected. And It actually shows the right id each time an item is selected. It’s the chats that don’t get loaded to the collection. Even if I post a message, it get’s posted to the right item overwriting the previous existing comments for that item

    0
    0
    1. This is the formula:
      ClearCollect(
      CommentsCol,
      Split(
      LookUp(
      WhistleBlowing,
      ID = Value(itemID),
      Comments
      ),
      Char(10)
      )
      )

      CommentsCol is the name of the collection




      0



      0
      0
      0
    2. This is the formula am using for the collection
      ClearCollect(
      CommentsCol,
      Split(
      LookUp(
      WhistleBlowing,
      ID = Value(itemID),
      Comments
      ),
      Char(10)
      )
      )




      0



      0
      0
      0
    3. Yes the itemID is populated correctly. I can see the right ID’s selected each time I select an item.

      0
      0
    4. What happens when you just do the lookup to the Comments column (no clearcollect and split actions) and put that into a label? Does that show the contents of the column?

      0
      0
    5. So I tried using just the look up and it doesn’t show the contents. on checking I realized that the ID of the Item I select is not passed to the chat. I have a gallery from where I select the items from.
      When I test with this.
      Set(
      itemID,
      //Param(“itemID”)
      “1”
      )
      The comments for the first item get populated. How do I pass the ID of the selected Item from the Gallery to the chatscreen (or to the itemID parameter)

      0
      0
    6. So if I understand correctly, you want to pass the item ID from inside the Power App? Then you don’t need to use a parameter, that’s only when you want to provide information when your app is loading. You can just use the Gallery.Selected.ID inside your Lookup, of change the OnSelect of your gallery to Set(itemID,Gallery.Selected.ID). Please note that both provide a numeric value for ID instead of a string value when using a parameter, so you don’t need the Value() function anymore inside your lookup.

      0
      0
  3. Hi Rik. Am not sure of this. I just noticed that for every item I select, when I post a new message, the chats from the first item(itemID) 1 gets posted to SharePoint with the message that I’ve just posted

    0
    0
    1. Please check your parameter, it seems like its not being used properly. The itemID parameter should contain the ID of the SharePoint item you want to chat about

      0
      0
    2. Hi Rik. I have the App on start set as follows:
      Launch(“https://apps.powerapps.com/play/915e38b0-8b1b-4ec2-8e9d-f46195f29b67?tenantId=f22d8bae-xxxx-4d32-xxxx-70cb4ff72c3d?itemID=1”);
      Set(
      itemID,
      Param(“itemID”)
      );

      This doesn’t load the chats for me. How do I make sure that the selected item is the one passed to the parameter.

      0
      0
    3. ?itemID=1 should be &itemID=1, so ampersand instead of questionmark because you the ? is already used for tenantId. Any other function/parameter that follows needs to be called with an &

      0
      0
  4. Hi Rik.
    I followed your this and I have everything set up bit for some reason I can’t get the chats to load but when I post a new message it gets posted successfully but deletes the already existing messages in that column for that item. Not sure what am doing wrong.

    1
    0
    1. Please make sure to load the existing chat messages into the communication collection by using the correct itemID parameter. Is this collection filled when you load the app?

      0
      0
  5. This lookup ?
    !IsBlank(
    LookUp(
    ‘Submitted Requests’,
    ID = Value(recID),
    Title
    )
    ),
    ClearCollect(
    communication,
    Split(
    LookUp(
    ‘Submitted Requests’,
    ID = Value(recID),
    Title
    ),
    Char(10)
    )
    )
    );

    0
    0
  6. I see, I have it like this because the ID is not relevant until the gallery is clicked on for a specific record. So for the chatbox how can I get the ID in there? The chatbox is within the datacard and has a separate submission button than the form.

    0
    0
    1. Yes, but you are using the lookup on the OnStart which is the first thing your Power App is running. So at the OnStart, your Power App doesn’t know that your gallery has provided the ID

      0
      0
  7. This is all in my onStart – Set(recID,Value(Param(“TestID”))); If(recID 0, Set(varRecord,LookUp(‘Submitted Requests’, ID = recID));Navigate(‘CR Form’));

    Then I have a gallery and this is in the onSelect – Set(recID,ThisItem.ID); which takes me to the correct record. I added a new column called Communication to my form. I don’t want the form to submit when a chat is submitted it is just a chatbox i want at the bottom of my form.

    0
    0
    1. Looks like you set the recID variable after the Lookup() function. That’s why your lookup doesn’t return anything. If you’re using a form, you can use the SharePointIntegration to get the ID

      0
      0
  8. My chatbox is in a form and the data card that it is inside is called communication. In my onStart I have this – If(recID 0, Set(varRecord,LookUp(‘Submitted Requests’, ID = recID)); So I changed all the ItemId to recID and the submit button still not work. I got the error the specified record was not found.

    0
    0
    1. You’re not providing the itemID parameter as stated in the ‘Identifying SharePoint item’ paragraph. That’s where your not found message comes from because the Power App has no clue which item you want to address

      0
      0
  9. Got it thank you. The only error I am running into now is specified record is not found. I followed the tutorial exactly and I have a sharepoint list with a column called Communication.




    0



    0
    0
    0
    1. Got it thank you. The only error I am running into now is specified record is not found. I followed the tutorial exactly and I have a sharepoint list with a column called Communication.




      0



      0
      0
      0
  10. Hi,
    The beginning is not clear for me. I have created a SharePoint list called Requests. Now regarding the multiple line of text column, what should I name it and how to use the formatting that you mentioned?
    Just please explain the beginning before moving to PowerApps codes cause it’s explained well for me.

    0
    0
    1. The name of the column is not relevant. You can use whatever you need as long as you point to that column in your Power App (through the Lookup function; in my example the column is called Communication)

      0
      0
  11. Hey, Good Work done here. I just wanted to ask that how is it automatically moving the gallery to the latest added message. Basically when the screen is full of messages and then when we add a message to the chat then it does get added but we have to scroll down to see it. Any idea how can this be fixed?

    0
    0
    1. Have you tried sorting your gallery? A gallery loads from top to bottom, meaning you sort on date in descending way, the newest will be on top.

      0
      0
  12. This is awesome..

    the only thing I changed was
    Set(itemID,Param(“itemID”)) ..

    I used quotes around the parameter. It wouldn’t load for me otherwise.

    0
    0
    1. I’m currently not planning on making video tutorials due to my busy schedule unfortunately, but if I’m having some more time I’ll think about it.

      0
      0
  13. i am new to PowerApps and i am not getting from where should i start to build this chat functionality and my requirement is to build a group chat within PowerApps

    0
    0
    1. I don’t recommend this for a group chat solution. There are much better ways to to that (e.g. Teams).
      I haven’t done this myself, but if you still do want to use Power Apps as your group communicator, I think you can basically use the same solution I described only you should differentiate your messages not by handler and reporter, but by yourself and others I guess. Again: I don’t recommend this and haven’t tried it myself so I won’t be able to guide you through this unfortunately.

      Just start by storing your chat feed somewhere and follow the steps in my blog. I used SharePoint as data provider, but maybe you can use another storage provider (e.g. CDS)

      0
      0
  14. Rik, are you aware of a way to force the gallery to automatically scroll to the bottom, so the most recent messages are immediately visible without having to scroll? Thanks

    0
    0
    1. Naturally, I ask a question and two minutes later find an answer that works.

      In your gallery you can set the Default property to Last(COLLECTIONNAME) and it will scroll or jump to the last item in the gallery.

      0
      0
  15. Very nice work Rik !

    I have a question, Where do you put the code for the part : ‘Separating the messages by X location’ ?

    Regards

    0
    0
  16. this has a flaw if a comment is sent with the semicolon (;), it will break the way powerapps is rendering and separating the information. Before sending the patch make sure that you remove any semicolons that may come in from the new comments box.

    0
    0
    1. Yes, you are right. It it up to you which separators you use. Always choose one that is least used within your work environment 🙂
      You can also build in a check on the separators before posting the message. You can wrap something like If(Or(‘;’ in txtChat.Text,’|’ in txtChat.Text),,) around the code on your Send button

      0
      0
  17. Hi, I start to learn a powerApp and i Follow your Tutorial about “Create a PowerApps chatbox to communicate about SharePoint items” but i stuck at the first step i can’t Collect my Data

    0
    0
    1. The error said “the specified record was not found ,Server Response: item Not Found ClientRequest id

      0
      0
  18. I got it working but cannot seem to get de iditem from sharepoint. It only works when is hardcode the param(itemId) to “1” or other id.

    0
    0
  19. Hello,
    I don’t understand the beggining.
    I have a Sharepoint List, I create a column “Field” with the message as :
    “21-05-2019 12:54:14;Reporter;rik@contoso.com;Hi, can I ask you a question?”
    And 1 line = 1 message.
    How can I do the formula without the Set variable ?

    Thanks

    0
    0
    1. No, unfortunately I don’t. But you should be good if you follow all the steps. I described them as accurate as possible including the screenshots.
      If you’re stuck on a specific step, please let me know and I will see if I can help you out

      0
      0
    2. Yes i am struggling with the creation of the chat feed container.

      Can you guide me through the storing chat feed part ?

      Thanks.

      0
      0

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

This site uses Akismet to reduce spam. Learn how your comment data is processed.