You’re offline. This is a read only version of the page.
I will start by admiting that I'm a novice and out of my league in this area. I'm attempting to wrap my head around using Logic Apps to handle the json output from a form to create a lead in our CRM solution. For reference of anyone that's interested, I'm coming from FormSite going to Dynamics CRM in the end.
At this point where I'm getting hung up is how to handle the data coming in.
The JSON is coming in in the format below:
Share
Report{
"headers": {
"Cache-Control": "no-cache",
"Connection": "keep-alive",
"Pragma": "no-cache",
"Accept": "text/html,application/json,application/xml",
"Host": "prod-12.northcentralus.logic.azure.com",
"User-Agent": "Java/1.8.0_151",
"Content-Length": "363",
"Content-Type": "application/json; charset=UTF-8"
},
"body": {
"id": "11665467",
"items": [
{
"values": [
{
"position": 1,
"value": "White"
}
],
"id": "0",
"position": 0
},
{
"id": "1",
"position": 1,
"value": "Testing"
}
]
}I've found references for handling arrays, but I'm not seeing how to handle an array of the type I'm dealing with, and I could be overlooking, or not be making the connection. Each of the items is not merely another instance of a single item type. Each item in the array is: the question's ID, it's place on the form, and the response value or in the case of a multi-select or drop down the 'values'. I only care about the ID, and it's value(s).
I'm wanting to get these value fields into the appropriate CRM field. From the research I've been able to do so far, it looks like I might be able to accomplish this through 'Compose', but again, I'm a novice.
I'm really not wanting someone to do the entire solution for me, but I am really struggling at figuring out how LogicApps will allow me to access the specific items, and their values at each field.
Thanks for your patience and assistance!
Nice solution, I can confirm this worked nicely. Thank you.
Thanks this helped me parsing through my JSON output 🙂
Hi @DavesTechTips ,
In VS Code, CTRL+F to bring up the Find menu. In there, click the RegEx button:
I used Find with 8 spaces and " to peel off the front, but the real RegEx used was:
":.*
Which searches for ":, then using .* peels off everything to the end-of-line. Replace with " , " results in:
Probably wasn't the SuperHero RegEx you were looking for, but there it is!!
Hi @ericonline
Thank you for this. i have fought with regex quite a bit but there seems to be caveats on various chars and data types. What regex did you use, or did you use a tool to create regex.
I can't help to feel that there is nothing regular about regex 😉
Just to follow up here...
I ended up:
That pulled back a nice clean response without all the cruft.
Hi @ericonline
Ouch, that sounds like a Flow that woudl require a nice big screen 😉
In the SharePoint Get Items action, you can limit the columns that will be returned from the SharePoint List by referencing a view. You could then possibly use Odata filters to only get the necessary data.
I am sure you know how to do this, but below is a quick video that demos this.
Let me know if you don't come right and if you could possible send me more info to look at to see if I can think of anything else.
Have a great day!
Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.
Hi @DavesTechTips ,
Thanks for the reply. The Select action would work IF I didn't have multiple lists to query. I'm trying to build a job that will pull data from 12 different Sharepoint lists. I think I'd have to have 12 different Selects and 12 different Parse JSON's to accomplish.
Also, one of the lists is 180 columns wide!
Back to the drawing board for me!
Hi @ericonline
The easiest would be to use the Select Action which will allow you to map certain columns (keys) and values.
Select certain JSON columns in Flow
Please let me know if you don't come rigth.
Have a great day.
Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.
Hi @v-xida-msft and @DavesTechTips ,
How about accessing only certain columns and values within an array and excluding others?
Example json:
All responses (
Answers (
{
"d": {
"results": [
{
"__metadata": {
"id": "c7e8ca72-9007-40c6-b775-538affd3d806",
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)",
"etag": "\"6\"",
"type": "SP.Data.TestProjectDataListItem"
},
"FirstUniqueAncestorSecurableObject": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FirstUniqueAncestorSecurableObject"
}
},
"RoleAssignments": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/RoleAssignments"
}
},
"AttachmentFiles": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/AttachmentFiles"
}
},
"ContentType": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/ContentType"
}
},
"GetDlpPolicyTip": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/GetDlpPolicyTip"
}
},
"FieldValuesAsHtml": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesAsHtml"
}
},
"FieldValuesAsText": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesAsText"
}
},
"FieldValuesForEdit": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/FieldValuesForEdit"
}
},
"File": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/File"
}
},
"Folder": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Folder"
}
},
"LikedByInformation": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/LikedByInformation"
}
},
"ParentList": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test//_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/ParentList"
}
},
"Properties": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Properties"
}
},
"Versions": {
"__deferred": {
"uri": "https://ourcompany.sharepoint.com/sites/test/_api/Web/Lists(guid'1b79c193-2379-4233-ba23-bc232e6f27f4')/Items(35514)/Versions"
}
},
"FileSystemObjectType": 0,
"Id": 35514,
"ServerRedirectedEmbedUri": null,
"ServerRedirectedEmbedUrl": "",
"ContentTypeId": "0x0100F454567E17A56C498EC3619A0BB9A73500D527A9BA30B7AC4C8FBB138AF4F017BC",
"Title": null,
"Column1": "235514",
"Column2": "testThing",
"Column3": "2020-01-01T06:00:00Z",
"ColumnN": "There are a LOT of columns to get past this one too!"
}
],
"__next": "https://ourcompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('Master%20Test%20Data')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d35514&%24top=1&%24orderby=ID+desc"
}
}
I only want "Column1" - "ColumnN", none of the other stuff in the array.
Thank you
Hi @v-xida-msft
As always, thanks for the info.
Just one question. Above you mention "In addition, if you want to access the specific item whose question ID is euqal to 1, please take a try with the following workaround:" and then specify the the following expression
outputs('Compose_2')?[1]?['id']From my understanding the above expression will get the second item in the list (with the array item 1 specified), an not actually search the items and return the specific item whose question item is "1".
Please let me know if I am missing something, or if there is a way to search the items and return the ones that have specific property values? I have been using xpath for this, but from your post I am thinking that there might be another way for me to accomplish this.
Have a great day.
twitter: @dawidvh
youTube: https://www.youtube.com/channel/UCIMe_ErQZP5chbI9UyW0xLw
>>If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.<<
#1
WarrenBelz
146,765
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,091
Most Valuable Professional
