Filter items out of gallery that are over 1 year old

[tagustin2020]

@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"
    )
)

[WarrenBelz]

Hi @tagustin2020 ,

Our father's day is in September, but it is nice to have two (yes I am a father and so are two of my sons).

You have also marked this post as solved - is this the case?

[tagustin2020]

@WarrenBelz 

Hi Warren,

Apologies for the delay in response. I know I marked this post complete and and for the most part it is, but I am interested in making it more bulletproof in terms of delegation which is why I am interested in your suggestion. I was busy with other work tasks last week and haven't been able to go through your formula edit suggestion yet to figure out why it is not yet working on my end. As you mention it is probably just a very minor syntax issue.

Teresa

Teresa

[WarrenBelz]

Hi @tagustin2020 ,

I used a lot of your existing formula in it, so you need to check that as well, but the main change is at the start in the With() statement using the newest 2,000 records making the rest not subject to Delegation as you are not querying the data source from that point on.

[tagustin2020]

@WarrenBelz 

Hello Warren,

Apologies for the delay. I finally got a chance to focus on this lengthy bit of code. I really appreciate you helping me. with it. Here is a portion of the formula. I can't spot the break in the first line. Can you? Hopefully, if this gets ironed out, the rest will fall into place.

Here is the full formula.

With({galItems:Filter(Sort('Domestic Shipping Requests',ID,Descending))}),
DropColumns(
    AddColumns(
        Filter(galItems,
        (IsBlank(txtSearchBox.Text) || txtSearchBox.Text in Company) && 
            '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 &&
        ((IsBlank(lclFilter.stats) || "All" in lclFilter.stats) || Status.Value in lclFilter.stats.Value) && CountRows(lclFilter.requestors)=0 || requestors in lclFilter.requestors) && (CountRows(lclFilter.logistics) = 0 || logistics in lclFilter.logistics)),
        "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),"requestors","logistics"),
            Coalesce(locSortColumn,"ReqNumber"),
           If(Coalesce(locSortAscending,true),Descending,Ascending)
        )
)

Kind regards,

Teresa

[WarrenBelz]

Hi @tagustin2020 ,

You have a Filter in the top with no criteria - I think you are trying to do just this

With(
   {
      galItems:
      Sort(
         'Domestic Shipping Requests',
         ID,
         Descending
      )
   },

The rest is highly complex and you really need to get all the brackets right to achieve the logic you are after.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[tagustin2020]

@WarrenBelz 

Hi Warren,

Thanks for the advice. I am going to have to set this aside until I can run it past Randy. He helped write the original formula. Like you said, it is complex and I'm a beginner so I'm not able to figure out on my own what is wrong. Fortunately, the Items formula for my gallery is working, its just not perfected in terms of delegation yet. It will take a little while to hit 2,000 entries so I have a little time to figure it out. You did help me evolve the formula by filtering out items older than 1 year and getting things to sort so the latest request is on top. Those are both great additions so thanks very much for helping me get that far.  I really appreciate you!

Teresa

[TheRobRush]

You might consider an archive via flow function. I have an evaluation app that takes in about 10 or so evaluations of sellers per day. Every night it moves the daily evaluations to a identical layout splist that holds the last 60 days of evaluations, and after that i move everything older than 60 days to a third identical splist. This way I am only delegating large groups of data when I get into very specific archival searches. This is rare as my users tend to only need last 60 days of data or so. If your team would have less than 2,000 items in the past year you could do this with only two lists, one for active, and one for items created outside the past 365 days. Cuts need to delegate down by only ever keeping the smaller list in focus unless a specific delve into archived records is needed. Ofcourse if your records go over 2k a year and you always need to see past year then will be best to get that delegation all sorted out.

As for your delegation issue,  curious, which specific items is it telling you are not delegable? If you click the yellow triangle, it should tell you. In original post you said it was the "In", I don't think that "in" is delegable with sharepoint(if you are using cds ignore me), I have tried it on a small list of my own just to double check and have the same experience. StartsWith, however, would be delegable.

[tagustin2020]

@TheRobRush 

Hi Rob,

Thanks for the note. I do plan to archive, but the team told me they need to retain a year's worth of requests in the app. The reason for my concern is that they weren't able to tell me how many requests they handle per day so I'm erring on the side of being conservative. We will probably be okay as they only work Mon-Fri and if they handle 10 requests per day we will fall under 2,000 requests per year. The only delegation warning appears to be the "in" operator (the data source is Sharepoint). When I tried using "Starts With" in my last app, users had trouble as they couldn't always remember what they named their request so "in" was easier for them. It might not be as much of an issue with a Shipping app where they are searching for companies by name so I suppose I could try it an see if anyone even notices. Thanks for helping me think it through. Teresa

[TheRobRush]

Yah I can see how it would be easier, however unless I am mistaken you can not delegate IN in sharepoint, so it will not be useful to your team once they pass 2,000 items. 

10 requests Monday through Friday for an entire year would add up to between 2,600 and 2,620 records depending on the year and how many weekdays fall into the calendar year. So I would definitely recommend finding an alternative to the "IN"

[WarrenBelz]

Hi @tagustin2020 ,

If you use Format Text and post in a Text box (like my post), it is much easier to look at and understand. You also did not say what exactly was not working.