Combining multiple Compose actions into a single array ignoring null values

[Gwill1983]

Hi all,

I have a form which contains a multiple answer response. In order to use the individual answers from this response, I have setup multiple (approx 20) compose actions containing if functions to extract each answer if they appear in the form response array.

What I am now trying to do is combine these into seperate arrays to email various members of my team if they are responsible for that action.

For example, Bob may be responsible for all forklift training requests. In the form response, I want to use the compose actions relating to forklift training and combine these into an array to send Bob an email for him to arrange the training. 

However, there wont always be a value in the compose action so I only want to combine the compose steps that have a value, and ignore the nulls completely.

Can anybody help?

[wskinnermctc]

First, I would not use 20 Compose steps, mainly because it is confusing when trying to put the values elsewhere in the flow. If you had to do it like that, I would replace each one of the Compose with an Initialize Variable as String and then use those variables elsewhere around the flow. So you can individually name each variable and it appears in the Dynamic Content options and other steps with the name.

Even then, I don't think I would use 20 variables. That is trying to deal with too many things.

You should design the flow so that it deals with the different things with the same steps. So I would start by putting the question responses into a single array, and then Apply to Each on that array.

So for each training type they select do actions to that value. This is where you might have to use a Switch or Conditions.

Here is a quick example showing putting the multi-choice response into an array. 

Form QuestionsExample to put multi-choice response into array.Example Results each training is a single Apply to Each item.

Could you think about the process and what you want to accomplish by using this method instead of 20 separate Compose steps?

[Gwill1983]

Thanks for the response. I was hoping that somebody would highlight how silly my solution was because it seemed very long winded. I just couldnt think of another way!

With this solution, how would I then seperate the individual answers.

For example, if Bob was in charge of chain saw & airplane training and Dave was in charge of forklift and crane? I need to construct an email with a list of the training requirements per response.

[wskinnermctc]

That is where you would use a Switch or Condition to break off what steps you want to happen. It also can depend on how you are getting Bob and Dave's email into the flow.

Here is a simple Switch and in each Case is where you would format an email for the training type. (Note that if Bob is Forklift and Crane trainer. And the form response contains Forklift and Crane training, then Bob would receive an email as Forklift trainer and then a second email as Crane trainer since this is acting on each response value.)

Example Switch

You could have up to 25 cases in a Switch. And the Equals value in the case must match exactly to the value that will be compared to in the Switch.

[Gwill1983]

Thanks. 

That switch function is just going to end up with 20 steps with 20 seperate emails isnt it?

I need to combine them into one email based on who is responsible so maybe this isnt a solution that will work

[wskinnermctc]

It doesn't have to be exactly like this.

I just wanted to show you that it will be better to put the responses into a single array and manage them like a single object. That was the initial question of your post.

If you want to work on a full solution, you need to describe a plan of all the things you have and are trying to do in detail. Such as data sources, how many trainers, what are other form questions, are you putting data into a list, email body differences? Show what you have built and what all you need.

Really would need to know more about the trainers, I don't know if you have 40 people needing notifications based on groups of trainings? How are these grouped? Is there a source of this trainer roster?  

[Gwill1983]

Thanks again,

These will be grouped by either HR organising, or Line Manager organising. 

There is no source for these so I am aiming to manage the distribution in the flow. The ideal for me would be that once the responses are seperated I am able to put 2 arrays together based on which courses are managed by HR and which are managed by Line Manager.

In your example, I would want to use the outputs from your steps to send 1 email to Bob with ALL training courses he is responsible for, and 1 email to Dave with ALL training courses he is responsible for. 

I can setup an excel spreadsheet to allocate the training courses if it makes it more straight forward?

[wskinnermctc]

Yes that visual would be very helpful.

[Gwill1983]

Hi.

These are the courses and the responsibilities. 

I like the way the flow is working currently with the array being seperated, but unsure how I can then group those individual outputs based on the responsibility rules to construct an output that can be used in an email. 

[wskinnermctc]

Could you just have 2 form questions to select training instead of 1? All manager training in one question, and all HR training in another question? Then you could easily email one or the other based on which questions had responses.

Otherwise all of those courses are going to have to be written into the flow or pulled from a reference sheet each time so that it knows where to check and send an email. I can do this too, but then you are trying to manage matching a reference source instead of simply changing the form questions.

[Gwill1983]

Unfortunately not. It would be too confusing for the end user as it is part of a very large form. 

I believe I have found a way to do this using the multiple compose actions as previously. I have put them all into a Scope so that it doesnt swamp the flow and have created a array for each based on which courses are organised by each. 

Had to do a bit of format tidying etc, but it does work. 

Not very dynamic if more options are added in future so not ideal!

[wskinnermctc]

Give me a minute and I'll try to make something easier.

[wskinnermctc]

Ok here is an example flow. The key is making two initial Arrays that will hold the list of courses each for Line Managers and for HR.

The question response is put into an array like it was previously, but now the individual item is compared to the Line Manager course list and the HR course list to see if it is included. Basically does it match any of the courses listed in either.

Then there are two more arrays that are like lists that will be used for the emails. When the item is compared to the source arrays and matches, then it will be added to that email list. 

Then the email lists are formatted as HTML tables and inserted into the emails.

A couple of notes:

• This will run if the question is blank. But don't worry about that now. I didn't want to clutter this example up more than necessary. You could easily insert another condition before sending either of the emails to see if the EmailListArray it has items or not. So yes or no send an email.
• I have made two separate conditions in order to compare the response to the course lists. So it is being compared to both. You could change this condition or even delete one since it would basically be if it doesn't match one then obviously it matches the other. However, I created separate conditions so it is more transparent and it could help identify question texts that are incorrect or not matching either the source course lists. 

Here is an overview of the questions from the form and overview of the flow:

Form QuestionsFlow Overview A summary of the flow:

• Get Response Details
• Initialize Line Manager Course Array that holds source list of courses assigned to Line Manager.
• Initialize HR Course Array that hold the source list of courses assigned to HR.
• Initialize Form Response Array that will have the form question responses put into the MultiResponse Array.
• Initialize two variables array that are blank and will be used later:
  • Email Line Manager Array - used for the Line Manager Email
  • Email HR Array - used for the HR Email
• Compare each value in the MultiResponse Array to the 2 source course arrays.
  • If the value matches a course in the Manager list, then add that value to the Email Line Manager Array.
  • If the value matches a course in the HR list, then add that value to the Email HR Array.
• Compose - HTML Table Style is just code that makes the email look better, you can format it with CSS
• Create an HTML Table for Line Manager - this uses the Email Line Manager Array
• Create an HTML Table for HR - this uses the Email HR Array
• Send Emails to Manager and HR with their tables. Insert the Compose HTML Style before each HTML table in the email body.

Here are detailed screenshots of the flow:

Flow Detail 1 - Create Source Arrays

You have to manually put in the courses and be sure that the names match the responses in the MS Form exactly. (This could be replaced with a SharePoint or Excel source list)

Here is my LineManagerCourses Array you can copy and paste:

[
  "Caliq Overview",
  "Caliq Sales Ordering Processing",
  "Working at Height - Construction",
  "Ladder Training",
  "IPAF",
  "PASMA",
  "Site Management Safety Training",
  "Site Supervisor Saftey Training (SSSTS)",
  "Construction Skills Certification Scheme (CSCS Card)",
  "Forklift Driver - Counter Balance",
  "Forklift Driver - Reach Truck",
  "First Aid at Work (3 Day Course)",
  "Zoho Training"
]

Here is the HR Course Array you can copy and paste:

[
  "Working at Height - Basic",
  "UKATA Asbestos Awareness",
  "CDM Awareness",
  "Occupational Driver Training",
  "Driving for Business",
  "COSHH - Dangerous Substances",
  "Emergency First Aid at Work (1 Day Course)"
]

I used your Excel list as a reference, but I did capitalize all of the words, so if your form is different you need to change those values.

There is a double space before each course name that is in "quotes" and a comma afterwards. Do NOT put a comma after the last course name (I always forget to remove the last comma and it makes an error).

Here is the step that puts the response question into an array no different than one of the previous post examples:

Flow Detail 2 - Create Response Array

Here is where the values of the Form MultiResponse Array is compared to each of the source lists. Then if it matches it is added to the corresponding email array.

Flow Detail 3 - Compare Response to Course Lists

Adding Zoomed Photos of the Apply to Each Conditions:

Flow Detail 3 Zoom 1Flow Detail 3 Zoom 2

This is the HTML Table Style code. This will be used for both email tables. It doesn't have any specific value reference within the flow and stays the same no matter what else you change. There is a good explanation about CSS Table Formatting at this link. 

Flow Detail 4 - Format CSS STYLE

Just copy and paste this into your flow: (I copied it from this example and removed the width 100% line.)

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  text-align: left;
  border-collapse: collapse;
}
table td, table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table tbody td {
  font-size: 13px;
}
table thead {
  background: #1C6EA4;
  border-bottom: 2px solid #444444;
}
table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
  border-left: none;
}
</style>

Now you use the Create an HTML Table and the arrays that you inserted values into when using the conditions: 

Flow Detail 5 - Create HTML Table and Email

Put the CSS Style output directly in front of the Create HTML Table in the email to apply the style to that table.

Below are the Email results if every option was selected in the form.

Email Results

I think this is a good method because you can easily match the form questions to lists in the flow. You could replace the manual array lists with an Excel or SharePoint source list with a couple of extra steps and the flow would remain the same. (Basically you would List Rows or Get Items for Manager courses and put it into the array instead of typing, and then you would have a second List Rows or Get Items for HR courses and put it into the array for HR.)

Dealing with a non-response just requires adding conditions before you create the emails. The condition would check if the EmailLineManagerArray is equal to empty() and if so then don't send an email or whatever. Same goes for adding a condition to check if EmailHRArray is equal to empty() if so then don't send an email.

Try to put this together and let me know if it works for you,