Automatic Sequence number in Power Automate and sharepoint

[Anonymous]

Hello,

I am fairly new to Power Automate so please accept my ignorance.

I have created a flow which is triggered by an entry in a form and adds a record to a sharepoint list. It works fine!!

Yet I want to automatically create a sequencenumber. 

The solution I'm trying now is that I have a separate Sharepointlist with two columns :

Sequence_name and  Last_sequence_number. 

Intention is that

1. the row is selected with the power automate action : GET ITEMS With Filter on specific Sequence_name
2. Add 1 tot the last_sequence_number
3. Store last_sequence_number in variable
4. Write back the new last_sequence_number
5. Add item to sharepointlist filled with formdata and variable.

I have a problem with what to do after step 1.  I do not seem to get correct data

Questions :

• Is the algorithm I have chosen ok? If yes how shoud I solve it
• Is there another way (Maximum??) to automate this

Hope someone can help

[Expiscornovus]

Hi @Anonymous,

Just to double check, your last_sequence_number variable is of type integer? If so, you could use an increment variable action to add 1 to the last sequence number value.

Hope this helps a bit. And let us know if you need any additional help.

[tom_riha]

Hello @Anonymous ,

do you have just 1 sequence or multiple sequences? If it's just a single sequence you could use the standard SharePoint item ID which is incremented by 1 for each new item in the list.

Otherwise your algorithm looks fine, I'd just add sorting into the 'Get items' action (by the sequence number in descending order), and top count 1 (to return only the last item with the highest number).

Order By: last_sequence_number desc
Top Count: 1

Then you can use the 'Increment variable' action as mentioned by @Expiscornovus on the only item in the returned array.

[AHCIJburg]

Hi,

Thanks. It is not the increment I am struggling with, but to fetch the right result from step 1.

1. the row is selected with the power automate action : GET ITEMS With Filter on specific Sequence_name

How to I fetch the right data.

kg

Vic

[Anonymous]

Hi Tom,

When I use the Id something strange happens to my calculated field Demo. As you can see in the first picture the Column Demo has Demo-1 and Demo-2 [="Demo-"&Id]. When I edit the record and change the title the calculated field loses the [Id]. Same thing happens if I create a new list-entry. 

The calculated fields will not become correct until I actually reset the calculation in the list-editor.

Any idea what might be wrong?

kg

Vic

[tom_riha]

Hello @Anonymous ,

I don't use calculated fields in the modern SP so I don't know what could be the problem. I'd rather put the whole string into a 'Single line of text' field or use JSON formatting on a column to build the string that way.

[Anonymous]

Hi Tom,

When using JSON something similar happens. If I want to format a column : "Id number : "++[$Id]" It does not work. If I choose another colum in stead of Id. It does work. Any thoughts?

kG

Vic

{
"elmType": "div",
"txtContent": {
"operator": "+",
"operands": [
"Id number :",
"[$Id]"
]
},
"attributes": {
},
"style": {
},
"customRowAction": {
},
"children": []
}

[tom_riha]

Try to use uppercase ID, [$ID] instead of [$Id].

[Anonymous]

Hi Tom,

That indeed does the trick. Thanks. One last question, then I won't bother you anymore (regarding this subject) :

Normally I add 4 zeros to the ID and only use the RIGHT 4 characters of that string so the values in the column could look like:

JSON Colunm    ID

VS 0008              8

VS 0009              9

VS 0010             10

VS 0011             11

Would you know if that is possible in JSON-Sharepoint

KG

Vic

[tom_riha · Accepted solution]

Hello @Anonymous ,

you can use padStart() function for that, 1st parameter is the text (ID), 2nd parameter is how many characters it should have in total, 3rd parameter is the character that should fill it to that number.

{
    "elmType": "div",
    "txtContent": {
        "operator": "+",
        "operands": [
            "Id number :",
            "=padStart([$ID], 4, '0')"
        ]
    }
}