cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keithschm
New Member

ValidationFailed. The schema validation failed.

I am getting data using Execute stored procedure (V2) from an Onprem SQL server using a Gateway an adding to a share point list. I ran it and grabbed the output for ResultSets and Generated from sample for Parse JSON.  But It keep failing Validation. Will pass for one record and work but not for more then 1. Any thoughts?

Here is the schema:

 

 

{
    "type": "object",
    "properties": {
        "Table1": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "CpnyId": {
                        "type": "string"
                    },
                    "Division": {
                        "type": "string"
                    },
                    "Project_Desc": {
                        "type": "string"
                    },
                    "Project": {
                        "type": "string"
                    },
                    "Office": {
                        "type": "string"
                    },
                    "Salesperson": {
                        "type": "string"
                    },
                    "Contract_Dt": {
                        "type": "string"
                    },
                    "Discount": {
                        "type": "integer"
                    },
                    "Good_Bad_Amt": {
                        "type": "integer"
                    },
                    "Sign_Contract_Price": {
                        "type": "number"
                    },
                    "Sign_TotalCost": {
                        "type": "number"
                    },
                    "Sign_MI": {
                        "type": "number"
                    },
                    "Sign_MI_Percent": {
                        "type": "number"
                    },
                    "Sign_Comm": {
                        "type": "number"
                    },
                    "Sign_Comm_Percent": {
                        "type": "number"
                    },
                    "Clear_Contract_Price": {
                        "type": "integer"
                    },
                    "Clear_TotalCost": {
                        "type": "number"
                    },
                    "Clear_MI": {
                        "type": "number"
                    },
                    "Clear_MI_Percent": {
                        "type": "number"
                    },
                    "Clear_Comm": {
                        "type": "integer"
                    },
                    "Clear_Comm_Percent": {
                        "type": "number"
                    },
                    "Cur_Contract_Price": {
                        "type": "number"
                    },
                    "Cur_TotalCost": {
                        "type": "number"
                    },
                    "Cur_MI": {
                        "type": "number"
                    },
                    "Cur_MI_Percent": {
                        "type": "number"
                    },
                    "Cur_Comm": {
                        "type": "integer"
                    },
                    "Cur_Comm_Percent": {
                        "type": "number"
                    },
                    "Dig_Date": {
                        "type": "string"
                    },
                    "Stage": {
                        "type": "string"
                    },
                    "Project_Status": {
                        "type": "string"
                    },
                    "NonWIPAmt": {
                        "type": "integer"
                    },
                    "SLX_Office": {
                        "type": "string"
                    }
                },
                "required": [
                    "CpnyId",
                    "Division",
                    "Project_Desc",
                    "Project",
                    "Office",
                    "Salesperson",
                    "Contract_Dt",
                    "Discount",
                    "Good_Bad_Amt",
                    "Sign_Contract_Price",
                    "Sign_TotalCost",
                    "Sign_MI",
                    "Sign_MI_Percent",
                    "Sign_Comm",
                    "Sign_Comm_Percent",
                    "Clear_Contract_Price",
                    "Clear_TotalCost",
                    "Clear_MI",
                    "Clear_MI_Percent",
                    "Clear_Comm",
                    "Clear_Comm_Percent",
                    "Cur_Contract_Price",
                    "Cur_TotalCost",
                    "Cur_MI",
                    "Cur_MI_Percent",
                    "Cur_Comm",
                    "Cur_Comm_Percent",
                    "Dig_Date",
                    "Stage",
                    "Project_Status",
                    "NonWIPAmt",
                    "SLX_Office"
                ]
            }
        }
    }
}

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
keithschm
New Member

I figured it out. It was a data type issue. If I had just looked it told me it was expecting a integer but got number. I changed the data type in the schema and all is working.

View solution in original post

3 REPLIES 3
Expiscornovus
Super User
Super User

Hi @keithschm,


Can you use the outputs of the second record, which is failing and use that to generate a parse json schema and compare it with your current schema? Maybe you are missing a couple of fields which are used in the second record?

 



Happy to help out! 🙂

Interested in more #PowerAutomate #SharePointOnline or #PowerVirtualAgents content?
Visit my blog, Subscribe to my YouTube channel or Follow me on Twitter


Thank you for the reply. I verified it is not the data by altering the SP to select different rows. All columns have data.

keithschm
New Member

I figured it out. It was a data type issue. If I had just looked it told me it was expecting a integer but got number. I changed the data type in the schema and all is working.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (3,992)