Answered

QueryBuildDatasource add range not equal to enum value

(0) Share

Report

Posted on by ALAhmed

916

I'm in AX 2012 R3 environment.I would like to add a query range to GeneralJournalEntry table and filter out rows that have a JournalCategory value different to LedgerTransType::Purch , i try this but not works :

datasource.addRange(fieldnum(GeneralJournalEntry,JournalCategory)).value(SysQuery::valueNot(enum2str(LedgerTransType::Purch)));

Update : it works for one range ,but what if i want add multiple value to range like this :

datasource.addRange(fieldnum(GeneralJournalEntry,JournalCategory)).value(SysQuery::valueNot(LedgerTransType::Sales));
datasource.addRange(fieldnum(GeneralJournalEntry,JournalCategory)).value(SysQuery::valueNot(LedgerTransType::Purch));

In sql we have : 
select * from GENERALJOURNALENTRY where GENERALJOURNALENTRY.JOURNALCATEGORY not in (2,3)

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

b_radlyjames 3,155 on at

Like (0)

Report

the 'addRange' method is the way to do this. Make sure you use enum2str on your 2 addRange lines.

Was this reply helpful? Yes No
Verified answer

Anton Venter 20,345 Super User 2025 Season 2 on at

Like (1)

Report

Standard query ranges result in an "OR" operator in the SQL and when you are doing a "NOT", you should have "AND" instead. To achieve this in AX using query ranges, use expressions in query ranges, see http://www.axaptapedia.com/Expressions_in_query_ranges

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

This also works:

this.query().dataSourceTable(tablenum(PurchTable)).addRange(fieldnum(PurchTable, InvoiceAccount)).value('!Transfer'+','+'!5050');

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (3)

Report

This works with multiple ENUMS:

this.query().dataSourceTable(tablenum(PurchTable)).addRange(fieldnum(PurchTable, PurchStatus)).value(strfmt('!%1,!%2',enum2str(PurchStatus::Canceled),enum2str(purchstatus::Invoiced)));

Was this reply helpful? Yes No
Dang Vo 31 on at

Like (0)

Report

that good for me

Was this reply helpful? Yes No