You could loop through each value and compare if the current value is greater than the last and if so, set the greatest value as the value.

You could try using a SortedList object or an ODBC RecordSet where you could use either the MAX aggregate or ORDER BY clause, with either, you will likely need to use them in conjunction with the Left, Right, or Substring scalar functions to get just the 6 digits you want.

https://www.robvanderwoude.com/vbstech_data_sortedlist.php

You could load the values into a disconnected RecordSet and then sort to find the largest value.

https://developer.rhino3d.com/guides/rhinoscript/disconnected-recordset-sorting/

You could try a combination of the oScript.Evaluate() method and the ProvideX SRT function.

https://manual.pvxplus.com/PXPLUS/functions/srt.htm

sSortedString = "" : sSortedString = oScript.Evaluate("SRT(""B,C,A,"", "",""))

The last character of the first argument must match the character specified as the delimiter in the second argument.

Do what Sage does with things like next invoice #, next cheque #...  Create a one line UDT (with a static value for your key column), and every time you add a value, also update this "next #" table.

Good point, i think i misunderstood what OP's intent was. Using a UDT with a single key record for the next number is useful and there is a class that has a very useful increment method that i need to refresh my memory on.

If the value you are incrementing is always going to be purely numeric, then you can use basic addition to increment it.

However, if the value can alphanumeric, then i suggest getting a handle to CI_NextNumber and using the NextNumber method.

https://help-sage100.na.sage.com/2019/FLOR/#Object_Reference/CI/CI_NextNumber.html%3FTocPath%3DObject%2520Reference%7CCommon%2520Information%7C_____138

It works on alphanumeric and numeric strings.

The first argument is a variable containing the value you want incremented and the second argument is the desired length for the incremented string. You can use the LEN function in VBScript on the variable you use as the second argument or hardcode the length you want.

Great!  along with some tricks you showed me, i have a lot more ideas brewing now.  You are always so resourceful.  Sorry i my original post was kind of 2 part question.  You totally answered how to tackle the sorting issue.  The use of ADO, and especially the Evaluate function were great idea.  I'll definitely keep that in my toolbox.  Thank you David!

This is the simplest and the fastest.  I have tables that have only of integers, or dates and numeric surrogate key, but would have never occurred to me to have just one row table.  This is very useful.  Thanks Kevin!  

I have a follow up question about this.  How should one implement a concurrency control in UDT for UDS/Business events?  The chance isn't that big but I could think of a couple scenario.  Like a VI job creating many new item codes, keeping the master row that holds the next# very busy with updates.  So while the VI job is running, if an employee tried to manually create a new item, thus trying to update the new# in UDT, couldn't the script potentially error out or can't do anything if the record is in edit state? The reverse could be true to that a VI job could error out or fail to update.  I don't recall being able to issue a wait command...

I am not sure (about UDT row locking), but if you open the one row UDT and SetKey (with the _bus, not _svc object), that "should" lock the row.  GetValue, SetValue, Write... and then the row "should" be unlocked for someone else to access.

Perhaps a Sage insider can confirm?

If I recall correctly, the UDT is only locked when opened by the UI object. If opened in a script using the CM_UDTMaint_Bus object, I don't believe any locking takes place. 

However, adding to what Kevin said, just make sure you are using SetKey (in theory, if the UDT only contains the one row, using MoveFirst might work fine instead of SetKey), GetValue, increment value, SetValue, Write, and then you might want to throw in a Clear for good measure to make sure the record does not remain in an edit state.

Sorry, bad wording on my part.  I meant how to deal with someone else trying to lock by issuing setkey() on the row when it's already locked by another setkey() in a different process. 

.