Copy files to SharePoint list in another SharePoint site based on a choice from a lookup field with Azure Logic Apps

Question

I have a SharePoint list in SharePoint Online with a workflow in Azure Logic Apps. This SharePoint list has a lookup field which targets another list. This targeted list has - beside of title column - two columns: email address and SharePoint list. The content in SharePoint list column is the whole URL address to the SharePoint list in another SharePoint site.

Based on the user choice in the lookop field different actions should happen:

a) email address --> the workflow sends an email with attachments to recipients (this already functions well)
b) SharePoint list --> the workflow shoud copy the attachments to a new list item in this specific SharePoint site

For achieving b):
I use a condition to check if email address column or SharePoint list column is filled. If SharePoint list column is filled I use Create item action to create list item in a hard coded SharePoint site just for testing purposes (I have put one fixed SharePoint site address to the field "Site Address" and the SharePoint list name to the field "List Name" in Create item action). This functions well so far, but this solution does not completely meet my goal, as there could be a lot of different SharePoint sites in the lookup field. So it should be dynamic.

How can I best achieve this? Thanks in advance for any assistance.

Answer 1

Hi Cem,

Thank you for reaching out on Microsoft Q&A!

You've requested a Teams session with me to solve the issue. To help others, and for them to be able to re-use (parts) of the solution I've added the steps we've discussed below:

• Your list reference in your source list contains an URL with a lot of information. We need to create another URL based on the original one. For this we use the split() function to split on the slash. With this we cut the URL into pieces which we use to create a new URL
• The 'Get Lists' action in SharePoint only returns the display name of the list, while the internal name (which is in the URL) differs. Therefor we need to take another approach to get the correct list on the destination site
• You want a specifc naming convention for the attachments in the destination list item attachment

The steps we took to solve your issue:

• Because your list URL which you filled in the source contains the internal list name, and this is not responded by the 'Get Lists' action (it only contains the displayName) we've used the 'Send HTTP request to SharePoint' action with your newly created site URL using split:
  concat('https://',split(body('Get_item_2')['SharePointListe'],'/')[2],'/sites/',split(body('Get_item_2')['SharePointListe'],'/')[4])
  For which we used the endpoint:
  /_api/Web/Lists
• This returns all list details, also containing the field 'EntityTypeName' with the internal list name, appended by the word 'List'. To filter the correct list detail we've used the 'Filter array' action filtering on the results of the HTTP request to SharePoint using the array below:
  body('Send_an_HTTP_request_to_SharePoint')['d']['results']
  Where we did an exact match on the internal list name appended with the word 'List' using the concat() function like this:
  concat(split(body('Get_item_2')['SharePointListe'], '/')[6],'List')
• Now we've got our destination site and list, we can create a new list item on that list using the 'Create Item' action. For this we just reuse the newly created URL and retrieve the list id where to create the item using:
  first(body('Filter_array_3'))['Id']
• As we've got our new item created now we can start adding attachments. You've already added a 'Get Attachments' action in your case to retrieve attachments from the source list, which we'll reuse for this purpose. We're going to loop over them to be able to use the 'Get Attachment Content' action which actually retrieves the files from the source list
• Next step is to write the attachments to the destination list. We can use the 'Add Attachment' action for this, were we're reusing the newly created URL again with the list ID and the newly created item ID from the destination list. The contents we can use from the 'Get Attachment Content' action of the source, and for the filename we've initialized a variable called 'indexNumber' with initial value 1.
  We're using this value to create the naming convention for your filename, together details from the source list, derived from the trigger. We're also using the displayName of the filename, to split it at the '.' and take the last part, which leaves the file extension. The complete expression is as follows:
  concat(triggerBody()?['Nummer'],'-',triggerBody()?['Title'],'-',variables('indexNumber'),'.',last(split(items('For_each_2')['DisplayName'],'.')))
• Last step is to increment the variable 'indexNumber' with 1 for the next iteration. Please note that we've set the concurrency control of the 'For Each' action to 1 for this to function properly (using variables in a loop is always something to carefully consider). In this case we needed the concurrency control also for writing attachments to the list item, as we'd otherwise would run into conflicts on the SharePoint actions.

Cem, would you please be so kind to attach the screenshots of the current solution below for future reference, to also help others? Thanks a lot for your time! It was nice working with you.

Please click “Accept answer” if you find this helpful. Feel free to drop additional queries in the comments below!

Kind regards,

Sonny

Answer 2

Hi @Cem

From your description, I understand that regarding the file list, you have a lookup column index whose target is the list address stored in another list. Do you want to dynamically fill in the create item information based on the list address provided by the index?

Then your lookup column is a multi-select right?

But if it is dynamic content, you will have to create dynamic items. If you write a fixed list here, you will not be able to match all lists.

If you need all the list columns of the items corresponding to the created items to be consistent, please ignore my idea.

My idea is to use switch to execute the corresponding create item according to the conditions that are met.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

Answer 3

Hi @Cem

First of all, thank you for your detailed explanation.

Let us first provide some answers to your problems:

1.

[Apply to each] is used to traverse the value returned by Get items (which will contain each piece of data in the list)

After you compare in the first level Apply to each, only items that meet the conditions will perform the next step. So you don’t need to use the second layer of Apply to each.

2.

Regarding your first(split(items('For_each_2')?['SharePointListe'],'/Lists')), the reason why there is no /value here is: your lookup column should not use this function.

Next, I will create a flow according to your column settings and give a detailed answer:

File list:

Target list:

List address list

Here is flow:

First use [Get items] to get the records of the File list

Then use [Apply to each] to traverse the value returned by [Get items]

Use Condition in [Apply to each]:

Used to determine whether the value of listAddress of [File list] is not empty

If yes,

Use the formula below to change list address to site address

first(split(items('Apply_to_each')?['listaddress'],'/Lists'))

Get lists: To obtain all lists under the site of the target list

first(split(items('Apply_to_each')?['listaddress'],'/Lists'))

Filter array: Filter out the data whose Display name in the lists returned by Get lists is consistent with the Diaplay name of the target list.

last(split(outputs('Compose'), '/'))

The purpose of this compose is to display the list address and also to use the following Filter array.

Now that we have obtained the value of the target list through the Filter array, we need to obtain its real Name (List ID) through this value

Create item:

first(body('Filter_array'))?['Name']

first(split(items('Apply_to_each')?['listaddress'],'/Lists'))

After the target list Create item is completed, we need to add the File list attachment to the target list.

We need to first use [Get attachments] to get the attachments of the file list (because there are multiple attachments, we will also use Apply to each later)

Get attachments:

Apply to each 2:

Get attachment content:

Add attachment:

This is a complete flow screenshot

I hope my explanation is helpful to you, if you have any questions, please feel free to ask me!

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng

Answer 4

@MayankBargali-MSFT : You know my workflow so far from other QnA threads. Can you please have a look on this issue and we can talk about it in a call?

Answer 5

Hi @Cem

I'm sorry that my content explanation did not satisfy you.

This forum currently does not support phone support. This is the official forum of power automate.

https://powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

It is recommended that you ask your questions here to get a more professional explanation.

If you still need phone support, it is recommended that you post your issue at this link and get phone support

https://admin.powerplatform.microsoft.com/support

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng