Filtering a Gallery by another one with multi selection

(0) Share

Report

Posted on by angelim

Hello Folks,

I have 3 galleries in my app screen. The first one have as a datasource a SQL table named '[dbo].[Departamentos]'.

The second one use as a datasource another SQL table named '[dbo].[Perfis]', with a filter that is conditioned to the gallery 1 choice and with a checkbox for each line.

In this gallery I'll show a list of systems avaliable as the choice in the gallery 1.

The third gallery use the same datasource as the gallery 2 but with another filter code, to show the profiles that contain the systems selected in gallery 2.

The relation between gallery 1 and gallery 2 is perfect but between gallery 2 and 3 didn't worked as I wish.

I got a way to show in the gallery 3 all the profiles that have at least one of the systems selected in the gallery 2 but what I'm trying to doing is showing in the gallery 3 only the profiles that have all the systems selecteds in the gallery 2.

If I select a combination of systems that don't have in any profile the gallery 3 will be empty.

See below the codes that I've used in each gallery and in the checkbox:

Gallery 1 (OnSelect): '[dbo].[Departamentos]'

Gallery 2 (OnSelect): Distinct(Filter('[dbo].[Perfis]';'Gallery 1'.Selected.IdDepart = IdDepart);IdSist)
Gallery 2 Checkbox (OnCheck): Collect(Sist;{IdSist:ThisItem.Result})
Gallery 2 Checkbox (OnUncheck): Remove(Sist;Filter(Sist;ThisItem.Result = IdSist))
Gallery 2 label with system name: LookUp('[dbo].[Sistemas]';IdSist = ThisItem.Result;NomeSist)

Gallery 3 (OnSelect): Distinct(Filter('[dbo].[Perfis]';IdSist in Sist.IdSist && 'Gallery 1'.Selected.IdDepart = IdDepart);NomePerfil)

Tables and columns used:
'[dbo].[Departamentos]': {IdDepart, Nome}
'[dbo].[Perfis]': {IdPerfil, IdDepart, IdSist, IdMod, IdPerm, NomePerfil}
'[dbo].[Sistemas]': {IdSist, NomeSist}

Can someone give me a "light" on this problem?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @angelim ,

Would you like to display "Perfis" in Gallery 3 that IdDepart equal to Gallery1 selected department and IdSist totally include Gallery 2 checked systems?

If so, could you please share the details of IdSist column of Perfis table? What is the column type? How did you store multiple IdSist values in a single field?

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
angelim 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni ,

Yes I think you got how it works but due the complexity I will try to do an example.

- I have in my Gallery 2 the systems A, B, C and D, due the choice that I've did in the Galerry 1;
- In my table '[dbo].[Perfis]' I have 3 profiles:
- Profile 1 - Systems A and D allowed;
- Profile 2 - Systems B, C and D allowed;
- Profile 3 - System A allowed.

If I select in the Gallery 2 the system A, I'll have in the Gallery 3 the profiles 1 and 3;
If I select in the Gallery 2 the systems A and D, I'll have in the Gallery 3 only the profile 1;
If I select in the Gallery 2 the systems A and B, I'll have in the Gallery 3 no profiles to be displayed;

The column IdSist in the table '[dbo].[Perfis]' is integer, null and FK of the column IdSist of table '[dbo].[Sistemas]'

I'm not storing multiple IdSist values in a single field, when I select a value in the Gallery 2 I'm storing this value in a collection called Sist.

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @angelim ,

If using a single FK column you would not be able to connect the two tables Perfis and Sistemas with a Many-to-Many relationship, it only connects them with a Many-to One relationship which means a profile links to only one system. There should be a junction table connecting to both of them:
https://stackoverflow.com/questions/13970628/how-do-i-model-a-many-to-many-relation-in-sql-server

So please confirm it in your data base.

Assuming it names as "[dbo].[Junction]" which includes 3 columns IdJunction, IdSist and IdPerfil. In Power Apps, since you have checked system ids in the Gallery2 and collect them into a collection, below are the steps:
1\ Create collections of the tables OnVisible of the screen:
ClearCollect(colJunk, '[dbo].[Junction]');ClearCollect(colPerfis,'[dbo].[Perfis]')

2\ Apply below formula on Items of Gallery3:
ForAll( Distinct( Filter( Ungroup( ForAll( colPerfis As A, If( CountRows( Filter( Ungroup( ForAll( Sist As B, Filter( TestG, IdSist = B.IdSist, IdPerfil = A.IdPerfil ) ), "Value" ), IsBlank(IdPerfil) ) ) > 0, Blank(), Ungroup( ForAll( Sist As B, Filter( colJunk, IdSist = B.IdSist, IdPerfil = A.IdPerfil ) ), "Value" ) ) ), "Value" ), !IsBlank(IdPerfil) ), IdPerfil ) As C, LookUp( '[dbo].[Perfis]', IdPerfil = C.Result ) )

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
angelim 7 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni ,

You are right, I had to step back to the my database analisys to check the first problem.

After that I've applied your codes and it worked amazing, thanks a lot!

P.S.: In the line 14 of the second code I've surrogated TestG for cloJunk.

Was this reply helpful? Yes No