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

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

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

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
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
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

Hi,

Did you get this issue fixed in the meantime?

Was this reply helpful? Yes No
JEMT 178 on at

Like (0)

Report

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

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

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

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