cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EdHansberry
Level: Powered On

Find next available number in a list?

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?

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Find next available number in a list?

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!

 

View solution in original post

11 REPLIES 11
Super User
Super User

Re: Find next available number in a list?

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
Level: Powered On

Re: Find next available number in a list?

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
Level 8

Re: Find next available number in a list?

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

EdHansberry
Level: Powered On

Re: Find next available number in a list?

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
Level 8

Re: Find next available number in a list?

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.

Super User
Super User

Re: Find next available number in a list?

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.

Super User
Super User

Re: Find next available number in a list?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Find next available number in a list?

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!

 

View solution in original post

Community Support Team
Community Support Team

Re: Find next available number in a list?

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:7.JPG

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 325 members 5,711 guests
Please welcome our newest community members: