- Power Apps Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power Apps
- Building Power Apps
- Microsoft Dataverse
- AI Builder
- Power Apps Governance and Administering
- Power Apps Pro Dev & ISV
- Power Apps Portals
- Connector Development
- Power Query
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Community Blog
- Power Apps Community Blog
- Galleries
- Community Connections & How-To Videos
- Community App Samples
- Webinars and Video Gallery
- Canvas Apps Components Samples
- Kid Zone
- Business Value Webinars and Video Gallery
- Emergency Response Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Ideas
- Power Apps Ideas
- Community Engagement
- Demo Extravaganza 2021
- Demo Extravaganza - Components 2021
- Power Apps Community Demo Extravaganza 2020
- Experimental
- Error Handling
- Power Apps Experimental Features
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Power Apps Community
- Forums
- Get Help with Power Apps
- Building Power Apps
- Re: Find next available number in a list?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Find next available number in a list?

04-03-2019
04:57 PM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
08:04 PM

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!

11 REPLIES 11

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
05:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
05:47 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
05:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
05:50 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
06:20 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
07:45 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-03-2019
08:04 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-04-2019
02:17 AM

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

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

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(, Not(Text(YourBigList) in Mid(AddColumns(BigNumberColumn,"YourOriginalList",Text(TextValue)).OriginalNumberColumn, 2,5))),TextValue

"",IntegerValue

Value()BigNumberColumn

), "", Ascending ) ).IntegerValueBigNumberColumn

** Note**: The

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

More details about the** AddColumns **function and

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

If this post

Announcements

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Top Solution Authors

User | Count |
---|---|

189 | |

65 | |

46 | |

35 | |

25 |

Top Kudoed Authors

User | Count |
---|---|

243 | |

106 | |

89 | |

85 | |

64 |