Finding the Max in a SharePoint List's number column

(0) Share

Report

Posted on by LVanella

The approach I'm trying to take is:

1. Create a collection of this number column's contents

2. Apply the Max function to the result

3. Set a variable equal to this value plus one, so it's effectively the next number in sequence

4. Pass this variable (and some other stuff) into a Flow for processing

My issue is that no matter what syntax or combination of commands I use, the Flow is receiving a "True" instead of some number. Here's the script:

ClearCollect(colReqNums, ShowColumns(PurchaseReq_1,"ReqNum"));
Set(varThisReqNum,Max(colReqNums,'ReqNum')+1);
PurchaseReq.Run(NewReqJSON,varCurrentUserEmail,varThisReqNum;

and alternatively

Set(NewReqJSON,JSON(varNewReq));
ClearCollect(colReqNums,Distinct(PurchaseReq_1,"ReqNum"));

Set(varThisReqNum,Max(colReqNums,Result)+1);

PurchaseReq.Run(NewReqJSON,varCurrentUserEmail,varThisReqNum;

PurchaseReq_1 is the data source name for my List. ReqNum is the column name.

Can someone please school me on whatever I'm missing? This general approach seems to work for others. I have looked over:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Get-the-max-value-of-my-gallery/m-p/601928#M189245

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-the-max-value-of-Sharepoint-list-column/m-p/386562#M112491

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @LVanella

I think you are making this more difficult that it has to be. First(Sort(PurchaseReq_1,ID,Descending)).ID+1 will give you the next ID in the sequence. I'm using ID as an example. ID can be replaced in the formula by any number column. This formula is delegatable ,Max() and Last() are not in SharePoint.

Was this reply helpful? Yes No
rebeccas 2,591 on at

Like (0)

Report
Copy link

Link copied!

If you are using a SharePoint list you can just look for the last ID which will give you the one at the end. To do that you will use: Last(SPListName).ID

You can set a variable from that so OnVisible: Set(varNumber, Last(SPListName).ID+1) and that will be the number you start with.

Was this reply helpful? Yes No
LVanella 8 on at

Like (0)

Report
Copy link

Link copied!

@Drrickryp : Well normally I don't like making things difficult, but PA is telling my the connector doesn't support the commands...First included. That's why I went down the road of making a collection first, thought I had to.
Following your suggestion, saving and republishing, I still end up with this in Flow

Suppose I can remove the references and disassociate the PA and Flow, then redo...because this isn't right. It was never a boolean.

@rebeccas that is a good approach to something else I'm working on. In this case, multiple records might have the same ReqNum, so a direct Last approach isn't quite what I want here, as ReqNum and ID will diverge pretty quick.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@LVanella you can safely ignore the warning because First() will only pick the first record in a table so delegation is never an issue. Try it and see. If the warning really upsets you, put my formula in a With statement

With({varThisReqNum:First(Sort(PurchaseReq_1,ReqNum, Descending)).ReqNum+1}, <someformula using varThisReqNum>, )

and use it as a temporary variable in another formula. I do not use Flow so I can't help you there.

Was this reply helpful? Yes No
LVanella 8 on at

Like (0)

Report
Copy link

Link copied!

Unfortunately it still wouldn't work in a direct call to SPO. I do have it functional now after starting with that column in a collection, then applying the compound function to it; the expected number is being passed along.

Thanks for the suggestions, much appreciated.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 155,644 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
@LVanella ,

Assuming your column ReqNum is Numeric, this will return a numeric Variable of the maximum ReqNum plus 1 and is Delegable

With( { wReq: Sort( PurchaseReq_1, ReqNum, Descending ) }, Set( ThisReqNum, First(wReq).ReqNum + 1 ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 155,644 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @LVanella ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No