Sorting List and finding a missing value

[MartySt]

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

[mdevaney · Accepted solution]

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

[MartySt]

@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!!

[mdevaney]

@MartySt 

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

[PaddyWann]

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);

[PaddyWann]

-

[NigelP]

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

[PaddyWann]

Apologize, I would recommend the sequence function in something like this. I didn't test, but this is the idea:

ForAll(sequence(CountRows("yourcolumn")),If(Not(Value in  yourcolumn),Collect(colmissing,Value))).

Then you can do a first or a last option. There is also a row numbering method using the sequence function to just renumber the column. 

ForAll(Sequence(CountRows(colSigners)),Patch(colSigners,Last(FirstN(colSigners,Value)),{recipientId:Value}));