cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

16 REPLIES 16
Super User III
Super User III

@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.
Check out my PowerApps Videos too!

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

@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.
Check out my PowerApps Videos too!

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

@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.
Check out my PowerApps Videos too!

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

@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.
Check out my PowerApps Videos too!

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

@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.
Check out my PowerApps Videos too!

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (6,381)