Answered

Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values

(0) Share

Report

Posted on by LK-22111434-0

I'm working on a cloud based flow that exports a SharePoint list to a SQL Database.

The flow is uses the SharePoint action 'Get Items' then iterates through them (Apply to each) and uploads row by row to a SQL database.

This seems to work fine for SharePoint list column types such as Number and Text, but for Choices, the value is replaced with:

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"xxxxValue1xxxx"}, [{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"xxxxValue2xxxx"} etc..

Any ideas on how to replace/trim this string down to the Values?

Many thanks!

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Expiscornovus 33,195 Most Valuable Professional on at

Like (2)

Report

Hi @LK-22111434-0,

You can use a Select action for that. In the Map field of the select you can use the Column Name value field. Make sure you switch the Map field to text mode first (with the button on the right side). After that you can use a join to turn it into a comma separated string (for example).

Below is an example

Test result

Was this reply helpful? Yes No
LK-22111434-0 10 on at

Like (0)

Report

Hi, thanks for the help,

Does the Select action need to be within the same 'Apply to Each' loop as the Insert Rows? (I also have several choice columns):

For the Insert Rows, how would I add the value from the Select into the column?

Was this reply helpful? Yes No
LK-22111434-0 10 on at

Like (0)

Report

Update, the solution worked great! I put the Select/Join within the same loop as the Insert Rows 'Apply to each' loop.

Thanks again for the help!

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report

HI @LK-22111434-0,

Yes, it needs to be in the same Apply to each loop as the Insert rows 👍

In the insert rows column you would use the outputs of your Join action.

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
LK-22111434-0 10 on at

Like (0)

Report

Brilliant thanks!

I'm having trouble with 'single choice' selection SharePoint columns, the Select error states it needs an array, but there's only one answer. Is there a similar way around this?

Example of single item in array:

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":-1,"Value":"xxxValue1xxx"}

Was this reply helpful? Yes No
Expiscornovus 33,195 Most Valuable Professional on at

Like (0)

Report

Hi @LK-22111434-0,

For single choice fields you don't need to use the Select action approach.

In that case you should be able to see a Column Name Value field in the Dynamic Content list which you can select.

Was this reply helpful? Yes No
LK-22111434-0 10 on at

Like (0)

Report

Thankyou that worked great, any advice on how to retrieve just the Email for the Person or Group field?

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser","xxListNamexx":"i:0#.f|membership|xxxEmailxxx","DisplayName":"xxLastNamexx, xxxFirstNamexxx )","Email":"xxxEmailxxx","Picture":"xxxPhotoxxx Size=L&AccountName=xxAccountNamexx","JobTitle":"xxJobTypexx"}]

If I used the Select, there's no 'person.Value' for the field, alterntively, putting in the Person.Email field returns null in the database.

Was this reply helpful? Yes No