Axaptapedia is now maintained by AgileCadence For more information please click here.
Packing date range values in queries
A query in default Ax can be packed for batch processing of user data. When a query is packed, ranges supplied by the user are packed as a text value and this includes date ranges. There is however a problem when the range is unpacked on a computer with a different regional setting than the computer issuing the request.
For example: a Dutch user wants a report for sales orders created after the 2nd of november 2007. Enters the value ‘>2-11-2007’ (‘D-M-Y’) in the range field. This is placed in a batch group and run on a batch server with regional settings English (‘D\M\Y’). The report is empty, because the date range resulted in no matches.
Changing the regional settings so all clients, server and batch server match is off course the best solution. But in international scenario's end users are still able to supply their own formats (there is no validation) and this basically contradicts the purpose of regional settings. Therefore, the solution could be changing the date range value in a generic way. There are two generic storages available: the date2strxpp format : d\m\y (see Global::date2strxpp(...)) and date2num (number of days since 01/01/1900). Since the date2strxpp format is not easy converted back (any2date and str2date does not work) converting to a number is the 'easiest'.
Alteration in Dynamics Ax[edit]
For this to work, two alteration have to be made:
- Logic to change the date range value to a generic value
- Hook into the packing and unpacking of a query
Note that this affects all packing of queries, both in batch as in usage data. It does not affect the UI: user still have to enter the dates using the formatting in their own regional settings, and after unpacking the date value will be in the regional settings of the users. After this change, any saved batch jobs with date ranges and usage date will have to be re-entered.
Logic to change the daterage-value to a generic value[edit]
To change a daterange-value, the logic has to take into account complex values like: "!1\1\2007, 2\1\2007, 3\1\2007..4\1\2007, =5\1\2007, <6\1\2007, >7\2\2007" So, in case of complex values, this is a challenge. For a generic approach the following code looks up the allowed control character like !,=<><space> and '..' Any date in between those characters are presumed dates.
Add to following code to the SysQuery class: <xpp> // --------------------------------------------------- // Method : SysQuery::convertDateRangeValue() // Author : Gerrit Hulleman // Created : 20071108 // Modified : 20071108 // Purpose : Convert a date range from and to a generic store value. // History : 20071108 - Initial version // // Parm _conversionType: 0 = Regional 2 Generic // !0 = Generic 2 Regional // --------------------------------------------------- static str convertDateRangeValue(str _rangeValue, int _conversionType) {
str dateRangeValue;
int tokenIdx; int tokenLength; str tokenValue; boolean tokenValueIsDate;
str result;
//
// Find the first entry of a 'control' token(not a date value)
// Sets: tokenIdx
// tokenLength
//
void loadNextIndex()
{
int foundIdx;
;
tokenIdx = strlen(dateRangeValue)+1; // Default -> set to full length
//
// Find the single-char control tokens
//
foundIdx = strfind(dateRangeValue, " ,=<>!'\"", 1, strlen(dateRangeValue));
if (foundIdx && foundIdx < tokenIdx)
{
tokenLength = 1;
tokenIdx = foundIdx;
}
//
// Find the multi char control tokens.
//
foundIdx = strscan(dateRangeValue, "..", 0, strlen(dateRangeValue));
if (foundIdx && foundIdx < tokenIdx)
{
tokenLength = 2;
tokenIdx = foundIdx;
}
}
//
// Load the next token
// Sets: tokenValue
//
void loadNextToken()
{
;
//
// Load the first token data: tokenIdx, tokenLength
//
loadNextIndex();
if (tokenIdx == 1)
{
//
// Found a control token at the beginning of the value. Load token.
//
tokenValueIsDate = false;
tokenValue = substr(dateRangeValue, tokenIdx, tokenLength);
dateRangeValue = substr(dateRangeValue, tokenLength+1, strlen(dateRangeValue)-tokenLength);
}
else
{
//
// Found a control token further in the value. Date present before the control token.
//
tokenValueIsDate = true;
tokenValue = substr(dateRangeValue, 1, tokenIdx-1);
dateRangeValue = substr(dateRangeValue, tokenIdx, strlen(dateRangeValue)-tokenIdx+1);
}
}
//
// Converts a date string to and from regional values as string
//
str convertDateValue()
{
int dateValueInt;
str localResult;
date dateValue;
;
if (_conversionType == 0)
{
//
// Convert to generic
//
dateValue = str2date(tokenValue, -1);
dateValueInt = date2num(dateValue); // Number representation of the value
localResult = strfmt("%1", dateValueInt);
}
else
{
//
// Convert to regional
//
dateValueInt = str2int(tokenValue); // Number representation of the value
dateValue = num2date(dateValueInt);
localResult = date2str(dateValue, -1, -1, -1, -1, -1, -1);
}
return localResult; } ;
dateRangeValue = _rangeValue;
//
// Progress the value provided.
//
while (dateRangeValue)
{
//
// Retrieve the next token
//
loadNextToken();
//
// If the token is a date, convert. Otherwise, token is a control value and add unconverted.
//
if (tokenValueIsDate)
result += convertDateValue();
else
result += tokenValue;
}
return result;
} </xpp>
Hook into the packing and unpacking of a query[edit]
Now it is up to Ax to convert the date range value to a generic value. Ax has a lovely place for this: SysQuery::packDatasource for packing and SysQuery::unpackDataSource for (you guessed it...) unpacking.
In the pack datasource, look for the following code: <xpp>
// Pack ranges
... // code removed
for (i=1; i <= elements; i++)
{
queryBuildRange = queryBuildDataSource.range(i);
if (queryBuildRange.status() == RangeStatus::Open ||
queryBuildDataSource.uniqueId() >= _queryNextUniqueId)
{
... // code removed
if(! range)
range = queryBuildRange.value();
// Begin modification, 08-11-2007, Ghull
if (range != "")
{
//
// Convert a date-range value from regional to generic value
//
dictField = new SysDictField(queryBuildRange.table(), queryBuildRange.field());
if (dictField.baseType() == Types::Date)
range = SysQuery::convertDateRangeValue(range, 0);
}
// End modification, 08-11-2007, Ghull
}
}
</xpp> Add the code indicated by the modification comments. Your code might look a bit differently, depending on version and custom code.
For unpacking, look for the following sections in the SysQuery.unpackDataSource <xpp>
// unpack ranges
_queryBuildDataSource.clearRanges();
rangeNoAddSet = new Set(Types::Integer);
if (_origRangeMap &&
_origRangeMap.exists(_queryBuildDataSource.uniqueId()))
{
origRangePack = _origRangeMap.lookup(_queryBuildDataSource.uniqueId());
elements = conLen(origRangePack);
for (i=1; i<=elements; i++)
{
[fieldId, rangeLabel, range, rangeStatus] = conPeek(origRangePack, i);
... // code removed
sysDictField = new SysDictField(tableId, queryBuildRange.field());
// Begin modification, 08-11-2007, Ghull
if (range != "" && sysDictField.baseType() == Types::Date)
{
//
// If it is a date-range value, it is stored in a generic format. See packDataSource and Devutil.
//
range = SysQuery::convertDateRangeValue(queryBuildRange.value(), 1);
queryBuildRange.value(range);
}
// End modification, 08-11-2007, Ghull
... // code removed
}
}
rangePack = conPeek(_dataSourcePack, #packRangePos);
elements = conLen(rangePack);
for (i=1; i<=elements; i++)
{
[fieldId, rangeLabel, range, rangeStatus] = conPeek(rangePack, i);
sysDictField = new SysDictField(tableId, fieldId);
if (sysDictField &&
!rangeNoAddSet.in(sysDictField.id()) &&
(sysDictField.flags() & #DBF_Store) &&
(rangeStatus == RangeStatus::Open || !_origDataSource))
{
... // code removed
// Begin modification, 08-11-2007, Ghull
if (range != "" && sysDictField.baseType() == Types::Date)
{
//
// If it is a date-range value, it is stored in a generic format. See packDataSource and Devutil.
//
range = SysQuery::convertDateRangeValue(queryBuildRange.value(), 1);
queryBuildRange.value(range);
}
// End modification, 08-11-2007, Ghull
}
}
</xpp>
Yes, this unpack needs the code in two places. Again, this might differ for your implementation.
Good luck, may the Dynamics Ax gods be with you.
