cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gaëlle
Level 8

Filtering separately two nested galleries

Hello fellow powerappsers !

 

What I have and what i want to achieve :

I have a screen with a Text Input as a SearchBox, and a toggle Control, with "RI" as a true value and "CM" as false one.

I also have Gallery1 which DataSource is a SP List called RI.

In that Gallery1, I have Gallery2 which DataSource is a SP List called CM. CM and RI are linked by a column named RI_Number (a single line of text, generated by me, in each List).

Here is the code to retrieve the items of Gallery2 :

Filter(CMList,RI_Number=ThisItem.RI_Number)

1. I would like to, depending of the toggle (and obviously the Text input) to search either on RI and either on CM.

Use case :

User wants to find a certain word, he thinks it can be found on RIs, so he enters the word in the text input, toggle the control to be on RI, and search.

Result : The Gallery1 displays all the items with Word in their title, and their nested CM.

 

Now, if He does not find what he was looking for. He wants to try on CM. So he toggles to CM.

Result : the CM gallery is filtered on CM's titles with Word in it, but their relative RI are displayed too.

 

How is it possible to do that  please ?

 

2. I would like to search on Title OR/AND on Comments field. (for each gallery). Is it possible ?
Like

OR(filter(RIList,Contains(Title,SearchBox.Text.Value)),filter(RIList,Contains(Comment,SearchBox.Text.Value)))

Is it doable ?

 

Thanks a lot, and have a wonderful day !

1 ACCEPTED SOLUTION

Accepted Solutions
tchin-nin
Level 10

Re: Filtering separately two nested galleries

Hi @Gaëlle

 

I would include the OR in the filter's condition since you'll always filter the RI DataSource. 

Can you try :

 

Filter(AddColumns(RI,"CM",LookUp(CM,RI[@RI_Number]=CM[@RI_Number])),(toggle.Value="RI" && (SearchBox.Text in Title || SearchBox.Text in Comments)) || (toggle.Value="CM" && (SearchBox.Text in CM.Title || SearchBox.Text in CM.Comments)))

I have stored the full CM object in a new column (by performing only one lookup) so we can easily filter it's values. 

 

This formula will give you a delegation warning because of the AddColumns function and also the in operator that can't be delegated. 

If your RI list may contain more than 500 (up to 2000) items, tell me, we'll try some workarounds but it will consequently impact the performances. 

 

Théo

6 REPLIES 6
tchin-nin
Level 10

Re: Filtering separately two nested galleries

Hi @Gaëlle

 

I would include the OR in the filter's condition since you'll always filter the RI DataSource. 

Can you try :

 

Filter(AddColumns(RI,"CM",LookUp(CM,RI[@RI_Number]=CM[@RI_Number])),(toggle.Value="RI" && (SearchBox.Text in Title || SearchBox.Text in Comments)) || (toggle.Value="CM" && (SearchBox.Text in CM.Title || SearchBox.Text in CM.Comments)))

I have stored the full CM object in a new column (by performing only one lookup) so we can easily filter it's values. 

 

This formula will give you a delegation warning because of the AddColumns function and also the in operator that can't be delegated. 

If your RI list may contain more than 500 (up to 2000) items, tell me, we'll try some workarounds but it will consequently impact the performances. 

 

Théo

Gaëlle
Level 8

Re: Filtering separately two nested galleries

Hello Theo !

Thank you very very much, this is a very clever move. Creating the lookup function works like a charm !

 

Only, yes, now that you mention it... My current lists are test lists and only contains up to 10 records.

But my final lists will contain way more than 2000 items...

And this is now that i am wondering of changing my process. But my customer wants to be able to search through all history (which is approx. 1500 items x8 for RI and we have 1 to 5 CM by RI...)

Should I index my columns ?

 

I'll approve your solution, because it is working, but maybe not for my case.

 

tchin-nin
Level 10

Re: Filtering separately two nested galleries

<p>Hi <li-user login="Gaëlle" uid="32232"></li-user>,</p>
<p>&nbsp;</p>
<p>I was think, you don't even have to use a toggle, you can directly search within the RI and CM columns.&nbsp;</p>
<p>No that you're mentioning it (I should have guessed), using the lookup won't work if the RI has more than 1 CM, since lookUp retrieves only one item. You should use a Filter to store an array, and display this array in the nested gallery.&nbsp;</p>
<p>&nbsp;</p>
<p>Why are you saying 1500 <strong>x8&nbsp;</strong> ? Is there a way to prefilter (with a delegatable function) your RI list, to retrieve "only" 1500 items and increase the delegation limit to 2000 so we make it work ?</p>
<p>&nbsp;</p>
<p>Théo</p>
Gaëlle
Level 8

Re: Filtering separately two nested galleries

Hi Theo,

I don't fully understand you :

What are you saying about the toggle?

that i can already search within both lists ?

But I think it will be more explicit for the user if he has to perform an action to get some results, or they won't understand why they will be having ri or cm results.

 

Effectively, the lookup won't work, I'll do the filter... do you think I should store the filter array in a UpdateContext ?

 

ANd I was saying 1500x8, because currently my customer has 8 different RI lists with a max of 1500 Items / list. And I wanted to reunite these 8 Lists into 1 big. Sames goes for the CM, but each RI can have 1 to multiple CMs, that is why I am wondering about changing my process.

 

Thanks a lot !

 

Gaëlle
Level 8

Re: Filtering separately two nested galleries

Theo,

So far so good :

Here is the formula i used :

Filter(AddColumns(RI,"CM",Filter(CM,RI[@RI_Number]=CM[@RI_Number])),(toggle.Value="RI" && (SearchBox.Text in Title || SearchBox.Text in Comments)) || (toggle.Value="CM" && (SearchBox.Text in CM.Title || SearchBox.Text in CM.Comments)))

And as i already did for the CM gallery (within the RI one), I wrote the following in Items property :

Filter(CM,RI[@RI_Number]=CM[@RI_Number])

 

But for the big data results, this is not satisfying : It only gives me 125 results. I have a label text with:

CountRows(RIGallery.AllItems)

And it is always the same, 125.

But I have 4 items, test-duplicated to have 5090 items in my list. So, at top, I should have 1272-ish items.

(well, I hope they won't have all the same words but..)

 

So, Maybe we have a little problem with big data... Do you know any workaround maybe ?

 

Thanks a lot for the time you are giving me Smiley Wink

 

tchin-nin
Level 10

Re: Filtering separately two nested galleries

Hi,

 

What I'm saying is that you can search directly in RI and CM objects, no need the toggle. But if you think that having the toggle will help users, I have no problem with this. 

 

The first formula is good and you don't need to refilter your CM DataSource for the nested gallery. You can just use ThisItem.CM. The CM property you've just added with the AddColumns contains an array of all CM linked to the RI and like all arrays, it can be displayed in a gallery. 

If you want to visualize the shape of your data, juste ClearCollect your first formula and look how it looks (File/Collection)

 

Your gallery is containing only 125 items, but if you scroll down you'll see this number increasing, welcome to the best demonstration of how powerfull is Delegation !

But your filter should operate on your 500 first items. Let's say you have 3000 items (ID 1 to 3000) and 100 containing "test". When the filter operates, it will only look at the 500 first items. If 85 of the items containing "test" have an ID > 500 then your gallery will only show the first 15 (because the filter operates only on the first 500 items). Welcome to the dark side of Delegation .

 

There are only 2 things I can advise :

  1. Try to work with SQL which is wayyyy more delegatable options than SharePoint. (Anyway on your case the AddColumns is not delegatable either in SQL but you could use a View)
  2. To fight delegation issues, you can prefilter your DataSource. 

Let's keep the same example, now your 3000 items have a text column "department", and you know that each department doesn't have more than 500 items. Then you can add a Dropdown list to force the user selecting a department and prefilter the items displayed on your gallery, so you know that your search filter operates only on less than 500 items. 

 

I hope it helps,

Théo