CODEWITHSUNDEEP
axaptax++dynamics-ax-2012dynamics-ax-2012-r3
Mike
Mike

Reputation: 1938

How to Join a data source using a `Like` `LEFT`

I first created a SQL query and now I'm converting it into an AOT query in AX 2012 R3. I have everything else working except for one thing. I have a join in my SQL that is: JOIN dbo.INVENTJOURNALTABLE AS INV3 ON INV.INVENTBATCHID LIKE left(INV3.DESCRIPTION,17). In SQL this works and returns the data that I need.

I don't know how to create this join in the AOT query though.

That INV3.DESCRIPTION is a string that contains the InventBatchID.

Is there a way to do this kind of join?

Upvotes: 0

Views: 860

Answers (1)

Alex Kwitny
Alex Kwitny

Reputation: 11564

In order to accomplish what you want with a Query object, it's difficult/unusual, and AX isn't designed to do this in a straight forward way and requires a very creative solution.

I would generally push towards writing a pure x++ query as I don't believe LEFT and LIKE can be natively combined, especially in a query. You can use * in the value for "like" as an option.

You may be able to accomplish using expressions in query ranges somehow.

If you must have a query, a way I can think is by combining a View, Computed Column, and a Query...and I can't guarantee it will work, but I can give you enough to have something to test with. Some of the information in this answer is pretty concentrated so look closely at everything to understand it.

Create a View, add the below computed column, then add it to a Query as pictured below. For the computed column, you need to add a new String field to the view and set the ViewMethod property to the name of the method. See here for more info about adding Computed Columns.

The Computed Column accomplishes the left(..., 17) part and you can browse the view to confirm.

The last part is trying to join either by a relation (pictured below, but it does not accomplish the like) or setting the Value property using an expression by following the link above. You may need to create a custom expression in \Classes\SysQueryRangeUtil. You have some experimenting to do to see if it works.

enter image description here

private static server str compColDescLeft()
{
    #define.ViewName(InventJournalTableView)
    #define.DataSourceName("InventJournalTable_1")
    #define.FieldDescription("Description")
    #define.LeftCount("17")

    str             sReturn;
    str             sLeftDesc;
    DictView        dictView;

    dictView = new DictView(tableNum(#ViewName));

    sLeftDesc = dictView.computedColumnString(#DataSourceName, #FieldDescription, FieldNameGenerationMode::FieldList, true);

    sReturn = "left(" + sLeftDesc + ", " + #LeftCount + ")";

    return sReturn;
}

Upvotes: 1

Related Questions