Increase "Count" column in Sharepoint list using Flow

[hungatebevis]

I have a Flow which sends an email when an item is created in a Sharepoint list.  I have another Flow which sends a chaser email out a week later for each item in the list, and also updates field to record the date the chaser email was sent.  Each time the chaser email is sent out it will update this same date field.  I would like to create a Count column to record how may times this chaser email is sent.

I have so far managed to do the following, but this of course only gets it up to "2" and I can't figure out how to make it so it keeps increasing.  I'd also really appreciate ideas for a better trigger than "When an item is created or modified".  Eternally grateful!

[ChristianAbata]

hi @hungatebevis  see this could be solved here

https://powerusers.microsoft.com/t5/Building-Flows/Auto-Increment-ID-duplication-using-flow/m-p/5188...

I if I understhand well you need an autoincremental column.

[hungatebevis]

I think I'm probably being stupid, but I can't follow that solution at all, try as I might.

[efialttes · Accepted solution]

@hungatebevis 

"I have another Flow which sends a chaser email out a week later for each item in the list, and also updates field to record the date the chaser email was sent."

THis second Flow, is it "recurrence trigger" based? THen you added a 'Get items' filtering the results created 7 days ago by means of an ODATA expression or by means of a 'Filter array action block? Then you iterate through your results by means of an 'Apply to each'? If not please share a screenshot from your current flow design

So, assuming your second flow consists of a  REcurrence trigger + GEt items + Apply to each' , the Initialize variable must be before the 'Apply to each'. THen my suggestion is to assign to the 'Update item' 'Count' input the following expression:

add(item()?['Count'],1)

But, from the screenshots you haveshared so far, there is not any 'Apply to each', so I maybe the second flow is 'When an item is created or modified' trigger based, then you added a 7 days delay, then you send the chaser and finally Updates the item? THis would explain the partial screenshot you shared. IF so, the expression should be

add(triggerBody()?['Count'],1)

...but once you adda a 7 days delay, maybe you added a 'Get item' action block, just to decide if you should send the chaser or not...

As you can see, all is guessing... please share a complete screenshot from your second flow design if possible

Thanx!

[hungatebevis]

Hi - yes, absolutely correct:

I'm using formatDateTime(addDays(utcNow(), -7),'yyyy-MM-dd') to find those items for which the email was sent 7 days ago.

I have just tried out your two options in, I think, the correct place, but the Update Item is failing.  Here's a screenshot of what I've done, in case it helps identify my mistake:

[efialttes]

Hi again?

How is your Column 'Count defined' in Sharepoint? Single line of text? Number?

If single line of text please try the following

string(add(int(items('Apply_to_each')?['Count']),1))

If number try with this instead

add(items('Apply_to_each')?['Count'],1)

Thanx!

[hungatebevis]

It's a number type column in Sharepoint, but I just switched it to text and also number and tried both of your suggestions, and both failed.  I took it out altogether and it ran, so it's definitely this that's making it fail.  Can you think of where else I might look for the issue?  Appreciate your help!

[efialttes]

Hi again!

Just guessing... maybe Sharepoint stored the column with an internal name different from 'Count'

Please add a 'Compose' action block just before 'Update item',

assign as the input of this first 'Compose' the dynamic content 'Count' from 'Get items'.

Hover your mouse over the dynamic content and share the name displayed.

Thanx!

[hungatebevis]

Ah yes, good thinking. Sadly though it does say ['Count'].

[efialttes · Accepted solution]

Wait, wait, wat... does 'Count' column have a default value assigned in your Sharepoint List?

If not, try to assign it 0 in all your items that have this column value empty.

THen try with last two expressions suggested

Thanx!

[hungatebevis]

Aha, it works! Actually it can't even be zero. It has to be at least one.  Thanks so much for your help!

[efialttes]

OMG!
HURRAY!
IT WORKS!
 
I've seen you marked this topic as solved. Thanks for your kindness!
This way other community members facing this same problem in the future have the chance to find a solution faster.

Thanx for your contribution to keep this community great!