Help just can't understand these Unable to fetch flow failures

[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..."

[grantjenkins]

@GavinF69 Looks like you've been having a bunch of fun with this 😉

I'll do some testing with the information you've provided and get back to you with some options on how to get it all working.

[grantjenkins]

On initial testing my multiple line text from Forms to SharePoint to Word works as expected. I'm not sure why you're getting yours coming back as an array of string.

One important thing I've found is that the Word Template solution by Damo has some issues due to Quick Parts and opening Word documents in Word Online. The Word document will need to be opened in Word Desktop before the properties will be visible in Word Online. If you open the Word document in Word Online before Word Desktop, then you will just see the property placeholders and not the actual content.

This will also be the case if you change some of the properties in your SharePoint Library - the changes will only be seen once you've opened the document in Word Desktop.

I know you said there is a cost involved with Word Premium connector but might be worth looking at the issues with the free version and see if it's worth it or not. You would only need to license the person running the flow (flow owner).

I'll do some further testing with null values and let you know how I go.

[grantjenkins · Accepted solution]

For my objects that are potentially empty I used the following expression which would add a single space " ".

coalesce(item()?['PropertyName'], ' ')

[GavinF69 · Accepted solution]

Hey Grant,

Thanks for responding, I'm happy to report I came to the same conclusion (or at least similar).  I can also just about sign this off as closed, everything is working I'm getting my reports created and I can see the content in the various SharePoint columns as I expect to see them. There is one last item to solve which I will add to the bottom in case someone has a suggestion on how to fix it... But for now this is how I solved the troubles I was having...

1. I used one of the templates available in Power Automate to create a new flow that would write the output to a SharePoint list and then went about declaring various variables to deal with numeric fields from forms by initialising them to 0 in case a null was presented, I also then went about defining various string and array formatting options depending on the multi-choice fields...

All numeric values were defined as float so that decimals can be handled, if you use integers then the values have to be whole numbers... The other formatting options defined as compose statements with either a carriage return, a comma or a space...

In my apply to each loop, I created various conditions to deal with null fields...

Example of one of the condition fields below that does a "if the field is not null then do nothing" (the condition no/false branch)

I also then went about defining each field that required strange characters to be removed using the various methods I defined in my compose actions prior to the "apply to each loop"

and finally put the data out to SharePoint from within the apply to each loop...

Once I had confirmed all data was ok in SharePoint, I could set about copying pieces of the code to my cloud flow that creates a document in SharePoint, pretty much the way Damien describes on his channel https://www.youtube.com/@DamoBird365... 

1. https://youtu.be/KVIWDRjPKUk or 2. https://youtu.be/Qj8SIiGJRnc

Getting the code to the cloud flow was as simple as clicking the 3 "..." on the code block and selecting copy to clipboard 

then going to the cloud flow and inserting or creating a new action and pasting the step from the "my clipboard"

The only nuances I encountered was you can copy the individual code snippets, but don't try and copy the entire "apply to each" section, rather create a new one and insert the code definitions you want in the apply to each section. If you try and use the entire apply from the template method, it inherits the properties associated with the output to list...

Another nuance I discovered was the start of the apply to each, you need to make sure you point to the trigger of your cloud flow or else you won't see the desired dynamic fields in the create item or even worse, the various field code snippets don't work as they can't find the input action. For the apply to each in your cloud flow just use the following expression on the "Select an output from a previous step" field... 

array(triggerOutputs()?['body'])

the last bit was the final step in the apply to each loop, when using the template option it uses a "Create Item SharePoint" function, when you want to update your document template stored in SharePoint, you must use the "Update file properties" function and point it to the itemID of your create file step of the cloud flow...

Flow template SharePoint looks like this

while update of a word template stored in SharePoint looks like this

Hopefully my trials and tribulations will help somebody else down the line....

Now my final challenge is to rid myself of the following message appearing in the created word document when opening from SharePoint using the "Open in app" option...

If anyone has any pointers on how to rid myself of this message, I would be grateful...

PS, I have already inspected the file properties using the "info" link on the File menu, all the dynamic data is there and there are two fields  showing "invalid" but no real error message to tell me why it is "invalid" ...

SharePoint site column is defined as "multi-line" plain text (to get around the 255-character limit), the Column is defined in a SharePoint content type just like all the other fields and it seems to be totally random as to which fields are reflected as being in error (i.e., invalid), since the right content is in the field...

Well thats it, now back to researching a solution for this latest problem...