cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jmaverik
Level: Powered On

Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date picker

Hello, 

 

I have a gallery that I'm trying to filter a date range and limit to the user's ID.  

Right now, I have two datepickers and a text box that defaults to the user's ID (Office365Users.MyProfile().MailNickname).  

 

I used the following formula to filter it: 

Filter(UnplannedPercent, DateValue(Text(StartDate), "en-us") >= DateValue (Text(DPStart.SelectedDate), "en-us") && DateValue(Text(EndDate), "en-us") <= DateValue(Text(DPEnd.SelectedDate), "en-us"), StartsWith(WindowsID, TimeOff1.Text))

 

It works, but won't include any data after the 2000 line limit.  

 

I'm getting a delegation warning on the DateValue, >= and && functions.  Is there any way to make this work with a SharePoint list?  Worst case scenario, I would like to at least sort by a single date and the user's ID.  

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

Hi @Jmaverik,

if your list is <4000 items you can create a collection using the technique described here:

https://powerusers.microsoft.com/t5/General-Discussion/Collections-maxes-out-at-2000-when-populating.... Then all functions will work without delegation errors.

8 REPLIES 8
Super User
Super User

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

Hi @Jmaverik,

if your list is <4000 items you can create a collection using the technique described here:

https://powerusers.microsoft.com/t5/General-Discussion/Collections-maxes-out-at-2000-when-populating.... Then all functions will work without delegation errors.

KroonOfficeSol
Level 10

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

@Jmaverik

 

You could try to do this workaround.

 

First add a calculated on other field column to your SharePoint list. Make this a number field and add this formula:

=Year(YourDateField) & RIGHT("00" & MONTH(YourDateField);2) & RIGHT("00" & DAY(YourDateField);2)

Now try to filter your datasource based on a number. First put just the number in the filter() formula to check if the delegation warning disappaers. If so then replace the numbers by the output off the calender control. The formula your use should be something like this:

Value(Text(CalenderControl.SelectedDate, "yymmdd"))

Matching values with values should be possible for the connection.

 

Didn't try this out so I hope this works.

 

Paul

 

Jmaverik
Level: Powered On

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

This sounds interesting.  If I wanted to use a date range, would I have to have two calculated fields?  A Start and End date field?  

 

I'm having an issue with the calculated field.  I keep getting a Syntax error.  If I remove the ;2 from the formula, it doesn't error out, but gives me a #VALUE! error.  Here is the formula I'm using: 

 

=Year(StartDate) & RIGHT("00" & MONTH(StartDate);2) & RIGHT("00" & DAY(StartDate);2)

 

Also tried:

=Year([StartDate]) & RIGHT("00" & MONTH([StartDate]);2) & RIGHT("00" & DAY([StartDate]);2)

KroonOfficeSol
Level 10

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

Did you go to new column - more and picked the right calculated column option?

If yes try , instead off ; in the right function and also ' instead off "

Still doesn't work first do month part, then add "00" &, and then surround by right() formula. Just to see where it breaks and gives the error.

Paul
Jmaverik
Level: Powered On

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

After I changed the semicolon to a comma it took the syntax but continued to give me a Value error. (I included a screenshot) I tried changing the quotes to single quotes, but that didn't work and I couldn't get the Right function to work at all.  Should the formula start with =Text(Year(StartDate)....?  Or INT maybe?  Here are some of the things I tried: 

 

=Year(StartDate) & RIGHT(MONTH(StartDate),2) & RIGHT('00' & DAY(StartDate),2)   (Syntax Error)

=Date(Year(StartDate), RIGHT(MONTH(StartDate),2), RIGHT("00" & DAY(StartDate),2))   (Syntax Error)

=(Year([StartDate]) & RIGHT("00" & MONTH([StartDate]),2) & RIGHT("00" & DAY([StartDate]),2))  (Value Error)

=Concatenate(Year(StartDate), Right("00" & Month(StartDate), 2)  (Value Error)

=Month((StartDate),2)  (Value Error)

=Year(StartDate) & Month(StartDate)  (Value Error)  

 

There were several others.  I'll keep playing with it to see if I can get it to work.  If you see something I missed let me know.  Thanks.  

 

KroonOfficeSol
Level 10

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

@Jmaverik

 

The red error signs are under the fieldname you provide, so here lies the problem. Try to do the same with the EndDate field to see if this works. If not then erase all and start with simple Month(StartDate) and save this, to see if it works, then go back and add the right formula and save this to see if this works. It should do, so if not then check the fields you try to put in are they really date fields or something else. Else try to make an textline and only put in the fieldname and save this to see if you don't get an error.

 

Greatings Paul

Jmaverik
Level: Powered On

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

I wanted to say thanks for your help, but I'm not sure this is going to work.  I changed the name of the column to Start_Date, which removed the red lines, but I continued to get the syntax error.  I was able to get the formula to go through without a syntax error when I changed the column type from number to date, but it just returned a "1".  I also tried making it a DateValue to convert the date to a number, but that also returned a syntax error.  

 

When I used: 

=Month(Start_Date)  the column returned the correct number month, but whenever I tried to combine it with the Right function or the concatenate I kept getting the syntax error.  

 

I included screenshots of the calculated column and my date field in case you have any ideas.  

 

This is a really interesting idea, and I would love to see if it works, but I just don't know enough about SharePoint formulas to chase down the issue.  

KroonOfficeSol
Level 10

Re: Delegation issue with filtering a Gallery from SharePoint List - Filter text box and two date pi

@Jmaverik

 

Try this options

Right("12345", 2) // should return 45
Right("12345";2)
Right("12345";;2)
Right('12345',2)
Right('12345';2)

Maybe you have problems because off your region settings.

If this works add the other parts, Try & and +
Don't work, try to Text(Month(Start_date))

 

I did it the first time to try to give you an answer and I managed to get it done in just 5 minutes. I have basically no experience with SharePoint at all (made my first few list last week to try), so I would say you should get this working. If not yet then Google.

 

Paul

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 38 members 4,473 guests
Recent signins:
Please welcome our newest community members: