Date Calculation Incorrect During Patch() Formulas in Published Mode

[gymcode]

I have a Patch() function that create 1 parent 'Reservation' record, and multiple child 'Reservation Details' records, and it skips weekends and a collection of holiday dates.

When I am executed Patch() function in Editing mode, both 'Reservation and 'Reservation Details' records were created on the correct days (weekdays).

However, when the app is published, 'Reservation Details' records created there was incorrect (1 day behind).

Scenario

- FromDate_Datepicker : Mon (05 Feb 2024)
- ToDate_Datepicker = Sun (11 Feb 2024)

	05 Feb	06 Feb	07 Feb	08 Feb	09 Feb	10 Feb	11 Feb
	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Editing Mode	Created	Created	Created	Created	Created	Skipped	Skipped
Published Mode	Skipped	Created	Created	Created	Created	Created	Skipped
Published Mode is 1 day behind	*Sun	*Mon	*Tue	*Wed	*Thu	*Fri	*Sat

Based on the results above, Published Mode is 1 day behind Editing Mode.

Question: May I know how can I resolve this, and is a code change to fix this possible?

Thank you.
_____________________________________________________________________________________

Below is my full Patch() function:

//Creates 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);
// Store created parent 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);

//Create 'Reservation Details' record for each date and associated it with 'Reservation' record created by 'Reservation Details'.'Reservation'
//Only create records if date matches weekdayt dates in WeekdayDates collection
Patch(
    'Reservation Details',
    ForAll(
        Sequence(
            DateDiff(
                FromDate_Dp.Value,
                ToDate_Dp.Value,
                TimeUnit.Days
            ) + 1
        ),
        With(
            {
                _Date: DateAdd(
                    FromDate_Dp.Value,
                    Value - 1,
                    TimeUnit.Days
                )
            },
            If(
                _Date in WeekdayDates,
                {
                    Date: _Date,
                    Reservation: newReservationRecord,
                    Name: newReservationRecord.'Reservation ID' & "_" & _Date,
                    'Reserved Desk': newReservationRecord.'Reserved Desk'
                }
            )
        )
    )
);

 
- 'Reservations'.'Start Date' is correct
- 'Reservations'.'End Date' is correct
- 'Reservation Details'.'Date' is wrong

[mmbr1606]

// Creates 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);

// Store created parent 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);

// Create 'Reservation Details' record for each date and associate it with 'Reservation' record
Patch(
    'Reservation Details',
    ForAll(
        WeekdayDates,
        If(
            Value >= FromDate_Dp.Value && Value <= ToDate_Dp.Value,
            Collect(
                ReservationDetailsCollection,
                {
                    Date: Value,
                    Reservation: newReservationRecord,
                    Name: newReservationRecord.'Reservation ID' & "_" & Text(Value, "[$-en-US]yyyy-mm-dd"),
                    'Reserved Desk': newReservationRecord.'Reserved Desk'
                }
            )
        )
    )
);

looks like so, lol

[gymcode]

Hi @mmbr1606 , thanks for the formula. I reverted back to classic datepicker control and tried to use tis code, but received a similar error, that it cannot use a non-record value in this context.

// Creates 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);
// Store created 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);


// Create 'Reservation Details' record for each date and associate it with 'Reservation' record
Patch(
    'Reservation Details',
    ForAll(
        WeekdayDates,
        If(
            Value >= FromDate_Dp.Value && Value <= ToDate_Dp.Value,
            Collect(
                ReservationDetailsCollection,
                {
                    Date: Value,
                    Reservation: newReservationRecord,
                    Name: newReservationRecord.'Reservation ID' & "_" & Text(Value, "[$-en-US]yyyy-mm-dd"),
                    'Reserved Desk': newReservationRecord.'Reserved Desk'
                }
            )
        )
    )
);

[mmbr1606]

hey @gymcode 

maybe this modification helps solving the issue stated in your last post:

// Creates 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);

// Store created parent 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);

// Create 'Reservation Details' record for each date and associate it with 'Reservation' record
ForAll(
    Filter(
        WeekdayDates,
        Value >= FromDate_Dp.Value && Value <= ToDate_Dp.Value
    ),
    Collect(
        ReservationDetailsCollection,
        {
            Date: Value,
            Reservation: newReservationRecord,
            Name: newReservationRecord.'Reservation ID' & "_" & Text(Value, "[$-en-US]yyyy-mm-dd"),
            'Reserved Desk': newReservationRecord.'Reserved Desk'
        }
    )
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[gymcode]

Hi @mmbr1606, thank you for the formula. There is no error now, but I realized the Patch() formula is missing, so the 'Reservation Details' records weren't created 😅

[gymcode]

Appreciate your asisstance @mmbr1606. In the meantime, I'll be using the original query, but -1 from the date value.

// Create 'Reservation Details' record for each date and associate it with 'Reservation' record
Patch(
    'Reservation Details',
    ForAll(
        Sequence(
            DateDiff(
                FromDate_Dp.Value,
                ToDate_Dp.Value,
                TimeUnit.Days
            ) + 1
        ),
        With(
            {
                _Date: DateAdd(
                    FromDate_Dp.Value,
                    Value - 1,
                    TimeUnit.Days
                )
            },
            If(
                _Date in WeekdayDates,
                {
                    Date: _Date-1,   <<---- Temporary workaround
                    Reservation: newReservationRecord,
                    Name: newReservationRecord.'Reservation ID' & "_" & _Date,
                    'Reserved Desk': newReservationRecord.'Reserved Desk'
                }
            )
        )
    )
);

 
So at least users who are using the app will have the correct dates. 

[mmbr1606]

in the code above the patch is included

here again:

// Create 1x parent 'Reservation' record
If(
    BookingType_Self_Cb.Checked,
    ClearCollect(
        ReservationRecord,
        Patch(
            Reservations,
            Defaults(Reservations),
            {
                'Start Date': FromDate_Dp.Value,
                'End Date': ToDate_Dp.Value,
                'Reserved For': 'Reserved For (Reservations)'.Employee,
                Facility: Building_Ddl.Selected,
                Area: Area_Ddl.Selected,
                'Employee Name': Self_Section_EmployeeName_Ddl.Selected,
                'Reserved Desk': Self_Section_Seat_Ddl.Selected,
                'Booked By': Self_Section_EmployeeName_Ddl.Selected
            }
        )
    )
);

// Store created parent 'Reservation' record in a variable 'newReservationRecord'
Set(
    newReservationRecord,
    First(ReservationRecord)
);

// Create 'Reservation Details' record for each date and associate it with 'Reservation' record
ForAll(
    Filter(
        WeekdayDates,
        Value >= FromDate_Dp.Value && Value <= ToDate_Dp.Value
    ),
    Patch(
        'Reservation Details',
        {
            Date: Value,
            Reservation: newReservationRecord,
            Name: newReservationRecord.'Reservation ID' & "_" & Text(Value, "[$-en-US]yyyy-mm-dd"),
            'Reserved Desk': newReservationRecord.'Reserved Desk'
        }
    )
)

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[mmbr1606]

oh you mean at the bottom,

see my code above

[gymcode]

Hi @mmbr1606 , thanks for the updated formula.

I am able to execute the formula successfully now, but in Published mode, the date time is still showing the same behaviour of being 1 day off 😩

[mmbr1606]

good to hear that but also bad hmmm

you are using sharepoint as a datasource? if yes, sometimes it can take a bit longer till SP has the right version of the app.

sorry stupid question, but did you save/publish and click the new version when u opend the app in production? in the editing sudio it is working and showing the right values?

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[gymcode]

my datasource is within CRM itself. I am in Sandbox environment, yet to deploy to Production. 

In editing studio, yes, it is working and showing correct values.

It is just in published mode, in the page's direct URL, the created record dates are off.

It's also only specific to 'Reservation Detail'.'Date' value. Other date values in 'Reservation' entity is correct, such as 'Reservation'.'Start Date' and 'Reservation'.'End Date'.