Need to read a number, find the highest, then update a empty variable based on highest number found

[Moedeb]

Scenario:

We have 20 carparks available and a waiting list.

All users on the waiting list are assigned a 'queue' number based on when they were added to the queue.

EG:
first 20 people have a queue number of 1,

the next 20 people have a queue number of 2

and so on and so on until all people in the queue are given a queue number 

What I want to do is when someone new registers to be added to the queue, they need to be auto assigned a queue number based on whatever the highest current queue number is and either get that number assigned to them

• if there are currently less than 20 people with that queue number,
• or assigned the next number, if there are already 20 people assigned that number.

I was thinking this would be done via a flow where the trigger is a new user is added to the carpark list (variable on sys_user table called 'u_carpark_tf' changing to true), then potentially a lookup records to find the value of the variable field called 'u_queue_number' and find what the highest number is, then count how many records are currently using that number, then based on that assign a value to the users 'u_queue_number' variable on their record.

The 'u_carpark_tf' is a true/false variable and the 'u_queue_number' variable is just a string field.

The issue is I don't actually know how to do this in reality. Would really appreciate someone being able to assist.

[Ankur Bawiskar · Accepted solution]

@Moedeb 

is this for catalog item? this logic needs to trigger post submission of some form?

how are you maintaining the chunk of 20 people and the queue number

What's your actual requirement?

this is the sample script which you can use in server side, you can enhance it further if you are planning to use onChange + GlideAjax

Note: make -> "u_queue_number" as Integer and not string

Something like this in before insert business rule

(function executeRule(current, previous /*null when async*/) {

    // Get the highest queue number
    var gr = new GlideRecord('your table');
    gr.orderByDesc('u_queue_number');
    gr.query();
    
    var highestQueueNumber = 1;
    if (gr.next()) {
        highestQueueNumber = gr.u_queue_number;
    }

    // Count the number of users with the highest queue number
    var grCount = new GlideAggregate('your table');
    grCount.addQuery('u_queue_number', highestQueueNumber);
    grCount.addAggregate('COUNT');
    grCount.query();
    
    var count = 0;
    if (grCount.next()) {
        count = grCount.getAggregate('COUNT');
    }

    // Assign the queue number
    if (count < 20) {
        current.u_queue_number = highestQueueNumber;
    } else {
        current.u_queue_number = highestQueueNumber + 1;
    }

})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Moedeb]

@Ankur Bawiskar some of the how will be potentially manually update to start with as there is already a maintained list in an excel spreadsheet, but ultimately once it is working there will be a catalog request for someone to apply to be added to the list, then this would be used to add them at the correct queue point.

As far as maintaining this will be done via flows with triggers at different points such as when they need to move forward in the queue.

Thank you for the assistance. I'll mark it correct once I test it.

[Moedeb]

Works great thank you @Ankur Bawiskar