Get a value from list column if string is found on the row (in different column)

Like (0) Share

Report

Posted on 26 Feb 2023 17:53:10 by AlexB7

Hello,

I am hitting a wall in making following flow to work so hope some guru here will help me out.

I have a SharePoint list with two columns. Column "A" contains the keywords (unique) and column "B" has list of persons.

Aside of that i have a channel where messages are sent from various sources (emails, other flows, people, etc).

Trigger of my flow is when new message is sent to that channel - This works

Then I am checking if a specific keyword is present in the message - This works as well

What i want to do next is to search in the same message if it contains any of the keywords from List column "A" - This i think I have as well, but not sure.

Now IF any keyword is found i want to pick the name/content from the same row but from column "B" from the list. <- This is what i am struggling with

Last step is to send a reply to the message with info what keyword was found and highlight the responsible person (so they can take it from there). - This i know how to do.

More practical example ... List looks like this

apple, Thomas

orange, John

citron, Anne

Message: There is a problem with server orange

First scan looks for "problem", if found then

Second scan search in Column "A" and finds "orange"

Send reply to the message with text "Orange server is broken @ John please check this"

What i have so far:

- Check the msg for the first keyword:

- Get items from sharepoint list + apply for each for the first column (hope this is correct)

Now how the hell i get the content of second column for each match?

Thanks a ton in advance for any suggestion!

Regards,

Alex

I have the same question (0)

All responses (8)

Answers (1)

AlexB7 12 on 10 Mar 2023 at 12:53:43

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

Here is the version where I first replace the special chars with space and then add the space to the string search. Works fine and does not create duplicate replies. I think i will keep this approach for now.

Was this reply helpful? Yes No
AlexB7 12 on 10 Mar 2023 at 10:12:11

Like (0)

Report
Re: Get a value from list column if string is found on the row (in different column)

@grantjenkins Thanks for pointing to the pagination, i was not aware that there is an actual limit by default (instead of unlimited).

As for the filter ... I've added the space via concat Fx ...

concat(item()?['ManagedObject'],' ')

Now It works OK as long as there is an actual space at the end. But in some cases it follows with ":" and in other with "~". (So like "Something DM0:blabla" or "Something DM0~xxxxxxx somethin"). Sadly i can't change the source generating the text.

One approach is to get the message text and force-replace these symbols by space before looking for the strings but that might cause issues if the symbol IS actually part of the string (not now, but could be a case in future)

Since there is not many of the extra characters i was thinking about just checking all possibilities, like this:

or( concat(item()?['ManagedObject'],' '), concat(item()?['ManagedObject'],'~'), concat(item()?['ManagedObject'],':') )

Ofc this will not work as the syntax is wrong. Also it is a bit barbaric i guess.

Another way is to just do the Filter+Check multiple-times for each of this extra character. It duplicates the replies in certain situations (if the message text has strings with both space and "~" for example).

Even though it works (into some extent) it does not seems to be the right approach.

Alex

Was this reply helpful? Yes No
grantjenkins 11,059 Moderator on 09 Mar 2023 at 08:09:40

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

@AlexB7

The Get items action by default will only return the first 100 items. If you want more than this, you can go into Settings, turn on Pagination, and set a Threshold to something larger than what you expect your list to contain. Ideally, you would cater for some growth over the next few years, so you don't have to update the flow when you get more items over time. I would also set the Top Count to 5000 so it will return your items in batches of up to 5000.

For the filter and substrings - can you show what you did exactly when you added the spaces? Can you just add an underscore _ within the expression so it would check for DM0_ instead of just DM0?

Was this reply helpful? Yes No
AlexB7 12 on 08 Mar 2023 at 14:24:14

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

Dear @v-liwei-msft ,
Further progress with the recommended approach revealed two issues. Maybe you can take one more look please? 🙂

Issue 1: The filter array seems to have limit of 100 passes. So when the pattern is within the first 100 rows of the list it gets found but the moment it is lets say on line 203 it is not. (Our list list will be 300-400 rows)
I don't have any limits defined on the Get Items block, Is this a hard-limit or something?

Message content:

List has the PC2 entry but it is more deep down in the list

Flow run returns NULL

Issue 2: Some patterns could be sub-strings of other patterns. Then two or more matches are found. It should not match on the sub-string, only the full pattern from the list. I've tried to add a "space" to the Filter condition but then it wont match anything - logically as the values in the MS List won't contain the space at the end.

List looks like this:

Thank you in advance!

Regards,
Alex

Was this reply helpful? Yes No
AlexB7 12 on 27 Feb 2023 at 14:50:08

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

Seeing how simple some stuff can be done i need to stop "overthinking" things. Thanks again for fantastic feedback 🙂

Side-note: Just in case someone ends reading this ... i had to slightly modify the "multi-select" flow. It was "stacking" the names from each iteration and also i had to handle the fact that in some cases the initial message can match on more than two entries in the list.

So I "nullify" the variable so ensure for any keyword matching it starts with empty string (otherwise the second pass would keep the names from first one)
Also the reply was moved outside of the internal loop otherwise it was sending reply after each name for one row (instead like 2 names for the same row).
And also added condition to handle situation where no keyword is found.

At this point if more than one rows are matching the text from the input message it sends reply for each row. Would be nice to "merge" everything into one message but that is just a cosmetic stuff i will handle at some time later on.

Was this reply helpful? Yes No
v-liwei-msft on 27 Feb 2023 at 10:04:45

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

Hi @AlexB7

You can save the multi-select person column by adding a string variable

1.

2.

Best Regards,

Levi

Was this reply helpful? Yes No
AlexB7 12 on 27 Feb 2023 at 09:50:10

Like (0)

Report
Re: Get a value from list column if string is found on the row (in different column)

Wow a full solution, thanks a ton! The filter array was the missing piece of puzzle.

It works like a charm but there is one catch which i forgot to mention. The Person column (yes, type "Person") has "Allow multiple selections" set to "YES" (as in some cases more than one person is responsible). Problem is that once i enable that flag the flow fails with error
ResponseSwaggerSchemaValidationFailure. The API 'sharepointonline' returned an invalid response for workflow operation 'Get_items' of type 'OpenApiConnection'. Error details: 'The API operation 'GetItems' requires the property 'body/value/0/Person' to be of type 'Object' but is of type 'Array'.'
Which makes sense and i assume i will need another "for each" to get the @ mention tokens for each of the name in the cell?

Was this reply helpful? Yes No
Verified answer

v-liwei-msft on 27 Feb 2023 at 03:24:09

Like (0)

Report

Re: Get a value from list column if string is found on the row (in different column)

Hi @AlexB7

Suppose ColumnB is a column of type Person.

I have made a test for your reference:

1.

2.

3.

4.

5.

6.

Best Regards,

Levi

Was this reply helpful? Yes No