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!

7 comments:

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

    ReplyDelete

  2. Hello ! thank you it was really fun! If you like programire and interesting facts so you should go to check for more here
    click


    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. insert_recordset XYZ (Id) select Id from ABC where ABC.Id like '*1*';
    is throwing "Cannot execute a data definition language command on ABC (ABC). Employee: 0.
    The SQL database has issued an error." can any one of you help in this?

    ReplyDelete