Reading SharePoint list and sending email dynamically

[Sivaperumal31]

Hello, 

I have a SharePoint list (Lets call it Transaction list) sample below containing various transaction data of clients with country column.

 

I have to send email to different country heads whenever new transaction entries are added in share point list for their country.

Email id of country managers are maintained in another share point list (Lets call it Country Manager list) which contains country and email ids of managers of that country. 

  

I managed to build the flow in power automate to read transaction list and send email to fixed email ID so far.

But I want to read Country Manager list also and somehow look up in power automate and send email to those managers with list of transaction entries from transaction list of respective country dynamically. 

• Both lists have country as common column
• Transaction list may contain multiple customer lines with same country 
• And Country list may contain multiple entries for the same country as there might be more than one manager.

Appreciate your insights on how Can I achieve it. 

Thank you.

@grantjenkins , FYI 

[v-jefferni]

Hi @Sivaperumal31 ,

You can use new entry's Country for Filter query of Get items from Country list. Then, use a Condition control to check if there are more than one manager. 

Best regards,

[Sivaperumal31]

Thanks for the response @v-jefferni , 

This is to get the unique email for country managers 

But my requirement is to read Transaction SharePoint  list first  and get corresponding email ids from another SharePoint list based on country and send email with content from transaction list

[v-jefferni]

Hi @Sivaperumal31 ,

The trigger is for Transaction list, when a new entry added into this list. 

Best regards,

[Sivaperumal31]

Hi @v-jefferni , 

Got it now , thanks . 

But I receive an error at set variable step with expression at runtime like below. 

Unable to process template language expressions in action 'Set_variable_2' inputs at line '0' and column '0': 'The template language expression 'first(outputs('Get_items_3')?['body/value']?['Managers'])' cannot be evaluated because property 'Managers' cannot be selected. Array elements can only be selected using an integer index.

Please note that my trigger is not When an item gets created as in your example, But Get items from Transaction list, Not sure if its relevant.

Thank you.

[v-jefferni]

Hi @Sivaperumal31 ,

It is exactly where the issue comes from. The example flow was built on the requirement you mentioned:

"I have to send email to different country heads whenever new transaction entries are added in share point list for their country."

So, I cannot understand the trigger Get items from Transaction list. Could you please explain further?

Best regards,

[Sivaperumal31]

Hi @v-jefferni ,

Apologies for not being clear earlier. 

Earlier I have built single workflow to read from excel and update in share point (transaction list) and also tried reading same list and send email using your solution. 

Now I have separated the workflow.

1 Created One flow to read excel and update share point transaction list alone.

2. Second workflow to read country list and send email dynamically whenever transaction list is added with new entries following your solution.

I still get the same error though , at set variable step.

And create 

[v-jefferni]

Hi @Sivaperumal31 ,

What column type is the Managers column? The error message indicates it's not a single line text column.

BR,

[Sivaperumal31]

Hi @v-jefferni , 

Manager column is defined as single line of text. 

• Also I created to fresh share point list for country and tried again but receiving the same error. 
• Not sure if its relevant, but when I key in the JSON expression in set variable , below highlighted items were not populated from help list, I had to type in manually , instead of choosing from drop down help list.
• .
• Found below thread for almost same issue, but i could not figure out the solution 

https://powerusers.microsoft.com/t5/Building-Flows/Array-elements-can-only-be-selected-using-an-inte...

Another issue: Feedback will be appreciated, Less priority anyway.

1. I ran the first flow to update entries in transaction list - 4 entries. 
2. Once its completed, Sending email flow by reading country list was triggering automatically 4 times for every entry added in transaction list (which is expected).

But what if I have multiple entries for the same country in transaction list, And I don't want to send email twice or more for every entry added in transaction list for same country. Is it possible?

Thanks

[v-jefferni]

Hi @Sivaperumal31 ,

Sorry, the expression should be:

first(outputs('Get_items')?['body/value'])?['Managers']

BR,

[Sivaperumal31]

Hi @v-jefferni , 

Many thanks, it worked now for True condition now , (For single manager for one country)

But I get the same error from false condition when more than one managers are updated for given country in County list . 

I tried modifying the json expression for false condiiton, but its not working. 

[v-jefferni · Accepted solution]

Hi @Sivaperumal31 ,

Please add a Select operator above Set variable 2:

BR,

[Sivaperumal31]

Thank you so much @v-jefferni , Amazing. It worked now. 

[JGBT]

Could you maybe help me with my problem good sir?