Extract text/digits from teams chat, then look up in Excel

[Robotech12]

Hi, looking for any assistance, if possible, this is the main goal:

1-An user will write in a group chat the "OPP-##########" (will always be 10 digit code plus the OPP-). The OPP# and general message will vary, for instance it can be:

"Hi all, I have this OPP-########## to discuss today" 

"these are for today OPP-#########, OPP-##########"

2-Extract the "OPP-##########" and then VLookup in an excel file to bring key data such as Date, Status, Owner, etc..

This is my flow so far, but having trouble.

I am not sure if my process is correct, having difficulties to set the body of the teams message in a variable.

any help is appreciated. Thank you.

[trice602]

Hi @Robotech12 ,

Here's a similar flow that may be a good example to help out.

First, I parse json to get my lookup value that I want to find in Excel, then List rows present in a table.

Next, I filter my array to find the chat message variable and find it in my Excel table.  Then I parse json the results from my filter array.

Next I am returning the results in Teams for the end-user

{
  "type": "AdaptiveCard",
  "body": [
    { 
      "type": "Image",
      "url": "https://somwhere-my.sharepoint.com/aaaaaaa/bbbbbbbbbbbbbb/Documents/logo2.png"
    },

   {
      "type": "TextBlock",
      "text": "Hello, here's the latest update.",
      "size": "Large"
    },
   {
      "type": "TextBlock",
      "text": "Always glad to help! The Robots 🤖",
      "size": "Large"
    },
   {
      "type": "TextBlock",
      "text": "ID no: @{items('Apply_to_each')['ID_no']}",
      "size": "Large"
    },
   {
      "type": "TextBlock",
      "text": "Current Status: @{items('Apply_to_each')['Status']}",
      "size": "Large"
    },
   {
      "type": "TextBlock",
      "text": "Last Comment: @{items('Apply_to_each')['Comments']}",
      "size": "Large"
    },
   {
      "type": "TextBlock",
      "text": "Updated by: @{items('Apply_to_each')['UpdatedBy']}",
      "size": "Large"
    },
    {
      "type": "TextBlock",
      "text": "Please select an option:",
      "wrap": true
    }
  ],
  "actions": [
    {
      "type": "Action.Submit",
      "title": "Send Follow-up 📨",
      "data": {
        "selectedOption": "Option 1"
      }
    },
    {
      "type": "Action.Submit",
      "title": "Escalate to Tier 2 🆘",
      "data": {
        "selectedOption": "Option 2"
      }
    }
  ],
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json"
}

The results look like this:

If this reply was helpful, please mark as a solution so others can find it quickly too!

[AlexEncodian]

 @Robotech12 

The quickest way to extract the code will be to use a regex query on the text provided using Encodian's action. The below will extract an array of values that follow your desired pattern i.e. "OPP-" followed by 10 digits.

FYI – Encodian actions with the “Utility“ prefix only consume 1/20th of an Encodian action count . For example, a Standard plan has 500 actions so utility actions can be used 500x20 = 10,000 times. They are designed to be an incredibly cost effective productivity boost for your flow builds! 

---
 *Encodian Flowr is a Standard Power Automate connector with 100+ actions to supercharge flow builds. Conversion, merging, splitting, OCRing, data extraction, QR/Bar codes, searching and much more. And we just added "Utility" actions, which are almost free to use and accelerate solution builds and remove maintenance overheads with actions like Extract text between values, extract URLs, extract email addresses, Password/GUID/HMAC creation, escaping/unescaping HTML, parsing HTML tables, etc. 

[Robotech12]

Hi @trice602 thank you for the feedback, is the below correct ? I do not have much experience in JSON.

Do I need to modify this code to get the OPP-##########(10 digits)?

 

Is this one corret? The Opportunity ID will be the actual OPP-######### in the Excel file.

I am not sure on how to modify the PARSOn Jason 2 part, is similar to the 1st one?

[trice602]

Hi @Robotech12 ,

In my example the chat messages are just the ID# and end-users are instructed to just key in the number for the status.  

The scheme for Parse json comes from the output of the previous step and you copy the output and click on Generate from sample and paste it in and it will build it for you.  You would do here and also after the filter array, you need to parse the json and get your scheme.

To find OPP-XXXXXXXX in the content of a message, you will need to add a compose here and get the number only with this expression.  Please keep in mind this is going to find one OPP# in the message, it's not coded to lookup more than one.

split(body('Parse_JSON')?['body']?['content'], '-')[sub(length(split(body('Parse_JSON')?['body']?['content'], '-')), 1)]

Update your Filter array to find the number in the Excel sheet.

[Robotech12]

Thank you @trice602  I just made the changes:

1-I changed In "Get message details" to Message ID

2-From the "Get Message Details" I did a test and got the Outpot and copied, then I generate from Sample to get the Schema.

in compose I put the Split expression

but I am getting an error:, is because the Jparson I wrote incorreclty the Jparson?

[trice602]

Hi @Robotech12 ,

What was the chat message?  In this example, with the extra compose, it is looking for OPP-XXXXXXXXX.

[Robotech12]

I wrote these examples @trice602 

I have OPP-1234567890 for today
For OPP-1234567890 need to talk with John

[trice602]

Hi @Robotech12 ,

Ok, I modified my example because it was just an example anyway and made this work for yours.  Here are the results:

My excel sheet:

I added these two steps:

This step is going to find the starting position of OPP in your messages and bring back the character number.

The compose directly above the Filter array is a substring.  We want the text starting in character position number of OPP and then next 14 characters, so we get a result like OPP-1234567890

substring(body('Parse_JSON')?['body']?['content'],body('Find_text_position'),14)

Make sure your flow looks exactly like this example, with the exception of an adaptive card, you could just return the results as a message, but I used a default card sample, added a logo for testing, and works.

Second test results...

If this was helpful. please mark as a solution.  I will be glad to help you get your project up and running this weekend; I am pretty much just building flows for the next 48 hours (give or take a little sleep in between)!

[Robotech12]

Thank you @trice602 , just doing it, but I am doing something wrong, but not sure where. Apologies in advance for all the questions.

On the 2nd step, am I correct here? In "get message details", should it be the "Message Id" ?

To generate the Parse JSON, I run a test and copy the Outpot Body and Generate.

but then I am getting this error:

[trice602]

Hi @Robotech12 ,

I think you are really close here, always glad to help!

Yes, the Message ID is what you will use but notice yours is in an apply to each and not needed.  Delete that entire Apply to each and re-enter your Get message details as shown below.  Your trigger and first step should look exactly like this:

On Parse JSON and Find Text Position --

Yes, you run it and get your sample JSON.  When you go to the Parse JSON action, in your scheme, it should be completely blank when you originally started.  Now, highlight all the text and delete it.  Click on the Generate from Sample button and that's where you paste in your sample JSON, this will create the scheme for you.  Your scheme is not correct and is the root cause and your find text position action is looking for text and should be content.  

[Robotech12]

Hi thank you @trice602 

Trying that part but every time I choose the "Get message details" and write "Message ID", the "apply to each" pops up every time. I have tried to move it out but not possible. is there an issue or configuration?

[trice602]

Hi @Robotech12 ,

The issue here is the trigger you are using is going to fire on the entire Team ecosystem, whereas mine is limited to a single chat channel and is the root cause why this wraps in an apply to each.  Please see my example:

My example "When a new channel message is added":

[Robotech12 · Accepted solution]

Hi, @trice602 thank you,  just managed to make it work ! this is the snapshot. Now I am getting the message correctly. Just wanted to know, the flow gets 1 OPP, but what happens if there is 2 Opps in a message? would it be more difficult? when " when keywords are mentioned in teams chat" can work?

thank you for the  help

[trice602 · Accepted solution]

Hi @Robotech12 ,

That's great!  I am really happy for you!  Here's my recommendation:  mark this post as a solution to help others find it quickly, and then create a new post question, suggesting... "I have a working flow that takes on OPP-1234567890 from a message and I am getting my results, etc, etc - works great!  I now want to build onto this and check for more than one OPP-12345678 in a message.  How can I do that?

A new thread is going to help others jump into the conversation (and I will also too).

[Robotech12]

Hi, sorry to activate this post again, this flow was working flawlessly. I was doing exactly the same steps for another chat but now I am getting this error, I am totally lost @trice602 , any help is appreciated,. I have tried several times and is the same. It crashes in the "Find text position".

this is the error:

"Evaluation of inputs for the expression action of kind 'IndexOf' failed: 'Required property 'text' expects a value but got null. Path ''.'."

[trice602]

Hi @Robotech12 ,

No worries, glad to help!  Here's the next steps:

1) Create a new post

2) Show the flow

3) show the error

4) AND please show the text it is trying to use in Find text position

I will also look for your new post and join the conversation (and please tag me in it).