cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
angelim
New Member

Filtering a Gallery by another one with multi selection

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

4 REPLIES 4
v-jefferni
Microsoft
Microsoft

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.

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.

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.

View solution in original post

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.

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 Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (46,916)