One of the most common uses for Inteligent automation is gathering data, this space is normally covered by Power Apps but Power Automate can do it, and in a lot more dynamic/reusable way.
Automations can often be thought of as targeted, precise solutions to a certain problem. And this is very much true, but it's not always the case. In fact, making reusable solutions isn't that challenging, and just takes a little more planning.
In this how to we are going to create an automation that emails owners of a certain data set, these owners open a link to a SharePoint form, validate the data and update when necessary. Any SharePoint form can be selected when the automation is triggered.
As th data is stored in a SharePoint list, for protection the automation will share the single row with the data owners.
The List
The list can as many fields and types as needed as long as long as it has 2 key fields:
- mailer: Person selector allowing multiple
- validated: Person selector allowing multiple
The mailer is all the people you want to validate/update the data
The validated is for each validator to log that they have completed the update.
So once the mailer and validated match we know the data is complete.
The Flow
The trigger needs:
- List Site
- List ID
- Url of Form
We then loop over all the rows, and each of the rows we loop over the mailer field and append to create the 'email to'.
Finally we share the row with the data owner and email them the form link
Form
Every SharePoint list has a built-in form, often shown on a slide out menu to the right. But it can also be opened on its own (without the list).
https://{yourSharePointSite}/Lists/{listName}/EditForm.aspx?ID={rowID}
But we can make the experience a little better by
- Hiding menus (
env=WebView) - Add complete screen and a redirect to it (
Source={url})
https://{yourSharePointSite}/Lists/{listName}/EditForm.aspx?ID={rowID}&env=WebView&Source={yourCompleteScreenUrl}?env=WebView
The complete screen can be quite simple and on any site, as long as everyone has access to it.
To create the link we concat our dynamic input values to create this (with id from the list row)
@{triggerBody()['text_2']}&@{items('Apply_to_each')?['ID']}'&env=WebView&Source={yourCompleteScreenUrl}
Note the {yourCompleteScreenUrl} should include ?env=WebView
But that doesn't quite work, as the redirect/source url's parameter is getting read as parameter of primary url. To fix this we need to uri encode the formURl
@{triggerBody()['text_2']}&@{items('Apply_to_each')?['ID']}'&env=WebView&Source=@{encodeUriComponent({yourCompleteScreenUrl})}
The form can look, well lets say not the best, as its only the size of a smart phone so is very narrow.
Foruntanly we can add some theming (don't get too excited, its basic but better then nothing)
To access it select New item, in the top right there is the edit icon.
First we can edit what fields are shown with edit columns. So any admin only fields can be removed.
Next we Configure layout
We can update the forms Header, with icons, fonts, size and colour. The basic structure is a josn with each element (div/icon) having type, and class attributes and style (CSS), and children.
The Body unfortunaly has a lot less customizatiom, with only the addition of sections that allow 3 inputs per row and section titles. You can not change formating or even the width of the inputs.
The Footer is similar to ther header but obvicoulsy at the end of the form. In below we added hyperlink to a email from the form input
Put all together you end up with something like this:
Link at the end of blog for more documentation on form themes.
If you wanted you could use a Power App here, the SharePoint form version. Its kind of a middle ground as its not as powerful as a proper app but is a lot more flexible (auto share with list). Though you will need to add a ID param to make the link work (again see end of blog for more documentation).
Although the process can be a little convoluted compared to a targeted solution, creating the flexibility to run with any list, any group (without the need for a security group) and change management might mean its worth it.
Additional Steps
Schedule
A parent flow can be created on a schedule, which then calls the flow.
Auto Reset
An additional step to update the SharePoint item to reset the validator field to empty again
Follow Up
Additional step to convert the validated to a string, we use a 'does not contain' to find if the mailer is in the validated field. If the email is there then we don't send a follow up email validator.
Further Reading
Top comments (0)