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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
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')

 

View solution in original post

12 REPLIES 12
RandyHayes
Super User III
Super User III

@dangladesh 

If you are storing this in a Text column in your SharePoint list, then it will be exactly what you store.

Can you share your Patch formula and perhaps that can shed some light.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

@RandyHayes- Thanks for the comment, I appreciate your help! Unfortunately, I'm finding that to not be the case. It would be great if I'm just missing something along the way. Here's some more info:

 

SharePoint columns:

dangladesh_0-1607614103020.png

 

Variables in PowerApps:

dangladesh_1-1607614179788.png

 

Here are the assignment statements for the variables that are used in the Patch:

Set(runtime_participation, Text(runtime_participation_date, "[$-en-US]yyyy-mm") & "-01T" & Text(runtime_participation_date, "[$-en-US]hh:mm:ssZ"));
Set(runtime_reminder, Text(runtime_reminder_date, "[$-en-US]yyyy-mm") & "-01T" & Text(runtime_reminder_date, "[$-en-US]hh:mm:ssZ"));
Set(runtime_failure, Text(runtime_reminder_date, "[$-en-US]yyyy-mm") & "-05T" & Text(runtime_reminder_date, "[$-en-US]hh:mm:ssZ"));

 

And finally, the Patch:

Patch('DataSource', Defaults('DataSource'), {
   Runtime_Participation: runtime_participation,
   Runtime_Reminder: runtime_reminder,
   Runtime_Failure: runtime_failure
})

 

RandyHayes
Super User III
Super User III

@dangladesh 

Try to put a label on your screen and set the text property to : runtime_participation

Does it show the correct information you want written to your list?

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

@RandyHayes- It does show the string as I'd like it to be stored in SharePoint.

 

I even attempted to store the value in a text input control and reference that value (TextInputControl.Text), but it was still being parsed and reformatted at some point when Patched.

RandyHayes
Super User III
Super User III

@dangladesh 

Well, I would have sworn on a pile of money that there would be no issue (should have gotten me to do that 😉 ) with writing ANY text value to SharePoint.

 

I gave this a test on a sample list trying to set to a single text column.  Setting it to "2020-12-01T17:37:11Z" directly in SharePoint was no problem.

Setting to the same in PowerApps...yep...it screwed it up.

 

I can see in the monitor that the request is sent correctly to SharePoint (or the connector I should say).

RandyHayes_0-1607639936172.png

And the response back from the connector is:

RandyHayes_1-1607639964887.png

 

I believe this is a bug in the connector.  It should not be altering the text going to a text column.

My only suggestion is to open a support request and report this as a bug.  It is clearly converting it.

 

I would offer some workarounds, but I sense that having the date/time in this format in the Text column is important, otherwise you would have used a Date/Time column.

 

 

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

@RandyHayes- Thanks for all your time and insight. I will definitely reference this thread in my support ticket.

 

Currently, I'm using the contents of these text fields as parameters for a replace() function within a Flow that builds scheduled Flows based on a template. Alternatively, I'm thinking I could expect a date instead of a literal and convert to the proper string format within the Flow. Thanks again!

RandyHayes
Super User III
Super User III

@dangladesh 

Sounds good.

I don't believe the problem would be fixed through flow as it uses the same connector.  It appears the connector is where the problem comes in.

I'd be excited to know what comes of it.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
RandyHayes
Super User III
Super User III

@dangladesh 

How did this work out for you? Were you able to get anywhere on this issue?

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

Sorry for such a delay! I opened a ticket with Microsoft and just heard back today. @RandyHayes, our suspicions were correct: this was in fact an issue with the SharePoint connector. The text field is being auto-parsed. Unfortunately, this was described as a "design limitation", and nothing will be done to fix it.

 

The issue is that the text field passed to the connector can be parsed as a date, and is therefore parsed and reformatted. However, if the item passed to the SharePoint connector is a date, I can choose how to format it.

Unfortunately, since Power Apps formats dates with functions that return text fields, whatever is passed to the SharePoint connector will still just be a text field that will ultimately be parsed and reformatted. My workaround uses a Power Automate Flow to pass a formatted date, which is then converted to a text field preserving my chosen date format.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,465)