Display method to pull most recent confirmation

Hi all,

Still new to AX, so far I’ve been able to get away with only using very basic display methods. However, this one involves multiple sources and it eludes me.

I would like to add a display method to the Open Sales Order Lines form (Sales and Marketing>Inquiries>Order Status). This would pull the document name of the most recent confirmation for that particular sales line. We need this in order to gain visibility for partially-confirmed orders (eg. a two line order which has only had one line confirmed).

I’m not entirely sure how I would go about this, but I wrote the following SQL query which I believe will have the desired effect?

SELECT FIRST CustConfirmJour.ConfirmDocNum
FROM CustConfirmJour JOIN custConfirmTrans ON custconfirmJour.confirmID = custconfirmTrans.confirmID JOIN SalesLine ON SalesLine.InventTransID = custConfirmTrans.InventTransId

The purpose of this would be that for each sales line you can see what the last confirmation printed for it is. However, it will also be necessary to put a range on this so you only retrieve blank values (ie. sales lines that haven’t been confirmed yet) which could be a problem due to the nature of display methods.

I have no idea how I would go about putting a method on the SalesLine table with such an advanced query.

Honestly any help is very much appreciated, thanks in advance

Luke

Your query asks for the first record, but doesn’t define any sorting, which doesn’t make much sense. If you want “the most recent confirmation”, you should sort the transactions by ConfirmDate.

If you know how how to use a simple select statement in a display method, what problem do you have with a slightly more complex select? It’s the same thing. Or is your problem actually in writing the query?

We have Last* methods on saleTable to get last packing slip, last confirmation…

\Data Dictionary\Tables\SalesTable\Methods\lastConfirmDate

You can use them for reference.

Hi Martin,

I just threw up the statement I had to explain my thought process - thank you for reminding me about the sort however.

My problem is in integrating an SQL statement like this into a display method at all - up until now I have only ever needed to return calculations on variables from the current table.

If I created on the SalesLine table a display method called ReturnConfirmation for example, what would it look like to slot my SQL query into it?

Sorry I didn’t explain that very well, hope to hear back from you,

Luke

Thank you very much, this is very helpful!! :slight_smile: