Find next available number in a list?

[EdHansberry]

I need to find the next available number in a list. This is not the same as finding the highest number and adding 1. For example, I have the following numbers in my database:

• 1000011
• 1000045
• 1005646
• 1006565
• 1568795
• 1894834

The first and last digit are meaningful, but the middle 5 are not. So the "core numbers" would be:

• 00001
• 00004
• 00564
• 00565
• 56879
• 89483

In Excel, I'd simply use PowerQuery to generate a list of numbers 00001 - 99999, then do an antijoin to only keep numbers in my big list that aren't in the original list, then pick the minimum number. Which, in this case, would be 00002.

If that were added to the database then, 00003 would be next, then 00005, as an 00004 already exists.

Getting the core number in PowerApps is easy - the MID() function.

I cannot figure out how to do the logic though to start moving through those numbers to find the first available number.

Ideas where to start?

[ericonline]

Hi @EdHansberry ,

Initially, i'm thinking of the string manipulation functions like Split(), Len(), Right(), Left() etc. You can wrap the results of these manipulations in Value() to perform numeric calculations on them once you have em' cornered.

Good luck! 
Let us know what you end up with! Thats a cool problem

[EdHansberry]

Yeah. Converting from numbers to text and back are not the problem. A Do-While loop would work, but that doesn't exist in PowerApps. Nor, to my knowledge, the ability to create an array of sequential numbers. Kinda stumped.

[mogulman]

You say database.  What type of database?  Is it SQL Server?  You could write a stored procedure and call it from Flow.

[EdHansberry]

Right now it is an Excel file as the database. It will be in SQL Server, but I don't know anything about writing stored procedures, or doing SQL stuff natively in SQL server. I use SSMS sparingly, and use PowerQuery for all of my data transformational needs.

I'm hoping for a native PowerApps solution.

[mogulman]

Doing what you want requires a procedural language.  The M-Language is pretty powerful and provides a lot of interesting capabilities.  Stored procedures are not that difficult to learn and if you are going to use SQL Server for anything significant you are likely going to need them for other purposes.

[ericonline]

Mmmm. I’m fairly certain you can do something like (pseudo code here cuz I’m on my phone):
ForAll(spreadsheet,
Sort(
Mid(<midStuff>), Ascending)
)

Basically, for all the numbers in the given column, peel off the first and last numbers, sort the results in ascending order.
I’m fact, I’m certain I’ve seen this somewhere in the forums here.

[ericonline]

https://powerusers.microsoft.com/t5/Expressions-and-Formulas/deleting-first-and-last-Character-of-a-...

No?

[CarlosFigueira · Accepted solution]

This can be done natively in PowerApps, using a logic similar to the one you described. The main problem here is to create the list of numbers from 1 to 99999 - there's nothing in PowerApps that can do that, but you can do it "by hand" (or using code) by using this construct:

[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,...]

Here's the expression that would give you the "next" number (assuming that the numbers are stored in a collection or a table called 'numbersInDatabase'):

First(
    Filter(
        [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
        Not(Value in AddColumns(
            numbersInDatabase,
            "CoreValue",
            Value(Mid(Text(Value), 2, Len(Text(Value)) - 2))).CoreValue)),
).Value

The idea is to extract the value from the original table, and use the Not operator with the in operator to implement the anti-join pattern that you mentioned, then taking the first of the remaining values.

The attached app shows how this can be implemented; to open it save it locally, then go to https://create.powerapps.com, select Open, then Browse, then find the file that you saved before.

Another alternative that you can use instead of creating a list of all 100k numbers, is to have a list of ~100 (or 1000) numbers that start with the next available number, and every time you add a new number, you "shift" the list to start from that number. The attached app also shows this alternative.

Hope this helps!

[v-xida-msft]

Hi @EdHansberry ,

Could you please share a bit more the data structure of your Original List and Big List?

Further, do you want to find the next available minimum number within your Original List?

I assume that you add a Text type column to store the list of numbers 00001 - 99999 in your Big List, is it true?

I have made a test on my side, please take a try with the following workaround:

Set the OnStart property of the App control or OnVisible property of the first screen of your app to following:

ClearCollect(OriginalList, 1000011, 1000045, 1005646, 1006565, 1568795, 1894834);
ClearCollect(BigList, "00001", "00002", "00003", "00004", "00005", "00007", "00006","00008","00010","00009")

Set the Text property of the Label control to following:

First(
       SortByColumns(
                     AddColumns(Filter(BigList, Not(Text(Value) in Mid(AddColumns(OriginalList,"TextValue",Text(Value)).TextValue,2,5))), "IntegerValue", Value(Value)),
                     "IntegerValue",
                      Ascending
       )
).Value

On your side, you should type the following:

First(
       SortByColumns(
                     AddColumns(
                                 Filter(YourBigList, Not(Text(BigNumberColumn) in Mid(AddColumns(YourOriginalList,"TextValue",Text(OriginalNumberColumn)).TextValue, 2,5))), 
                                 "IntegerValue", 
                                 Value(BigNumberColumn)
                      ),
                     "IntegerValue",
                      Ascending
       )
).BigNumberColumn

Note: The BigNumberColumn represents the Text type column in your Big List, which used to store the list of numbers 00001 - 99999. The OriginalNumberColumn represents the column in your Original List to store the original number, e.g. 1000011, 1000045, 1005646, 1006565, ... etc.

Please take a try with above solution, then check if it help in your scenario.

More details about the AddColumns function and SortByColumns function, please check the following article:

AddColumns function, SortByColumns function

Best regards,