cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimRob
Employee
Employee

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
Jcook
Most Valuable Professional
Most Valuable Professional

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
Jcook
Most Valuable Professional
Most Valuable Professional

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





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

Jcook
Most Valuable Professional
Most Valuable Professional

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





v-litu-msft
Community Support
Community Support

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.

Here's the "Parse JSON" success. 

ParseJSON-Full.jpg

Jcook
Most Valuable Professional
Most Valuable Professional

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





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! 

Jcook
Most Valuable Professional
Most Valuable Professional

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

Get Caught Up on Power Platform Connections!

Since January, we've been honored to present nearly 20 episodes of Power Platform Connections, a webcast highlighting some of our amazing community members, Power Platform news and blogs, and a lot of general fun from our hosts, Microsoft PM's Hugo Bernier and David Warner. Every episode is chock-full of informative interviews, engaging conversation--and what's new and important to know about every aspect of Microsoft Power Platform.     If you've missed an episode, it's time to get caught up. Check out the Community Connections & How-To Videos in the Gallery. Find out what's going on the Power Platform Community today! 

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Welcome to Power Automate Week with April Dunnam!

Welcome to #PowerAutomate week! Hear from this week's host, Principal Power Platform Advocacy Team Lead, April Dunnam, as she offers a sneak peek into all the content we have planned for you this week, as we look forward to the Microsoft Power Platform Conference in Las Vegas. #MPPC23 Register Today - https://www.powerplatformconf.com

Power Automate Week Starts on July 31

Monday July 31st sees the launch of #PowerAutomate Week in anticipation of the upcoming Microsoft Power Platform Conference in Las Vegas.Hosted by Principal Power Platform Advocacy Team Lead, April Dunnam, there'll be a great Copilot demo, a preview of some exciting MPPC23 Power Automate speaker sessions, plus a special message from Vice President Stephen Siciliano.   Come join the fun on the Microsoft Power Platform Community LinkedIn page for all the latest conference content and click the link below to register for #MPPC23. We can't wait to see you in Las Vegas this October 2023!http://www.powerplatformconf.com/

Users online (3,784)