When creating a query and adding ranges, you might notice that there is a limit on how the range string can be. This limit is defined by the extended data type Range that you can find in the AOT. You can try extending the size to a bigger stringsize, but you can’t go too high before you run into limits that the sql database will have (I think this value is around 1700 characters).
What you can do is add multiple smaller ranges on the same field in stead of one long range.
I’ve written a method that splits a long range and adds it to you queryBuildDataSource:
private static void splitAndAddRange(QueryBuildDataSource _qbds,
fieldId _fieldId,
str _sRange)
{
#AOT
// used to split and build ranges
int splitPos; // max position for splitting
int cPos; // position of ','
str fRange; // first part of the range
str lRange; // last part of the range
QueryBuildRange qbr; // the extra qbr if needed
;
// define max lenght
// note: make this function none static and get the stringsize property only once while initialising your class
// to speed up performance
splitPos = treenode::findNode(#ExtendedDataTypesPath + #AOTRootPath + 'Range').AOTgetProperty('StringSize');
// define range string
// init cPos
cPos = -1;
// if range is too long -> split (3 should be 1500)
if(strlen(_sRange) > splitPos)
{
// while no comma found
while(cPos == -1 && splitPos > 0)
{
// if char at current position is ',', split position found
if(substr(_sRange, splitPos, 1) == ',')
cPos = splitPos;
// else try prev char
else
splitPos--;
}
// get the first part of the range
fRange = substr(_sRange, 0, splitPos - 1);
// make new range and add it
qbr = _qbds.addRange(_fieldId);
qbr.value(fRange);
// get the remaining part of the range
lRange = substr(_sRange, splitpos, strlen(_sRange));
// split the rest of the range
if(strlen(lRange) > 0)
{
KLFSplitAndAddRangeTest::splitAndAddRange(_qbds, _fieldId, lRange);
}
}
else
{
// range is not too long, don't split, just add
qbr = _qbds.addRange(_fieldId);
qbr.value(_sRange);
}
}
When you put this method in a class along with the following one, you can test the method.
static void main(Args _args)
{
Query query;
QueryBuildDataSource qbds;
str accountNumRange;
// used for info-ing
int rangeLength;
int rangeLengthAOT;
#AOT
;
accountNumRange += 'A0000000001,A0000000002,A0000000003,A0000000004,A0000000005,A0000000006,A0000000007,A0000000008';
accountNumRange += '0000000009,A0000000010,A0000000011,A0000000012,A0000000013,A0000000014,A0000000015,A0000000016';
accountNumRange += '0000000017,A0000000018,A0000000019,A0000000020,A0000000021,A0000000022,A0000000023,A0000000024';
accountNumRange += '0000000025,A0000000026,A0000000027,A0000000028,A0000000029,A0000000030,A0000000031,A0000000032';
accountNumRange += '0000000033,A0000000034,A0000000035,A0000000036,A0000000037,A0000000038,A0000000039,A0000000040';
accountNumRange += '0000000041,A0000000042,A0000000043,A0000000044,A0000000045,A0000000046,A0000000047,A0000000048';
accountNumRange += '0000000049,A0000000050,A0000000051,A0000000052,A0000000053,A0000000054,A0000000055,A0000000056';
accountNumRange += '0000000057,A0000000058,A0000000059,A0000000060,A0000000061,A0000000062,A0000000063,A0000000064';
accountNumRange += '0000000065,A0000000066,A0000000067,A0000000068,A0000000069,A0000000070,A0000000071,A0000000072';
accountNumRange += '0000000073,A0000000074,A0000000075,A0000000076,A0000000077,A0000000078,A0000000079,A0000000080';
accountNumRange += '0000000081,A0000000082,A0000000083,A0000000084,A0000000085,A0000000086,A0000000087,A0000000088';
accountNumRange += '0000000089,A0000000090,A0000000091,A0000000092,A0000000093,A0000000094,A0000000095,A0000000096';
accountNumRange += '0000000097,A0000000098,A0000000099,A0000000100';
query = new Query();
rangeLength = strlen(accountNumRange);
rangeLengthAOT = treenode::findNode(#ExtendedDataTypesPath + #AOTRootPath + 'Range').AOTgetProperty('StringSize');
info(strfmt('Range length: %1',rangeLength));
info(strfmt('range length AOT: %1', rangeLengthAOT));
info(strfmt('There should be %1 ranges', roundup(rangeLength / rangeLengthAOT, 1)));
qbds = Query.addDataSource(tablenum(CustTable));
KLFSplitAndAddRangeTest::splitAndAddRange(qbds, fieldnum(CustTable, AccountNum), accountNumRange);
info(strfmt('Range count: %1',qbds.rangeCount()));
}
The output is:
Range length: 1175
range length AOT: 250
There should be 5,00 ranges
Range count: 5
Using a tool I have I can also print out the query for a closer look:
You can clearly see that the 5 ranges have been added.
Related
Abed
on November 12, 2015 at 05:47
Since SP RU 7 in Dynamics Ax 2009 you can search for code idinse web controls. To do so you need set search papameter to All nodes . If you dont want to upgrade, you need to change \Classes\SysTreeNode\codeIsSource method: add two enum values UtilElementType::WebControl and UtilElementType::WebSourceFile to switch statement, to return true for them.