Insert link in email from previous Apply to each step

[slvedva]

Hi all,

I building one process and stuck with one issue. I have list with data files in sharepoint and additional I have list with vendors (id, email). Now I building reminder flow, that after some days system will send reminder to these vendors, which didn't modify the file in Sharepoint. Steps below:

1. Set variable

2. Get file list, today it's ~275 files, I filter out files, which is modify by the same user, which created.

3. Then I apply to each file, get creation date of the file, then set target date, get link to the file, get vendor id etc., in condition I compare target date with current date, when it's true, I get distinct vendors id, to which I need send out

4. Then I get array with vendor id's, to which I need send out emails, because target date = current date.

5. Then I get vendor list from sharepoint list

6. Then I apply to each vendor id, in condition I use 

contains(outputs('Compose_2'),outputs('Compose_3')) is true?

7. In this condition I just by country code select in which language send out the email.

The main question, how in this step 7 email body insert specific vendor extract permission link, which I get in step 3???

Because, when I insert any data from get file list in email body (step 7), I get automatically apply to each and when I run, then every vendor receive 275 the same emails, because I have 275 files for different vendors.

Any ideas, how to fix this issue? Maybe I build wrong sequence?

[Matthy79]

Hello @slvedva 

I think the flow setup is way to complicated. Why do you need all those data operations inside the apply to each? I think one filter array after "get file list" could do the work of getting the files of interest. Probably it could be done during "get file list" as well.

If you are not sure about it you could use "select" (outside apply to each) to add information to the array like target date and all that stuff.

You are talking about "get link to the file". This is also already available in the first array or do you mean a shared link? If so you could use your apply to each with just 2 actions inside. Create the link and a Compose action with addproperty to current item. This way you would have a new array with all the details and the shared link included.

I also suggest that you filter the language and apply to each "LV" and apply to each others. That way you don't have an if inside an apply to each. This slows down Power Automate.

I don't understand the whole flow so maybe xpath could help for some special cases as well.

If you provide sample data (JSON) of get list and how it works with the vendors and what filters need to be applied, I will try to provide a new flow setup.

[slvedva]

Hi @Matthy79 

1. Regarding apply to each, how to use this filter array without of apply to each?

2. I am new in power automate, how I can provide sample data (JSON) and others?

Main idea are, that I have list with files created in x day and list with vendors and emails. And after 5 working days after file is created and if file modify by the same user, which is created (Creator always fixed system user) system should send out reminder email to specific vendor email, in email body should be included shared link. Each file in file name contain vendor id, which I have also in vendor list.

Maybe I can somehow share this flow with you and you can check deeper?

[Matthy79]

Hello @slvedva 

you don't need to share the flow. If you go to a flow run history you will see the output of "get vendor list" (you should anonymize vendor data).

I assume that "Get file list" is "Get files (properties only)"?

I also need the information how you match those 2 outputs.

[slvedva]

Hi @Matthy79 

1. I put 2 screens from get vendor list

2. I put screen from get files properties only for one record

3. Both sources I match with key field vendor id=sapid

When I read files, each file have filename like xxxx_Data_File.xlsx and the first xxx digits till symbol "_" equal with vendor id. I use function like -> 

first(split(item()?['{FilenameWithExtension}'], '_'))  in output I have number, which equal with vendor id

When I read vendor list, I already have field name "SAPID", which is equal with vendor id.

[Matthy79]

So is it correct that you just need the matching email of the vendor (and maybe the title) appended to the list files array?

[slvedva]

Basically to the list files array we can match with vendor id. Maybe I try a little bit describe the process:

1. I have list with files, where in filename I have vendor id; File creation date and modify by

2. The first I filter out all files, where 

  2.1 modify by = created by = our system user (that mean these files is still don't edited by vendor)

  2.2 current date = file created date + 5 working days

3. From the point 2 I have list with files, which is not edited by vendor and that mean, I need send emails to these vendors.

Then I compare each file vendor id from filename and compare with vendor list to get the email for reminder, BUT in this email I should include shared link for each file. 1 vendor = 1 email = 1 shared link.

[Matthy79 · Accepted solution]

If I understood everything correct I would do it like this.

1.) Get vendors

2.) Get files (already filtered)

3.) Convert vendors to xml

4.) Select all files and add vendors information

5.) Filter for vendors LV and others

6.) Apply to each vendors LV and others to create the link and send the mail

Here is a sample scope that you can copy from clipboard to a dummy flow to see how it works.

{"id":"78f5532f-71d4-44d5-967d-421523b2527f","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}},"shared_converterbypower2apps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_converterbypower2apps/connections/shared-converterbypo-168dd966-d321-48d6-ae65-2bb3363fc29f"}},"shared_powerappsforappmakers":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_powerappsforappmakers/connections/shared-powerappsfora-72ad6d4c-b73e-4db7-a2ac-66bed864efe8"}},"shared_flowmanagement":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_flowmanagement/connections/shared-flowmanagemen-276a16fd-e558-4db2-8c1d-8f153142d8f4"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/1597557c9920416e9a930edfee990646"}},"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_Append_Vendor_Info","operationDefinition":{"type":"Scope","actions":{"Compose_Vendors":{"type":"Compose","inputs":[{"SAPID":"1234","email":"dummy1234@xxx.com","Country":{"Id":0,"Value":"LV"}},{"SAPID":"1235","email":"dummy1235@xxx.com","Country":{"Id":1,"Value":"XX"}},{"SAPID":"123789","email":"dummy123789@xxx.com","Country":{"Id":0,"Value":"LV"}}],"runAfter":{"Compose_Dummy_Files":["Succeeded"]},"metadata":{"operationMetadataId":"eaf99e79-4428-484a-adc7-51b1d0fe53b2"}},"Compose_Vendors_xml":{"type":"Compose","inputs":"@xml(json(concat('{ \"root\": { \"vendor\": ', outputs('Compose_Vendors'), '}}')))","runAfter":{"Compose_Vendors":["Succeeded"]},"metadata":{"operationMetadataId":"52ab2064-f1e6-404c-9182-e2e2d7509fcc"}},"Select_Files_including_vendors":{"type":"Select","inputs":{"from":"@outputs('Compose_Dummy_Files')","select":"@addProperty(item(), 'Vendor', json(coalesce(first(xpath(outputs('Compose_Vendors_xml'), concat('root/vendor[SAPID=\"', first(split(item()['Filename'], '_')),'\"]'))), string(outputs('Compose_safety_net'))))['vendor'])"},"runAfter":{"Compose_Vendors_xml":["Succeeded"]},"metadata":{"operationMetadataId":"bfc03ffd-18ef-48fa-b65b-302e8aa51f86"}},"Compose_safety_net":{"type":"Compose","inputs":{"vendor":{"SAPID":"0000","email":"safetynet@xxx.com","Country":{"Id":"0","Value":"LV"}}},"runAfter":{},"metadata":{"operationMetadataId":"1fcbbd44-1c6d-4f23-bfca-a56fb1f3d602"}},"Compose_Dummy_Files":{"type":"Compose","inputs":[{"Filename":"1234_Dummy.xlsx"},{"Filename":"1235_Dummy.xlsx"},{"Filename":"123789_Dummy.xlsx"},{"Filename":"222_Dummy.xlsx"}],"runAfter":{"Compose_safety_net":["Succeeded"]},"metadata":{"operationMetadataId":"d2c6c2c9-06c5-4fc6-ae9e-dda635de9f33"}},"Scope_LV":{"type":"Scope","actions":{"Filter_vendors_LV":{"type":"Query","inputs":{"from":"@body('Select_Files_including_vendors')","where":"@equals(item()['vendor/Country/Value'], 'LV')"},"runAfter":{}},"Apply_to_each_vendor_LV":{"type":"Foreach","foreach":"@body('Filter_vendors_LV')","actions":{"Compose_email_LV":{"type":"Compose","inputs":"Send mail to @{items('Apply_to_each_vendor_LV')['vendor']['email']} with link you created right before this action.","runAfter":{}}},"runAfter":{"Filter_vendors_LV":["Succeeded"]}}},"runAfter":{"Select_Files_including_vendors":["Succeeded"]}},"Scope_Others":{"type":"Scope","actions":{"Filter_vendors_others":{"type":"Query","inputs":{"from":"@body('Select_Files_including_vendors')","where":"@not(equals(item()['vendor/Country/Value'], 'LV'))"},"runAfter":{}},"Apply_to_each_vendor_other":{"type":"Foreach","foreach":"@body('Filter_vendors_others')","actions":{"Compose_email_others":{"type":"Compose","inputs":"Send mail to @{items('Apply_to_each_vendor_other')['vendor']['email']} with link you created right before this action.","runAfter":{}}},"runAfter":{"Filter_vendors_others":["Succeeded"]}}},"runAfter":{"Select_Files_including_vendors":["Succeeded"]}}},"runAfter":{},"metadata":{"operationMetadataId":"d89ca6fd-fd51-4143-841e-756223ead6c7"}}}

[slvedva]

@Matthy79 Sorry, I am not so big expert, but where and how I can copy your code in the flow?

[slvedva]

If the first 2 points and last more less is clear, but how realize these points: 

3.) Convert vendors to xml -->to which operation I can convert to xml

4.) Select all files and add vendors information --> I understand, that in this step we join files with vendor info? Which operation can do this join?

5.) Filter for vendors LV and others -->this I can do with condition, I am correct?

[Matthy79]

Just create a new flow (because this flow is just a sample of how it could be done) and create a new action and go "My clipboard"

After that you press "Ctrl+v" (if you have the code already in the clipboard) and click on "Scope Append Vendor Info"

[slvedva]

@Matthy79 thanks new learnings, after your code looks ok, what I need. Just questions, in all steps you use compose, but when I build flow to get vendors and get file list, I suppose to use get items and get files instead of compose, I am correct? I will try to build after your schema 😉

[Matthy79]

Yes correct. I just don't have access to your data so I created the output by using compose. But of course you will need those other actions to get list of files and vendors.

[slvedva]

@Matthy79 a little bit confusing

I am correct, the point 2 I replace with get files and point 3 I replace with get items (vendors), but in point 1 and 4 I use compose and in point 5 - select?

[Matthy79]

Yes exactly.

Depending on which fields you need from vendor you have to change 1 or leave it like it is.

[slvedva]

@Matthy79 I try manage the first 5 point, just have one issue, does this formula are correct: "addProperty(item(), 'Vendor', json(coalesce(first(xpath(outputs('Compose_Vendors_xml'), concat('root/vendor[SAPID="', first(split(item()['Filename'], '_')), '"]'))), string(outputs('Compose_safety_net'))))['vendor'])"

Because system connect filename with vendor data, but vendor data for ALL vendors take from this dummy data, which are under Compose_safety_net.

[Matthy79]

In the select action “from” needs to be the array of the files.

[slvedva]

But I have the array of files: 

[Matthy79]

Ah, ok. That should work of course too.

Right now I think that the xml is not 100% in the correct format for the formula. Since you are using select for creating the vendors, what is the formula for creating the xml creation? It has to be the body and not outputs of the select action. If this doesn't help please post the first letters of the output of "compose vendors xml". I don't need any data but the structur.

[slvedva]

I put here my steps:

1. Compose files, here additional I added the file id, which later I need to get the shared link.

2. Compose Vendors

3. Compose Vendor xml

4. And selection