Finding the Max in a SharePoint List's number column

[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#M...

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-the-max-value-of-Sharepoint-list-...

[Drrickryp]

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.

[rebeccas]

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.

[LVanella]

@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.

[Drrickryp]

@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. 

[LVanella]

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.

[WarrenBelz · Accepted solution]

@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.

[WarrenBelz]

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.