Thursday, February 10, 2011

Use X++ wildcard (LIKE and NOT LIKE) in X++ select statement

If you want to use wildcards in an AX X++ select statement (*), you will have to use the LIKE command. The following select statement will find all captions in the batch tasks (BatchHistory) table.

For x++ select statements:
select firstOnly batchHistory
     where batchHistory.Caption LIKE "*Test*"


For x++ queries:
queryBuildRange.value(*Test*);

Note the LIKE instead of a '==' and the wildcards inside of the quotations. All other combinations of wildcard usage will work here. This is the same functionality as what the users would put in the grid filtering(eg. '*TEST*' in the caption field filter on the batch tasks form).

However, if you want to find all Captions that do not have the word Test in them (NOT LIKE, !LIKE), you will have to modify the above example slightly.

For x++ select statements:
select firstOnly batchHistory
     where !(batchHistory.Caption LIKE "*TEST*");


For x++ queries:
queryBuildRange.value(!*Test*);

Note the "!(" and ")". Again, this is the same functionality as what the users would put in the grid filtering(eg. '!*TEST*' in the caption field filter on the batch tasks form).
I hope it helps you out!

3 comments:

  1. LIKE should be the last option to use. results may not be accurate and hits performance

    ReplyDelete