Collection not showing data from SQL Stored Procedure

[andrewraia]

I have a powerapp button the executes a flow using a sored procedure action to return a list of departments to populate a dropdown. The flow executes properly and the data is capture in the flow; however, the collection in powerapps is empty and the dropdown will not populate.

FLOW

JSON

{

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "DeptName": {

                "type": "string"

            },

            "DeptID": {

                "type": "integer"

            },

            "DeptOrder": {

                "type": "integer"

            },

            "OldID": {

                "type": "string"

            }

        },

        "required": [

            "DeptName",

            "DeptID",

            "DeptOrder",

            "OldID"

        ]

    }

}

My power app form has a button with the OnSelect = ClearCollect(DivisionList,'MYSQLSPROC'.Run())

The dropdown list has items=DivisionList and value = DeptName

This is driving me up a wall. In the flow I can see the output of the response step and all the data is there.

[Pstork1]

Is there anything in DivisionList after the flow runs?  If the collection is empty then there is something wrong with the results being returned.  In my experience its usually the schema.  I normally use filter Array and Data Select to remove unnecessary rows and columns from the results table before returning the results.  A single null value in the results could be causing the issue.

[andrewraia]

So I do see a few properties of some objects that are null. See the following..

  {

    "DeptName": "My Dept",

    "DeptID": 34,

    "DeptOrder": 350,

    "OldID": "099 "

  },

{

    "DeptName": "My dept",

    "DeptID": 45,

    "DeptOrder": 380,

    "OldID": null

  }

Not sure how to filter that out, but I might want to know there is no id for the record. Null values break collections?

[Pstork1]

Null values break JSON arrays unless you allow for them in the schema.  Normally you would do 

"Type": ["string", "null"] instead of just "Type": "string".  But Power Apps doesn't know how to handle that.

To remove those lines you can use a Filter Array action and filter on the field not equal to null.

If its fields you don't need you can use a Data Operation select action to pick which columns you want to return.

But first I would check the collection in Power Apps after the flow runs to see if you are getting any results. If you are just getting a boolean or no collection at all then something is wrong with the schema. 

[andrewraia · Accepted solution]

So I finally got it working but I had to start a new app from scratch which was annoying. I could not get rid of the old collection and it had some strange column that would not go away. I wound up doing this which seems to be working.

ClearCollect(LoadDivisions,'WMR-GetDivisions'.Run(), Filter(LoadDivisions,OldID="null"))

In this case it will work but there is another procedure where I don't know what columns will have null values, and I might have to replace them with something, I think there is a way to do that as well?