cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tagustin2020
Impactful Individual
Impactful Individual

Filter items out of gallery that are over 1 year old

@RandyHayes 

 

Hello Randy,

 

Our Shipping Request gallery is going to grow rapidly. Even if they only get 5 requests per day that is roughly 1,200 requests per year. The only non-delegable formula in my Items property at the moment is the Search formula which has an "in" operator. I set the data row limit to 1000 items for now and will keep an eye on our gallery count. I asked the Logistics Team how far back they need to be able to view records in the gallery. They said 1 year would be enough. Can you help me tweak the formula below to filter out any items that are more than 1 year old from "Today's" date?  Thank you, Teresa

 

 

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests',
            txtSearchBox.Text in Company,
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email),
            (!ckAssigned.Value || 'Logistics Staff Member'.Email=varUser.Email),
            'Request Date' >=dpFromDate.SelectedDate && 'Request Date' <=dpToDate.SelectedDate
        ),
        "requestors", With({lName: Split('Requestor Name'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "logistics", If(IsBlank('Logistics Staff Member'.DisplayName), 
        "pending", With({lName: Split('Shipped By'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & ".")),
        "stat", Status.Value,
        "method", 'Shipping Method'.Value
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                If(CountRows(lclFilter.requestors) > 0, requestors in lclFilter.requestors, true) &&
                If(CountRows(lclFilter.logistics) > 0, logistics in lclFilter.logistics, true)
            ),
            Coalesce(locSortColumn,"Title"),
           If(Coalesce(locSortAscending,true),Ascending,Descending)
        ),
        "requestors", 
        "logistics"
    )
)

 

  

20 REPLIES 20

@WarrenBelz 

@TheRobRush 

 

Hi Warren,

 

The reason that I did not bother to copy/paste the formula in the last post was that the entire formula was underlined in red so unlike my last screenshot post it was a bit harder to tell where the break was. I think I am good to go now as I changed my Search formula to StartsWith which made it delegable. The following formula is working. The Logistics Team needs to retain Request Forms for several years so I will be writing flows to write Power Apps data to Word > PDF and flows to archive anything older than 1 year. Thanks again both of you (and Randy Hayes) for your help with this formula. You're all awesome 😊 Teresa

With({galItems:
    AddColumns(
        Filter('Domestic Shipping Requests', StartsWith(Company,txtSearchBox.Text),
            'Request Date'>DateAdd(Now(),-1,Years),
            (!ckMine.Value || 'Requestor Name'.Email=varUser.Email),
            (!ckAssigned.Value || 'Logistics Staff Member'.Email=varUser.Email),
            'Request Date' >=dpFromDate.SelectedDate && 'Request Date' <=dpToDate.SelectedDate
        ),
        "requestors", With({lName: Split('Requestor Name'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(lName).Result, 1) & "."),
        "logistics", If(IsBlank('Logistics Staff Member'.DisplayName), 
        "pending", With({lName: Split('Logistics Staff Member'.DisplayName, " ")},
                    First(lName).Result & " " & Left(Last(FirstN(lName, 2)).Result, 1) & ".")),
        "stat", Status.Value,
        "method", 'Shipping Method'.Value
    )},
    
    DropColumns(
        SortByColumns(
            Filter(galItems,
                (IsBlank(lclFilter.stats) || "All" in lclFilter.stats || Status.Value in lclFilter.stats.Value) &&
                If(CountRows(lclFilter.requestors) > 0, requestors in lclFilter.requestors, true) &&
                If(CountRows(lclFilter.logistics) > 0, logistics in lclFilter.logistics, true)
            ),
            Coalesce(locSortColumn,"ReqNumber"),
           If(Coalesce(locSortAscending,false),Descending,Ascending)
        ),
        "requestors", 
        "logistics"
    )
)

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,544)