cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Consecutive date range into collection

So right now I have 2 date picker controls - a start date and an end date.

Because these date pickers are free form, the user can pick days that are one day apart or 30 days apart.

 

Is there a way to store each day in a collection?

 

For example, with start: 7/24/2017, end 7/30/2017, I want to have a collection with

DateRange = [7/24/2017, 7/25/2017, 7/26/2017, 7/27/2017, 7/28/2017, 7/29/2017, 7/30/2017]

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Consecutive date range into collection

Hi @mchien and @kalpana

 

Here is how you can put all dates within a range into a collection:

 

Have a screen with two date pickers, button and gallery (to show the date range collection)

1.png

 

 

Set the Button.OnSelect to this:

ClearCollect(
    DateRange,
    AddColumns(
        FirstN(
            [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
            DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days) + 1),
        "Day",
            "Day " & (Value + 1),
        "Date",
            DateAdd(DatePicker1.SelectedDate, Value, Days)
    )
)

This function will create a collection with three columns Value (from 0 to 29, it is used to produce Day and Date column), Day (basically just Day X - where X is the number of the day in the collection), Date (the actual date). It will include all dates between DatePicker1 and DatePicker2.

 

Limitations: This function can maximum have range of 30 days (Because of the FirstN() function), if you want to allow bigger ranges add more numbers after 29. It only works if DatePicker1 has date which is equal or smaller than DatePicker2. You can add more logic to it to solve this issue.

 

in Gallery.Item put the following function:

DateRange

 

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: Consecutive date range into collection

Hi mchien,

 

I have a workaround for your scenario, hope it could be a reference for you.


Add two Date picker controls on the same screen, say Date picker1 and Date picker2.


Add a Label control above Date picker2.


Set OnSelect property of Date picker2 as:

If(DatePicker2.SelectedDate<DatePicker1.SelectedDate,UpdateContext({text:"wrong selection"}),DatePicker2.SelectedDate>DateAdd(DatePicker1.SelectedDate,7),UpdateContext({text:"wrong selection"}),DatePicker2.SelectedDate>=DatePicker1.SelectedDate&&DatePicker2.SelectedDate<=DateAdd(DatePicker1.SelectedDate,7),UpdateContext({text:""}))


Then set the Text property of the label as text.

 

When a date out of the range is selected, an error message would be shown in the label.

3.PNG


Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate I
Advocate I

Re: Consecutive date range into collection

Thanks for the suggestion, but i'm looking to do a ForEach for each of the days within range, so your suggestion isn't applicable in my use case.

 

Highlighted
Frequent Visitor

Re: Consecutive date range into collection

Hi,

 

I am also facing the similar kind of issue. I need a range of date in collection. I have From date and To date, If anyone has the solution, please help.

Highlighted
Anonymous
Not applicable

Re: Consecutive date range into collection

Hi @mchien and @kalpana

 

Here is how you can put all dates within a range into a collection:

 

Have a screen with two date pickers, button and gallery (to show the date range collection)

1.png

 

 

Set the Button.OnSelect to this:

ClearCollect(
    DateRange,
    AddColumns(
        FirstN(
            [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
            DateDiff(DatePicker1.SelectedDate, DatePicker2.SelectedDate, Days) + 1),
        "Day",
            "Day " & (Value + 1),
        "Date",
            DateAdd(DatePicker1.SelectedDate, Value, Days)
    )
)

This function will create a collection with three columns Value (from 0 to 29, it is used to produce Day and Date column), Day (basically just Day X - where X is the number of the day in the collection), Date (the actual date). It will include all dates between DatePicker1 and DatePicker2.

 

Limitations: This function can maximum have range of 30 days (Because of the FirstN() function), if you want to allow bigger ranges add more numbers after 29. It only works if DatePicker1 has date which is equal or smaller than DatePicker2. You can add more logic to it to solve this issue.

 

in Gallery.Item put the following function:

DateRange

 

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Consecutive date range into collection

Dude, you are awesome. I could be late in responding, but your code works like a charm. I tried the following keeping a SharePoint list as datasource, worked like a charm.

 

Clear(DateRange3Collection);
ForAll(
    SharePointList,
    Collect(
        DateRange3Collection,
        AddColumns(
            FirstN(
                [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29],
                DateDiff(StartDateCol, EndDateCol, Days) + 1),
            "Day",
                "Day " & (Value + 1),
            "Date",
                DateAdd(StartDateCol, Value, Days)
        )
    )
)

Thank you once again.

Highlighted
Post Patron
Post Patron

Re: Consecutive date range into collection

I tweaked it to make it go more than 29 days and it seems to be working. 

ClearCollect(
    DateRange,
    AddColumns(
        FirstN(
            [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32],
            DateDiff('Single Bill Start'.SelectedDate, 'Single Bill End'.SelectedDate, Days) + 1),
        "Day",
            "Day " & (Value + 1),
        "Date",
            DateAdd('Single Bill Start'.SelectedDate, Value, Days)
    )
)

I don't understand the original concern of 29 limitation of FirstN()?

Highlighted
Frequent Visitor

Re: Consecutive date range into collection

I wanted to know about how the value variable comes here. I cannot see it anywhere else and what it is ?

Highlighted
Advocate II
Advocate II

Re: Consecutive date range into collection

Actually, for now, I believe the new function SEQUENCE could help solve this kind of issue.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sequence


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

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,748)