Unanswered

Extracting values from a Multiline Text column, comparing each extracted value to update another sharepoint list

(0) Share

Report

Posted on by imranzafar

I have a list having a multiline text column. I want to extract values and use each value to update the matching value in another list. Need help on how to get this done. Original list column has multiple site IDs. I want to separate each site ID, find the matching ID in other list, and then update Invoicing status column in the 2nd list. Need help with this pls.

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (0)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @imranzafar

You mentioned "Original list column has multiple site IDs." Is this the multiline text column? Could you please post a sample data?

Thanks

Was this reply helpful? Yes No
imranzafar 41 on at

Like (0)

Report
Hi @abm,

Hope you are doing good.
Attached are the two list:
Sample List >>> Column [Site IDs] has site IDs separated by commas.
I want to run a Flow that extracts these different Site IDs.
Invoice List >>> From extracted Site IDs from Sample list column, I want the Flow to match site IDs from this (Invoice List) column [Title] and then update [Invoice Status] column with comments "Received".
Would appreciate if you can help with this.

Thanks,
Imran

Sample List.png

Invoice List.png

Was this reply helpful? Yes No
v-dezhili-msft Microsoft Employee on at

Like (0)

Report

Hi @imranzafar ,

Do you want to change the Invoice Status of the Invoice List to "Received" when the value in the Title column of your Invoice List exists in the Site IDs column of the Sample List?
I have a test for your reference. In my scenario, if the Title of List5 exists in the Site IDs column of List4, change the Invoice Status of List5 to "A".
Best Regards,
Dezhi

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

@imranzafar Are you looking to do this when a new invoice is created in the Sample List (When a new item is created), or do you just want to manually run the flow and iterate through all the invoices in the Sample List?

Was this reply helpful? Yes No
imranzafar 41 on at

Like (0)

Report

@grantjenkins I want to do it "When a new item is created".

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report
This is likely how I would build the flow.

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

When an item is created will trigger the flow.

Apply to each uses the following expression to iterate through each of the Site IDs.
//Note that the internal name of my Site IDs column is SiteIDs split(triggerOutputs()?['body/SiteIDs'], ',')

Get items uses the following expression in the Filter Query. This will retrieve the item with the current Site ID.
Title eq '@{items('Apply_to_each')}'

Apply to each Update will iterate over each of the items returned (should only be one item) and update it with the Status Received.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
imranzafar 41 on at

Like (0)

Report

Not working for me. The condition is returning False as a result "If yes" scenario is not coming in place.

Was this reply helpful? Yes No
imranzafar 41 on at

Like (0)

Report

@grantjenkins Thanks a lot. Your suggested solution is working fine.
Can you please help further if I want to make this flow work as "When an item is created or modified"?
In this scenario, when the item is created, the above solution is OK.
For modified case, I will add an [Invoice Status] column in "Sample Invoice List" and as soon status becomes "Approved" flow is triggered to update "Sample Invoice Received" column to "Approved" against respective sites.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report
See update below.

I've added a Choice column in my Sample Invoice List called Status with two choices: Pending and Approved. The default value is set to Pending.

All you would need to do is delete the current When an item is created trigger and replace it with a When an item is created or modified trigger.

Then in the Update item, use the following expression to set the Status (Received or Approved) depending on the Sample Invoice List Status (Pending or Approved).

if(equals(triggerOutputs()?['body/Status/Value'], 'Approved'), 'Approved', 'Received')

Was this reply helpful? Yes No
imranzafar 41 on at

Like (0)

Report

@grantjenkins Thank you for the solution.
One problem that could be faced is there might be cases where a site ID is part of more than one invoice. How can this scenario be solved for both Received and Approved cases and adding Invoice number in Sample Invoice Received List against each site ID dynamically through trigger?

Was this reply helpful? Yes No