cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
Microsoft

Kusto Results into Parse JSON into Update CDS Record

Hi folks!

I've got a flow set up that when a CDS record is created, it pulls one of the fields, munges the input, runs a kusto query, then parses the JSON result.

 

So far, every step of the way gets output that I'm expecting, and successfully completes. Where I'm running into problems is writing that content back into the record with the "update a record" action. When I test what happens, everything is showing that it's running correctly, but the values coming from the parse JSON action are empty, even though they're correctly showing up in the dynamic content pane of the builder.

 

Flow Run:

Good Run.jpg

Parse JSON output:

ParseJSON.jpg

 

Null Values in "Update a record":

Null Data.jpg

 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Kusto Results into Parse JSON into Update CDS Record

Hello @TimRob 

 

There seems to be a issue with your schema. I tested a small sample on my side with the picture you provided. I used:

{
    "value": [
        {
            "OrgsId": "1234567890-0987654321",
            "FriendlyName": "Communication Engineering",
            "UniqueName": "org123456",
            "State": 1
        }
    ]
}

As my body for the parse JSON and also used the same JSON for generating the Schema within the Parse JSON action. When generating the schema from sample I got this:

{
    "type": "object",
    "properties": {
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "OrgsId": {
                        "type": "string"
                    },
                    "FriendlyName": {
                        "type": "string"
                    },
                    "UniqueName": {
                        "type": "string"
                    },
                    "State": {
                        "type": "integer"
                    }
                },
                "required": [
                    "OrgsId",
                    "FriendlyName",
                    "UniqueName",
                    "State"
                ]
            }
        }
    }
}

Which looks very different from your schema. Another test I ran was I wanted to see if I could get the Dynamic content from the Parse JSON using a Compose action.

Since the Body JSON provided is an array, indicated by: 

"value": [

When selecting the dynamic content for any of the values, flow automatically puts everything in a loop.

This makes me believe that your schema is the root cause of the null values.

 

To fix:

- You can copy the entire body from the Parse JSON action you provided above. And paste it in the Parse JSON 'generate from sample' button. This should correct the schema

or if this still does not work.

Could you provide the full details from the Parse JSON body and or the body of the Kusto action. (You can change the values and give dummy data if you need)





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: Kusto Results into Parse JSON into Update CDS Record

Hello @TimRob 

 

Could you try adding a Compose action and put one of the Parse JSON values inside like OrgsId and run the flow. Please let me know what value comes up in the Compose action.





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Microsoft
Microsoft

Re: Kusto Results into Parse JSON into Update CDS Record

I get a link to download the results, and then when I click on it, it opens a new blank page in the browser.

Highlighted
Super User III
Super User III

Re: Kusto Results into Parse JSON into Update CDS Record

Hello @TimRob

My thought is that their is something wrong with the Json Schema.

Would you be able to give me an example output from the kusto list results action, so I can try to parse on my end




Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Highlighted
Community Support
Community Support

Re: Kusto Results into Parse JSON into Update CDS Record

Hi @TimRob,

 

Could you please share the screenshot of the Parse JSON action?

 

You could run the Flow to get the body of the "Run query and list results" action, then copy it then click the button "Generate from example" to past it. 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Microsoft
Microsoft

Re: Kusto Results into Parse JSON into Update CDS Record

Here's the "Parse JSON" success. 

ParseJSON-Full.jpg

Highlighted
Super User III
Super User III

Re: Kusto Results into Parse JSON into Update CDS Record

Hello @TimRob 

 

There seems to be a issue with your schema. I tested a small sample on my side with the picture you provided. I used:

{
    "value": [
        {
            "OrgsId": "1234567890-0987654321",
            "FriendlyName": "Communication Engineering",
            "UniqueName": "org123456",
            "State": 1
        }
    ]
}

As my body for the parse JSON and also used the same JSON for generating the Schema within the Parse JSON action. When generating the schema from sample I got this:

{
    "type": "object",
    "properties": {
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "OrgsId": {
                        "type": "string"
                    },
                    "FriendlyName": {
                        "type": "string"
                    },
                    "UniqueName": {
                        "type": "string"
                    },
                    "State": {
                        "type": "integer"
                    }
                },
                "required": [
                    "OrgsId",
                    "FriendlyName",
                    "UniqueName",
                    "State"
                ]
            }
        }
    }
}

Which looks very different from your schema. Another test I ran was I wanted to see if I could get the Dynamic content from the Parse JSON using a Compose action.

Since the Body JSON provided is an array, indicated by: 

"value": [

When selecting the dynamic content for any of the values, flow automatically puts everything in a loop.

This makes me believe that your schema is the root cause of the null values.

 

To fix:

- You can copy the entire body from the Parse JSON action you provided above. And paste it in the Parse JSON 'generate from sample' button. This should correct the schema

or if this still does not work.

Could you provide the full details from the Parse JSON body and or the body of the Kusto action. (You can change the values and give dummy data if you need)





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




View solution in original post

Highlighted
Microsoft
Microsoft

Re: Kusto Results into Parse JSON into Update CDS Record

Ok, I find this "interesting" that I generated the original schema by using the "Generate from Sample", and then used that functionality a second time to make this work. I see where the problem is now, and I had tried to brute-force it previously by nesting the "Update a Record" in an "Apply to Each" action for the "value" being retuned by the "Parse JSON".

 

As always, a huge "THANK YOU" to the community for being helpful, patient, and knowledgeable! 

Highlighted
Super User III
Super User III

Re: Kusto Results into Parse JSON into Update CDS Record

Glad to help! 🙂 @TimRob 

 

Do some tests though, Run flow with multiple queries coming from Kusto, and single records too.

 

There is a chance your schema will not like one or the other. If this is the case, the schema can be modified to accept both array and non array values.





Did I answer your question? Mark my post as a solution!

If you like my post please hit the Thumbs Up


Proud to be a Flownaut!


Check out my blog for Power Automate tips, tricks, and guides
FlowAltDelete




Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (8,794)