Gallery Filter over 3 levels with mutiple drop down boxes (more restrictions resulting in more results??)

[SLMR]

Hello,

i have the following datamodel:

Top level: Kunde (Customer)

2nd level Projekt (project) with lookup column called "Kunde"

3d level Case (case) with lookup column called "Projekt"

4th level Kosten (costs) with lookup "Case"    This table has an additional looukp directly to Kunde calles externalAccounting

I want to display all costs of a selected customer.

It works fine with:

With(
       {
         _ProjectPerKunde: Filter(
          Projekts,
          Kunde.Kunde = filter_Kunde.Selected.Kunde
          ).Projekt
     },
      With(
             {
              _CasePerProjekt: Filter(
               Cases,
               Projekt.Projekt in _ProjectPerKunde
             ).Case
            },
              Filter(
              Kosten,
              Case.Case in _CasePerProjekt
                )
     )
)

In the next step i want to be able to only select a specific projekt of this customer

With(
      {
      _ProjectPerKunde: Filter(
       Projekts,
       Kunde.Kunde = filter_Kunde.Selected.Kunde And Projektnummer=filter_Projekt.Selected.Projektnummer
      ).Projekt
        },
      With(
         {
          _CasePerProjekt: Filter(
           Cases,
          (Projekt.Projekt in _ProjectPerKunde)
       ).Case
      },
     Filter(
     Kosten,
     Case.Case in _CasePerProjekt
       )
  )
)

Now it shows me all costs.

I do not understand how i can get more records if i make the filter more restrictive

Any help is welcome, this drives me mad.

This is an easy problem and i can not see what i do wrong

[MudassarSZ365 · Accepted solution]

Hi @SLMR ,

Filter Projects by Customer: First, collect all projects related to the selected customer.

ClearCollect(
    colProjectsPerCustomer,
    Filter(
        Projekts,
        Kunde.Kunde = filter_Kunde.Selected.Kunde
    )
);

Filter Cases by Selected Project: Then, if a project is selected, further filter the cases by that project.

ClearCollect(
    colCasesPerProject,
    If(
        !IsBlank(filter_Projekt.Selected.Projektnummer),
        Filter(
            Cases,
            Projekt.Projektnummer = filter_Projekt.Selected.Projektnummer
        ),
        Filter(
            Cases,
            Projekt.Projekt in colProjectsPerCustomer.Projekt
        )
    )
);

Filter Costs by Cases: Finally, filter the costs by the cases from the selected project.

ClearCollect(
    colCostsPerCase,
    Filter(
        Kosten,
        Case.Case in colCasesPerProject.Case
    )
);

Try this revised approach, and if it helps solve your issue, please consider accepting it.

[SLMR]

Hello this will definitly help.

I used collections as a workaround in other cases.

The problem is that i do not understand what is wrong with my apporach.

The logic i used seems to be right, but the results i get are completly wrong? 

[SLMR]

Hello @MudassarSZ365 

I just looked deeper in your example code.

The point is that if filter_projekt is not blank than i only need the cases for them case.projekt eq filter_projekt and  case.projekt has to be in the collection colProjektPerCustomer

I think i will have to go with a Forall loop and than compare each item. This should work.

Anyway my filtering will be much more complex in the end.

So i do not think that i can place the code in items property of the gallery.

But i could use as items properties someting like Filter(cost, cost in colFileterdCost)

The problem is that the code will become a little bit longer.

Is there any possiblity to place the code at some central point so that i can call it from different points?

I do not want to copy the code into the select event of each component where it would be necassary

I think this is the part where power apps does not really what i want.

[SLMR]

For anyone who wil run into the same problem:

I ended up with placing a refresh button where i centrally call the function do all the filtering.

Clear(colCasePerProjekt);
Clear(colCostPerCase);
Clear(colProjectsPerCustomer);
If(
    ch__Kunden_all.Checked = true,
    ClearCollect(
                        colProjectsPerCustomer,
                        Projekts
                  );
    ,
    ClearCollect(
                       colProjectsPerCustomer,
                      Filter(
                      Projekts,
                      Kunde.Kunde = filter_Kunde.Selected.Kunde
                      ).Projekt
           )
);

If(
   ch__Projekt_all.Checked = true,
   ClearCollect(
                       colCasePerProjekt,
                       Filter(Cases, Projekt.Projekt in colProjectsPerCustomer.Projekt)
                     )
      ,
      ClearCollect(
                        colCasePerProjekt,
                        Filter(
                       Cases,
                        Projekt.Projekt = filter_Projekt.Selected.Projekt
                       )
        )

);

If(
     ch__Case_all.Checked = true,
     ClearCollect(
                         colCostPerCase,
                         Filter(Kosten, Case.Case in colCasePerProjekt.Case)
                      )
   ,
  ClearCollect(
                     colCostPerCase,
                    Filter(
                    Kosten,
                      Case.Case = filter_Case.Selected.Case
                )
     );
)

For the gallery items i then use colCostPerCase with further filter to attributes specific to the cost table