cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rprahalad
Helper V
Helper V

How do I filter based off of a Lookup Column?

I have a table called myTable. It has a Lookup Column called RegionId pointing to this master table called "Regions".

 

I have a Search form where there is a Multi-Select combo box that gets populated from this master table Regions as well.  User selects couple of regions from this combo box and clicks on Search.

 

Now, I have to search the selected values in the Lookup column of myTable. How do I overcome the delegation error when I do this?  

 

ClearCollect(colSearchResults, Filter(myTable, RegionId in cboRegions.SelectedItems);

 

I understand the delegation and the "in" operator on a Lookup BUT what is the way to work around it? Even with Views, it gives this delegation error.

 

Please let me know, as I just can't figure out a way because I might as well code it off a flat Excel like structure and not have any ER's.

 

Thanks

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
R3dKap
Super User
Super User

Hi @rprahalad,

I'm afraid there is no workaround for this case. The easiest solution is to load your table into a collection BEFORE applying the IN operator to it. When you deal with data in memory (through collections) you don't have any delegation problems.

The only drawback of this is that you will be limited to a maximum number of 2000 records loaded in your collection. So, if you can manage to load only the necessary records you really need (less than 2000) before filtering it down to the selected regions, that would be the solution.

View solution in original post

4 REPLIES 4
R3dKap
Super User
Super User

Hi @rprahalad,

I'm afraid there is no workaround for this case. The easiest solution is to load your table into a collection BEFORE applying the IN operator to it. When you deal with data in memory (through collections) you don't have any delegation problems.

The only drawback of this is that you will be limited to a maximum number of 2000 records loaded in your collection. So, if you can manage to load only the necessary records you really need (less than 2000) before filtering it down to the selected regions, that would be the solution.

View solution in original post

HI @R3dKap , I honestly did NOT know that that was the case and I discovered it just today. Ha Ha. I wish you had replied earlier (Just kidding of course:-)).

 

Yes, what you said is the exact work around. I actually simplified the question above BUT this was more of an advanced search page and there are about 20 drop downs and one free text search  box. Luckily for me, there are few drop downs that are absolutely required and even more lucky, those are single select boxes.

 

I started teeing off from those columns and actually stopped to look at the collection and absolute accidentally did the 'in' on  that and suddenly woke up to realize that it was not giving me delegation warning when I do IN on a collection! Those mandatory dropdowns were a boon, as it will definitely narrow it down to even less than 500 rows. 

 

But I will still mark your answer as the Accepted Solution because it absolutely is.  Thank You so much again for responding!! Finally I think I have mastered (Yea right..:-) the CRUD in PowerApps. 

R3dKap
Super User
Super User

Glad I could help @rprahalad. Just remember something that most low-code developpers forget or don't pay enough attention to: LOW-CODE means LOW-APPLICATIONS. Power Apps canvas apps & SharePoint are not made for large-scale/huge-data applications. It is perfect for small personal needs or needs for small teams or simple small application for many users. But as soon as it becomes more complex, you should turn to Dataverse as it is scaled for larger applications. You will always be able to build whatever applications using canvas apps & SharePoint, but you could easily end-up with a solution that has pages of codes everywhere which will be horrible to maintain (I'm saying this because I experienced it). Sometimes, you have to accept saying: Power Apps is not the right tool for this need.

😉

Couldn't agree more:-). Yes, luckily its DataVerse for us and some of the delegation issues are mitigated. 🙂

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

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (73,495)