cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Wierd problem in Gallery

Hi friends,

 

I have a list of less than 4000 items on SharePoint Online.

 

image.png

 

 

 

 

 

 

 

I need to show today's items so I use IsToday function: Filter('Visit Records',IsToday(ServiceDate))

It does not shows anything and says "Delegation warning"

So I tried using LastN and also FirstN but the output is limited to a short priod of time: SortByColumns(LastN('Visit Records',10000),"ServiceDate",Descending)

 

image.png

 

 

 

 

 

 

 

And I have tried complete syntax, but it returned an empty gallery: Filter(LastN('Visit Records',10000),IsToday('Visit Date'))

Also increasing the row limit to maximum which is 2000 and no success.

Please let me know if there's any solution.

Thank you,

Faramarz

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Wierd problem in Gallery

@MikeLockwood @farsami 

If you can avoid the local collections...you are much better off.  There are some issues with keeping things "connected" properly that you will need to provide formulas to do throughout your App.

So, the reality is (as discussed in other posts about delegation), you really want your datasource to give you the results you need.  This is not possible with SharePoint for many reasons.  In fact, it's not possible with SQL either, but in SQL you can create views and functions that will assist with it...in SharePoint, you cannot.  BUT, you can take steps in "preparing" your SharePoint list for PowerApps so that you can take advantage of those things which CAN be delegted.  For date, this is quite simpe to do.

You might take a look at this recent post in which this exact subject was discussed.

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

View solution in original post

13 REPLIES 13
Highlighted
Super User
Super User

Re: Wierd problem in Gallery

Hi Faramarz

Unfortunately, SharePoint is limited in terms of executing delegated queries against date/datetime fields.

With SharePoint, the equals operator is delegable. Therefore, one workaround you can try is to store todays date in a variable, say in the OnStart property of your app.

Set(TodaysDate, Today())

You can then try the following formula in the Items property of your Gallery to see if that resolves the problem.

Filter('Visit Records', ServiceDate = TodaysDate)

 

Highlighted
Super User
Super User

Re: Wierd problem in Gallery

@timl  I'm not sure that the equality operator is delegable for a DateTime column.  Can you verify that?  I've noticed it does not produce a delegation warning on the Formula, but I don't know off hand if it actually delegates. 

The Connector Reference for SharePoint says "No can do"...but I am curious if there is a discrepancy.

_____________________________________________________________________________________
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.
Highlighted
Helper II
Helper II

Re: Wierd problem in Gallery

Thank you for you propmpt response.

I try that but I get the following error:

image.png

 

How can I change the format of a date?

 

Highlighted
Helper II
Helper II

Re: Wierd problem in Gallery

Maybe that's the reason I got the error.

Highlighted
Super User
Super User

Re: Wierd problem in Gallery

I usually just use : Filter(DataSource, DateField=Today())

 

Yes...it gives the Delegation warning but I have not found it to be a problem. I have some that Set the Variable...still does it. 

 

If you do want to go the Set Variable option to make it work you have to format them both to match...

 

Filter(DataSource, Text(DateField,"dd/mm/yyy")=Text(_CurrentDate,"dd/mm/yyyy"))

 

The only reason I ever use the Set option is if I have a date picker...but that is just my personal preferance.  

 

You will still get the delegation warning and will pull the same data. I haven't found a way to do it that doesn't show a delegation warning and I also haven't found one that it actually mattered...it still pulls in my data just fine.

Super User
Super User

Re: Wierd problem in Gallery

@farsami @RandyHayes 

Sorry guys, I completely forgot that with SharePoint, the = operator doesn't work against date fields 😞 You end up getting the error that you see.

I don't think there's any simple workaround for this bug at the moment.

 

 

Highlighted
Helper II
Helper II

Re: Wierd problem in Gallery

Thanks you for your support.

I believe there is another problem with FirstN and LastN as I said in my first post, can you explain that for me.

 

Highlighted
Helper II
Helper II

Re: Wierd problem in Gallery

As I said I try that but it return nothing for me.

Highlighted
Advocate III
Advocate III

Re: Wierd problem in Gallery

I have found that to have greater flexibility in my criteria against data in a sharepoint list I collect it into a local collection to start with then from their can apply the filters.  I can see this would have some limitations as the data could quickly get out of sync but so far this has been working.

Thanks,
Mike L.

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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