Answered

Reading SharePoint list and sending email dynamically

(0) Share

Report

Posted on by Sivaperumal31

Hello,

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

Transaction list.JPG

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.

Country list.JPG

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

Categories:

Building flows

I have the same question (0)

All responses (13)

Answers (1)

v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Sivaperumal31 75 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Sivaperumal31 ,

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

Best regards,

Was this reply helpful? Yes No
Sivaperumal31 75 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
Sivaperumal31 75 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Sivaperumal31 ,

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

BR,

Was this reply helpful? Yes No
Sivaperumal31 75 on at

Like (0)

Report
Copy link

Link copied!
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-integer-index/m-p/179168#M18410

Another issue: Feedback will be appreciated, Less priority anyway.
I ran the first flow to update entries in transaction list - 4 entries.
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

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Sivaperumal31 ,

Sorry, the expression should be:

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

BR,

Was this reply helpful? Yes No
Sivaperumal31 75 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No