cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omh
Helper II
Helper II

Filter not returning all data in a gallery - delegation issue with IN operator

Hello,

 

I have an application which consists of the following:

- Gallery1: a gallery which has a SQL view as source. The items in this gallery are filtered based on the text inserted in a text field:

Filter(Search('[dbo].[vw1]',filterTextBox.Text,"Field1","Field2","Field3","Field4","Field5","Field6","Field7"),Field8=glbEntity).

I need to filter data in another gallery (Gallery2), based on the results from Gallery1.

- Gallery 2: I need to filter the data in another view based on the id-s returned in Gallery1. If I put in the Items Filter('[dbo].[vw2]', PK_ID in Gallery1.AllItems.Result ) I get the delegation warning and not all the rows are being returned. From what I have read the in operator should be delegable with SQL.... I have also tried to return distinct values in Gallery1, but with no success..

 

Has anyone overcome the delegation issue with in?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions

@omh 

Well that's a bummer. Knowing PowerApps, I was concerned that a solution this simple may not work, lol.

 

My next idea would be to create an on demand in-app collection of Gallery 1 and use that to reference in Gallery 2 eg

 

Use a button or if you are using screen transition between the galleries then on that transition place code similar to:

 

ClearCollect( colSelectedGallery1, Gallery1.AllItems)

View solution in original post

4 REPLIES 4
Eelman
Super User
Super User

@omh 

I don't work with SQL but my quick read of the docs it seems to me that 'in' only works on Text fields. Are your IDs numbers?

 

If so, you may be able to wrap your DBs in AddColumns() and convert your IDs to Text. Would probably have to do it in both Galleries.

 

Here's  the link I referenced: https://docs.microsoft.com/en-au/connectors/sql/#powerapps-delegable-functions-and-operations-for-sq...

 

Hi @Eelman ,

 

Thank you for your answer. Indeed, I have noticed that only Text fields. I will try what you suggested and let you know.


Regards,

Oana

hi @Eelman ,

 

I have added columns of type nvarchar in both views/galleries but I still get the delegation warning..

 

Filter([dbo].[vw_2], PK_ID_text in gallery1.AllItems.PK_Text )Capture.PNG

 

 

 

@omh 

Well that's a bummer. Knowing PowerApps, I was concerned that a solution this simple may not work, lol.

 

My next idea would be to create an on demand in-app collection of Gallery 1 and use that to reference in Gallery 2 eg

 

Use a button or if you are using screen transition between the galleries then on that transition place code similar to:

 

ClearCollect( colSelectedGallery1, Gallery1.AllItems)

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors
Users online (2,466)