cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JosL
Level: Powered On

Bug when using collection as IN filter on SQL datasource

I have a SQL datasource that I'm showing in my PowerApp as a Gallery as follows (pretty default):

 

SortByColumns(Search(Filter('[dbo].[klanten]',type = Dropdown1.Selected.Value), TextSearchBox1.Text, "displayName","naam","type"), "displayName", If(SortDescending1, Descending, Ascending))

 

This works fine of course. I then added the following to my apps first screen (OnVisible):

Set(globalAdmins,["jos.lieben@xxx"]);Set(CurrentUser, User());ClearCollect(powerAppRightsRead,Filter('[dbo].[powerAppAcl]',userUpn=CurrentUser.Email && Or(rights="R",rights="RW")));ClearCollect(powerAppRightsWrite,Filter('[dbo].[powerAppAcl]',userUpn=CurrentUser.Email && rights="RW"))

 

Save + reload the app, and my SQL datasources suddenly become 'Not connected' in the data sources view (under the view menu). Removing the code in OnVisible does not fix the issue, I have to revert to a previous version of the app. The only exception being the powerAppAcl table (in the same datasource).

3 REPLIES 3
mr-dang
Level 10

Re: Bug when using collection as IN filter on SQL datasource

Hi @JosL,

 

I've formatted your formulas below for readability: 

 

SortByColumns(
    Search(
        Filter('[dbo].[klanten]',
            type = Dropdown1.Selected.Value
        ), 
    TextSearchBox1.Text, "displayName","naam","type"
    ), 
"displayName", If(SortDescending1, Descending, Ascending)
)

 

Set(globalAdmins,["jos.lieben@xxx"]);

Set(CurrentUser, User());

ClearCollect(powerAppRightsRead,
    Filter('[dbo].[powerAppAcl]',
        userUpn=CurrentUser.Email && 
        Or(rights="R",rights="RW")
    )
);

ClearCollect(powerAppRightsWrite,
    Filter('[dbo].[powerAppAcl]',
        userUpn=CurrentUser.Email && 
        rights="RW"
    )
)

 

If I understand correctly, your datasources are showing up as "not connected" in the datasource panel.

 

Here's my initial thoughts

  • It's possible you've opened the app in the wrong environment. If you open the app in an environment where the SQL server is not a connection, your data tables will not be connected.
  • Have you altered the structure of the SQL table recently? If so, you may need to remove and re-add the table as a datasource.

If you can get a session ID, that be helpful if it is not one of the cases above.

 

Studio: https://aka.ms/pasessionid

Web: https://aka.ms/sessiond

 

Mr. Dang

_______

Microsoft Employee
@8bitclassroom
Highlighted
JosL
Level: Powered On

Re: Bug when using collection as IN filter on SQL datasource

Hi Mr Dang,

 

thanks for the quick reply. My session ID: "70f38558-54c9-42da-a040-70b4d6569d10"

 

I did not alter the SQL table and did not connect to the wrong environment. Everything is connected and working fine and breaks the moment I add that code. If I do anything else but add that code (add tables, selections whatever) it also works fine.

 

I exclusively use the web browser (Chrome) to edit the app.

 

 

mr-dang
Level 10

Re: Bug when using collection as IN filter on SQL datasource

@JosL,

Can you send me a PM and we can schedule a skype call where I can help you trace how data is moving in your app?

 

Mr. Dang

Microsoft Employee
@8bitclassroom

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 347 members 6,598 guests
Recent signins:
Please welcome our newest community members: