Regex in QueryBuildRange value?

(0) Share

Report

Posted on by Community Member

Is it possible to use regular expressions in QueryBuildRange value method?

I want to get the name of the countries that start with an English Alphabet (a-zA-Z).

The SQL query is working perfectly in the SQL Server Management Studio and is as:

select TRANS.COUNTRYREGIONID, SHORTNAME from 
[AxDB].[ax].[LOGISTICSADDRESSCOUNTRYREGIONTRANSLATION] as TRANS,
[AxDB].[ax].[LOGISTICSADDRESSCOUNTRYREGION] as COUNTRY
where COUNTRY.COUNTRYREGIONID = TRANS.COUNTRYREGIONID
and SHORTNAME like '[a-zA-Z]%'
group by TRANS.COUNTRYREGIONID, SHORTNAME;

The query returns the following (only 3 records are shown to indicate what I am trying to achieve):

CountryRegionId	ShortName
AUS	Australia
ARG	Argentina
BRA	Brazil
...	...

The LogisticsAddressCountryRegion and LogisticsAddressCountryRegionTranslation have been added to a form as FormDataSource, and LogisticsAddressCountryRegionTranslation is InnerJoin'd to the LogisticsAddressCountryRegion via CountryRegionId foreign key relation. What I want is to display the CountryRegionId and ShortName on a grid control on a form and have changed the parent datasource's executeQuery() method as:

        public void executeQuery()
        {
            this.query().dataSourceTable(tableNum(LogisticsAddressCountryRegionTranslation))
                .addRange(fieldNum(LogisticsAddressCountryRegionTranslation, ShortName))
                .value(Global::queryValue('[a-zA-Z]*')); 

            this.query().dataSourceTable(tableNum(LogisticsAddressCountryRegion))
                .addGroupByField(fieldNum(LogisticsAddressCountryRegion, CountryRegionId));

            this.query().dataSourceTable(tableNum(LogisticsAddressCountryRegionTranslation))
                .addGroupByField(fieldNum(LogisticsAddressCountryRegionTranslation, ShortName));

            super();
        }

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

Martin Dráb 237,978 Most Valuable Professional on at

Like (0)

Report

You don't need distinguishing between lowercase and upper-case, because AX database isn't case sensitive.

But AX doesn't allow you to use regular expressions in the WHERE clauses; it supports only wildcards (* and ?). You would have to find a different solution (e.g. two conditions, 'higher or to equal to a' and 'lower or equal to z'). If you're dealing with data corruption (instead of writing normal business logic), am direct SQL query may be the best option.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

What do you mean by direct SQL query? Is there a way to overwrite the base query for a particular datasource object using string literal representation i.e. "select * from xyz"?

and thank you, the solution worked perfectly using .value(Global::queryRange('a*', 'z*'));

Was this reply helpful? Yes No
Martin Dráb 237,978 Most Valuable Professional on at

Like (1)

Report

I mean that you could open SQL Server Management Studio in a development or a test environment and run a T-SQL code directly. The query looks to me as if you have a problem with invalid data in country names.

It's also possible to execute T-SQL from X++ through the Connection class, but it's tricky (no compile-time control, bypassing security, the need to handle partitions and data areas explicitly, difficult processing of output and so on) and therefore you generally shouldn't use it. Note that you can achieve something similar with computed columns in views.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thankyou for the information, I will gladly look those up.

The reason for executing the query is because the tables has country names in multiple languages, thats why filtering them based on their alphabetic literals. I also found out that we can filter using the following code as well (similar to queryRange):

QueryBuildRange.value(Global::queryValue(a*..z*));

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,978 Most Valuable Professional on at

Like (1)

Report

If your intention is filtering by language, use the LanguageId field. Filtering by a-z is very vaguely related to language. For example, if you had Czech translation, your query would include some countries (e.g. Dánsko) but not all (e.g. Čína).

Was this reply helpful? Yes No

Community site session details

Regex in QueryBuildRange value?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Regex in QueryBuildRange value?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates