cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MartySt
Helper I
Helper I

Sorting List and finding a missing value

Hello,

 

In my app, there is data that is created with a unique ID for each piece of equipment. I have it so that when you create a new equipment field, a new unique ID is attached to it. This is done by setting the ID datacard in my edit form to Last(Sort(MasterList,ID)).ID+1. So it is looking up the max value from the list, and then adding 1. 

 

This works great! Except, in one scenario:

 

ID

1

2

3

4

 

If you delete ID #3, you are left with 

 

ID

1

2

4

 

Which means the next time you create a piece of equipment, you get ID #5, and in the long run this causes issues for me.

 

 

I would like to have some sort of code that could find any "missing" values from my column of ID numbers, something that would return the number 3 if we're looking at my example from above.

 

And in the case of multiple missing numbers, would return only one value, the lowest one, so another example would be

 

ID

1

2

4

6

 

Both 3 and 5 are missing from the list above, but I would like to return 3 only, so that the lowest 'spot' gets filled in first.

 

Thank you!!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
mdevaney
Super User
Super User

@MartySt 
I am curious: what issue does this cause for you in the long run?  In my experience every piece of equipment should have its own unique ID number that does not get repeated even when the equipment is retired.  Otherwise, we could become confused on whether equipment ID refers to the new equipment or the old equipment #3.

Other questions arise when there are gaps in the numbering system.   Was something deleted or purpose, or by mistake?  Did we accidentally skip a number?

 

Instead of deleting equipment, I would suggest you have at least 2 columns for each piece of equipment: ID and Active.  When the equipment is retired simply change it to inactive.

 

ID Active
1 Yes
2 Yes
3 No

 

By doing it this way you will always have a complete master list of equipment and not have any missing numbers in your list.

 

Then if you want to show only active equipment in a gallery you can use a FILTER function.

 

Filter(your_datasource_name, Active = false)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

6 REPLIES 6
mdevaney
Super User
Super User

@MartySt 
I am curious: what issue does this cause for you in the long run?  In my experience every piece of equipment should have its own unique ID number that does not get repeated even when the equipment is retired.  Otherwise, we could become confused on whether equipment ID refers to the new equipment or the old equipment #3.

Other questions arise when there are gaps in the numbering system.   Was something deleted or purpose, or by mistake?  Did we accidentally skip a number?

 

Instead of deleting equipment, I would suggest you have at least 2 columns for each piece of equipment: ID and Active.  When the equipment is retired simply change it to inactive.

 

ID Active
1 Yes
2 Yes
3 No

 

By doing it this way you will always have a complete master list of equipment and not have any missing numbers in your list.

 

Then if you want to show only active equipment in a gallery you can use a FILTER function.

 

Filter(your_datasource_name, Active = false)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

@mdevaney 

The issue this causes is related to another part of my app that references the ID, but retiring equipment to an inactive list definitely fixes that! Thanks for the simple and fast response!!

@MartySt 

You are very welcome Marty.  Thank for taking the time to consider my different approach to problem at-hand.

PaddyWann
Advocate I
Advocate I

 

I did this and it seemed to work. I needed to use missing vehicles in the sequence since sometimes the powerapps/dataverse finds a max number for some reason. So If I add one to that max number, it adds a one but the system goes haywire.  So I have to find the missing number.  My numbers are led by zeros to be 4 digits fyi. 

 

ClearCollect(colVehNumbers,VanFleetVehicles.VehicleID);

ForAll(colVehNumbers,If(Not(Text(ThisRecord.VehicleID+1,"0000") in VanFleetVehicles.VehicleID),Collect(colVehMissing,Text(ThisRecord.VehicleID+1,"0000"))));

Set(varFirstID,First(colVehMissing).Value);

-

Hi

I have tried this and it only works if there is one missing number.

 

If there is a sequence of two or more missing numbers, then it only finds the first one in the sequence.

 

Regards

 

Nigel

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

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

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,668)