List SharePoint and send email using PowerAutomate

[Lucas_Ignatius]

When create data on SharePoint List but input based on Department, there is an error flow PowerAutomate because using "Append to String Variable" is can't or empty.

This is my flow

SharePoint List, input based on department and stages, 

Example :

stage 1 : input by marketing and send email to Supply Chain

Stage 2: input by Supply Chain and send email to Procurement

Stage 3 - Final : input by Procurement and send email to Creator 

[Expiscornovus]

Hi @Lucas_Ignatius,

Can you share a screenshot of your flow configuration and specifically how you configured the Append to string variable 2?

[Lucas_Ignatius]

Hi @Expiscornovus 

I attached my screenshoot.

Thank you for helping me

[Lucas_Ignatius]

can anybody help my problem?

[Expiscornovus]

Hi @Lucas_Ignatius,

Your expression looks ok. Can you go to your failed flow run and have a look at the outputs of the trigger action?

Via the click to download you should be able the see the value of the AssignedToProcurement field e-mail property. Can you check if it is not empty over there?

[Lucas_Ignatius]

Hi @Expiscornovus 

Yes, it will be blank or null because is filled by stages.

stage 1 : user created and input data in SharePoint and send email to user assign

stage 2: when user have received email notification from stage 1 , next he / she will be input again on SharePoint List. and next it will be send email again for stage 3

stage 3: user will be received email notification of what data input on user stage 2, user on stage 3 input data on SharePoint List and flow will be sent email notification to user creator in stage 1.

Can you please help me for my case?

[Expiscornovus]

Hi @Lucas_Ignatius,

Yes, but you are using the value from the trigger action. At that point in time it is still empty because the item is just created and you will get this error. 

The design of your flow needs to be changed to support those updates to the item in other stages for it to work.

One approach would be to use a different trigger action at the start of your flow, the When an item is created or modified:

https://docs.microsoft.com/en-us/connectors/sharepointonline/#when-an-item-is-created-or-modified

This way when a person updates the item for stage 2 you can also capture it. You could for instance use a switch control to determine the stage and the related actions for that stage. I would also add a stage field to your list so it is easier to keep track of the stage and it is a field which you can use in the switch control.

Priyesh Wagh has written this nice blog post about using a switch control: https://d365demystified.com/2020/04/29/switch-case-in-a-flow-power-automate/

Drawback of this approach is that every stage of the process has its own flow run. If you don't want that you could have a look at a state machine pattern. There are a lot of examples out there about that. But be aware, that is a bit more configuration compared to the switch control approach 🙂

[Lucas_Ignatius]

hi @Expiscornovus 

I also was tried that but problem when stage 3 input on SharePoint List because email notification will exact same with person on stage 2 input on SharePoint List.

After person on stage 3 input on SharePoint list will be send email notification to user creator

So, for your advice is must use switch control for my case? or do you have any solution for make it easy to me

[Expiscornovus]

Hi @Lucas_Ignatius,

Only changing the trigger action won't work. My proposed approach was to use that trigger action in combination with a switch and a stage field in the SharePoint list. 

Below is an example of what I was talking about. 

[Lucas_Ignatius]

Hi @Expiscornovus 

This my flow, but have a problem for switch value on, what expression if column assign person have value will run and choose stage 3?

[Lucas_Ignatius]

anyone can help my problem?

[Expiscornovus]

Hi @Lucas_Ignatius,

Apologies that my suggested approach wasn't clear. 

In that example I suggested to use a new column called Stage in your list. In my example I was using a single line of text where the default value is Stage1. 

You can use that new field for the On field of the Switch action. I would also suggest to place the switch directly after the When an item is created or modified trigger action.

Within each case you can add the actions you want to execute per stage. And the end of each stage you can place an update item action to send it to the next stage. You can update the value of the Stage column to the new Stage, Stage2 for instance. 

Hopefully this makes it a bit clearer?

[Lucas_Ignatius]

Hi @Expiscornovus 

The stage you mean is choose by user or automatically can change from stage 1 and then next to stage 2 and next again to stage 3?

and how can be change from stage 1 to stage 2 in SharePoint? is automatic or manually by user to change stage?

[Expiscornovus]

Hi @Lucas_Ignatius,

The Stage column is just a helper column to track the correct stage value. The user doesn't have to update it manually in this proposed approach.

When a new item is created the value will automatically set to Stage1 because it is the default value of the column. And in the flow we are updating the value after the steps at the end of the case via the update item action from Stage1 are completed to Stage2.

[Lucas_Ignatius]

Hi @Expiscornovus 

I was tried, and is error when input in stage 1, and then when on stage 2 email always continuously send email.

This email continuously send when user on stage 2 input SharePoint List

[Expiscornovus]

Hi @Lucas_Ignatius,

After a quick look at your screenshot I noticed that the Stage field is empty in the second Update Item. I would expected that to be set to Stage3. If it keeps in Stage2 and you keep updating the item it will go vicious circle. You don't want that 😅

In addition to this approach it might be useful to add a trigger condition to the trigger action. To exclude update item events from the account running the flow (service account) and only trigger the flow when users update the list items.

[Lucas_Ignatius]

Hi @Expiscornovus 

Thank you for always support, but this have still problem at stage 2 where user input sharepoint email in Stage3 is send too,

Can you suggest a idea about this? because stage3 will be run if user stage3 (procurement) input in sharepoint

My flow

Email receive in same time (stage2 and stage3)

[Ramiz]

Hi @Lucas_Ignatius ,

When the Update Item is running, it's triggering the flow again. As @Expiscornovus mentioned, you need to add a trigger condition in the flow so that it doesn't run from the service account. 
Try adding this condition in your trigger condition.

@not(equals(triggerOutputs()?['body/Editor/Email'], 'sharepointadmin@abc.com'))

Change the email in this condition to the email that flow is using. 

[Lucas_Ignatius]

Hi @Ramiz and @Expiscornovus 

I was tried and email change to be my email because I was created sharepoint and flow , but when I test flow is not running and email still not receipt even in stage 1.

And then I test again with another email is still problem at stage2, which Stage 2 input on sharepoint email in Stage3 is send too

[Ramiz]

Hi @Lucas_Ignatius ,

Please check whether you have any other flow that is sending the email.

You can check the run history of the flow as well after Stage 2 that how many times it is running. It should run only once.