Power Platform Community Forum Thread Details

Hi all,

Thanks in advance for reading. I built a great SharePoint integrated Power Apps form for a task tracker that we use.

I have a two part question, with my major concern on Part 1.

Part 1

We have two due date columns, requestor due date and our internal due date (typically earlier than the requestor's due date so we can review and sign off).

The SharePoint site's Regional Settings is set to UTC-5 for Eastern time.

Prior to using the Power Apps form, everyone's times entered as if they were eastern times/due dates, as that is where our HQ is. So even a Western time user picks 2/24/23 4:00 PM for close of business tomorrow.

As I was developing the form, I gave a user in Central time a copy of the link so they could test the form in the background before integrating it into SharePoint itself.

Her dates and times were still passing through the form after submission and recording exactly as she entered them (so if she picked tomorrow at 16:00 hours, SharePoint showed tomorrow at 4:00 PM).

The only major change I can find between that version and today is that I added the SharePoint integration properties so the form is now the default form.

Now when a user in Western time (and also the Central time user) submits, it adjusts the time saved in SharePoint. My Western time zone user selected tomorrow at 18:00 hours (wanting 6:00PM), SharePoint reflected tomorrow at 9:00 PM, so it is adding the time difference from Eastern to their time zone.

Other settings background:

The corresponding SharePoint internal field name is DueDate

The Data Card is Requestor Due Date_DataCard3

The Date Value is DateValue3

The Properties of the date/time control (DateValue3) are:

Date time zone: Local Changing to UTC gives us an error in the form because we don't allow dates in the past to be selected, so it thinks the current date/time is already in the past since we're lagging behind UTC.

DateTimeZone: DateTimeZone.Local

OnSelect: false

What hasn't worked: I've tried:

- Toggling off Enable enhanced Microsoft SQL Server connector (from a Shane video, thought that would solve it, it didn't. I thought he said it applied to SharePoint, too, but apparently not).

- Leveraging ChatGPT for some help. Some of their suggestions:

Setting 'OnSelect' of DateValue3 to:

Patch(
 'OPO Task Tracker',
 Defaults('OPO Task Tracker'),
 {
 DueDate: DateAdd(
 DateValue(DateValue3.SelectedDate),
 -(TimeZoneOffset(DateValue3.SelectedDate) - (-300))/60,
 Hours
 )
 }
)

#2:

Patch(
 'OPO Task Tracker',
 Defaults('OPO Task Tracker'),
 {
 DueDate: DateAdd(
 DateValue(DateValue3.SelectedDate),
 Value(Text(Now(), "[$-en-US]zz")),
 Hours
 )
 }
)

Patch(
 'OPO Task Tracker',
 Defaults('OPO Task Tracker'),
 {
 DueDate: DateAdd(DateValue(DateValue3.SelectedDate), If(-time zone offset >= 0, time zone offset, 24 + time zone offset), Hours)
 }
)

Patch(
 'OPO Task Tracker',
 Defaults('OPO Task Tracker'),
 {
 DueDate: DateAdd(DateValue(DateValue3.SelectedDate), -TimeZoneOffset(DateValue3.SelectedDate, Sign(-(time zone offset))), Hours)
 }
)

Patch(
 'OPO Task Tracker',
 Defaults('OPO Task Tracker'),
 {
 DueDate: DateAdd(DateValue3.SelectedDate, 0, 0, 0, 0)
 }
)

and several others. It gives such wildly different things it's hard to follow.

The one thing that has me thinking, but I have no idea where to take this is: Another Reddit user helped me with a Drop down single choice default to always show Morning/Afternoon/Evening based on Eastern time no matter where the user is. That code was this:

[If(And(Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))>=12,Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))<16),"Afternoon", If(And(Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))>15,Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))<23),"Evening", If(And(Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))<12,Hour(DateAdd(DateAdd(Now(), TimeZoneOffset(), Minutes ), -5, Hours))>1),"Morning","")))]

So it seems to me there's something to that pattern, but I'm not looking to pass through the text values Afternoon, Morning and Evening, but rather just whatever hour/min value the user picks to pass that through the form submission with no conversion based on their local time.

I do have a workaround, and it's a simple conversion chart, but with everything else working perfectly, this was a major blow to my ego (because of course, the first user to use it after demo'ing to the team and my boss discovered it, even though they said they noticed it yesterday while testing but never mentioned it, so that was cool.)

Part 2:

Having the default of the second due date (our internal due date) be the same day as the requestor due date, minus one hour:

Everything above referenced the Requestor Due Date field. So what I have been able to do is set the Due to OPO default to DefaultDate equaling DateValue3.

I haven't figured out how to have the hour subtract an hour from what is selected as the Requestor due date's hour. If I try setting the 'Default' of the HourValue4 to HourValue3-1 it returns an invalid argument error with data type number. Expecting Number, Text, Boolean.

The prior default is: Text(Hour(Parent.Default),"16") which defaults to 16. (and the least of my worries at this point.

Any help/insight on part 1 is most appreciated, part 2 is purely a bonus at this point.

Categories:

Building Power Apps