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

[dangladesh]

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?

[RandyHayes]

@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.

[dangladesh]

@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:

Variables in PowerApps:

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]

@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?

[dangladesh]

@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]

@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).

And the response back from the connector is:

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.

[dangladesh]

@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]

@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.

[RandyHayes]

@dangladesh 

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

[dangladesh]

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.