Creating / updating item in SharePoint List based on channel message in Teams

[WojciechJez]

Hi,

I have created Issue Tracker (Lists) in Teams Channel. I have set simple flow - when a new message appears in channel with message subject: "SCAN STARTED" then create an item in Issue Tracker - and it works.

But now I want to create another flow which will modify/update above added item when a new message will appears with message subject "SCAN COMPLETED". Is it possible to create one flow which will collect data from this two messages? I will add that the time of appearing between this 2 messages is from 20 minutes till few hours.

Also I would like to add some values (strings and numbers) from message body directly to some columns, i.e. one of the lines in message body is "Scan scarted by: User1" I would to add this User1 to column "Issue created by", other example is "Problems found: 0" - number 0 I would like to add to column "Number of findings".

Best regards,

Wojtek

[grantjenkins]

Couple of questions just to clarify how it would work, etc.

1. After you've started a scan can you have another scan start before the first scan is completed?
2. What field type is Issue created by? You said you wanted to add User1 - how would User1 be entered in the message? I'm assuming this is a text field given you mentioned update some values (strings and numbers) but just wanted to be sure.
3. Would the format of the message be something that is auto-generated and in the same format each time?

From what you said it would look something like that below (is that correct, or something else)? Would be good to see an actual example of what you have.

[WojciechJez]

1. It can happen - but mainly I try not to do it.

2. Issue created by is a "Person or Group" and User1 is a AD login.

3. The messages are generated automatically. The format of first message (scan started) is different then second message (scan completed) but there will be no other formats. See the screen below.

Those are emails send to Teams dedicated channel.

[grantjenkins]

I'll see what I can come up with later today. I had something in the works, but need to change a few things as didn't realise it was an email being posted in, and subject also includes more ": Asset 1", plus Issue created by being a Person or Group field.

Just one question that's probably the most tricky part. How do you relate a completed scan to a started scan when there might be multiple started scans waiting for completion. This is in relation to you saying you might start a new scan before the first scan is complete. If you started a new scan before the previous one was finished would this new scan be for a different Site name, or potentially still the same Site name?

[WojciechJez]

Different scan - different Asset - different subject 🙂

Of course after some time it can be another scan of the same asset but it will have different date, and the previous one will be closed (completed).

[grantjenkins]

Finally got time to have another go at this one.

Assumptions:

• Critical, Severe and Moderate are all summed to get total number of Problems found.
• Scans will take at least 5 minutes to run.
• The Teams channel only has the two types of messages

For this example, I'm using the following list. I added some column formatting for the problems.

Status is type Choice.

Issue created by is type Person.

Critical, Severe and Moderate are type Number.

Number of findings is type Calculated that is the sum of Critical, Severe and Moderate.

Total scan time is type Single line of text.

The full flow is below. I'll go into each of the actions.

The trigger is setup to wait for a new channel message in Teams.

Html to text converts the HTML from the message to text.

Compose splits the text by new line giving us an array of text items.

split(outputs('Html_to_text')?['body'], decodeUriComponent('%0A'))

Filter array takes in the Compose output, skips the first row, and removes any empty lines.

skip(outputs('Compose'),1)
empty(item())

Compose Convert to JSON converts the Filter array to JSON format. There's likely a better way to do this, but spent a long time researching and couldn't find anything 😞

replace(replace(replace(string(body('Filter_array')),': ', '": "'), '[', '{'), ']', '}')

Parse JSON converts the output to proper JSON with the appropriate schema.

{
    "type": "object",
    "properties": {
        "SCAN STARTED": {
            "type": "string"
        },
        "SCAN COMPLETED": {
            "type": "string"
        },
        "Site name": {
            "type": "string"
        },
        "Started by": {
            "type": "string"
        },
        "Critical": {
            "type": "string"
        },
        "Severe": {
            "type": "string"
        },
        "Moderate": {
            "type": "string"
        },
        "Total Scan Time": {
            "type": "string"
        }
    },
    "required": [
        "Site name"
    ]
}

The Switch uses the first row in the Filter array and splits it to get the header which will be either SCAN STARTED or SCAN COMPLETED.

split(body('Filter_array')[0], ':')[0]

If it's SCAN STARTED it will create a new item using the Site name and Started by values.

If it's SCAN COMPLETED it will return the latest item in the list (Order by = Created desc) where the Title is = SCAN - Site name and Status is Started. This should bring back the appropriate item that we need to update.

We then update the item returned using the ID of the first item from Get items (should always only be one but will still be within an array). We update Critical, Severe and Moderate converting them to integers.

first(outputs('Get_items')?['body/value'])?['ID']

int(body('Parse_JSON')?['Critical'])
int(body('Parse_JSON')?['Severe'])
int(body('Parse_JSON')?['Moderate'])

Hopefully this works for you with the messages that come through from Teams. I tried to use what you had provided as samples.