Integrating with Power Automate Flow and 0365

Instead of connecting a restful service in the backoffice, FormBuilder is able to post data directly to PowerAutomate "HTTP Request" end points. (Using the step "When a HTTP request is received") There is a Template of the flow with the main steps called Base flow template.

https://emea.flow.microsoft.com/manage/environments/Default-a05ef69e-6149-4fba-a40c-df338810f644/flows/shared/4fe133cd-acda-4a48-aa2d-ad6823c24451

Use Case

TBC - Need to provide an overview of appropriate use cases for O365/Sharepoint over other corporate capabilities.

Form Builder Setup

The form builder json needs a specific submit slug as shown here.

{ "Environment": "Int", "URL": "https://prod-244.westeurope.logic.azure.com:443/workflows/45cc0c6dd30d4ebda679d81f55a1c850/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=io_gVeLWk4Q6rbp3hkqcWPyPEm4p8sIa15PiGH3vY7o", "Type": "flowtoken", "AuthToken": "TestToken" }

The main difference is the addition of the "Type": "flowtoken" The URL is set to the url provided in the When a HTTP request is received part of the flow. There will be a dev and live flow. This will be reflected in the json. LOCAL INT and QA go to the Dev url and Stage and Prod had the live url.

Power Automate Setup

The flow will receive json object from the form builder. In the headers will the be authtoken. Within the first step it needs to know the format of the json. There is a bash script to help create that json. This is written in the first step.

Power Automate/flow are found here. https://emea.flow.microsoft.com/en-us/

Click on My Flows to see the flows. Share with me is ones others have created or you've shared.

Common PowerAutomate actions and connectors

• Adding to a Sharepoint data list (Microsoft lists). Spreadsheets in the cloud.
• Creating or uploading a file to a sharepoint folder(s). HTML files with easy reading coloured rows.
• Sending email internal and external (based on form data)
• Creating teams notifications.
• Checking security based on auth token.
• Decision trees based on an answer. (Do a different file/email/message based on how a form was filled in)
• Adjustments [not sure what this is but I might remember]
• Ref numbers (guids)
• Upload attachments to the document library (store in the same place as the html). Can't yet attach an uploaded file to a datalist. (Additional set up instructions below)

Adding file uploads to a form and handling them in a flow.

So we create a form as per normal and use the same json. The file uploads will come across like this.

"FileUpload": { "type": "array", "items": { "type": "object", "properties": { "TrustedOriginalFileName": { "type": "string" }, "Content": { "type": "string" }, "UntrustedOriginalFileName": { "type": "string" }, "KeyName": { "type": "string" } }, "required": [ "TrustedOriginalFileName", "Content", "UntrustedOriginalFileName", "KeyName" ] } }

We then need to initialize a few things before we deal with the actual files.

1. Initialize file index - currentIndex Integer 0
2. Initialize file content - fileContent String ""
3. Initialize file length - fileLength Integer 0

Under the Authentication check and inside the if Yes branch. We add an : Apply to each which goes through the files attached.

1. FileUpload (comes from the questionId)
2. Get content length - fileLength - length(triggerBody()['FileUpload'][variables('currentIndex')]['Content'])
3. Get trimmed content - fileContent - substring(triggerBody()['FileUpload'][variables('currentIndex')]['Content'], 1, add(variables('fileLength'), -2))
4. Create file - Folder path links to the document library created for these. Filename - TrustedOriginalFileName - base64ToBinary(variables('fileContent'))
5. Increment variable - 1

Adding files as Sharepoint List attachments

Same as above, but instead using an Add attachment sharepoint node

This node has to go after the list creation as it references the list item id.

Working with the AddAnother element

The AddAnother element introduces dynamic data into the form answers. A soloution to this is to ignore the element when creating the initial data object list and appending the additional answers at the end.

1. Create a int variable to keep track of the current AddAnother array index.
2. Create a string variable to keep hold of the current AddAnother Object
3. Before the empty variables have been removed, create a for loop to iterate over the array
4. Inside this loop:
   • Set the string variable created in (2 to a Json object e.g. {"Name": "John", "Age": 24}
   • Convert the String variable to a JSON value using the json() function is expressions
   • Add this value to the data objects array
   • Increment the index variable

This will append each object created in the AddAnother element to the end of the Html file.

Issues of note

The flows can not contain any premium actions/steps such as create a row in excel or create a word document if it's being ran as a person who doesn't have the Premium account.

Changes to the json form will likely cause a problem with the flow as it's expecting certain json formats.

Keeping a dev version and a live version is necessary to make changes and check they work but they must always be made the same.

Hints and tips

You can build a new flow based on the base template. You can then Save As to the new name.

If you're building a datalist you can export the layout as an excel spreadsheet. A useful thing to do is ensure that all the fields are filled in as you'd expect them, else the import makes some odd assumptions and you have to change them back.

You can then import from an excel spreadsheet to create a new datalist. You will/may have to re connect these individual cells in the flow step. [There is an email with more details about this... I'll dig it out.]

You can reorder the datalist columns by dragging and dropping. Sometimes it doesn't like this and to have to right click and "move left" etc

Current developments

• Empty Properties - Form - Flow - Data List - Emails (LIVE and actively used) empty-property

• MAARS - Form - Flow - Data List - Teams Message - Emails - HTML to Folder (LIVE and actively used) - Lee Woolfe/Someone else maars-referral

• Report a serious accident - Form - Flow - Data List - Teams Message - Emails - HTML to Folder (LIVE and actively used) - Julia/Sarah B report-a-serious-accident

• IAP (Stockport independent appeals panel) - (Ready for playback) independent-appeals-panel

• Report an issue - Form - Flow - Data List - Email (In ready for playback to Kay/Laura) On hold until it's needed. ?

• Add a group (Stockport Groups) - Form - Flow - Data List (In ready for playback) on hold until it's needed. add-a-group

• Request a replacement alley gate key(s) - Form - Flow - Datalist/ Fileupload / HTML (in dev) alley-key-request

• Alleged Dog Attack - Form - Flow - Datalist (in dev) alleged-dog-attack

• Reporting blue badge misused - Form - flow - datalist (in dev)

• Replace lost stolen taxi plate - form -flow -datalist (in dev) replacement-for-lost-or-stolen-taxi-plate

• replace taxi badge paper licence - form -flow - datalist (in dev) replacement-taxi-badge-or-vehicle-paper-licence

• Dog fouling - form flow upload html (in dev) report-a-dog-fouling-problem

• taxi accident report - form flow upload and html (in dev) taxi-accident-report

• Taxi change of address

• Returning taxi plates

• Request a replacement for a lost taxi badge