Set five values in field using Query Range

Finance and Operations/AX Developer Forum
1

Hi Guys,

I need a little help here…

I am creating a report which is can’t generate Inventory Report.

We have many Warehouse locations in our AX System, I only need 8 locations which is the correct location…

How can i do it in X ++?

here what I’ve started…

qbrLocation = query.dataSourceTable(tableNum(InventDim)).addRange(fieldNum(InventDim, InventLocationId));
   qbrLocation.value(strFmt("Small 2016",,,,,,,);

TIA

2

Hello JEMT,

you can review any of the standard report to check how you can use range f.e.: InventPriceOverview Report , check process report in InventPriceOverviewDP class

if your question is how to pass multiple values to the range then
you can use
qbrLocation.value(strfmt("%1,%2",“WH1”,“WH2”));

1 Like
3

You don’t have to bother constructing a query with some special syntax; simply add several ranges for the same field:

QueryBuildDataSource inventDimDs   = query.dataSourceTable(tableNum(InventDim));
FieldId              locationField = fieldNum(InventDim, InventLocationId);

inventDimDs.addRange(locationField).value(queryValue("Location A"));
inventDimDs.addRange(locationField).value(queryValue("Location B"));
inventDimDs.addRange(locationField).value(queryValue("Location C"));
4

Hi Martin,

it works, but how will i put it OR operator in that code?
for example: c.INVENTLOCATIONID = ‘Small 2016’ or c.INVENTLOCATIONID = ‘SMALL 2016’

because there is lot of location and and until now the inventory team is still validate the inventory because it has a content, so that i need to specify the location…

thank you

1 Like
5

Hi Jemt,

You can use an OR condition in the dynamics query.

Given below the code snippet:

static void AE_qbrRange(Args _args)

{

Query q;

QueryBuildDataSource qbds;

QueryBuildRange qbr;

queryRun qr;

CustTable ct;

q= new Query();

qbds = q.addDataSource(tableNum(CustTable));

qbr = qbds.addRange(fieldNum(CustTable, AccountNum));

qbr.value(

strFmt(’((%1.%2 == “10”) || (%1.%2 == “20”))’,

qbds.name(),

fieldStr(CustTable, CustGroup)));

qr = new QueryRun(q);

while (qr.next())

{

ct = qr.getNo(1);

info(strFmt("%1, %2",ct.AccountNum, ct.CustGroup));

}

}

6

Hi Anitha,

Thank for you response, i will try that…

can you check this code if will work?


    inventDimDs = query.dataSourceTable(tableNum(InventDim));
    locationField = fieldNum(InventDim, InventLocationId);
    
    
    inventDimDs.addRange(locationField).value(queryRange("Small 2016", "SMALL 2016"));
    inventDimDs.addRange(locationField).value(queryRange("Big 2016", "BIG 2016"));
    inventDimDs.addRange(locationField).value(queryRange("Laz 2016", "LAZ 2016"));
    inventDimDs.addRange(locationField).value(queryValue("Refurb"));
    inventDimDs.addRange(locationField).value(queryValue("Demo 2016"));

i try the code given by martin.

Thank you

7

You don’t have to add any operator by yourself. The piece of code I gave already generates SQL code with a WHERE clause with several conditions separated with the OR operator.
You’re so focused on a complicated solution that you’re overlooking the simple one. :slight_smile: Please try my code to see what it does; you can also look at the SQL code it generates.

No, your last code won’t work. queryRange() is used for an interval of values, such as ‘aaa’ to ‘aaz’. Also, AX uses case-insensitive collation in DB (unless somebody installed it incorrectly), therefore “Small 2016” and “SMALL 2016” are considered the same values in database. Therefore using queryRange() is logically wrong, because you’re not trying to create a range, and your queryRange() will have no effect anyway, because the lower range and the upper range are the same.