Finance | Project Operations, Human Resources, ...

Select Random Rows or Records From table using X++ (No Direct SQL Please)

(0) Share

Report

Posted on by Piyush Adhikari

2,546

Hello Folks,

I need to Select Random Rows or Records From table using X++.

In SQL below query is working fine.

SELECT TOP 10 * 
FROM CustTable
ORDER BY NEWID();

but I want to achieve the same result in D365 using X++  without any Direct SQL statement 

Please suggest

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

ergun sahin 8,826 Moderator on at

Like (0)

Report

You can't choose random records in Ax like this (at least I couldn't remember). However, it is possible to generate a random value and compare(>= or <=) it to a field like RecId and use the first 10 records in a while loop.

You can use System.Random to generate random value.

docs.microsoft.com/.../system.random

You can identify a range by looking at the first and last RecIds.

Was this reply helpful? Yes No
Suggested answer

Jack D 60 on at

Like (0)

Report

If you would explain the business case why you would need random records it could maybe help with providing a better solution. Since D365 is using a relational database you will always get ordered data instead of random records.

simple idea;

To get some sort of random data is selecting for example the first 100 records. Put the records in an array (container) and generate a random value between 1 and 100. When for example the number is 15 get the data in the 15th element of the array.

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

Hi Piyush,

what's the business requirement that you're trying to solve by fetching random records?

Was this reply helpful? Yes No
Piyush Adhikari 2,546 on at

Like (0)

Report

Business Requirement -

we have to a specific number (Configurable) of records or rows from Work Orders Table for Sample Collection.

After Sample Collection , Auditor will work on those work Orders

This activity will be repeated more than 1 . So every time should pick random work orders for sample collection

Was this reply helpful? Yes No
Piyush Adhikari 2,546 on at

Like (0)

Report

***Business Requirement -

we have to Select a specific number (Configurable) of records or rows from Work Orders Table for Sample Collection.

After Sample Collection , Auditor will work on those work Orders

This activity will be repeated more than 1 . So every time should pick random work orders for sample collection

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Perhaps this is something that can be discussed with the auditor. I have not yet heard similar requirement - usually the "random" samples are picked manually and not via some random generator. I wonder why the auditor wants to let the system decide which records to pick - isn't this something that could potentially compromise the quality of the to-be-audited data?

Anyway, one simple approach would be to check lowest and highest RecId in the table, then generate random numbers between those two numbers, and fetch the corresponding record. If there's no record for the generated random RecId, pick new.

You can of course also get all RecIds and select random values from there but it would be a bit slower since the system would need to provide all existing RecId values.

But most likely you need more criteria - otherwise the auditor would most likely get records from past years and not from the year that they're auditing.

Was this reply helpful? Yes No