Microsoft Dynamics AX (Archived)

Filter grid record

(0) Share

Report

Posted on by JAYAKRISHNAN M S

145

i have 2 tables header and line.

filter line grid 'InventTable' record by if any header exist without executequery relation(exists or NotExist).

so i used below code.

container con having header values like 'ItemId'

for(i = 1; i <= conLen(con); i++)
{
info(strFmt("ItemId inside execqry: %1", conPeek(con, i)));

qbr = qbds.addRange(fieldNum(InventTable, ItemId));
qbr.value(SysQuery::valueNot(conPeek(con, i)));
}

above code havn't any error but didnt get expected result.

these filtered only last record.

Anybody help.....

Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (4)

Sort by

Verified answer

Martin Dráb 239,385 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Why exactly do you want to avoid the right solution - a join? It would be a more efficient solution for less work.

Was this reply helpful? Yes No
JAYAKRISHNAN M S 145 on at

Like (0)

Report
Copy link

Link copied!

Already done with joins and this is another task assigned.

As above executeQuery returns query like

"SELECT * FROM InventTable(InventTable_1) WHERE ((NOT (ItemId = N'100-000026')) OR (NOT (ItemId = N'100-000025')) OR (NOT (ItemId = N'100-000122')))".

can i replace 'OR' instead of 'AND' condition..?

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

You can, by using query expressions instead of adding the range in a for loop. If you search online, you can easily find a lot of examples.

Was this reply helpful? Yes No
Verified answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Copy link

Link copied!
Hi,

There are quite a number of ways to accomplish one way is to construct the required range value and pass it to the query(as below code).

static void QueryAnd(Args _args) { QueryBuildRange qbr; QueryBuildDataSource qbds; Query query = new Query(); int i; str1260 itemIds; container con; qbds = query.addDataSource(tableNum(InventTable)); con = ["X1","X2", "X3"]; for(i = 1; i <= conLen(con); i++) { itemIds = (itemIds == "") ? strFmt("!%1", any2str(conPeek(con, i))) : strFmt(itemIds + ", !%1", any2str(conPeek(con, i))); } qbds.addRange(fieldNum(InventTable, ItemId)).value(itemIds); }

Output:

NAME: qbds
VALUE:
SELECT * FROM InventTable(InventTable_1) WHERE ((NOT (ItemId = N'X1') AND NOT (ItemId = N'X2') AND NOT (ItemId = N'X3')))
TYPE: QueryBuildDataSource

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Jayakrishnan

You can try this.

static void Job3(Args _args)
{
container con;
Query query;
QueryBuildDataSource qbds;
QueryRun queryRun;
InventTable inventTable;
str queryString;
int i;

con = ["T0013","T0020","T0100"];

query = new query();

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

for(i = 1; i <= conLen(con); i++)
{
queryString = queryString + strFmt("(%1 != %2%3%4)",fieldStr(InventTable,ItemId),'"',conPeek(con,i),'"');

if(i != conLen(con))
queryString = queryString + " && ";
}

qbds.addRange(fieldNum(InventTable, ItemId)).value(queryString);
queryRun = new QueryRun(query);

while(queryRun.next())
{
inventTable = queryRun.get(tableNum(inventTable));
info(inventTable.ItemId);
}

info(query.toString());
}

Was this reply helpful? Yes No
JAYAKRISHNAN M S 145 on at

Like (0)

Report
Copy link

Link copied!

Thank you All........

Small change become added nd its correctly working.

"queryString = queryString + strFmt("(%1.%2 != %3%4%5)",query.dataSourceTable(tableNum(InventTable)).name(),fieldStr(InventTable,ItemId),'"',conPeek(con,i),'"');"

Was this reply helpful? Yes No