Vienna Power Apps and Power Automate User Group

Please login or click SIGN UP FOR FREE to create your PowerPlatformUG account to join this user group.
Expand all | Collapse all

Filter your SharePoint list items by creation date

  • 1.  Filter your SharePoint list items by creation date

    Bronze Contributor
    Posted Feb 27, 2020 12:02 PM
      |   view attached

    As you (hopefully) know, it is a good practice to load only as much data into your Flow as you need.

    Therefore, when you gather items e.g. from a SharePoint list, you will often want to use the odata filter to limit the number of items you are retrieving (you can also use a certain view, but unfortunately that only limits the number of columns received, but not the number of items).

     

    Yesterday one of my colleagues approached me because he had difficulties using the odata filter for a date query. He only wanted the items from his SharePoint list that were created on the same day.

    After some research and putting together the pieces of the puzzle I managed to provide him the solution, which looks like this:

    Created gt '@{addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),-1)}'

     

    This is the formula you need for getting the items created since yesterday. The cricital parts are the UTC time format and the single quotes around the function.

    Have fun with your filtered list of data in Flow!



    ------------------------------
    ----------------------------------------
    Gerhard Noll | Manager
    Modern Workplace
    Avanade Österreich GmbH
    Mail: g.noll@avanade.com
    www.avanade.at
    ---------------------------------------
    ------------------------------


  • 2.  RE: Filter your SharePoint list items by creation date

    Posted Mar 02, 2020 07:55 AM

    Just because it fits the topic:

    I also recently ran into the task of filtering records from a SharePoint list by a DateTime field in PowerApps. Doesn't sound bad when you hear it, so i just went ahead and wrote a formula like this:

    Filter(myAppointments, StartTime > Now())

    But unfortunately, SharePoint doesn't seem to be able to handle server-side filtering on DateTime fields since I always got a "delegation error". This basically means, that a pre-defined number (by default 500, extensible to 2000) of records is pulled into the PowerApp where it is then filtered (on client-side).
    This is not good, since having >2000 then "breaks" the whole filter.

    The solution: we made an additional column for the DateTime value where we didn't store the value as DateTime but as an integer. In PowerApps you can then also transform a timestamp like Now() into an integer by using the Value()-function. The new formula then looked like this:

    Filter(myAppointments, StartTimeInteger > Value(Now()))

    Since I was here comparing two numbers, the filter query could be executed on server-side.

    Small change, but a huge impact. Hope this might help anyone :)



    ------------------------------
    Christoph Gschliesser
    Product Manager
    BE-terna GmbH
    Innsbruck
    ------------------------------