cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Filter Default view

Hello,

 

I recently tried to implement archive in my app using status field onSuccess form - it worked correctly - after changing the status it was copying a record from one sharepoint list to another and then deleting it.

I thought that this is efficient but it has a serious flow - attachments and server generated fields cannot be copied so such a solution was unacceptable for me (I know that I can use custom fields and copy author + created field to them, but attachments without a complex flow won't be achieved).

 

So I gave up on that and thought that I should change my gallery view and status dropdown filter to "hide" propositions with certain status, and show them on a different gallery. This way I could create archive.

 

With some help of people I created o good dropdown filter with Item properties:

 

Filter(
    Choices([@IdeaBox_1].Status),
    !(Value in ["Zrealizowana","Odrzucona"])
)

This shows all possible status field except Rejected/Done.

This is a first step to make a gallery filter well since I wanna hide Rejected/Done status by default.

 

The harder part is this huge filter on gallery:

Sort(Filter(Search(AddColumns(IdeaBox_1,"AuthorName",'Utworzone przez'.DisplayName,"CategoryCombo",Status.Value,"WhoChanged",'Zmodyfikowane przez'.DisplayName),SearchBox_1.Text,"Nazwa","Opis","AuthorName","WhoChanged"),

(If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,1)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,1)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,2)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,2)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,3)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,3)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,4)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,4)).Value in Status.Value))

&&(Utworzony) >= DatePicker1_2.SelectedDate && (Utworzony) <= DatePicker2_2.SelectedDate +1), Utworzony, If(SortDescending2,Descending,Ascending))

 

This dropdown (ComboBox2_1) works like that "include ||" I can select up to 4 choices that should be visible in this gallery - the one that are not hidden).

I also have a DatePicker that can filter through two dates, and an input field that filter through a lot of data.

And on top of that a button with ascending descending created date.

 

Is there a way add another filter that would hide all records that has a value in combobox "Rejected" and "Done".

Could anyone guide me where to add this in my function to not mess up with rest of filters?

 

I believe that this filter should have a highest priority and rest should be nested/accounted after this.

 

After I create this I will copy this function to add another gallery that would print only propositions with "Done" and "Rejected" and this way I will have fully functional archive and also I will save my app from 500-2000 limit since the records will be split in a great numbers.

 

Thank you in advance for your help.

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: Filter Default view

@Elitezone 

I am not entirely clear on your logic of "records that has a value in combobox "Rejected" and "Done"

However, if you are looking to have the filter for the choices in the combobox and to not include rejected and done, then consider changing your formula to the following:

Sort(
    Filter(
        Search(
            AddColumns(IdeaBox_1,
                "AuthorName", 'Utworzone przez'.DisplayName,
                "CategoryCombo", Status.Value,
                "WhoChanged", 'Zmodyfikowane przez'.DisplayName
            ),
            SearchBox_1.Text, "Nazwa", "Opis", "AuthorName", "WhoChanged"
        ),
        
        (CountRows(ComboBox2_1.SelectedItems)= 0 || CategoryCombo in ComboBox2_1.SelectedItems) &&
        !(CategoryCombo in "Rejected|Done") &&
        Utworzony >= DatePicker1_2.SelectedDate && 
        Utworzony <= (DatePicker2_2.SelectedDate +1)
     ), 
     Utworzony, 
     If(SortDescending2,Descending,Ascending)
 )

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter Default view

@RandyHayes 

It does not show any errors, but it does not show any records by default.

Seems like none record is matching the function you provided.

Highlighted
Super User III
Super User III

Re: Filter Default view

@Elitezone 

Try to "walk" your formula back until you find the offending issue.

First - see if the dates are an issue:

Sort(
    Filter(
        Search(
            AddColumns(IdeaBox_1,
                "AuthorName", 'Utworzone przez'.DisplayName,
                "CategoryCombo", Status.Value,
                "WhoChanged", 'Zmodyfikowane przez'.DisplayName
            ),
            SearchBox_1.Text, "Nazwa", "Opis", "AuthorName", "WhoChanged"
        ),
        
        (CountRows(ComboBox2_1.SelectedItems)= 0 || CategoryCombo in ComboBox2_1.SelectedItems) &&
        !(CategoryCombo in "Rejected|Done") 
     ), 
     Utworzony, 
     If(SortDescending2,Descending,Ascending)
 )

 

Still an issue?  See if the status check is the issue:

Sort(
    Filter(
        Search(
            AddColumns(IdeaBox_1,
                "AuthorName", 'Utworzone przez'.DisplayName,
                "CategoryCombo", Status.Value,
                "WhoChanged", 'Zmodyfikowane przez'.DisplayName
            ),
            SearchBox_1.Text, "Nazwa", "Opis", "AuthorName", "WhoChanged"
        ),
        
        (CountRows(ComboBox2_1.SelectedItems)= 0 || CategoryCombo in ComboBox2_1.SelectedItems) &&
        Utworzony >= DatePicker1_2.SelectedDate && 
        Utworzony <= (DatePicker2_2.SelectedDate +1)
     ), 
     Utworzony, 
     If(SortDescending2,Descending,Ascending)
 )

 

Still nothing?  See if the combobox is the issue:

Sort(
    Filter(
        Search(
            AddColumns(IdeaBox_1,
                "AuthorName", 'Utworzone przez'.DisplayName,
                "CategoryCombo", Status.Value,
                "WhoChanged", 'Zmodyfikowane przez'.DisplayName
            ),
            SearchBox_1.Text, "Nazwa", "Opis", "AuthorName", "WhoChanged"
        ),
        
        !(CategoryCombo in "Rejected|Done") &&
        Utworzony >= DatePicker1_2.SelectedDate && 
        Utworzony <= (DatePicker2_2.SelectedDate +1)
     ), 
     Utworzony, 
     If(SortDescending2,Descending,Ascending)
 )

 

Depending on which formula produces results will determine what to explore next.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter Default view

@RandyHayes 

The last one worked and I check that it sucesfully filter records that has Status "Rejected" or "Done".

 

All filters works as well except Status one that was handled by combobox Status, and this function:

(If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,1)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,1)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,2)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,2)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,3)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,3)).Value in Status.Value)||
If(IsBlank(Last(FirstN(ComboBox2_1.SelectedItems.Value,4)).Value),true, Last(FirstN(ComboBox2_1.SelectedItems.Value,4)).Value in Status.Value))

but this was deleted from the function.

This filter to not show Done/Rejected is linked with this ComboBox, as this combo box has items:

Filter(
    Choices([@IdeaBox_1].Status),
    !(Value in ["Rejected","Done"])
)

Because I do not want to allow users to filter through data that is filtered out of this screen.

Rejected and Done records are placed in a different gallery and in that gallery combobox is not filtered.

Highlighted
Super User III
Super User III

Re: Filter Default view

@Elitezone 

Actually, your original formula did not need all of those If statements - it was replaced by this part of the formula:

(CountRows(ComboBox2_1.SelectedItems)= 0 || CategoryCombo in ComboBox2_1.SelectedItems)

 

So, where are you now?  Are you still encountering the problems?  What are you seeing/not seeing and need help with?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter Default view

But that of function is missing in the function provided:

Sort(
    Filter(
        Search(
            AddColumns(IdeaBox_1,
                "AuthorName", 'Utworzone przez'.DisplayName,
                "CategoryCombo", Status.Value,
                "WhoChanged", 'Zmodyfikowane przez'.DisplayName
            ),
            SearchBox_1.Text, "Nazwa", "Opis", "AuthorName", "WhoChanged"
        ),
        
        !(CategoryCombo in "Odrzucona|Zrealizowana") &&
        Utworzony >= DatePicker1_2.SelectedDate && 
        Utworzony <= (DatePicker2_2.SelectedDate +1)
     ), 
     Utworzony, 
     If(SortDescending2,Descending,Ascending)
 )

 

This function does not mention ComboBox2_1

Where to put it?

 

Right now all works except ComboBox2_1

Highlighted
Super User III
Super User III

Re: Filter Default view

@Elitezone 

I'm a little confused - are you stating that the original formula I provided you did not take into account ComboBox2_1?  Because it did have it in there.  Or are you stating that during your troubleshooting, that now that part is not working (and thus removed) in your current formula?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: Filter Default view

@RandyHayes 

Randy you told me to go part by part and strip my formula.

Only last formula worked, and it didn't include ComboBox2_1. So the ComboBox was the issue.

 

Functions that included it showed gallery always with 0 results and so I used only the last one.

Highlighted
Super User III
Super User III

Re: Filter Default view

@Elitezone

Yes Yes...I understand now.  I just wanted to clarify to make sure we were on the same page.

 

So, let's pick up from there.  Using the original formula I provided, if the ComboBox2_1 has nothing selected, you get no records?

If the combobox is empty (nothing selected), then technically it is equivalent to the last formula of the "testing" formulas.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

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 designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,666)