cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewraia
Advocate I
Advocate I

Collection not showing data from SQL Stored Procedure

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

andrewraia_0-1616450825814.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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?

View solution in original post

4 REPLIES 4
Pstork1
Dual Super User III
Dual Super User III

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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
Dual Super User III
Dual Super User III

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. 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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?

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,892)