WoW SysQuery ur in AX for this purpose ?

Hi Everyone,

I have been using sysquery for various purpose without knowing its exact use.

Recently i had to write a lookup in a form but the lookup has to filter only those records with empty value for one particular field.

For example, If we have four fields (F1, F2, F3 and F4) in the table Table.

To create a basic lookup we have the following code:

Query query = new Query();

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(Table), this);

sysTableLookup.addLookupField(fieldNum(Table, F1));

sysTableLookup.addLookupField(fieldNum(Table, F2));

qbds = query.addDataSource(tableNum(Table));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

Assume there is requirement that i need to filter only those records whose value is empty. Let us take we need to filter only those records with empty value F3.

Then below is lookup code for the same.

Query query = new Query();

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(Table), this);

sysTableLookup.addLookupField(fieldNum(Table, F1));

sysTableLookup.addLookupField(fieldNum(Table, F2));

qbds = query.addDataSource(tableNum(Table));

qbds.addRange(fieldNum(Table, F3)).value(strmin());// or 

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

but in front end i was still getting all the records and during debugging i checked the value of query, the query contains is "Select * from Table". 

But i had added a range F3 and value in the query but why the no range was added to the query?

Then my mind suggested that y not try SysQuery.

Query query = new Query();

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(Table), this);

sysTableLookup.addLookupField(fieldNum(Table, F1));

sysTableLookup.addLookupField(fieldNum(Table, F2));

qbds = query.addDataSource(tableNum(Table));

qbds.addRange(fieldNum(Table, F3)).value(SysQuery::valueEmptyString());// or 

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

Then on debug i got the value of query as "Select * from Table where Table.F3 == "" ".

Great right?. Thanks microsoft for this wonderful class.

The idea is if you want to filter any empty values on the fields then we should use SysQuery which helps to add range.

Please add your comments explaining your experiences working with SysQuery.