Extend PurchTable2Line Framework (AX 2012)

This is a follow-up to my initial blog post how to extend the SalesTable2Line Framework from 2011. However, this post is a walkthrough how to update PurchLine fields from the PurchTable header.

Fields and Field Groups

Create an extended datatype called ERPCarrier which extends the Name datatype. Provide a label called Carrier.On the PurchLine create two new fields called ERPCarrierRequested and ERPCarrierConfirmed based on the datatype ERPCarrier. Provide two meaningful labels, Requested Carrier and Confirmed Carrier. Create a field group called ERPCarrier and add both fields to the group.

On the PurchTable add two new fields called ERPCarrierRequested and ERPCarrierConfirmed based on the datatype ERPCarrier. Provide the same labels as on the PurchLine. Create a field group called ERPCarrier and add both fields to the group. Moreover, add both fields to the field group HeaderToLineUpdate!

image

On the PurchTable form, add the PurchTable field group ERPCarrier in the header view in the group delivery.

image

Add the PurchLine field group ERPCarrier in the line view in the tab delivery.

image

Code

On the AxPurchTable class add two parm Methods for the two new fields

public ERPCarrierId parmERPCarrierConfirmed(ERPCarrierId _carrierId = “)
{
    if (!prmisDefault(_carrierId))
    {
        this.setField(fieldNum(PurchTable, ERPCarrierConfirmed), _carrierId);
    }

    return purchTable.ERPCarrierConfirmed;
}

public ERPCarrierId parmERPCarrierRequested(ERPCarrierId _carrierId = “)
{
    if (!prmisDefault(_carrierId))
    {
        this.setField(fieldNum(PurchTable, ERPCarrierRequested), _carrierId);
    }

    return purchTable.ERPCarrierRequested;
}

On the AxPurchLine class add two parm methods for the two new fields

public ERPCarrierId parmERPCarrierConfirmed(ERPCarrierId _carrierId = “)
{
    if (!prmisDefault(_carrierId))
    {
        this.setField(fieldNum(PurchTable, ERPCarrierConfirmed), _carrierId);
    }

    return purchLine.ERPCarrierConfirmed;
}

public ERPCarrierId parmERPCarrierRequested(ERPCarrierId _carrierId = “)
{
    if (!prmisDefault(_carrierId))
    {
        this.setField(fieldNum(PurchTable, ERPCarrierRequested), _carrierId);
    }

    return purchLine.ERPCarrierRequested;
}

Next, on the AxPurchLine class add two set methods

protected void setERPCarrierConfirmed()
{
    if (this.isMethodExecuted(funcName(),
                              fieldNum(PurchLine, ERPCarrierConfirmed)))
    {
        return;
    }

    this.setAxPurchTableFields();

    if (!this.parmERPCarrierConfirmed() &&
         this.axPurchTable().parmERPCarrierConfirmed())
    {
        this.parmERPCarrierConfirmed(
              this.axPurchTable().parmERPCarrierConfirmed());
    }
}

protected void setERPCarrierRequested()
{
    if (this.isMethodExecuted(funcName(),
                              fieldNum(PurchLine, ERPCarrierRequested)))
    {
        return;
    }

    this.setAxPurchTableFields();

    if (!this.parmERPCarrierRequested() &&
         this.axPurchTable().parmERPCarrierRequested())
    {
        this.parmERPCarrierRequested(
             this.axPurchTable().parmERPCarrierRequested());
    }
}

On the AxPurchLine class add a new static method which is used to set the new fields.

public static void setTableFields_ERPCarrier(XppPrePostArgs _args)
{
    AxPurchLine     thisAxPurchLine = _args.getThis();
    thisAxPurchLine.setERPCarrierRequested();
    thisAxPurchLine.setERPCarrierConfirmed();
}

On the AxPurchLine class, go to the setTableFields method and expand the event handler. Add a new Post X++ event handler. Provide the AxPurchLine as class for the event handler and the newly created method setTableFields_ERPCarrier as event handler method.

image

On the PurchTable2LineField class, open the getFieldDescription method and scoll down. Add  the following code to handle the two fields.

case fieldNum(PurchTable, ERPCarrierConfirmed):
    description =  fieldid2pname(tablenum(PurchLine), 
                                 fieldnum(PurchLine, ERPCarrierConfirmed));
    break;

case fieldNum(PurchTable, ERPCarrierRequested):
    description =  fieldid2pname(tablenum(PurchLine),
                                 fieldnum(PurchLine, ERPCarrierRequested));
    break;

Test

Compile your code an build incremental IL. Open the table PurchTable2LineParameters and delete all records. Restart the AOS to make sure no cached version is used. In AX go to Accounts Payable > Settings > Parameter > Tab Updates and click the button “Update order lines”. Set the Update Requested Carrier and Confirmed Carrier to Always.

image

Open a purchase order in AX and edit the purchase header. Provide a requested carrier e.g. UPS and a confirmed carrier e.g. DHL. Save your changes. Check if the values from the header have been copied to the purchase lines.

Multiple Aggregations in X++ Join

At work we’ve recently discussed how to calculate a customers invoice amount for multiple years. One idea was to loop over the customer accounts (while select)  and individually calculate the sum for each year with individual statements (select sum). However, this can be done by a single statement.

CustInvoiceJour     custInvoiceJour;
CustInvoiceTrans    custInvoiceTrans1;
CustInvoiceTrans    custInvoiceTrans2;

while select InvoiceAccount,InvoiceId from custInvoiceJour
group by InvoiceAccount
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans1
where custInvoiceJour.InvoiceId == custInvoiceTrans1.InvoiceId &&
custInvoiceTrans1.InvoiceDate >= str2DateDMY(‚1.1.2011‘) &&
custInvoiceTrans1.InvoiceDate <= str2DateDMY(‚31.12.2011‘)
outer join InvoiceId, sum(LineAmount) from custInvoiceTrans2
where custInvoiceJour.InvoiceId == custInvoiceTrans2.InvoiceId &&
custInvoiceTrans2.InvoiceDate >= str2DateDMY(‚1.1.2012‘) &&
custInvoiceTrans2.InvoiceDate <= str2DateDMY(‚31.12.2012‘)
{
info( strFmt(„Customer:%1 2011:%2 2012:%3“,
custInvoiceJour.InvoiceAccount,
custInvoiceTrans1.LineAmount,
custInvoiceTrans2.LineAmount));
}
//Customer:DE-001 2011:3.814.145,50 2012:3.842.223,00

This will result in the following output

Mutliple sum() in X++ Join

Check values for customer DE-001 with excel

Check Mutliple sum() in X++ Join

Add Sound to Infolog

I’ve been asked by a colleague if it is possible to let the Infolog make a noise depending on the log type, e.g. a *beep* for an info, a *pling* for a warning and an evil noise for an error. Fortunately we can use .NET in AX 2009. The System.Media.SystemSound class already provides access to the typical system sounds. I’ve modified the Info class in AX this way:

Exception add(
    Exception _exception,
    str _txt,
    str _helpUrl = “,
    SysInfoAction _sysInfoAction = null,
    boolean buildprefix = true)
{

    int numOfLines,i;
    int actionClassId;
    container packedAction;
    xSession session;   

    System.Media.SystemSound sound;
    InteropPermission permission;

    ;

// default infolog code here …

    permission = new InteropPermission(Interopkind::ClrInterop);
    permission.assert();
   
switch(_exception)
    {
        case(Exception::Info):
        sound = System.Media.SystemSounds::get_Asterisk();
        break;
        case(Exception::Warning):
        sound = System.Media.SystemSounds::get_Hand();
        break;
        case(Exception::Error):
        sound = System.Media.SystemSounds::get_Exclamation();
        break;
    }    
    if(sound!=null)
        sound.Play();

    CodeAccessPermission::revertAssert();

    return super(_exception, (buildprefix?getprefix():“)+_txt);
}

Visualize DocuRef notes in grid

With Dynamics AX you can attach document references like Word files, Excel sheets, mail, fax and notes to any displayed record. The icon to do so can be found in the forms toolbar. Unfortunately it’s hard to discover if a document is attached to a record. Therefore it’s helpful to display an icon in the overview grid to indicate attached documents.

document references

Create a new Integer extended data type called DocuRefIcon and label it with Documents (@SYS124737). Set the help text to Document Handling (@SYS22623).

Add a display method showDocuRefIcon() to the CustTable. Document references use a RefTableId and RefRecId to reference any record in Dynamics. If there is a document reference for the current CustTable record return resource id 3028 otherwise return id 0.

//BP deviation documented
display DocuRefIcon showDocuRefIcon()
{;
    if(DocuRef::exist(this.dataAreaId,this.TableId,this.RecId))
        return 3028;
 
    return 0;
}

Open the CustTable form and a new icon to the Overview grid. Set width and height properties to 16. Set CustTable as datasource and showDocuRefIcon as data method. Save and open the form. Attach a document reference to a customer and you’ll see a notes icon in the new grid column.

document handling icon in custtable grid