【Publisher】Easy! Sending message to Microsoft Teams from a published report

This time, let's simply send a message to MS Teams via the web interface using the **Expression**.
## Preparation ##
#### Authentication settings when using ChannelLink ####
When using ChannelLink in **Expression** to send messages, you can pass your Microsoft Graph API credentials directly to the function you want to use. However, if it is set in the query each time, management becomes complicated when there is a change. Normally, you would prepare one dedicated authentication account for the application server where Sharperlight is installed, so if you can set it somewhere in Sharperlight, it will be easier to manage. This can be achieved in Sharperlight's **Site Setup**.

Launch **Site Setup** from the [Sharperlight](https://sharperlight.com/) application menu.
Once **Site Setup** has launched, select **Product Settings -> System**. The properties for Microsoft Graph API are displayed on the right side, so fill in the necessary information. Save the settings with the 'Close' button and restart the Sharperlight service.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1a7b6de3-c880-7b73-9a64-aaa073698832.png)
## Creating a web interface ##
We use a web report created with [Sharperlight](https://sharperlight.com/) **Publisher** as the web interface.
Launch **Publisher** from the [Sharperlight](https://sharperlight.com/) application menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5b9cfe72-4d4e-9f38-1026-4b236915cd35.png)
Once the **Publisher** is launched, start creating a web report with the `New` button.
Fill in the unique **Code**, **Group**, **Title** and **Report Title** when the report dialog opens.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0c0f1fcf-e4fe-d5eb-ad60-c665e5b169ba.png)
Open the query builder with the `Edit Query` button.
This time it's just to send a message, so it's fine if only one line of the query result is displayed. This is just for displaying the result of sending message.
In the **Filter** area select **System** for `Products` and **Products** for `Tables`. Set `Top n Rows` to **1** in the table filter options. This will return only one row.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0febed05-681e-20e1-01f2-e22d40a2cc02.png)
Set the `Product Id` column in the **output area** to see the results in the preview. It looks good!!
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a7f247ad-0301-3579-752d-a99ce933f855.png)
Next, create a field to enter your message. This will make it appear as a text type filter in the web report prompt.
Add a text column by selecting **Other -> Text** from the right-click menu in the **Filter** area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0c18f3c3-6315-9b77-196c-ecbebd55c6c2.png)
This filter column does not require a range input, so we will have a single input field. To do so, open the options for the filter column you added and set the **Single Values Only** option. Also, the `Description` should be **message** and the `Filter Name` should be **Message**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3a4b832d-858f-a1a6-0c97-6b205397c23f.png)
The base of the query is now complete. Save with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f89bb9d9-bca1-fba8-387a-3dc38795e704.png)
Save the report as well with the `Apply` button and with the `View` button to see what the actual report looks like. It looks like it needs a little make-up, but so far I think it's fine.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1d535f8f-9ed6-2fec-823b-e49487791264.png)
### Example using ChannelLink ###
To use `ChannelLink`, you need to get the URL link of the channel that receives the message on the Microsoft Teams side and use that URL. This time, I would like to prepare URLs for several channels so that I can select which channel to use in the web report prompt. 　
Now reopen the previous query and start editing.
Create a destination channel selection filter. 
Add a text column by selecting **Other -> Text** from the right-click menu of the **Filter** area. 
Open the options for the added filter column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/06a16402-060e-b5ea-3bb2-c28a500b171a.png)
Set the `Filter name` to **channel**.
`Description` is **Channel**.
Select the **Single Values Only** option.
Specify the **Show Filter values in Dropdown Picklist** and **Show Description** options.
It also uses a subquert to prepare the lookup data. Open the query builder with the `Edit Sub Query` button.
Define a few channel URLs obtained in Microsoft Teams as custom data set like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5cdb0034-8b6b-0392-67a0-e23678f2cb45.png)
Output `URL` column and `Title` column. If selected as a filter, the first column will be the **selected value**, so this time we want the URL, so the `URL` column must be at the top. Save the subquery for the channel filter with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/43c15fdf-32d6-cbfb-68f7-2a895279ba36.png)
Turn on the **Sub Query is used for Lookup value selection**.
This completes the channel filter option settings. Save the option settings with the `OK` button.
This completes the filter settings.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d8ef2c32-dc3c-c0e4-fc97-9401bc0c7eb5.png)
Next, let's create a function that actually sends a message using **Expression**.
Select `Add Expression` from the right-click menu of the **output** area. Select `Microsoft Teams - Create Message using Channel Link - MsTeamsCreateMessage(....)` among the functions. The template is copied into the edit field.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f525b2d6-dc98-3f52-76c3-5366f07209e6.png)
This part is already defined in the site setup as mentioned above so we will use that.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/078b0024-610b-ca39-bfb0-1e65ad57692c.png)
So replace it with this function **AzureGraphAccessTokenSiteSetup**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7dd30f23-5ea2-6a6f-ebc4-c2d86fc8297c.png)
The second argument is the URL of the channel, so specify the reference name of the channel column you set as the prompt like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/570c75fc-42ff-910e-2052-489e8c829240.png)
The third is Subject, something like the title of the message. I will write it directly this time.
The fourth is the message, so specify the reference name of the message column set as the prompt.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7b227aa4-ef2c-a137-18a5-91f00c383a8a.png)
The fifth is True if the message to be sent is in HTML format, False otherwise.
The sixth is an argument that specifies the importance.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d2c92ef7-c552-ad13-598d-80cb18f285b6.png)
Finally, specify the `Data Type` as **Text** and save the **Expression** with the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/37cc1b68-210e-29fa-75c8-637668c88201.png)
Rename the added **Expression** description to **Send Message**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0f442232-eaed-ea44-2ae4-b212bcb6b0bb.png)
Now the channel selected as the prompt and the input message are passed to this **Expression** and sent by the send function.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/93067b87-3a2c-0854-0dea-c103f30f5df4.png)
After that, add a new **Expression** to display the transmission result.
The syntax below determines the result of sending a message based on whether a value containing the string **error** is returned in the **Expression** column that defines the function that sends the message.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6d60c478-400a-d99e-7e6c-88ebba1f1e90.png)
```
IIF(Instr({%SenMes}, "error",0)
,"Transmission Error"
,"The message was sent successfully"
)
```
Save with the `OK` button. Name it **Results**. The **Product Id** column has no meaning, so delete it from the **output** area and you're done. Also, we don't need to display the **Send Message** column, so we will **Hide** it.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1890addf-27aa-87c8-2147-d191403967aa.png)
Save the query with the `OK` button. Save the report with the `Apply` button and view the report with the `View` button. It looks like you need a little makeover.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/352fa246-4591-faba-85af-4cd65470f04e.png)
But it seems to work fine. It was sent properly.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fb69705a-b98c-9399-a5e6-ec8ff6cfde87.png)
Then I'll do some makeup.
Re-open the report definition and on the Options tab set `Stack Filters` to **ON** and `Items per Row` to **1**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/537471d0-d7b8-d18f-2d17-31a704ad51fd.png)
Then set the following CSS definition in the `Custom Styles - CSS` option.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2568ca9e-6200-5528-7519-7edafd267635.png)
```
<style type="text/css">
#promptFieldSet > div.dataEntryItem{
    width: 620px;
}
#pi2{
    width: 600px !important;
}
input.promptButton.k-button{
	display:none !important;
	visibility:hidden !important;
}
</style>
```
That's all for the make-up.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4849a75d-dd17-2225-0c44-0dc009d0b59a.png)
### Examples using webhooks ###
Next, we will create a function that uses Incoming Webhook. We will duplicate the report created with the function using ChannelLink earlier, and edit the duplicated report. It will save you a lot of time.
Select the previous report in the Publisher and select `Clone` from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/96526d8d-941c-5643-c448-dcbea64556e7.png)
Open the cloned report. First, start editing the query with the `Edit Query` button.
Remove the `channels` filter as we don't need channels for the webhook method.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fe89f7cf-a7b1-076e-6da4-626fa5baa963.png)
Go to the **output** area and open the `Send Message` column where the function to send the message is defined. Clear previous syntax. Find `Microsoft Teams - Create Message using Webhook Link - MsTeamsCreateMessageWithWebhook(...)` from the function group and display the template.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e519d05d-b557-ce28-5674-ddaa8606b9f7.png)
Once here this expression is saved.
Add a new **Expression** and paste the `Webhook URL` you got from Teams in the `Expression Formula` field. Let the reference name be **WebhookURL**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0b97e271-99b1-351d-c22e-06d82d4ea8fa.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c2794667-f9da-16f8-b39f-879eff21b2de.png)
Open the `Send Message` column again. Replace the first argument of the function with the reference name from the `Webhook URL` column.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/33ebc808-9ae6-acaf-f98a-7d315d3f3358.png)
Change the rest of the arguments like this. The message refers to the Message filter.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d25c8b65-c836-d563-55b3-f944dcd0a06c.png)
This send function is complete.
Finally edit the syntax of the `result` column. Use syntax like this: If the message is successfully sent, a number will be returned, so use the IIF statement to display a green icon if the message is normal, and a red icon if there is an error.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c679be8e-88cc-d7b0-61c9-d5534b6a5f0c.png)
```
IIF( IsNumeric({%SenMes})
	, "_icon:RadioOptionOn" 
	,"_icon:RadioOptionOff"
)
```
Finally, the **Outputs** area looks like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9492985f-dfff-84a0-f0f0-1263fda0c857.png)
Save the query with the `OK` button.
Edit the CSS definition a little before saving the report. I deleted the channel filter, so I need to fix it a little.
Change this from **pi2** to **pi1**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6d9ecd33-0134-8404-2925-6b112b12a856.png)
Also save the report with the `Apply` button and check the report with the `View` button.
Sending a blank message resulted in an anomaly with a red icon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fca0c4f8-6ba1-2093-0a91-afe5a44d28fd.png)
This is what it looks like in case of normal termination.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ad3fa249-fb8c-d808-e548-f44a7c13f559.png)
Here is the message I received! :thumbsup:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eb55f62f-9563-c492-0828-32b0b01d20dc.png)
This concludes the verification of the function to send messages to Microsoft Teams using Web Reports.

Thank you.