Join first line in AX 2012

Posted by on

You sometimes need to join only one related record for each parent record, for example one order line for each order header. I’m always suspicious of such requirements, because it often means that users/analysts didn’t realize that there may be more than one record, or they didn’t think enough of identifying the one record they actually need. Nevertheless there are valid scenarios when showing only the first or the last record is exactly what’s required.

If you wrote it in T-SQL, you could use a subquery, but how to achieve the same in AX? If you read my blog post about Subqueries in views, you already know that computed columns can be for building subqueries. Let’s apply the same approach to this problem.

In my example, I’ll create a form showing information from sales lines and from the last shipment for each sales line. It’s based on a real implementation, nevertheless it’s simplified so we don’t have to deal with too complex computed column or form. (Also please don’t forget that computed columns are not supported in AX 2009 and older versions).

View

First of all, create a view and call it SalesLineLastShipmentView. Add SalesLine datasource and drag InventTransId field from the datasource to the Fields node.

ViewWithInventTransId

InventTransId is the primary key of the SalesLine table and we’ll later use it for a join with SalesLine. The view will also contain ShipmentId (the primary key of WMSShipment table), which will be used for a join as well. To get the ID of the last shipment, first create a method on the view and put the following code there:

public static server str lastShipmentId()
{
    SysDictTable orderTransDt = new SysDictTable(tableNum(WMSOrderTrans));
 
    SysDictTable productCategoryDt = new SysDictTable(tableNum(EcoResProductCategory));
    DictView dv = new DictView(tableNum(SalesLineLastShipmentView));
 
    str s = strFmt('SELECT TOP 1 %1 FROM %2 WHERE %2.%3 = %4 ORDER BY %5 DESC',
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, ShipmentId), DbBackend::Sql),
        orderTransDt.name(DbBackend::Sql),
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, InventTransId), DbBackend::Sql),
        dv.computedColumnString(tableStr(SalesLine), fieldStr(SalesLine, InventTransId), FieldNameGenerationMode::WhereClause),
        orderTransDt.fieldName(fieldNum(WMSOrderTrans, DlvDate), DbBackend::Sql));
 
    return strFmt('ISNULL((%1), \'\')', s);
}

If it’s not clear what it does, wait a moment until we look at the resulting SQL query; it will be much more readable.

Now right-click the Fields node of the view and choose New > String Computed Column. Open properties of the column and set them as follows:

  • Name = ShipmentId
  • ExtendedDataType = WMSShipmentId
  • ViewMethod = lastShipmentId

Properties

Save the view, open it in table browser if you have some shipments in your system, you should see them mapped to InventTransIds.

ViewInTableBrowser

If you’re curious, open SQL Server Management Studio, find views in your AX database, right-click SalesLineLastShipmentView view and choose Design. It opens a view designer and you can find the actual SQL query there. This extremely useful especially if your view doesn’t work as expected and you want to see how exactly was translated to SQL.

This is the definition of our view:

SELECT INVENTTRANSID, DATAAREAID, PARTITION, RECID, CAST(ISNULL
	((SELECT TOP (1) SHIPMENTID
		 FROM dbo.WMSORDERTRANS
		 WHERE (INVENTTRANSID = T1.INVENTTRANSID)
		 ORDER BY DLVDATE DESC), '') AS NVARCHAR(10)) AS SHIPMENTID
FROM dbo.SALESLINE AS T1

You should recognize the subquery defined in our view method. It finds the first only (TOP (1)) WMSOrderTrans record for each SalesLine and return the ShipmentId. If none is find, it returns an empty string instead of NULL (because AX doesn’t expect NULL values in this case). That’s handled by ISNULL() function.

Note that you should always use ORDER BY if you look for first or last records, because otherwise the order is not defined and a different row may be returned every time you execute the query.

Form

Now we have a mapping between InventTransId and ShipmentId and because we want to see more fields in a form, we have to join tables to our view and build a form showing the fields.

Create a new form and add three datasources:

  • SalesLine (the root datasource)
  • SalesLineLastShipmentView (inner-joined to SalesLine)
  • WMSShipment (joined SalesLineLastShipmentView with outer join, because some sales lines have no shipment at all)

Because we don’t have any relation between SalesLine and SalesLineLastShipmentView, we have to add it in code. Override init() method of SalesLineLastShipmentView datasource and add the following code below super():

this.queryBuildDataSource().addLink(fieldNum(SalesLine, InventTransId),
                                    fieldNum(SalesLineLastShipmentView, InventTransId));

We don’t need the same procedure for WMSShipment, because it’s already handled by the table relation on WMSShipmentID data type.

The last thing is to create a form design. To keep is simple, create a grid and drag some fields there from SalesLine and WMSShipment.

FormWithData

Summary

Views are powerful even without computed columns. For example, if you simply wanted the highest shipment ID for each line, you could build a view like this (and join it with SalesLine and WMSShipment tables):

SELECT InventTransId, MAX(ShipmentId) AS ShipmentId FROM WMSOrderTrans
    GROUP BY InventTransId

Nevertheless computed columns add another dimension to views and allow you to use many more features of T-SQL. This ultimately allows you to implement business requirements that would be difficult to achieve by other means. Unfortunately the syntax for defining computed is far from perfect – it’s not easy to write nor read and you get little compile-time control. Here definitely is room for improvement. But this is definitely no argument for giving up all the power that computed columns offer.

Download

Zipped .xpo with the view and the form (built in AX 2012 R3

22 Comments

  1. Hi Martin,

    The topic is very beneficial one. Thanks for your post.

    the question I am asking is not related to the current post but it is a generic one.
    Adding View as datasource to a form takes more time for the data to display and it affects the performance. Could you please suggest to fast up the data retrieval.

    Thank you,
    Ramakrishna T.

  2. There is no general problem with views and therefore no general solution. It’s not clear what you’re comparing the view with if you say it takes more time (than something else). You’ll have to identify your particular problem before you can start resolving it.

  3. Another solution involves a NOTEXISTS join + extended query syntax. Take the related record table datasource, add a datasource of the same table with a notexists join to it, add the relation and then use the extended syntax in a range to compare via RecId (“(RecId < Parent.RecId)" for example). That one would work in 2009 as well.

    Btw, shouldn't the example code have comapred PARTITION and DATAAREAID in the computed column as well?

    • I’m well aware of notexists joins, but I don’t see how you woould replace the this subquery with a join. You don’t know which RecIds to exclude – finding the right record is the whole point of the subquery with a sort and TOP 1.

      Yes, you would have to take partitions and companies into account in a real implementation.

      • Sorry for forgetting to mention that,
        yes, the new NE datasource should have relations so that it exclude all records which don’t satisfy the original join.

        We can always do this exclusion since AX 2009+ allows a joined datasource to have relation conditions against the its parent’s parent. In most cases though, it is possible to avoid that and do the relation versus the related table instead, allowing for a simpler solution.

        For example:
        DataSource_A(Main)->DataSource_B(Related). We add to the related datasource another datasource – using same table.
        So now: A->B->B_NE. A->B has the ordinary relation, say A.AccountNum = B.AccountNum. B->B_NE is an NE join, where
        B.AccountNum = B_NE.AccountNum, and we add a range to RecId in B_NE: “(RecId > B.RecId)”. What we’re asking is:
        ‘For every record of A, give me all records of B which have the same AccountNum – and where there does not exist another record in B which have the same AccountNum but a lower RecId.’ Since RecId is unique, only one record for each A could satisfy that.

        • Oops, meant ‘higher recId’ in that sentence, and ‘only one B record for each A’.

          • I still don’t see how you would achieve the same thing I did without a subquery. Can you please share the SQL statement that would do that?
            What you’re describing doesn’t seem to meet the requirements, as far as I can say in the moment.

          • I designed it in AX, pastebin:
            http://www.pastebin.ca/3063040

            Here’s the view definition in SQL:

            SELECT T1.ACCOUNTNUM, T1.DATAAREAID, T1.PARTITION, T1.RECID, T2.DATAAREAID AS DATAAREAID#2, T2.PARTITION AS PARTITION#2, T2.VOUCHER
            FROM (SELECT VIRT.ID AS DATAAREAID, T1.ACCOUNTNUM, T1.PARTITION, T1.RECID
            FROM dbo.VENDTABLE AS T1 INNER JOIN
            dbo.VIRTUALDATAAREALIST AS VIRT ON T1.DATAAREAID = VIRT.VIRTUALDATAAREA
            UNION ALL
            SELECT T1.DATAAREAID, T1.ACCOUNTNUM, T1.PARTITION, T1.RECID
            FROM dbo.VENDTABLE AS T1 INNER JOIN
            dbo.DATAAREA AS DAT ON T1.DATAAREAID = DAT.ID AND DAT.ISVIRTUAL = 0) AS T1 INNER JOIN
            dbo.VENDTRANS AS T2 ON T1.ACCOUNTNUM = T2.ACCOUNTNUM AND T1.DATAAREAID = T2.DATAAREAID AND T1.PARTITION = T2.PARTITION
            WHERE (NOT EXISTS
            (SELECT ‘x’ AS Expr1
            FROM dbo.VENDTRANS AS T3
            WHERE (RECID > T2.RECID) AND (PARTITION = T2.PARTITION) AND (T2.ACCOUNTNUM = ACCOUNTNUM) AND (T2.DATAAREAID = DATAAREAID) AND (T2.PARTITION = PARTITION)))

          • And this is a cleaner sql, as we can see, only one or zero vouchers are returned for a vendor :

            SELECT T1.ACCOUNTNUM, T2.VOUCHER
            FROM dbo.VENDTABLE AS T1
            INNER JOIN dbo.VENDTRANS AS T2 ON T1.ACCOUNTNUM = T2.ACCOUNTNUM
            /*AND T1.DATAAREAID = T2.DATAAREAID AND T1.PARTITION = T2.PARTITION*/
            WHERE (NOT EXISTS
            (SELECT ‘x’
            FROM dbo.VENDTRANS AS T3
            WHERE (T3.RECID > T2.RECID) AND (T3.PARTITION = T2.PARTITION) AND (T2.ACCOUNTNUM = T3.ACCOUNTNUM) AND (T2.DATAAREAID = T3.DATAAREAID) AND (T2.PARTITION = T3.PARTITION)))

  4. Can you show how you’ll join exactly one shipment with the highest delivery date for each sales line?

    • I saw the question as ‘join only one related record for each parent record’, so I gave my solution for that (which also has a consistent order).

      Getting ‘highest shipping date’ is a somewhat different question. But still doable with this method! One way:

      A) Do the same query as earlier but using condition on DLVDATE. Because DLVDATE isn’t unique you can get multiple records with same maximum date. We will put this in a view. Because the data isn’t grouped, we can also put recId in the view. (SQL example below, can be done in AX directly as I have shown earlier).

      B) Do the same recId-query as I gave earlier, but use view as datasource rather than the original table. (SQL example below)

      **A)
      CREATE VIEW WMSMAXORDERTRANS AS
      SELECT T1.InventTransId, T2.ShipmentId, T2.RECID
      FROM dbo.SALESLINE AS T1
      LEFT OUTER JOIN dbo.WMSORDERTRANS AS T2 ON T1.InventTransId = T2.InventTransId
      WHERE (NOT EXISTS
      (SELECT ‘x’
      FROM dbo.WMSORDERTRANS AS T3
      WHERE (T3.DLVDATE > T2.DLVDATE)
      AND (T2.InventTransId = T3.InventTransId)))

      **B)
      SELECT T1.InventTransId, T2.ShipmentId
      FROM dbo.SALESLINE AS T1
      LEFT OUTER JOIN dbo.WMSMAXORDERTRANS AS T2 ON T1.InventTransId = T2.InventTransId
      WHERE (NOT EXISTS
      (SELECT ‘x’
      FROM dbo.WMSMAXORDERTRANS AS T3
      WHERE (T3.RECID > T2.RECID)
      AND (T2.InventTransId = T3.InventTransId)))

        • Thank you for your contribution, nevertheless I believe you now see why I said that your approach couldn’t be used to achieve the same result as what what this blog post is about.

  5. Thanks Martin for share your knowledge, that is a good post!!! I think that is a solution when need to filter a display method into form.

  6. Thanks a lot for the great post!
    Is there a way to retrieve multiple fields from one subquery instead of making the same subquery execute for each field?

    Subquery:
    SELECT TOP 1 PriceUnit, QtyOrdered FROM SalesLine WHERE SalesLine.SalesId = SalesId

    And have 2 computed fields, Price and Quantity, populated from this subquery.

    • No, you can’t define two computed columns that would somehow share the same subquery. Nevertheless you can get an identifier of the line (such as InventTransId or RecId) from your computed column and then use a join to get fetch fields you need.

      • So I guess the join that would use this identifier will be performed outside the given view (in another, outer, view probably?).
        Anyway, thanks a lot for your feedback!

        • The view gives you the ID and you can then use a query to join it with other tables. That’s exactly what my blog post demonstrates.
          Of course you can use the query in another view, if it makes sense for your particular scenario.

  7. Hey Martin,

    thank you for your helpful examples. When I was using computed columns with subqueries in AX7 I noticed that you have to select the right dataAreaId by yourself. At least that is what I have experienced when I tried to retrieve the shipmentId from WMSOrderTrans (I added the a dataArea clause to the sql expression):

    SysDictTable orderTransDt = new SysDictTable(tableNum(WMSOrderTrans));
    str computedColumnInventTransId = SysComputedColumn::returnField(_viewTableStr, _datasourceName, _inventTransIdFieldStr);
    str computedColumnDataAreaId = SysComputedColumn::returnField(_viewTableStr, _datasourceName, _dataAreaIdFieldStr);

    str s = strFmt(‘SELECT TOP 1 %1 FROM %2 WHERE %2.%3 = %4 AND %2.%5 = %6 ORDER BY %7 DESC’,
    orderTransDt.fieldName(fieldNum(WMSOrderTrans, ShipmentId), DbBackend::Sql),
    orderTransDt.name(DbBackend::Sql),
    orderTransDt.fieldName(fieldNum(WMSOrderTrans, InventTransId), DbBackend::Sql),
    computedColumnInventTransId,
    orderTransDt.fieldName(fieldNum(WMSOrderTrans, DataAreaId), DbBackend::Sql),
    computedColumnDataAreaId,
    orderTransDt.fieldName(fieldNum(WMSOrderTrans, DlvDate), DbBackend::Sql));

    return strFmt(‘ISNULL((%1), \’\’)’, s);

    Johannes

    • Yes, it’s the same in AX 2012. It’s very clear when you look at the definition of the view in the post – it clearly doesn’t have any filters by Partition and DataAreaId.
      I didn’t want to complicate the post, but it seems that I should have pointed it out, because you’re not the first one commenting on it.

  8. Hey Martin,

    In above example you are fetching data from SalesLine and only last shipment Id (i.e. one field) for those lines.
    Is there any way we can get all the data related to the last shipment Id against each SalesLine and make a view out of that data?

    • OH got it. My bad. I have somehow missed the last section.
      Thanks for the blog btw 🙂

Comments are closed.