Operators in a Switch Statement Used for Sorting/Filtering a Gallery

[kristinegriba]

I have a gallery based on a Sharepoint list, that I am sorting and filtering with a switch statement based on radio button selection, however, I now want to add a second level of filtering to one of the switch statement options. 

Here is the switch statement in its current form, which is working quite well:

Switch(scrGalReviewStatus_Radio.Selected.Value,

"Yes", Sort(Filter('LIST Ready For Review', LISTcol_Reviewed = "Yes"), LISTcol_reviewedDS, Descending),

"Clear Notes",Filter('LIST Ready For Review', LISTcol_Reviewed = "Clear Notes"),

Filter('LIST Ready For Review',LISTcol_Reviewed = "No"))

Here's what I'd like to add to the "Yes" option: that the gallery only include items not more than 30 days old since an item's submission date. I want to try something like this, but I'm not able to get anything to work (red text represents the second level for filtering in the "Yes" option only):

Switch(scrGalReviewStatus_Radio.Selected.Value,

"Yes", Sort(Filter('LIST Ready For Review', LISTcol_Reviewed = "Yes" && (Today() - LISTcol_ReviewedDS) < 30), LISTcol_reviewedDS, Descending),

"Clear Notes",Filter('LIST Ready For Review', LISTcol_Reviewed = "Clear Notes"),

Filter('LIST Ready For Review',LISTcol_Reviewed = "No"))

"LISTcol_ReviewedDS" = is the column in my Sharepoint list that represents the date an item was submitted... it's like a date stamp. Once it's more than 30 days old, that item is not to be included in the gallery anymore.

Hopefully, I have given enough information for someone to understand what I am trying to accomplish... and many thanks in advance!

Kristine

[TheRobRush]

I think to get difference between the two dates you will need to look at DateDiff

[kristinegriba]

Thanks for your reply... so something along the lines of:

"Yes", Sort(Filter('LIST Ready For Review', LISTcol_Reviewed = "Yes" && DateDiff(Today(), LISTcol_ReviewedDS), LISTcol_reviewedDS, Descending),

Is that in the ballpark for me to start trying?

Thanks again...

[TheRobRush]

I will give you an example using my own datasource, so you will need to change the source names columns variables etc. But this should give you an idea. This code will return for me only employees on my roster whose name is not blank AND who were created in the last 60 days

Sort(
     Filter(
        /////MY DATA SOURCE Local Roster
        LocalRoster,
        And(
            ////FIRST CHECK THAT TITLE IS NOT BLANK
            IsBlank(Title)=false,
            ////VERIFY ITS CREATED COLUMN IS FROM WITHIN PAST 60 DAYS
            DateDiff(Created,Today(),Days)<60)),
            ////SORT BY TITLE
            Title,
            ////SORT THAT A TO Z
            Ascending)

[kristinegriba]

Okay, that gives me an idea, thank you, I think I will need to tweak mine a bit still as I'm getting a delegation warning... the double blue

Image looks fuzzy on my screen, so here's the code copy-pasted:

"Yes", Sort(Filter('LIST Ready For Review', LISTcol_Reviewed = "Yes" && DateDiff(Today(),LISTcol_reviewedDS,Days)<30),LISTcol_reviewedDS,Descending),

I see you used "And" ... is there any difference to my having tried "&&"?

[TheRobRush · Accepted solution]

I see you used "And" ... is there any difference to my having tried "&&" - No just how I write them

Unless there has been an update recently I do not believe DateDiff (or almost anything on a date column for that matter) is delegable via sharepoint so you will always get a delegation warning when working on the creation side, it won't show up for user though. If your datasource is going to go over the 2k max records you will need to trim down and likely put target records in a local collection first, then have your gallery display the filter based on DateDiff on the local collection instead of source data

[TheRobRush]

Another way around the Date delegation issues would be to make a column that is not date /technically/ but can be used as such. See @RandyHayes post on this here

[kristinegriba]

Awesome, sir!! Many thank yous!! And I will definitely keep in mind your idea with regards to a local collection as a workaround should my record count get particularly large.

[kristinegriba]

Well, that's very interesting ... I will definitely bookmark that post from Randy Hayes. I had a niggly feeling that I was trying to ignore, that I'd be bumping up against something like that. Thanks again for your help! 😁