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.

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
Community Support
Community Support

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.

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.

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 ,

 

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,403)