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

(0) Share

Report

Posted on by SLMR

518 Moderator

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

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

MudassarSZ365 591 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
SLMR 518 Moderator on at

Like (0)

Report

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?

Was this reply helpful? Yes No
SLMR 518 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
SLMR 518 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No