Unanswered

Array output to email, but only not null fields

(0) Share

Report

Posted on by clicker666

I have a flow that starts as an online application for Legal aid services on the Internet in MS Forms. Once submitted it gets processed by a Power Automate flow that reduces the form answers to only the fields with answers in them, then emails that to a mailbox for processing.

Here's my current flow:

In it's current state (not all fields entered) it does produce the right data if you check the output on filter array. I put in a Last name, first name, middle initial, and birthdate - leaving out the maiden name. The result I get is as expected but with odd characters, ["Smith\nJohn\nJ\n\n1999-01-30]" as the body of the email.

What I am hoping to see in the email is:

Last Name: Smith

First Name: John

Middle Initial: J

Date of Birth: 1999-01-30

Any ideas how I get the field names and values appear as above but ONLY for not null value?

Steps:

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @clicker666,

Like this:

Right now you are just mashing all your values in the array with no structure. You have to set it up like this in JSON, write the black letters in by text and reference your forms values with dynamic content within the text.

Then pop the array into a HTML table for example to display it in an email:

✔ Marking my post as a solution not only confirms that your question has been answered, but also helps others find solutions to similar problems.
By doing so, you'll be making a valuable contribution to the community.

Best Regards
Heartholme

Was this reply helpful? Yes No
clicker666 91 on at

Like (0)

Report
Copy link

Link copied!

Close... so close.
I can't quite make mine look like yours. The data looks correct if you examine the output.

But the HTML table throws this error:

The property 'columns' must be specified unless the 'from' property value is an array of objects.

If I put in a couple of headers it shows the headers, but no data.

It does send me an email though with Column1 and Column2 in the form as shown above.

Was this reply helpful? Yes No
Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@clicker666

Indeed, you're very close.

You are missing an " after each dynamic content.

In some cases you don't need to put "" around the items your referencing, for example if it's numbers or if the values come with "" from your source. In your case you need to enclose each dynamic content in a " on each side.

Please try to correct that and let me know that it works 😊

Btw, remove the maiden name line to exclude that.

BR

Heartholme

Was this reply helpful? Yes No
clicker666 91 on at

Like (1)

Report
Copy link

Link copied!

That didn't change it. In your example at the top you have the quotes around the fixed value and not around the field value, and in your last post it's the opposite. In both cases it doesn't work.

The other issue I need to figure out, which was why I had the filter array, was to remove the null values. There are over 100 possible answers on the form, but only some will be relevant. For example if you select you prefer to be called by a certain name that will show up on the email, but if not, it won't. If you pick criminal law, you will list charges against you, family law will show the issue, etc. The output can't show every possible field, because that's about 21 pages long haha, just the populated fields which should fit on 1 to 2 legal size pieces of paper.

Here's what the answers show up as from the form submission, hence the need to prune it down to only not null values:

Was this reply helpful? Yes No
Heartholme 1,278 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@clicker666

Sorry, there seems to be a change in Power Automate, before you could just build your arrays in a compose, but it seems like it recognizes /n line shift now suddenly. I didn't catch that earlier, but you need "" before each property, like "First Name" and each value "John". This worked for me.

JSON (So make your json in one line)

{"First Name" : "Stan","Middle name" : "J","Last Name" : "Smith","Date of birth" : "1971-05-05"}

To filter multiple null values, since you have a lot, you have to first put the values in an array without the properties.

Like this:

The issue now, is that it's hard to put properties on your values, because you won't know which value is which.

If you go the other way and filter the values with properties, the filter array will remove the entire registration since it contains one or more null values.

Hopefully this will help you one step further.

BR

HH

Was this reply helpful? Yes No
clicker666 91 on at

Like (1)

Report
Copy link

Link copied!

Still stymied. I'm wondering if maybe I need to try a different method.

Perhaps create a MSSQL DB for the client applications, move the form results into that SQL DB, and then execute a report generation based off of a SQL query.

That also future proofs us a bit if we want to connect those applications to another SQL based product down the road.

Was this reply helpful? Yes No
clicker666 91 on at

Like (1)

Report
Copy link

Link copied!

Just to update:

The solution is tedious, but basically goes like this:
Create a variable for the body of the email, initially compose using static values. For example, all applicants should have Last name, First name, Middle name. The next optional value is Maiden name - so if maiden name is null, continue to the next part of the form, if maiden name is not null, append the value to the end of the array.

Like I said, it will certainly be tedious to add in all the conditions, but can be done.

Was this reply helpful? Yes No