filter a column by todays date

[d3ell]

Hi All,

I am having trouble with filtering a column in a sharpoint list by todays date.

My datasouce is: CustomerCareLog 

The column i want to filter a gallery by is: AppointmentDate

I have tried lots of things, but i either get errors saying that the column types dont match or i get a delegation warning.  I would like to achieve this without a delegation warning.

so far i've tried using the Today and IsToday function, they both return the delegation warning

Filter(CustomerCareLog,IsToday(AppointmentDate))

I've tried converting inserting a date picker and setting its default date to today and filtering by that, but i get a red error circle saying the expression 2019-03-16 eq AppointmentDate is not valid

Filter(CustomerCareLog,DatePicker1.SelectedDate=AppointmentDate)

I've tried ensuring the format of the date picker to match my sharepoint list, but that doesnt make a difference

I've tried creating a label with 'Today()' and tried referencing that and i get a warning that the data isnt the same type:

Filter(CustomerCareLog, AppointmentDate=LabelToday.Text)

With that in mid i tried to covert the AppointmentDate column to Text but  this gives me a delegation warning again...

Filter(CustomerCareLog, Text(AppointmentDate,"dd/mm/yyyy")=LabelToday.Text)

Any one got the solution, surely there is an easy way to do this without delegation warnings that i'm simply missing?

Help/Advise appreciated!

Thanks

[RandyHayes]

@d3ell 

It doesn't matter what tricks you might do to cast the dates in different ways, the bottom line is - Filter on SharePoint date columns are not delegable.  Please refer to the SharePoint connector reference for the information on Delegation.

If filtering by Dates is a requirement and delegation is truly an issue, then you will need to plan for that in your SharePoint list by having the dates in another column (or just one depending on your SharePoint needs) with either a Text value or a numeric value. These can all be delegated properly.

I hope that helps answer your question.

[d3ell]

Oh 😞

I see that sort and sortbycolumns is delegable, is there no way that I can use this to show items from today’s date?

Thanks

[RandyHayes]

@d3ell 

I suppose you could use the sort functions on the dates, but not entirely sure how that will get you to todays date.  Even if you then throw in a operator for = on the sorted dates, you're going to get a delegation warning.

I don't know your data source or how many records you are dealing with, but if the last set of records in your database is todays date records, then you could Collect the filter of the items with a sortbycolumns in descending order.  

   ex.  ClearCollect(logCollection, SortByColumns(CustomerCareLog, "AppointmentDate", Descending)

Then (again not knowing your data) conceivably, the first records in that collection will be todays date. 

Don't confuse delegation and record limit.  They are two different things.  

[d3ell]

Thanks for your input, unfortunately the Appointment date is mixed randomly in the column so dont think that would work.

I only have 200 record or so at the minute, but we add approx 5-10 records a day to the database so my calculation was that in a year we would hit the limit on delegation and i'm just trying to work around possible issues in teh future when all of a sudden this stops working when we hit the 2000 records limit i currently have set.

Thanks

[RandyHayes · Accepted solution]

@d3ell 

So, since you are starting this somewhat fresh, now is a perfect time to pre-shape your data for your app.

If filtering by Date is important - and since you can't delegate date filtering, then plan for it now.

One way is to create a text column in your list and each time an item is created, fill it in the with date in the YYYYMMDD form.  (you can either do this with Flow or with PowerApps - deoending on the interface you will use - If just PowerApps, then you can do it there.  If users can enter records directly in SharePoint, then use a flow).

Now that you have a column with a text formatted date, you can use the StartsWith on that column to filter by dates - without delegation issues.  So, if looking for todays date, you can Filter(yourList, StartsWith(yourTextDateColumn, "20190318"))

This will give you all the items with that date.  If you want to find all items for the month, you could do Filter(yourList, StartsWith(yourTextDateColumn, "201903")) etc.

The more you can shape your data ahead of filters, the better off you are for these type of actions.  

This also applies to ID columns in SharePoint (which are not delegable except for = ).

Little things like that help in the long run.  Knowing that delegation limitations chart is important.

I hope that helps some.

[d3ell]

Great advice, I shall give this a go!

When I add the extra column into my SharePoint list I will add this into PowerApps as another card which won’t be visible but will read from the date column and format the text into YYYYMMDD as you described I think this should be simple enough and then also setting up the filters to filter by this column.

Is there a quick way to get this date to auto generate on all the existing records or will I have to open each record individually (so that the new card can populate a value) and then save and do this on all records? Or is there a way to get all the existing records to do this!? Is there a way to use the patch function on all records?

Thanks again in advance

[RandyHayes]

@d3ell 

Depends on how many records you have.  

BTW - normally I would suggest a Calculated column to give you this exactly in SharePoint, but you can delegate a query on a calculated column 😞

Anyway...if you don't have a huge amount of records, I'd almost start with the Excel route.  Go into Quick edit mode in the list, highlight the column of dates and copy, then paste into excel, put a formula in the adjacent column to format in the YYYYMMDD format, then highlight all of those results and copy and paste back into the new column in the QuickEdit mode.

If there is more than makes that process easy, then you could consider a Flow to do it, or even put together a quick PowerApp to Update them all.

[d3ell]

Hi,

I have created a new column in Sharepoint called AppointmentDateCalc and i have set up a new card in Powerapps (that i will hide) which i want to change the date into the format you describe. - I have unlocked the card and removed from the default stting Parent.Default and put in:

Text(AppointmentDateValue,"YYYYMMDD")

Where appointment date is the datepicker.  When i do this though the text in the box isnt converting.  Any ideas.

for example where my datepicker is set to 12/03/2019, the value that powerapps is putting in this card with the above formula is: 3/12/2019.  - I have tried setting this as a number but if i do that, nothing gets inputted into the cell.

Not sure if it matters but the column i created in sharepoint was a 'Text' column.

thanks  

[RandyHayes]

@d3ell 

Your text format should be "yyyymmdd".

Sorry if I misled on the uppercase of that.  Flashback to other development worlds!  But in PowerApps - it's lowercase.