Switching to CDS, Need help with importing Data

[Reylin]

Background
My team has been making PowerApps for several months now, but we've been using SharePoint as our main data source. Recently some of our databases became very large, and we kept running into delegation problems with our apps. They became very slow and it was hard to get the same functionality that we used to have. 
We decided to switch to Common Data Service to get that functionality back and increase our App speed/efficiency.

Question

How do you import Attachments from Sharepoint List to Common Data Service? 

Problem (in depth)

I originally imported the data from the Sharepoint Lists to CDS through Excel, but Excel doesn't do attachments. 
So I tried it with Power Automate, but I can't find a Flow action that adds attachments to CDS items.

Currently I'm trying to see if I can make a PowerApp that gets the Attachments from the Sharepoint List and adds them to the corresponding CDS entity through the Attachment Control... but this seems like a stretch, and definitely not what PowerApps was made for. 

Does anyone know a good way to import Attachments to CDS? I'm dealing with thousands of items, so it would be nice if there was an efficient way to do this. 

[HSheild · Accepted solution]

Hi @Reylin ,

Funnily enough I have been working on a similar thing today.  You can use Power Automate to do this with the Common Data Service (current environment) connector and the Create a new record action for the Note entity.  Notes are where documents are attached in CDS.

Here is an example below.

Don't forget to set the Regarding field so that the Note and attachment show against the correct record in CDS.

[Reylin]

Thank you so much for your reply! I'm going to work through this with your solution. I'll let you know if I have any questions. Once I get it working I'll mark it as the solution 🙂 

[v-xida-msft]

Hi @Reylin ,

Based on the needs that you mentioned, I agree with @HSheild's thought almost. If you want to move SP List records into corresponding CDS Entity along with Attachments, I think the Power Automate could achieve your needs.

The thing you need to know is that the Attachments of any CDS Entity would be stored in the Notes Entity. I have made a test on my side, please consider take a try with the following workaround:

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[Reylin]

Ok so I've ran into a problem with importing data from SharePoint to CDS. I have a Lookup Column (with sensitive information, I'm sorry I have to block it out), but the Lookup Value is getting an error when I try to import it with the Flow. 
I've tried all 3 Dynamic Content requests shown here, none of them work. The CDS entity column is also Lookup Column as well, is that a conflict for the Flow? 

What would you suggest I should try in order to import this data? 

[HSheild]

Hi @Reylin ,

I assume that you are using the Common Data Service (current environment) connector in this instance?  Lookup fields need to be treated a bit differently in that you need to prefix the GUID with "/" and the plural name of the entity, then wrap it in brackets.

For example, if I was to set a Contact lookup field on the Account entity I would have to set the lookup field to

/contacts(ContactGUID)

where ContactGUID is the GUID that you want to set in the lookup. 

I hope this makes sense.

[Reylin]

This does make sense, thank you. I will try this on Monday and hopefully I can finish setting up the Flow! 
Thank you so much

[Reylin]

@HSheild @v-xida-msft 

Ok so I am able to import attachments, yay! 😄 
but I'm now having trouble with Option Sets. For some reason, they're not importing through Flow. I have 5 option sets, one of them is a 2-option set (boolean). 
But in Sharepoint they are just regular option sets with string values. 
In CDS these options sets have index values and labels. When I "Create New Record" with CDS, it requires the index values in the fields (or boolean value for the 2 option set). But I have no way of getting these index values based on the strings coming in from Sharepoint. 

Please help!

Error: 

"message": "An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal 'TEST' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format.\r\n   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)\r\n   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)\r\n   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)\r\n   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)\r\n   at Microsoft.OData.ODataPayloadValueConverter.ConvertStringValue(String stringValue, Type targetType)\r\n   at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference)\r\n   --- End of inner exception stack trace ---

[HSheild]

Hi @Reylin,

When working with option sets like this when the source does not know the option set value I would use a Switch action in Power Automate. To do this initialise a variable (of type integer) to hold the option set value. Then use a switch action to look at the text value from SharePoint and set the variable to the corresponding option set value from CDS. When you update CDS use the variable’s integer value instead of the text.

This is not an ideal situation because you have to hard set the option set integer values within Flow and if you change the option set values in CDS in the future then you’ll have to update the Flow too. I don’t think there is any other way at the moment unfortunately.

[Reylin]

@HSheild @v-xida-msft  Thank you so much for the help, both of you. 

So I finally got everything working. And while its not the best system for importing SharePoint (lookup values and option sets are a pain), it works and that is what matters. 

If anyone needs to do this sort of thing again, here is how I did it: 

The Variables are Arrays containing my Option Sets. I plug in the label and the corresponding CDS value for each option. 

I use a Filter Array action to grab the right CDS value based on the label from the SharePoint dynamic content with a simple:
*From varProvince item()?['label'] is equal to Sharepoint Dynamic Content

And then I use this code in the "Create New Record" action under the right Option Set field: 

body('Filter_varProvince')?[0]?['index']

Lookup fields are a bit trickier because there could be many more options. Here I am planning on using a "List Records" CDS action to grab the Entity that I'm looking up the data from. I'll use a Filter Query: Title eq (Sharepoint Dynamic Content here), and set the Top Count to 1 just to be safe. 
Then use the data from the "List Records" action to get the GUID for the lookup record, and plug that into the Lookup Field in the "Create New Record" action. 
Luckily this will be less work than the Local Option Sets. 
------------------------------
For the Attachments, I grab the attachment data with the 2 Sharepoint Actions (Get Attachments, and Get Attachment Content inside the Apply to Each action). Then I use "create a new record" action with the Notes entity. Title and File Name are the Display Name from of the attachments from Sharepoint. Plugin the corresponding CDS data in the Regarding and Regarding Type fields. IsDocument set to Yes. And lastly the actual data will be in the "Document" field. I used a Compose Action for this with the code: 

string(body('Get_attachment_content')?['$content'])