cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dangladesh
Frequent Visitor

Patching SharePoint - text variable changed to date before updating single line text

I am having a similar problem to the issue described in this post: https://powerusers.microsoft.com/t5/Building-Power-Apps/PowerApps-SharePoint-forcing-a-text-field-to...

 

I am attempting to patch a SharePoint list containing three Single line of text columns. However, since the text being submitted to these columns is a UTC timestamp, the value inserted into the text columns ends up being some other date format.

 

Example: 2020-12-01T06:00:00Z is changed to 12/01/2020 06:00:00

 

The value in this text column needs to be unchanged. I have tried storing the value in a Text variable, using the Text() function in the Patch(), and first putting the text value into a text input control. None of these three strategies have worked.

 

Any suggestions on how to prevent Power Apps or SharePoint from parsing this date and simply treating it as a text value?

 

19 REPLIES 19
dangladesh
Frequent Visitor

Here is an example of the workaround, in Power Automate. The returned value of this call to formatDateTime() is not parsed or reformatted:

formatDateTime('2020-12-01T07:00:00Z', 'yyyy-mm-ddThh:mm:ssZ')

 

RandyHayes
Super User
Super User

@dangladesh 

That is good to know.  Personally, I would not consider that issue with the connector as "design limitation"...I am pretty sure that is a BUG!!  

 

Well, I am glad you were able to devise a workaround for it at least and I will keep this in mind should it cross my path again.

 

AND, thanks for following up and responding to this thread.  This is very important so that other with the issue can discover the problem and the workaround for it...Thank You!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
mousman85
Helper IV
Helper IV

I've just had this same issue, mine is within PowerAutomate though, i have an external form, sending date responses formated like this: 2021-06-01T14:00:00+00:00"  . Sharepoint decided to format it 01/06/2021 3:00pm no idea why its changing the date to Jan and then the time to 3:00pm the field in SharePoint is set as a text field,  I can't use data /time fields because if the form Date isn't filled in it comes in blank and it won't let you use a blank date in the Date Field, and i have about 20+ different date fields in the form so i'd have to set check conditions for each date field. I hope they solve the issues with date and time in sharepoint. 

HEATFreight
Super User
Super User

Holy smokes! This is so maddening... I spent a not-insignificant amount of time getting my date and time controls to work properly and to successfully populate a text field with zulu time like: 2021-10-25T17:09:00Z, but now I find out that Sharepoint is ludicrously unreliable at storing text verbatim. A text field should be 1:1 with its input (the only exceptions being valid filtering rules, like disallowing escape sequences that could allow command escalation and whatnot). Necessary input sanitization aside, text inputs to a spreadsheet should be 100% verbatim. What the eff Microsoft!

RandyHayes
Super User
Super User

@HEATFreight 

It's actually not SharePoint - it's an issue in the Power Platform SharePoint connector.  

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Is there a way to encapsulate the text and unpack it easily when queried again? In Power Automate for instance, or even Power Apps. Like what about Base64?

I tried to use the method in this thread with the JSON() function, but I don't think it works for strings of text, but perhaps a text file? Oh god not another rabbit hole, now I have to go see if I can make a .txt of a calculated input field within a Power App and then safely base64 encode it through the SP list into Power Automate (or back to Power Apps). Not that I really need to, I patch form.Updates data to collections and pass JSON of the collection as arguments to the Power Automate functions and I can parse a JSON response from Power Automate if I want, rather than directly using the SP list for those outflows and inflows, but if I need consistent results for later use in case of error or the need to edit and resend the same request or otherwise to have a certain since of data integrity, then I need the SP list to match the data 1:1.

Anybody get me?

HEATFreight
Super User
Super User

I tried to follow this Base64 example, but I'm not sure it works for text strings from an input field. Like I'm calculating zulu time in a hidden text input via the selected values in a date/time picker.

Here's the thread with Base64 instructions but they do it for an image file and I don't know how to revise the code for my use case (or make a .txt within Power Apps)

HEATFreight
Super User
Super User

Here's my zulu time calculation:

Text(DateTimeValue(Text(EarlyDate.SelectedDate+Time(If(EarlyAMPM.Selected.Value="PM",12,0)+Mod(Value(EarlyHour.Selected.Value),12),Value(EarlyMinute.Selected.Value),0)),Language()),"yyyy-mm-ddThh:mm:ssZ")


Which returns something of the format:

2021-10-25T17:09:00Z
sopatte
Microsoft
Microsoft

I encountered this problem when I needed the text stored as a UTC date & time in the specified format, which was for consumption in a Power Automate flow Delay Start step. I used Power Apps Monitor to verify that the correct text was being sent, but the Sharepoint continued to show the date & time in another format. I worked around by appending a space to the fomatted date. Power Automate then consumed the date & time stamp with no additional processing; it must be that such trailing whitespace is ignored by the parser used in the Power Automate step.


Text(T,DateTimeFormat.UTC) & " "

HEATFreight
Super User
Super User

Thanks @sopatte , will have to try that!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,317)