Dropdown filter with Choice column in SP list, 2 collections and 3 filters on my data table

(1) Share

Report

Posted on by KalvisT

131

Hei,

I've run into a problem. Firt of all, i have a filter for my SP list datatable that filters it by the logged on user, so one can't see irrelevant cases.

I want to add 2 dropdown filters. One filters by a Choice column 'Process' and the other is MonthY

My current filter on the datatable is Filter(mydatasource, 'Created By'.DisplayName = User().DisplayName)

When I add 2 colections to screen3

ClearCollect(collectProcess, {Result: "All"}); \\ My choice column dropdown doesnt get "Add" added to the top of the list, but a blank space.

Collect(Choices([@'MySPlist'].Process));

ClearCollect(collectMonthY, {Result: "-"}); \\ This works for my dropdown and the first value also shows -

Collect(collectMonthY, Distinct(MySPlist, 'MonthY);

How could I solve that it does show the All for the choice column dropdown? How could I get the collection right so if works for both?

And how could I combine all 3 filters on the datatable ?

The first filter is Filter(mydatasource, 'Created By'.DisplayName = User().DisplayName), how could the If statemnt look like?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi,

I'm thinking that you are looking to be able to select any part of your filters and have them filter the datatable.

For your first problem you are collecting Result when you should be collecting Process:

ClearCollect(collectProcess, {Process: "All"}); Collect(collectProcess, Choices([@'MySPlist'].Process));

Then for your datatable you should look at using something like this - you may need to modify this to the applicable dropdown, column and datasource names:

Filter( mydatasource, 'Created By'.DisplayName = User().DisplayName && ( dropdown1Name.Selected.Process = "All" || Process = dropdown1Name.Selected.xyz ) && ( dropdown2Name.Selected.Result = "-" || MonthY = dropdown2Name.Selected.Result ) )

If you could give that a try and let me know if that works and come back with any issues you encounter.

Cheers,

Sancho

Was this reply helpful? Yes No
KalvisT 131 on at

Like
a
(1)

Report
Copy link

Link copied!

Hei, thanks for the help

But It still doesnt show All withe the dropdown filter for process.
And the filter formula has problemas with
Proces = dropdown1.Selected.xyz // = sign is underlined red and .xyz.

Was this reply helpful? Yes No
iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Yes xyz should be Process (or whatever the value you get from Intellisense for that dropdown, which is why I said you will need to modify it)

Could you try changing that and let me know?

Thanks,

Sancho

Was this reply helpful? Yes No
KalvisT 131 on at

Like
a
(1)

Report
Copy link

Link copied!

So my collection looks like this :
ClearCollect(collectProssess, {Prossess: "All"});
Collect(collectProssess,Choices([@Kvalitet_2021].Prossess));
ClearCollect(collectMonth, {Result: "-"});
Collect(collectMonth,Distinct(Kvalitet_2021, MånedY));

And filter looks like this, problem is still with the = sign when i compare Prossess with the dd.prossess.selected.Prossess. Incompatible types for comparison (Record, Text)

Filter(Kvalitet_2021, 'Behandlet av'.DisplayName = varUser &&
(
    ddProssess.Selected.Prossess = "All"
    ||
    Prossess = ddProssess.Selected.Prossess
)
&&
    (ddMonth.Selected.Result = "-"
    ||
    MånedY = ddMonth.Selected.Result
    )
)

Was this reply helpful? Yes No
iAm_ManCat 18,256 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Ok,

I think it might be that you need to check whether any of the options have further intellisense options by typing . next to them, or else try using SelectedText if that is an option.

Filter( Kvalitet_2021, 'Behandlet av'.DisplayName = varUser && ( ddProssess.SelectedText.Value = "All" || Prossess = ddProssess.SelectedText.Value ) && ( ddMonth.Selected.Result = "-" || MånedY = ddMonth.Selected.Result ) )

Was this reply helpful? Yes No
KalvisT 131 on at

Like
a
(0)

Report
Copy link

Link copied!

Sadly I still can't get it to work. Not compatible values. The other 2 filters work fine. And nothing happens if I write .next. Would or could it be different if I'd hardcode the values from my choice column in SP List?

Was this reply helpful? Yes No
Verified answer

KalvisT 131 on at

Like
a
(0)

Report
Copy link

Link copied!

I found a solution

My onvisible for screen 3 is
ClearCollect(collectMonth,{Result:"-"});
Collect(collectMonth,Distinct(Kvalitet_2021,MånedY));
ClearCollect(Prossess, {Value: "All"}, Choices([@Kvalitet_2021].Prossess));

And filter is
Filter(
    Kvalitet_2021,
    'Behandlet av'.DisplayName = varUser && Substitute(
        ddProssess.SelectedText.Value,
        "All",
        ""
    ) in Prossess.Value,
    (ddMonth.SelectedText.Value = "-" || MånedY = ddMonth.SelectedText.Value)
)

Was this reply helpful? Yes No