Skip to main content

Notifications

Power Automate - Building Flows
Answered

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

Posted on 25 Nov 2024 16:17:19 by 10
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! 
 
  • LK-22111434-0 Profile Picture
    LK-22111434-0 10 on 27 Nov 2024 at 09:01:30
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
    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.
  • Expiscornovus Profile Picture
    Expiscornovus 30,623 on 26 Nov 2024 at 16:36:24
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
     
    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.
  • LK-22111434-0 Profile Picture
    LK-22111434-0 10 on 26 Nov 2024 at 14:54:19
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
    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"}
  • Verified answer
    Expiscornovus Profile Picture
    Expiscornovus 30,623 on 26 Nov 2024 at 14:39:03
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
     
    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

  • LK-22111434-0 Profile Picture
    LK-22111434-0 10 on 26 Nov 2024 at 14:02:07
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
    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! 
  • LK-22111434-0 Profile Picture
    LK-22111434-0 10 on 26 Nov 2024 at 09:50:59
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
    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? 
  • Expiscornovus Profile Picture
    Expiscornovus 30,623 on 25 Nov 2024 at 16:34:20
    Exporting SharePoint List to SQL Database and removing '[{"@odata.type":"#..' from Choice values
     
    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
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletterโ€ฆ

November 2024 Community Newsletterโ€ฆ

Community Update Oct 28โ€ฆ

Power Platform Community Updateโ€ฆ

Tuesday Tip #7 Community Profile Tipsโ€ฆ

Welcome to a brand new series, Tuesday Tipsโ€ฆ

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,246

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,884

Leaderboard