Can you find the highest integer value using a flow?

[Moedeb]

I have an integer field on the sys_user table called "u_queue_number".

What I want to do if possible via a flow is to seek out all records where u_queue_number is not blank and find what the current highest value of that field is and also how many records current have that value.

Ideas?

[Ankur Bawiskar]

@Moedeb 

you can use flow variable and determine this with script.

not possible with OOB flow action etc

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

[Moedeb · Accepted solution]

@Ankur Bawiskar 

I understand that it would likely need to be done via a flow script, but was hoping someone might be able to assist with the how.

Since I posted my question I have managed to make it work, but just not sure it's the best way to do it?

Ultimately, I created a flow variable called "Highest Queue Number" then did a scripted date 

 var largestQueueNumber = 0;
    var userGR = new GlideRecord('sys_user');
    userGR.addQuery('u_queue_number', '!=', '');
    userGR.query();
    
    while (userGR.next()) {
        var currentQueueNumber = parseInt(userGR.u_queue_number, 10);
        if (!isNaN(currentQueueNumber) && currentQueueNumber > largestQueueNumber) {
            largestQueueNumber = currentQueueNumber;
        }
    }
    return largestQueueNumber;

Result:

So it works, but is it the best way?

[Ankur Bawiskar]

@Moedeb 

yes that's the only way.

Without using script it's not possible

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

[neetusingh]

@Moedeb - 

Your solution for finding the highest value of the `u_queue_number` field in the `sys_user` table is functional, but there are a few improvements you can make to enhance efficiency and clarity. Here’s a refined approach I would do:

1. Combine Queries: Instead of looping through each record to determine the maximum value, you can use a single query with aggregation to find the highest value directly.
2. Count Records: After finding the maximum value, you can perform another query to count how many records have that value.

Here’s an optimized version of your script:

var userGR = new GlideRecord('sys_user');

userGR.addQuery('u_queue_number', '!=', '');

userGR.orderByDesc('u_queue_number'); // Order by descending to get the highest first

userGR.setLimit(1); // Limit to 1 record

userGR.query();

var highestQueueNumber = 0;

var countOfHighest = 0;

if (userGR.next()) {

    highestQueueNumber = parseInt(userGR.u_queue_number, 10);

    // Now count how many records have this highest value

    var countGR = new GlideRecord('sys_user');

    countGR.addQuery('u_queue_number', highestQueueNumber);

    countOfHighest = countGR.getRowCount(); // Get the count of records with the highest number

}

return {

    highestQueueNumber: highestQueueNumber,

    countOfHighest: countOfHighest

};

- Ordering: By using `orderByDesc`, you can retrieve the record with the highest `u_queue_number` directly without needing to loop through all records.

- Limiting Results: The use of `setLimit(1)` ensures that only one record is fetched, which is more efficient than processing all records.

- Counting Records: After identifying the highest number, a separate query counts how many users have that number, which is more straightforward than maintaining a counter during iteration.

This approach minimizes database calls and improves performance by leveraging GlideRecord's capabilities more effectively. It also enhances readability and maintainability of your script. Overall, while your initial solution works, this refined method is more efficient and cleaner.