Filter fields type Date/Time (like Createddatetime) in query of Form
Views (142)
Below a piece of x++ about the build of a filter related to the query of a form
QueryBuildDataSource qbds;
str rangestr, rangeStrFrom, rangeStrTo;
str dateFrom, dateTo;
if(createdDateFrom.dateValue() != datenull())
{
dateFrom = datetimeutil::toStr(datetimeutil::newDateTime(createdDateFrom.dateValue(), 0, datetimeutil::getCompanyTimeZone()));
rangestrFrom = strfmt('(%1 >= %2)',
fieldstr(salestable, createddatetime),
datefrom);
}
if(createdDateTo.dateValue() != datenull())
{
dateto = datetimeutil::toStr( datetimeutil::newDateTime(createdDateTo.dateValue(), 86400, datetimeutil::getCompanyTimeZone())); //max value of time
rangestrTo = strfmt('(%1 <= %2)',
fieldstr(salestable, createddatetime),
dateTo);
}
if(rangestrFrom && ! rangestrTo)
rangestr = rangestrFrom;
else if(!rangestrFrom && rangestrTo)
rangestr = rangestrTo;
else if(rangestrFrom && rangestrTo)
rangestr = "(" + rangestrFrom + " && " + rangestrTo + ")";
if(!salesLine_ds.query().dataSourceTable(tableNum(SalesTable)))
{
qbds = salesLine_ds.query().dataSourceTable(tablenum(salesLine)).addDataSource(tablenum(salestable));
qbds.relations(true);
}
else
qbds = salesLine_ds.query().dataSourceTable(tablenum(salesTable));
qbds.clearRange(fieldnum(salesTable, createddatetime));
qbds.addRange(fieldnum(salesTable, createddatetime)).value(rangestr);
This was originally posted here.
*This post is locked for comments