by I want to show you today something that makes some people confused. When you create a form, you can define that a field is a number, date, or a string. It’s good practice to do so because it forces your users to insert the data correctly, without the need to format the submitted data. But when you use Power Automate to fetch the data provided to parse further, you get a nasty surprise. Let’s do a basic example to demo the way it works (an issue, in my opinion).
Microsoft Forms are a fantastic way for you to get data from your users without a lot of effort. If you’ve never used Microsoft Forms, here’s an article that goes through the fundamentals.
A basic travel form
I use this example a lot, but it’s the right way because it’s something that most companies have to track, and few do it in a formatted way. It’s so easy to automate the overall process, but we’ll get into that at another time.
Here’s the form:
I’ve added a variety of items so that we can see different types of data. Let’s build the Power Automate that will fetch the information when it’s submitted. If you’ve never used Power Automate, I have a bunch of articles that show how to do simple and more advanced processes in Power Automate. Explore it and let me know if you have any questions.
Let’s fill in the form:
And the Power Automate is triggered correctly.
Notice something?
The issue
As you know, the body comes formatted in JSON, where you can have things like boolean values, arrays, strings, and more, but as the example above demonstrates, all items are strings. You can get quite confused when you get errors on your Power Automate when types don’t match and errors. It starts throwing errors. Let’s try to insert the values straight from the form to a SharePoint List.
The result
We can’t even select the rating because it’s a string, and SharePoint expects a number.
The solution
SharePoint and Power Automate will validate you so you won’t be able to insert invalid data into the fields, but you may not understand right away why? If I define my rating as a number and the list column as a number also, why can’t I pick it from the list? Because it’s a string, not an int.
To be able to insert the data, you have to convert the data, which sucks, and it’s highly error phone, especially with dates, but that’s what you need to do.
Converting a date
You need to use the formatDate to achieve that:
formatDateTime(body('Get_response_details')?['<ID of your Departure Field>'],'yyyy-MM-dd')
formatDateTime(body('Get_response_details')?['<ID of your Arrival Field>'],'yyyy-MM-dd')
For the numeric value you need to convert it into an int:
int(body('Get_response_details')?['rbcb95172544043bea7a0b1f927094c3d'])
You’ll get something like this:
And if we check the SharePoint list we get:
Final Thoughts
I know this isn’t ideal at all. If you format data in both ends you would expect that you would not need to convert it on the way but you have to in this case. Be aware of this and format your data accordingly so that you don’t have invalid on your SharePoint Lists or databases.
Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Microsoft Forms-related articles here
Photo by Tom Roberts on Unsplash
Thank you this helped me out. One thing you may want to add if the date of the form is null that your formula errors out still. You need to wrap an if statement to check for a null value.
if(equals(outputs(‘Get_response_details’)?[‘body/’],”),null,formatDateTime(outputs(‘Get_response_details’)?[‘body/’]))
Very good point!
Hi – we’re considering using MS Forms on our external facing web site in order to allow the public to enter in requests/suggestions – we’d import that data via PowerAutomate into SharePoint. We’re concerned about malicious actors trying to enter HTML/scripts/code etc into the form. Is there a way to prevent this? Thanks!