Microsoft Dynamics AX (Archived)

QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

(0) Share

Report

Posted on by JEMT

178

Hi Experts,

Need some help.

I customize a form that can view the some records but i want to remove some of it.

How to translate this SQL Query to X++ Codes (Query).

select * from INVENTTRANSORIGIN
where ((((REFERENCEID not like '%INF-SO%' and REFERENCEID not like '%CLA-SO%' ) 
	or (DATAAREAID != 'wiz')) 
	and ((REFERENCEID not like '%INF-PO%') or (DATAAREAID != 'inf'))) 
	and ((REFERENCEID not like '%CLA-PO%') or (DATAAREAID != 'cla')))

public void init()
{
    super();
    
    qbdsTrans = this.query().dataSourceTable(tableNum(InventTransOrigin));
    
}

public void executeQuery()
{

  qbrDataAreaId = qbdsTrans.addRange(fieldNum(InventTransOrigin, DataAreaId));
  
    super();
}

Thanks & Regards,

*This post is locked for comments

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Jhon ,

Read this article:

docs.microsoft.com/.../xpp-data-query

https://docs.microsoft.com/en-us/dynamicsax-2012/developer/select-statement-syntax

https://stoneridgesoftware.com/x-select-statements-that-look-more-like-sql/

Was this reply helpful? Yes No
JEMT 178 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Hossein,

Thanks for the reply you link is useful. I know that query syntax in X++

but how about in Datasource form (init and executequery) ?

Thanks.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Jhon Edmar Tarlac ,

Here is the tutorial of Expressions in query ranges:www.axaptapedia.com/Expressions_in_query_ranges

Best Regards

Tom Cai

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report
RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

X++ code is this:

INVENTTRANSORIGIN iNVENTTRANSORIGIN; select * from iNVENTTRANSORIGIN where (((!(iNVENTTRANSORIGIN.REFERENCEID like '*INF-SO*') &&!iNVENTTRANSORIGIN.REFERENCEID like '*CLA-SO*' ) || (iNVENTTRANSORIGIN.DATAAREAID != 'wiz')) &&(!(iNVENTTRANSORIGIN.REFERENCEID like '*INF-PO*') || (iNVENTTRANSORIGIN.DATAAREAID != 'inf'))) &&(!(iNVENTTRANSORIGIN.REFERENCEID like '*CLA-PO*') || (iNVENTTRANSORIGIN.DATAAREAID != 'cla')))

Was this reply helpful? Yes No
JEMT 178 on at

Like (0)

Report
RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Tom,

Yean i already check that page and i tried this code below.

qbds1 = this.query().dataSourceTable(tableNum(InventTransOrigin)); qbrTrans4 = qbds1.addRange(fieldNum(InventTransOrigin, DataAreaId)); qbrTrans4.value(strFmt('(((!(%1 like *%2*) && !%1 like *%3*) || (%4 != %5)) && (!(%1 like *%6*) || (%4 != %7))) && (!(%1 like *%8*) || (%4 != %9)))', fieldNum(InventTransOrigin, ReferenceId), //%1 queryValue('INF-SO'), // %2 queryValue('CLA-SO'), // %3 fieldNum(InventTransOrigin, DataAreaId), //%4 queryValue('wiz'), // %5 queryValue('INF-PO'), //%6 queryValue('inf'), //%7 queryValue('CLA-PO'), //%8 queryValue('cla'))); //%8

but i got error.

Query extended range failure: The '!' operator can only be used with the 'like' keyword.

Was this reply helpful? Yes No
Hossein.K 6,648 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi,

Did you get this issue fixed in the meantime?

Was this reply helpful? Yes No
JEMT 178 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Hossein,

No, i did not yet fix

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Jhon,

Do you try my code?

Can you tell me about what you want?

Was this reply helpful? Yes No
JEMT 178 on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Hi Hossein,

I did not try your code because i already do that using JOB but what i want to achieve now by using QueryBuildDatasource and Range in the Form and Datasource.

I already share above the code i tried and the error.

Can you please check my code above? it seems like i missing something...

Thanks in advance.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

RE: QueryBuildRange in Datasource FORM: How to AND, OR and put NOT LIKE in X++

Regarding your error message "the '!' operator can only be used with the 'like' keyword."

Try using "NOT LIKE" instead of the ! mark.

Was this reply helpful? Yes No