cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pwalshosit
New Member

Is there an easier way to filter through large numbers of Microsoft form fields?

Greetings!

 

We have a new staff complaints submission form using Microsoft form. This form has over 110 fields. The form has multiple sections depending on the staff's choices. An average number of fields answered out of the 110 would be 8.

 

Objective: I need to email the results of the answered fields only. Filtering out all other blank fields. 

 

What I have tried: I have used a Select action to individually split out each field for re-usability. I am able to use Parse JSON, but am unable to single out each result to check if its value is blank.

My thought was to iterate through each field and only append fields to an array that have results. Then I would use that array to build the email content.

 

Question: Is there an easier way to filter through large numbers of Microsoft form fields?

 

The images below are of my select and JSON actions.

 

pwalshosit_0-1632371378366.png

pwalshosit_1-1632371409477.png

 

*The reason I went down the Select route was due to Microsoft supplying the questions as ID's only and I needed the questions in a readable format to match the answer with the question. 

2 ACCEPTED SOLUTIONS

Accepted Solutions

@pwalshosit  This is due to your array size which is one.Use Split() and split it by comma that will break your select array and then use in apply to each.

Please do not forget to convert your select array in string because Split() works on string.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

View solution in original post

Thanks for the tip on Split.

 

I have been able to solve my issues by using a list of expressions to clean up each value.

 

  1. I get all Form fields from the action Select
  2. Convert the Array to a String
  3. Clean up the string with a few Replace expressions
  4. Then I Split the string back to an Array
  5. Now I can perform an Apply to each for each array value
  6. From there I run a LastIndexOf expression (lastIndexOf(variables('string'), '":"')
    • This gets the index number of the last character before the answer
  7. Then run a Substring expression by 3
    • This should error if no answer has been submitted for that value because you can't exceed the maximum indexed characters of the value
  8. Results with Errors are skipped and successful results are appended to an array
  9. Now I have an array with all fields that have had an answer 😄
  10. An easy 1 2 3 step process.... 😐

Big ups to Microsoft short sight on not being able to email form results easily.

 

View solution in original post

4 REPLIES 4
Hardesh15
Super User
Super User

@pwalshosit 

My thought was to iterate through each field and only append fields to an array that have results. Then I would use that array to build the email content. -->Right Approach

 

Inside apply to each first use condition action and the append to array. In this way your array will have non blank items.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

Unfortunately, I am unable to get the results in a single format to check for null values.

 

The Apply to each using the body of Select or Parse JSON does not iterate through each individual question, it returns a single line of values.

Is there an expression that can break up the body?

I feel I may be overthinking this. There must be others with a Microsoft form that has unanswered fields they want filters out.

 

Passing the body of the JSON to a Apply to each.

pwalshosit_2-1632374582554.png

 

The results of the Apply to each: it's one result :(. 

pwalshosit_1-1632374563567.png

 

 

 

 

@pwalshosit  This is due to your array size which is one.Use Split() and split it by comma that will break your select array and then use in apply to each.

Please do not forget to convert your select array in string because Split() works on string.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

View solution in original post

Thanks for the tip on Split.

 

I have been able to solve my issues by using a list of expressions to clean up each value.

 

  1. I get all Form fields from the action Select
  2. Convert the Array to a String
  3. Clean up the string with a few Replace expressions
  4. Then I Split the string back to an Array
  5. Now I can perform an Apply to each for each array value
  6. From there I run a LastIndexOf expression (lastIndexOf(variables('string'), '":"')
    • This gets the index number of the last character before the answer
  7. Then run a Substring expression by 3
    • This should error if no answer has been submitted for that value because you can't exceed the maximum indexed characters of the value
  8. Results with Errors are skipped and successful results are appended to an array
  9. Now I have an array with all fields that have had an answer 😄
  10. An easy 1 2 3 step process.... 😐

Big ups to Microsoft short sight on not being able to email form results easily.

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,013)