Adding a incrementing number to a SharePoint List fails if the list field is originally blank

[roxy1]

Hi,

I've built a flow that when an email arrives (with a certain subject), a Sharepoint list has an ongoing count for each month for these emails.

The first trouble I had is that the 'apply to each' skips when there is no data in the SharePoint List at all (except for column headings). I dealt with that by adding a row with the month and year to test it (I am unsure of a better fix).

The other problem I have is that if the SharePoint corresponding list column (which is formatted Number) is blank if it is the first email arriving for that month, it won't make the number '1'. It only works when there is an existing number there and adds to it.

Hoping someone can help:

SharePoint List (it only adds to the number when I actually type 1 in there, the blank columns will not!)

[grantjenkins · Accepted solution]

Here's how I'd likely build my flow to do this.

I've created a simple list that uses the Title (renamed to Month and Year) and Total which is a Number column. I'm 

The full flow is below. I'll go into each of the actions.

Compose Month and Year gets us today's Month and Year using the following expression.

formatDateTime(utcNow(), 'MMMM yyyy')

Get items will get items where the Title (Month and Year) is equal to the formatted date from Compose Month and Year. It will return either one or zero results.

Condition checks the length of Get items using the following expression.

length(outputs('Get_items')?['body/value'])

If the length is zero then it will create a new item, otherwise it will get the item and then update the item.

If yes:

Create item will use the output from Compose Month and Year and set the initial Total to 1.

If no:

Get item will get the item returned from Get items. Even though we know only one result was returned, it's stored in an array, so we want to get the first item. We can use the following expression to get the ID.

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

Update item uses the ID and Title from Get item and uses the following expression to increment the current Total by 1.

add(outputs('Get_item')?['body/Total'], 1)

Any questions, please don't hesitate to ask.

[roxy1]

Hi @grantjenkins Thanks again for your help and explanations. When I see your solution it looks like my flow was over complicated! Thank you I really appreciate it 🙂 

[roxy1]

Uh oh @grantjenkins  - when I've tested the flow to add a number to an existing row (the 'no' branch) it fails on updating the item. It says it could not find the list item. I had used your expression and changed the column name from "Total" to what my list column was called.

The issue could be that the "get Item step" is returning the ID as 3, and the 'Update Item" is returning it as 2?!

[grantjenkins · Accepted solution]

In Update item you need to use the ID from Get item and use the add expression for the field you want to update.

[roxy1]

Of course😓!! I think that was me not thinking properly on a Friday! 

[roxy1]

For anyone else who stumbles across this post: I had further issues as I had 3 flows running - each for a separate email that would arrive in the shared mailbox. It would fail when it recognized there was already a line for the month and year but could not add to the blank cell value. I ended up having to edit my list column settings and setting the default of the column to '0' in order for it to add 1 to an existing row:

[grantjenkins]

I actually thought the only way you would add a new row was via the flow which gave it a default value of 1. So, if it already existed, it would have at least 1 already. I didn't realize you already had existing rows in there 🙂

[roxy1]

All ok Grant - I've been adding onto the flow since I first asked for help so you weren't to know! Thanks so much for your help as always 😅