Total length of a column that is filtered from an array and contains two values

[Julien2]

Hello,

Recently I have created a flow that filters the active cases from Case entity in CRM.

In this entity I have a "Country" column, this column might be the US or Canada.

After I get the data from the filter array, some cases might have these country labels.
My filter array will only look for Status labels that are equals to "Active".

What I want to achieve is the following:

I want to show the count/length total for each country.

For example:

Title        Country
        
TEST          US
TEST2         Canada
TEST3         Canada
TEST4         Canada
TEST5         US

The Output of the length will be for example:
The total active cases in the US are 2.
The total active cases in Canada are 3.

Can please someone explains in detail and provide an example of what I should use actions and expressions to achieve the following scenario?

Any help will be greatly appreciated.
Thank you!

[efialttes]

Hi!

You already got this info in an array of objects like the one below, and you want to show totals per Country, right?

[{"Title": "TEST", "Country": "US"},{"Title": "TEST2", "Country": "Canada"}, {"Title": "TEST3", "Country": "Canada"},{"Title": "TEST4", "Country": "Canada"}, {"Title": "TEST5", "Country": "US"}]

If you always have this two countries, then you just need to filter by each of them and apply length() to the output of each filter to get the totals per country.

But, if the list of countries changes from time to time, I think it is better to build a dynamic dictionary by means of a Select ad iterate thorugh it with an Apply to Each... You have a consolidation example in the following thread

https://powerusers.microsoft.com/t5/Building-Flows/Consolidated-email-based-on-date-in-excel/m-p/471...

In such thread, instead of Country, we are consolidating by Leader email address, and instead of showing Totals per country we are building a sending an email per email address

Did I understood your requirements properly? If not, please feel free to correct my wrong assumptions

Thanx!

[Julien2]

Hello @efialttes ,

Thanks for the explanation.

Mainly, I don't always have these two countries (But I have given this as an example), I have many countries and each case might contain a different country label as you mentioned in the second option.

The thread you mentioned is very helpful, and I have created the select action and mapped the "Country label" into it and as a result, it returns [{US,Canada...}].

After I have received the results of the select action I am stuck based on what I should filter to show later in the email the length per country label of the active cases filtered.

I would be most grateful if you could provide a screenshot of this scenario so I can understand this perfectly.

Please have a look at this screenshot:

No need to correct you have understood my scenario. 😉

Looking forward to your response.

Thank you!

[efialttes]

Sure!

Is it OK if I use as Select input the following array format?

[{"Title": "TEST", "Country": "US"},{"Title": "TEST2", "Country": "Canada"}, {"Title": "TEST3", "Country": "Canada"},{"Title": "TEST4", "Country": "Canada"}, {"Title": "TEST5", "Country": "US"}]

Or you prefer share an example of your current array format?

Thanx!

[Julien2]

Hi @efialttes ,

Actually, in the select action, we need only to get all the country values that are filtered so we don't need the titles.

I would be most grateful if you share an example of the country format without titles because, in the end, I will display in the email the total length per country of the active ones.

Looking forward to hearing from you.

Thank you very much for your time and support!

[efialttes · Accepted solution]

@Julien2 

Here you are my Flow design. Please note I am using as Select input the array format I suggested in my previous example

The compose at the bottom of my flow is a dummy one, i.e. just to print the final values stored in variable 'currentConsolidatedResults'

Hope this helps

[efialttes]

Hi again!

Here you are the execution results of my flow:

Please note these are the partial outputs:

Select outputs:

[
  {
    "Country": "US"
  },
  {
    "Country": "Canada"
  },
  {
    "Country": "Canada"
  },
  {
    "Country": "Canada"
  },
  {
    "Country": "US"
  }
]

Compose ConsolidatedCountries output (once union() did the magin to generate dynamicaly the Dictionary):

[
  {
    "Country": "US"
  },
  {
    "Country": "Canada"
  }
]

Since this output is an array with two objects; Apply to Each iterates twice, one for {"Country": "US"} and one for {"Country": "Canada"}

Hope this helps

[efialttes]

Hi again

Expression details, step by step:

Inside the Apply to Each:

If you need further assistance, please let us know!

[Julien2]

Hello @efialttes ,

Thank you very much for providing a detailed flow design.

I have followed what you did step by step, and after launching the flow, unfortunately, I have an issue in the filter array where I am mapping into it the following:

The error message of this filter array:

The country field type in this entity is "Option Set", in this filter array I have set this country field equals to the country field again.
(I think the logic is wrong here because they will never be equals).

Besides, the other actions return the right results as expected please have a look at these screenshots:
Entire FlowUnionSelect

What I am doing wrong in the filter array?

Looking forward to your response.
Your help is highly appreciated.
Kind regards,
Julien

[efialttes]

Hi!

On Flow Edit mode, please go to 'Filter array 2', click on 'Edit in advanced mode' and share with us the expression that will be shown

Thanx!

[efialttes]

Wait, wait wait....

I think the problem is the input you are currently using for "Filter Array 2".

I would use the output from 'Filter Array' instead

Hope this helps

[Julien2]

Hi, @efialttes ,

I have changed the expression back as you mentioned it and the same problem occurs please have a look here:

Where do I have to map the output of the filter array? Because if I have mapped inside "From" the body of the filtered active cases, as a result, it will generate all the records with all the columns details knowing that we only need the result of the countries in the select action.

Looking forward to your response.

Thanks!

[efialttes]

Hi!

Look at the orange arrow:

Use 'Filter array' output as input for your 'Filter Array 2'

Hope this helps

[Julien2]

Hi @efialttes ,

Unfortunately, the flow fails again with the same error message even when I've used the filter array output for the 'Filter_array_2'.

As a result, it returns all the records filtered from this array.

Please have a look at the screenshots below:

Looking forward to your response.
Thank you!

[efialttes]

Hi!

Well, we are closer to a happy ending.

Now the input of 'Filter array 2' is the right one, we need to work on the condition that is evaluated inside it.

Please click on 'Edit in advanced mode' and share the expression that will be shown, please.

You can get back to current Filter Array view by clicking again 'Edit in basic mode'

I nedd some additional info: hover your mouse over the Select Map element and share me a screenshot showing its content

Thanx!

[Julien2]

Hi @efialttes ,

Yes, we are very close hopefully.

Does this mean I have to keep the "Body" of the "Filter_Array" in the "Filter_Array_2"? or I have to add again the "Output" of the select in the "Filter_Array_2" as input?

Please find below the expression of "Filter_Array_2":

@equals(item()?['Country'], items('Apply_to_each')?['Country'])

This expression is the same one as you mentioned before. (The dummy variables)

Addition information of the select action:

I am using this expression inside the select action to get the labels of the countries filtered from the active cases:

item()?['_new_country_label']

Looking forward to your response.
Thanks.

[efialttes]

Hi again!

If I understood properly your flow design, you should remove current expression from 'Filter Array 2' and apply this one instead:

@equals(item()?['_new_country_label'], items('Apply_to_each'))

Please tell us the result after applying this suggested change

Hope this helps

[efialttes]

Hi again!

I just forgot to mention you need to replace current content of your 'Append to array' and add this expression instead:

concat(items('Apply_to_each'),' is present ',length(body('Filter_array_2')),' times')

Hope this helps

Thanx for your contribution to keep this community great!

[Julien2]

Hello @efialttes ,

Thank you very much for your support and your time.

Have a nice day!

Regards,
Julien

[efialttes]

Hi again!
One more thing.
Let's suppose that a case created with Spain (country), which means "The total active case in Spain IS 1." Again let's say a new case opened in the same country, so "The total active case in Spain ARE 2."

This means you should modify a little bit your current expression in 'append to array variable' action block. So instead of:

concat('The total active case in ', items('Apply_to_each'),' is ',length(body('Filter_array_2')),'.')

...you should use:
concat('The total active case in ', items('Apply_to_each'),if(equals(length(body('Filter_array_2')),1),' is ', ' are '),length(body('Filter_array_2')),'.')

Hope this helps