cancel
Showing results for
Did you mean:
Kudo Collector

## 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
Power Apps

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],
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!

11 REPLIES 11
Community Champion

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

Kudo Collector

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.

Impactful Individual

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

Kudo Collector

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.

Impactful Individual

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.

Community Champion

Mmmm. I’m fairly certain you can do something like (pseudo code here cuz I’m on my phone):
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.

Community Champion
Power Apps

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],
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!

Community Support

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(
"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.

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.

Announcements

#### A Celebration of What We've Achieved--And Announcing Our Winners

As the sun sets on the #SummerofSolutions Challenge, it's time to reflect and celebrate! The journey we embarked upon together was not just about providing answers – it was about fostering a sense of community, encouraging collaboration, and unlocking the true potential of the Power Platform tools.   From the initial announcement to the final week's push, the Summer of Solutions Challenge has been a whirlwind of engagement and growth. It was a call to action for every member of our Power Platform community, urging them to contribute their expertise, engage in discussions, and elevate collective knowledge across the community as part of the low-code revolution.   Reflecting on the Impact As the challenge ends, it's essential to reflect on the impact it’s had across our Power Platform communities: Community Resilience: The challenge demonstrated the resilience of our community. Despite geographical distances and diverse backgrounds, we came together to contribute, learn, and collaborate. This resilience is the cornerstone of our collective strength.Diverse Expertise: The solutions shared during the challenge underscore the incredible expertise within our community. From intricate technical insights to creative problem-solving, our members showcased their diverse skill sets, enhancing our community's depth.Shared Learning: Solutions spurred shared learning. They provided opportunities for members to grasp new concepts, expand their horizons, and uncover the Power Platform tools' untapped potential. This learning ripple effect will continue to shape our growth. Empowerment: Solutions empowered community members. They validated their knowledge, boosted their confidence, and highlighted their contributions. Each solution shared was a step towards personal and communal empowerment. We are proud and thankful as we conclude the Summer of Solutions Challenge. The challenge showed the potential of teamwork, the benefit of knowledge-sharing, and the resilience of our Power Platform community. The solutions offered by each member are more than just answers; they are the expression of our shared commitment to innovation, growth, and progress!   Drum roll, Please... And now, without further ado, it's time to announce the winners who have risen above the rest in the Summer of Solutions Challenge!   These are the top community users and Super Users who have not only earned recognition but have become beacons of inspiration for us all.   Power Apps Community:  Community User Winner: @SpongYe Super User Winner: Pending Acceptance Power Automate Community:  Community User Winner: @trice602 Super User Winner: @Expiscornovus  Power Virtual Agents Community: Community User Winner: Pending AcceptanceSuper User: Pending Acceptance Power Pages Community: Community User Winner: @OOlashyn Super User Winner: @ChristianAbata   We are also pleased to announced two additional tickets that we are awarding to the Overall Top Solution providers in the following communities:    Power Apps: @LaurensM   Power Automate: @ManishSolanki    Thank you for making this challenge a resounding success. Your participation has reaffirmed the strength of our community and the boundless potential that lies within each of us. Let's keep the spirit of collaboration alive as we continue on this incredible journey in Power Platform together.Winners, we will see you in Vegas! Every other amazing solutions superstar, we will see you in the Community!Congratulations, everyone!