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

Creating records from a Date Range

I am trying to create records in a datasource (SQL Server) based on a user inputted date range. (The user supplies the first date (StartDate) and the last date (EndDate).

 

Utilizing the following code...

 

Set(TotalDays,RoundDown(DateDiff(StartDate.SelectedDate, EndDate.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(EndDate.SelectedDate) - Weekday(StartDate.SelectedDate), 5) -
CountIf(PTDHolidays, Date >= StartDate.SelectedDate, Date <= EndDate.SelectedDate) + 1)

//Code supplied by Anh-Thu Chang, PowerApps Community Manager

...I am able to set a variable to the correct amount of dates (excluding weekends and our company holidays). So at this point I now know the first date, the last date and the amount of valid dates between them. From this I want to create a record in my datasource for each date. The records will be identical except for the date.

 

I've checked out all the posts about ForAll and Collections, as well as Lookup, Filter and Patch. What I can't figure out is how to iterate through this range, so I can record their individual values, say, in a Collection. Once I have them in a Collection then I can use ForAll and Patch to update my SQL datasource.

 

I hope I explained this clearly and somebody can steer me in the right direction. Any input will be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

You can use an expression like this one to store all the days that you need to insert into a new collection. The idea is to create all possible days between the start and end dates (and for now you need to specify all the numbers from 0 to the maximum number of days yourself - the example below works for up to 50 days), then filter all of those that are either on a weekend or on a holiday.

ClearCollect(
    DaysToStore,
    Filter(
        AddColumns(
            [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, 33, 34, 35, 36, 37, 38, 39,
             40, 41, 42, 43, 44, 45, 46, 47, 48, 49],
            "Day",
            DateAdd(StartDate.SelectedDate, Value, Days)),
        Day <= EndDate.SelectedDate,
        Weekday(Day, StartOfWeek.Saturday) > 2,
        IsBlank(LookUp(PTDHolidays, Date = Day))))

Hope this helps!

View solution in original post

3 REPLIES 3
CarlosFigueira
Power Apps
Power Apps

You can use an expression like this one to store all the days that you need to insert into a new collection. The idea is to create all possible days between the start and end dates (and for now you need to specify all the numbers from 0 to the maximum number of days yourself - the example below works for up to 50 days), then filter all of those that are either on a weekend or on a holiday.

ClearCollect(
    DaysToStore,
    Filter(
        AddColumns(
            [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, 33, 34, 35, 36, 37, 38, 39,
             40, 41, 42, 43, 44, 45, 46, 47, 48, 49],
            "Day",
            DateAdd(StartDate.SelectedDate, Value, Days)),
        Day <= EndDate.SelectedDate,
        Weekday(Day, StartOfWeek.Saturday) > 2,
        IsBlank(LookUp(PTDHolidays, Date = Day))))

Hope this helps!

Carlos,

 

Works perfectly. Man Happy

 

Can't tell you how much I appreciate your effort in helping me out.

 

I really appreciate it!

JADANL123
Advocate I
Advocate I

Thanks Carlos your code works perfectly. For my use case no filter for holidays/weekend was required so I adjusted your code like the following:

 

ClearCollect(
    DaysToStore,
    Filter(
        AddColumns(
            [
                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,
                33,
                34,
                35,
                36,
                37,
                38,
                39,
                40,
                41,
                42,
                43,
                44,
                45,
                46,
                47,
                48,
                49
            ],
            "Day",
            DateAdd(
                StartDate.SelectedDate,
                Value,
                Days
            )
        ),
        Day <= EndDate.SelectedDate
    )
)

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,349)