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

(0) Share

Report

Posted on by EBMRay

1,712

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!

Categories:

Building flows

I have the same question (0)

All responses (20)

Answers (1)

Sort by

efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!
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/471253#M57145

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!

Was this reply helpful? Yes No
EBMRay 1,712 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!
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!

Was this reply helpful? Yes No
EBMRay 1,712 on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

Hi again

Expression details, step by step:

Inside the Apply to Each:

If you need further assistance, please let us know!

Was this reply helpful? Yes No
EBMRay 1,712 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No