PowerApps Exchange

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

How to display information in a gallery which belongs between two selected dates

  • 1.  How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 12 days ago

    Hi,

     

    I have a gallery where I want to display information relating to dates that I have chosen from DatePicker fields.

     

    Basically a To and From - 

     

    The data us held in SP and I have another filter which brings up a location.  So when that location is selected and the To and From datepicker fields have been selected just the information for that location and date range display



    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------


  • 2.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 12 days ago
    Hi,

    Try this:

    Gallery.Items = Filter(SharePointList, DateValue(SharePointList.Date) >= DatePicker1.SelectedDate && DateValue(SharePointList.Date) <= DatePicker2.SelectedDate)

    Notes
    DateValue() is used as redundancy, in case the sharepoint list column is not under Date format (e.g. text, number, etc.)
    && is the equivalent of AND(), || is the equivalent of OR()

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------



  • 3.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 12 days ago

    Hi 

    I've recreated your formula, changing to fit my field name and SP list

    Gallery1.AllItems = Filter('Perimeter Issues',DateValue('Perimeter Issues'.Date)>=DatePicker1.SelectedDate && DateValue('Perimeter Issues'.Date)<=DatePicker2.SelectedDate)

    For some reason it doesn't display [Items] for Gallery 1... and I get an error message 'The function 'DateValue' has some invalid arguments'
    Datepicker 1 and Date picker 2 are my names.  'Perimeter Issues' is my SP name and Date is my column header.




    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 4.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 11 days ago
    Hi Chris,

    Try removing DateValue() from the formula. I've seen the function confuse powerapps before when the column is properly formatted in SharePoint as date.

    If you can show a sample of your Date column data that would help with troubleshooting as well.

    Another option is to use DateValue(Text(<rest of your formula here>)), which will force powerapps to workaround the confusion.

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------



  • 5.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago
    Edited by Chris Clark 11 days ago
    Hi,

    Removed the DateValue() and get error msg 'Expected operator.  We expect an operator such as +,*,or & at this point in the formula.'

    Gallery1.AllItems = Filter('Perimeter Issues',>=DatePicker1.SelectedDate && <=DatePicker2.SelectedDate)

    Added in 'Text'
    Gallery1.AllItems = Filter('Perimeter Issues',DateValue(Text('Perimeter Issues'.Date)>=DatePicker1.SelectedDate && DateValue(Text('Perimeter Issues'.Date)<=DatePicker2.SelectedDate)))

    Error msg The function 'text' has some invalid arguments. & The function 'DateValue' has some invalid arguments


    Date column data in SP is format dd-mm-yyyy and single line of text.

    I have amended the format of the dates to display dd/mm/yyyy as they display in the DatePicker. in my SP list

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 6.  RE: How to display information in a gallery which belongs between two selected dates

    Bronze Contributor
    Posted 12 days ago
    Edited by Mark Pearson 12 days ago
    I do something like this a lot with a bookings app.  Here are my notes:

    1. If you are just looking at date alone,  beware date time fields as time will come into play and can cause records not be found even though you know they are there.  Time is compounded by Region as well. So use DateValue()
    2. The result you want needs to be >= Date From and <= Date To (so between two dates.) I set a variable date and do my find so Filter(Source, VarDate >= Sournce dateFrom And VarDate <=Source dateTo)
    3. Date are not delegable so restricted to 500 Server Side and up to 2000 on Client Side
    4. To overcome the delegation you can sort by ID Descending to be working on only newer records.  So Filter(Sort(Source,ID,Descending), VarDate >= Sournce dateFrom And VarDate <=Source dateTo)



    ------------------------------
    Mark Pearson
    National Operations Manager
    61409321547
    ------------------------------



  • 7.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago
    Hi Mark

    Here is my formula amended from your example

    Filter(Sort('Perimeter Issues', Building = Dropdown1.Selected.Result, Descending), PickerFrom >= DatePicker1.SelectedDate And PickerTo <=DatePicker2.SelectedDate)

    Blue squiggly line under  = sign.  Relating to delegation warning.  PickerFrom and PickerTo my variables which I've applied to the OnSelect  property for the respective DatePickers.

    No data being displayed?

    Where have i gone wrong?

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 8.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago
    Hi Mark / Zhi

    I think I have resolved my issue with a combination of both your answers.

    My formula looks like this

    Filter('Perimeter Issues', Building = Dropdown1.Selected.Result, PickerFrom >= DatePicker1.SelectedDate And PickerTo <=DatePicker2.SelectedDate)
    The DatePicker fields have a UpdateContext variable Pickerto & PickerFrom and it doesn't show any delegation issues

    Thanks for pointing me in the right direction

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 9.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago

    Think I may have been a bit to quick to say it was a success

     

    The formula does work Filter('Perimeter Issues', Building = Dropdown1.Selected.Result, PickerFrom >= DatePicker1.SelectedDate And PickerTo <=DatePicker2.SelectedDate)

     

    but not quiet what i need.  All the information from my SP list appears without me changing the dates from the DatePicker fields.  

     

    What can i do to resolve this?



    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 10.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 11 days ago
    Hi Chris,

    Looks like you confused the syntax for Filter().

    Try Filter('Perimeter Issues',Building = Dropdown1.Selected.Result, Date >= DatePicker1.SelectedDate && Date <=DatePicker2.SelectedDate)

    You should be on the right track when you type the first few letters of "Date" and PowerApps auto-complete suggests Date in the f(x) dropdown. You would have seen something similar when typing "Building".

    Your attempt:

    Gallery1.AllItems = Filter('Perimeter Issues',DateValue('Perimeter Issues'.Date)>=DatePicker1.SelectedDate && DateValue('Perimeter Issues'.Date)<=DatePicker2.SelectedDate)

    Probably ran into issues with PowerApps when you used 'Perimeter Issues'.Date instead of just Date. PowerApps is smart enough to know that you are attempting to filter results in the table 'Perimeter Issues' based on the first argument of Filter().

    Notes

    (from https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup) Filter( Table, Formula1 [, Formula2, ... ] )

    • Table - Required. Table to search.
    • Formula(s) - Required. The formula by which each record of the table is evaluated. The function returns all records that result in true. You can reference columns within the table. If you supply more than one formula, the results of all formulas are combined with the And function.


    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------



  • 11.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago
    Hi Zhi,

    I do appreciate the assistance you are providing, but i'm getting confused

    I'm using this formula
    Filter('Perimeter Issues', Building = Dropdown1.Selected.Result,Date >= DatePicker1.SelectedDate && Date <=DatePicker2.SelectedDate)

    I get a red line under Date and the message 'Invalid argument type. Expecting a Number value'

    When I start typing Date i get a number of options suggested in the f(x) dropdown.  For example DateValue, DateAdd, DateDiff....

    Do i need to add any additional fx to the DatePicker fields?

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 12.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 11 days ago
    Hi Chris,

    Check if PowerApps knows that there is a date column in this Sharepoint list.

    To do that, create a new gallery and key this into the Items f(x) = Filter('Perimeter Issues', (including the comma)

    Then scroll through the dropdown to see what PowerApps recognizes as column names in your SharePoint list ("Building" should appear as one of them, and "Date" is probably something else entirely, like "Title1" or "Subtitle1"). Once you figure out what PowerApps has labelled your date column, use that in the Filter formula.

    PowerApps sometimes doesn't seem to like it when column names, variables, etc. are named identical to its own functions. In this case, there seems to be some issue between your Date column and PowerApps Date() function.

    ------------------------------
    Zhi Rui Foo
    Operations Development Executive
    +65 6483 3335
    ------------------------------



  • 13.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 11 days ago
    Hi Zhi,

    I've done what you suggested and your were right,  Building did appear but also did Date.

    I'm using Date in other parts of my app with the ThisItem.Date (fx)

    Just don't understand why it's asking for a Number Value?  I've used the fx of is another screen which updates SP for date Text(Now(), "[$-en-US]dd/mm/yyyy")

    In my SP list under settings I hover over the name Date and see what the Url reads and at the end displays Field = Date

    Really confused now and the thing is it is probably something really obvious

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 14.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 9 days ago

    Hi Chris

    I have replicated what you are trying to do, with success.


    The filter I used is the same as yours:

    Filter('Perimeter Issues', Building = Dropdown1.Selected.Value, Date >= DatePicker1.SelectedDate && Date <= DatePicker2.SelectedDate)

    Please check that your field types for your date field in SharePoint is Date and Time.



    ------------------------------
    Kind regards,

    Vishal Mistry
    ------------------------------



  • 15.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 4 days ago
    Hi Vishal,

    Thanks for this.

    The only problem I get now is when I Patch my SP list from the Collection I get an error

    ForAll(Perimeterissue,Patch(
    'Perimeter Issues',Defaults('Perimeter Issues'),
    {
    Area: Title,
    Hint: Hint,
    Building: Building,
    Issue: Issue,
    Comments: Comments,
    'Checked By': CheckedBy,
    Date: Date,
    Time: Time
    }))

    Error message The type of this argument 'Date' does not match the expected 'Date'.found type 'Text'.
    The function 'Patch' has some invalid arguments.

    Any idea how to resolve?


    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------



  • 16.  RE: How to display information in a gallery which belongs between two selected dates

    Posted 4 days ago

    Hi Chris,

    Try using DateTimeValue(Date) like this, you can also have a look at this article: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/show-text-dates-times

    ForAll(Perimeterissue,Patch(
    'Perimeter Issues',Defaults('Perimeter Issues'),
    {
    Area: Title,
    Hint: Hint,
    Building: Building,
    Issue: Issue,
    Comments: Comments,
    'Checked By': CheckedBy,
    Date: DateTimeValue(Date),
    Time: Time
    }))


    ------------------------------
    Kind regards,

    Vishal Mistry
    ------------------------------



  • 17.  RE: How to display information in a gallery which belongs between two selected dates

    Gold Contributor
    Posted 4 days ago
    Hi Vishal,

    Thanks. I'll try this and let you know

    ------------------------------
    Chris Clark
    Test Analyst
    ------------------------------