Increment a field value based on last entry in a list

[shanemeisner]

In PowerApp I have a number field(TPID) set with the following Formula: 

If(SharePointForm1.Mode = FormMode.New,First(Sort('Test Procedures',TPID,Descending)).TPID+1,TPID)

This works perfect if they ceate a new item from the view. It gets the last entry in the list that has a TPID and assigns the next one in line.

But, users would like to use the "Quick Edit" mode to populate a lot of data into the list. If they populate the data and then edit the item, then the field gets updated as it should.

I'm wondering if it would be possible to set the TPID field through a Flow on new items that are added through Quick edit, so users don't need to manually edit the form to update the ID field.

[abm]

Hi @shanemeisner 

Yes it is possible. Believe this is a SharePoint list data. If it is then you can trigger via When an Item is created. Then use the list items order by desc TPID and set the count as 1 which will give you the latest TPID. Then increase the current value. Finally use the update list item to update the TPID using the Id from the trigger when item is created.

[Gristy]

Yes you could do a Get Items, sorting descending on TPID and then add 1.

But could you just use the built in ID of the SharePoint list, then you know they are unique.

[shanemeisner]

Thanks for the reply @abm  & @Gristy  I'll review the flow this morning to see if I can get it working.
For more background on why we have a "New" ID field and are not just using the built in ID field for Lists...

We are building an app that will consist of two lists and a Document library. The first list "Test Procedures", will hold test procedures for audits.

The second list will be for Evidence Requests, which will tie to the Test procedures. When an audit is requested, then the test procedures will be tied through a "TP Number" field in Evidence requests to retrieve a few columns of data for the Auditors to review in the evidence Request item, and they will need to be able to sort on the TP number column to see how many times that Test procedure was used for auditing(there are over 250 TP's).

When I tried to use the defualt ID in the TP list, it brought back more then just the number. Thus the need to create a "second" ID field.

[abm]

Hi,

Thanks for the update. If you need any further help please update the post.

[shanemeisner]

@abm @Gristy  Ran into a roadblock.. I set my "Get items" to be descending based on the TPID number field:

and that part is working as the current last TPID is 11:

But when I try use the compose to increment it by one:

and run the flow, it fails with this error:

InvalidTemplate. Unable to process template language expressions in action 'NewTPID' inputs at line '1' and column '2636': 'The template language expression 'add(triggerBody()['TPID'],1)' cannot be evaluated because property 'TPID' doesn't exist, available properties are '@odata.etag, ItemInternalId, ID, Title, Modified, Created, Author, Author#Claims, Editor, Editor#Claims, {Identifier}, {IsFolder}, {Thumbnail}, {Link}, {Name}, {FilenameWithExtension}, {Path}, {HasAttachments}, {VersionNumber}'. 

Any thoughts?

[abm]

Hi @shanemeisner 

Try the below syntax and let me know.

triggerBody()?['TPID']

[ScottShearer]

@shanemeisner 

You can set the top count to one so that only one item will be returned.  While you are in a loop, it won't matter because it will only loop once.

[shanemeisner]

@abm Thanks, I was missing the "? .. But that now brought back a new error:

InvalidTemplate. Unable to process template language expressions in action 'NewTPID' inputs at line '1' and column '2639': 'The template language function 'add' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#add for usage details.'.

I think I see the issue, but not sure how to "fix" it..

The Condition is checking the "TPID" from the newly created item which is equal to "Null".

So, it seems the TPID value for the Compose expression is the "New item" TPID value, rather then the "GetItem" TPID value. Not sure how to force the compose expression to use the TPID value that was found in the "Get Items".

[ScottShearer · Accepted solution]

@shanemeisner 

try this:

first(body('Get_items')?['Value'])?['TPID']

[shanemeisner]

Thanks @ScottShearer  that was the missing piece!!