Problem Copying One SharePoint List to Another SharePoint List including all Attachments. Flow creates multiple entries.

[Phillip-JCI]

Hi guys, I had posted this in the Flow Community hoping for help, but unfortunately either it doesn't have as many members as the PowerApps community, or no one has my use case? But its related to my PowerApps app. But I haven't gotten lucky on responses and suggestions didn't work. Unfortunately I need to resolve this asap, so I figured I'd try my luck here for better my odds.

On the PowerApps side, everything seems to work fine, my problem is how Flow is interacting with the PowerApps SharePoint List Submission.

Long story short; because I needed specific permissions on who can view the PowerApps submissions. The workflow is PowerApps writes to a Edit to end users SharePoint List; List 1 that everyone can see. That triggers a flow, which then essentially copies over that submission and it's attachments to a new SharePoint list; List 2 which is a list with my desired permissions then deletes the submission from List 1. The Submission in List 1 is working perfectly fine, and contains all attachments fine.

This was per @RezaDorrani suggestion to do this.

What I'd like is for that submission and more than 1 attachment to be copied over. And I am having all kinds of trouble with more than one attachment. The actual text values of the submission move over fine.

Also I would like those attachments to be also sent in the email that generated as well. Below is my entire Flow as is right now, I  tried playing with initialize array, and saving the attachments as an array but didn't work. I tried to breakdown everything as much as possible for clarity.

I think this would be a use case alot of users would need? So I am surprised at how confusing it seemingly being to do something fairly straight forward.

Perhaps any of you guys also may know?

@timl @RandyHayes @Shanescows @Meneghino 

Thanks!

 ----

I am attaching a complete breakdown of my current functional flow. It does not contain the initialize variable portion as of yet, because that was not working and this is a production environment, so I had to remove it, until we have something that is tested and working.

Pain Point:

Problem Copying One SharePoint List to Another SharePoint List including all Attachments. Flow creates multiple entries.

Environment:

There are two SharePoint Lists. MasterDB and Submissions. MasterDB is henceforth known also as List 1, and Submissions as List 2. The PowerApps applications writes to MasterDB. All data including attachments is saved correctly in MasterDB. Up to 10 attachments must be possible.

What I want to accomplish?

The Goal is to take a “entry” from MasterDB or List 1 and copy/transfer it over to Submissions or List 2, including transfer over any attachments embedded into that entry.

The reason this is necessary is because MasterDB has to have edit permissions in order for PowerApps to work correctly, however we do not want end users to have access to all the submissions, so Submissions or List 2 has special permissions attached to it, and thus entries need to be moved over.

Assumptions: PowerApps is able to write a SharePoint List entry into MasterDB including attaching any Attachments to that entry.

Microsoft Flow:

My Current Flow:

When an Item is created in MasterDB (Triggers the flow)

Gets Attachment from that entry.

Now Checks whether there is an attachment or not, which splits the flow as a condition.

If Yes: The entry contains an attachment, it gets all the attachment content, creates an entry into Submissions or List 2. This entry is a copy of all the fields in MasterDB or List 1. Then adds the attachment(s) to the List 2 entry.

Deletes the MasterDB entry

In parallel Does these two actions;

• Generates an Email from a Shared Mailbox that contains all the column fields in the entry for an end user.
• Generates an Email from a Shared Mailbox to the Admin, or Myself that tells me that an entry has been created. It’s how I monitor that the process is working correctly.

If No: Creates an entry into Submissions or List 2. This entry is a copy of all the fields in MasterDB or List 1.

Then generates an Email from a Shared Mailbox that contains all the column fields in the entry for an end user. However the Dynamic Content Has Attachments, is false. This seems redundant from the shared email below also, but was working and not sending 2 emails, so I kept it.

Then In parallel Does these two actions;

• Generates an Email from a Shared Mailbox that contains all the column fields in the entry for an end user.
• Generates an Email from a Shared Mailbox to the Admin, or Myself that tells me that an entry has been created. It’s how I monitor that the process is working correctly.

What works?

If a Submission does not contain any attachments, the flow works successfully.

If a Submission contains one attachment only, the flow works successfully.

What’s broken?

If a Submission contains more than one attachment, the flow works however instead of putting all attachments into a single entry, it actually loops and creates the same entry multiple times, depending on how many attachments were in MasterDB or List 1. I.E There are two attachments in MasterDB, the flow triggers, and will create two identical entries into Submissions or List 2. The two identical entries, one will have attachment 1, and the second identical entry will have attachment 2, so the process is looping.

What’s missing?

I would like to add all the attachments likely via an Array to the Shared Mailbox emails. So that the end user receives the same attachments that are stored in the SharePoint List. I know this should be possible via array, but because of the looping attachment entry problem, I want to troubleshoot the flow, and then add that functionality.

Ideal Flow:

When an Item is created in MasterDB, (Trigger Flow)

Get all Content; both Column Entries and Attachments in this MasterDB Entry.

If there is an Attachment

Get Attachment Content,

Create an Entry into Submissions or List 2, that mirrors the MasterDB Entry.

Add all Attachments from MasterDB Entry, List 1 to Submissions Entry List 2.

Delete the MasterDB List 1 Entry, since its been essentially duplicated in Submissions List 2.

Send an Email from a shared mailbox to the End User with all the dynamic content fields that were contained in all the columns from this entry. Including Add to this email as an attachment, all the attachments that were in the MasterDB entry.

Then in Parallel

Send an Email from a shared mailbox to a specific admin, include the attachments from the MasterDB entry as well.

In simple terms. Take the MasterDB Entry, move it to Submissions. Send Admin Email that confirms entry with attachments if applicable, and Send an end user an Email with essentially a copy of the submission with attachments if applicable.

I am open to changing any part of the flow, I just need it to do this functionality. We are close, but far since that duplication issue and overall bugginess of the process.

Anyone feel free to chime in,

Thanks!

[timl]

Hi @Phillip-JCI 

Thanks for tagging me, and for your detailed explaination.

Unfortunately, this is an area that I'm not overly familiar with. I hope one of the SharePoint gurus here can quickly answer your question.

Alternatively, maybe one of the offical Microsoft people here can reseach an answer for you - Kris, can you help out here? @v-xida-msft 

[rebeccas · Accepted solution]

For this you will need:

When an item is created

Create item

Delay (I would say about two min because it takes a while for attachments to get on)

Get item (yes, you need this even though you have the 'When an item is created')

Get attachments (make sure you have that s on there, from the 'Get item')

Inside Apply to each for 'Get attachments'

     Inside--   Get attachment content (Id is from 'Get item' and File Identifier is from 'Get attachments'

    Inside--    Add Attachment (Id is from 'Create item', File Name is the DisplayName of 'Get attachments' and File content is from the 'Get attachment content

Then you can add your additional steps that delete the original and anything else as a new step (outside the Apply to each) below here.

Having those two steps inside the 'Apply to each' should grab all the attachments fine. If you notice it missing one of them or something you may add a little more time onto your delay.

Hope this helps!

[Phillip-JCI]

Hi @rebeccas , thank you for your input, it worked perfectly. I have just one more minor add that hopefully you can help. I'd like to attach the attachments to the email. To my understanding and research, I have to do it as an array. My flow failed.

This is the structure of the failed one. If I remove Initialize Variable and Append to array variable. Then it works as your described.

Do you have an idea how I can add it to the email that is sent to outlook? This was my understanding how to do it, but I'd imagine my Append to array variable is either in the wrong position, or wrongly formatted.

Thanks!

Thanks!

[rebeccas · Accepted solution]

For this you need to do (after the 'Get attachments' is fine)

Initialize variable (Name: AttachmentArray, Type: Array)

Inside your same 'Apply to each' (just put after the 'Add attachment')

'Append to array variable'

Name: AttachmentArray

Value:

{

"Name": DisplayName, <-- for this you can click the dynamic content of your Get attachments

"ContentBytes": body('Get_attachment_content')['$content'] <-- this you will not click, you will have to do in the Expression part

}

Then on the email part you will click that icon on the right side that makes it one line instead of two at the attachments and then select the 'AttachmentArray' variable.

Let me know if you have any trouble...I think that is all.

[Phillip-JCI]

Thanks @rebeccas ,

Your solution worked as intended, and I am able to launch my platform. I really appreciate you help.

When I saved it, it created an extra apply to each, which I used "value" inside the body, and seems to work fine. Not sure if I should change that or not.

To pass it forward here's a copy of my working flow guys, so hopefully if you run into this use case, you won't go crazy as I did looking for a solution.

Thanks!