Unanswered

Sorting List and finding a missing value

(0) Share

Report

Posted on by 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:

If you delete ID #3, you are left with

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

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

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

mdevaney 29,989 Moderator on at

Like (1)

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

Was this reply helpful? Yes No
MartySt 33 on at

Like (0)

Report

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

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@MartySt

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

Was this reply helpful? Yes No
PaddyWann 101 on at

Like (0)

Report

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

Was this reply helpful? Yes No
PaddyWann 101 on at

Like (0)

Report

-

Was this reply helpful? Yes No
NPrice99 1,302 on at

Like (0)

Report

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

Was this reply helpful? Yes No
PaddyWann 101 on at

Like (1)

Report

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

Was this reply helpful? Yes No
NziokiB 83 on at

Like (0)

Report

@PaddyWann Thank you. I had been looking for a similar solution and this worked perfectly well:
ForAll(sequence(CountRows("yourcolumn")),If(Not(Value in yourcolumn),Collect(colmissing,Value))).

Was this reply helpful? Yes No