Not a developer about to throw the towel in with power automate

[GavinF69]

Hi Community,

This is my last attempt to get some help with power automate the "low-code" / "no-code" solution... Not being a developer but willing to learn new things, I tried following many examples and videos etc. to simply Create a MS Form, take the output of the form to populate a word template file on SharePoint and store the resultant document to SharePoint Online... The creation of the form was relatively straight forward, the creation of the Word Template was also all good, then I had to learn about site columns and site content types in SharePoint in order to pass the dynamic values from Forms through Power Automate to the Template file...

All went great until I noticed because of field limitations in SharePoint (i.e. Single line = 255 characters), I needed to change some of the column definitions to multi-line. The I found out if you have a date value for a field or a numeric value for a field and bypass it in the flow (through a branching operation) the data passed (i.e. "NULL") would fail the flow, so then we go about dealing with potential NULLS in the flow (flow is growing exponentially now...) just to deal NULLS in these fields... then we go on to learn about dealing nonnumeric data passed from Flow to power automate because we didn't set a restriction on the form field to only accept a numeric value, no sooner we get that sorted we start to hit the next set of challenges, specifically where flow passes, yet run fails and then when checking the flow output I see the following....

Simple retrieve the information from the form and if NULL set it to blank through the ' ' at the end of the coalesce expression (don't ask why coalesce, been around the block with expressions now including the replace(replace... , json( ) defined ones and coalesce seemed to be the one not returning errors like "input expects a string, you passed a null, or input expects an array you passed a string, etc. etc.

I put break points in the flow to literally check each step before moving on to next definition or running the flow in its entirety, when satisfied I get no errors I run flow and then start seeing things like this...

yet when expanded the below cryptic message appearing

In fact, just about every compose field showing this type of error (here I was thinking compose is just an arbitrary placeholder to display "data in motion", nevertheless the flow finally fails at the Update file properties section with a similar message to the one displayed on the compose action...

and then I notice the message at the top of the screen saying I hit a threshold....

all of this just because I wanted to get rid of strange characters like " [ \ | etc. being presented with multi-select options in my MS form....

Can anyone point me in the right direction to finding out what this error actually means and why it occurs? I have even gone so far as downloading Visual Studio 2022 hoping to connect to the data to see where and why it is failing but that is presenting other challenges like SharePoint foundation not installed, what would be great if there was somewhere (a site or something) or even if MS showed you in the flow or presented an error code in the flow to give you a hint where to look for the problem.... telling me it can't fetch something is not helpful if you don't tell me why, being an old IT type guy, I see "443" as part of the error message and I assume that there is something wrong with the credentials or SSL layer, but my connections are fine, I authenticate on the connectors to both SharePoint and Forms, the PC I'm using is sitting as registered in the Azure AD so where to now????

The two other posts I made on this forum Re: Query regarding Apply to each "loop" to remove... - Power Platform Community (microsoft.com) and Re: Dealing with NULL's in Power Automate using co... - Power Platform Community (microsoft.com) show a lot more detail of the flow in its various rewrite iterations, if that might be useful, but I got past those errors and now this is the latest... 

PS: I don't want to use the Premium Power Automate word template connector as Mick Hucknall from Simply Red once said "Money's too tight to mention..."

[RobElliott]

@GavinF69 you've actually asked this in the Power Apps space rather than the Power Automate space so won't have quite as many Power Automate super users etc viewing it. Go over to https://powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums and post it there. I don't immediately know the reason for the error you are getting but someone on the forum will be able to help.

Rob
Los Gallardos

[SebS]

Just curious, what part of the whole Your Task failing?

The part when you trying to fill the word doc or the part where You try to store data in Sharepoint from the MS form ?

[GavinF69]

Thanks @RobElliot, I've posted hopefully in the right place, I guess that happens when you tired and hit the ? mark in flow to go to community forums

[GavinF69]

Hi @SebS , The entire flow fails when the last part Update file properties doesn't run, although it does create the document in SharePoint, just none of the dynamic content populates to SharePoint columns nor the actual word template... At first it was populating the SharePoint columns and not the template, but now it doesn't even do that... 

[SebS]

@GavinF69 

I wish I could help You, but I do not understand what You are up to and why.

Here are a few things that confuse me a lot:

Knowing MS Form is a saved Excel file with data of all inputs in OneDreve of the user, Why not move the file, which is in excel, to Document Library if You want to store it in SharePoint?

Another one is Why not then use the file and Function to list Rows to update World Doc or move data to Sharepoint List and Update World Dock from there using Get-Item

Knowing the MS Form outcome is stored in Excel, and the outcome of Get Response from MS Form is Excel row, the issue with dates is clear. It is ISO 8601 Format when SharePoint stores Date in UTC

So when You are doing any Updates, you need to know two things.

1. You need to convert the Date from iso8601 to UTC

2. You need to ignore blanks that excel creates and SharePoint does not understand it as it deals with Null in the Date column

A lot of people look at videos when people Parsing the Strings creating some Compose, variables when You just need to use Expressions. Here is an example of how You deal with Dates coming from excel

if(empty(items('Apply_to_each')?['Shift Start Date']),null,addDays('1899-12-30',int(items('Apply_to_each')?['Shift Start Date']),'yyyy-MM-dd'))

First, You determine if Cell is Empty if it is You Apply Null if it's not. You cast Excel Date to an Integer and use addDays to create a date from an integer and format it to whatever You like.

But when You use List row From an excel file, there is an option on the bottom to convert automatically but to use that File need to have a table, and that means You need to create one. I don't remember if the MS form file automatically comes with a table or not.

But cause I never deep dive into filling word doc, I may be wrong but at last here are a few points You may look in to and hope some of them will help you resolve the issue.

[GavinF69]

Thanks SebS for replying, I finally got over all my troubles around 2 am this morning... I posted an explanation of how I solved so hopefully it will help someone else too... I did use the Forms through power automate to SharePoint trick to inspect data and then when working went back to creating a document using pretty much the same code.