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.

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!

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!

Previous articleFive indispensable gadgets I use in my home office
Next articleMigrate content to Microsoft 365 – Understand the motivation
avatar
My name is Rik de Koning and I work as a Functional Consultant for Portiva, which is one of the larger Office 365 implementation partners in The Netherlands. I started my career in 2011 as a SharePoint support engineer and in 2013, I went to become a SharePoint consultant where I did various projects on SharePoint Server 2010 and SharePoint Server 2013. In the last couple of years I started focusing more and more on Office 365, especially on SharePoint Online, PowerApps and Microsoft Flow. I like being challenged and for PowerApps and Flow being quite new, there are always some challenges when implementing these services. When I’m not working, I like spend some time with my family (girlfriend and son), hang out with friends, do some sports (like fitness, running, kitesurfing and snowboarding) playing videogames and just relax a little. If you have any questions, please do not hesitate to contact me!

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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