AX 7. How to create new excel template for general journal lines.

Open lines in Excel is a new cool feature in AX 7 that allows you to edit general journal lines using excel.

OpenInExcel

But unfortunately it supports only two account types out of the box: ledger account and customer account. Today we will create a new one for bank.

First of all, we need to create a new Data Entity. There is no difference between Data Entities for account types so I simply copied LedgerJournalLine entity, renamed it and changed AccountType range value to “Bank”.

DataEntity

Public collection name and public entity name properties should be changed, relation entity roles should be updated as well.

Next we need to create a new Excel template file.  I used Excel workbook designer in AX to generate new template based on the new Data Entity. I made my design pretty similar to existing templates for general journal.

Excel

Please note that template has two data entities: one for header and another for lines. As you can see I took the same header entity as other general journal templates has.

This template file should be added to AOT into resources node.

Finally, we need to create a new class. This class should be derived from DocuTemplateRegistrationBase and implement LedgerIJournalExcelTemplate.

 

using Microsoft.Dynamics.Platform.Integration.Office;

/// <summary>
/// The <c>BankJournalExcelTemplate</c> is the supporting class for the Bank Journal Template.
/// </summary>
class BankJournalExcelTemplate extends DocuTemplateRegistrationBase implements LedgerIJournalExcelTemplate
{
    // resource that contains xlsx file.
    private const DocuTemplateName ExcelTemplateName = resourceStr(BankJournalTemplate);
    // lines Data Entity
    private const EntityName LineEntityName = tableStr(BankJournalLineEntity);
    private const FieldName LineEntityJournalNum = fieldStr(BankJournalLineEntity, JournalBatchNumber);
    private const FieldName LineEntityDataAreaId = fieldStr(BankJournalLineEntity, dataAreaId);
    private const FieldName HeaderEntityName = tableStr(LedgerJournalHeaderEntity);
    private const FieldName HeaderEntityJournalNum = fieldStr(LedgerJournalHeaderEntity, JournalBatchNumber);
    // header Data Entity.
    private const FieldName HeaderEntityDataAreaId = fieldStr(LedgerJournalHeaderEntity, dataAreaId);

    public void registerTemplates()
    {
        this.addTemplate(OfficeAppApplicationType::Excel,
                         ExcelTemplateName,
                         ExcelTemplateName,
                         "@GeneralLedger:LedgerJournalLineEntryTemplateDescription",
                         "@MyLabels:BankLedgerDailyJournalTemplateName",
                         NoYes::No,
                         NoYes::No);
    }

    public boolean isJournalTypeSupported(LedgerJournalType _ledgerJournalType)
    {
	// only daily journals are supported in this template.
        return _ledgerJournalType == LedgerJournalType::Daily;
    }

    public DocuTemplateName documentTemplateName()
    {
        return ExcelTemplateName;
    }

    public Set supportedAccountTypes()
    {
	// Set with supported account types.
        Set accountSetTypes = new Set(Types::Integer);

        accountSetTypes.add(LedgerJournalACType::Bank);
        accountSetTypes.add(LedgerJournalACType::Ledger);

        return accountSetTypes;
    }

    public Set supportedOffsetAccountTypes()
    {
        // Set with supported offset account types.
        Set offsetAccountTypeSet = new Set(Types::Integer);

        offsetAccountTypeSet.add(LedgerJournalACType::Ledger);

        return offsetAccountTypeSet;
    }

    public boolean validateJournalForTemplate(LedgerJournalTable _ledgerJournalTable)
    {
        return LedgerJournalExcelTemplate::validateJournalForTemplate(_ledgerJournalTable, this);
    }

    public EntityName headerEntityName()
    {
        return HeaderEntityName;
    }

    public EntityName lineEntityName()
    {
        return LineEntityName;
    }

    public FieldName headerJournalBatchNumberFieldName()
    {
        return HeaderEntityJournalNum;
    }

    public FieldName headerDataAreaFieldName()
    {
        return HeaderEntityDataAreaId;
    }

    public FieldName lineJournalBatchNumberFieldName()
    {
        return LineEntityJournalNum;
    }

    public FieldName lineDataAreaFieldName()
    {
        return LineEntityDataAreaId;
    }

    public FilterCollectionNode appendHeaderEntityFilters(FilterCollectionNode _headerFilter, ExportToExcelFilterTreeBuilder _headerFilterBuilder)
    {
        return _headerFilter;
    }

    public FilterCollectionNode appendLineEntityFilters(FilterCollectionNode _lineFilter, ExportToExcelFilterTreeBuilder _lineFilterBuilder)
    {
	// creates excel filter to show only lines that have Bank account type
        // and Ledger offset account type
        FilterCollectionNode lineFilter = _lineFilterBuilder.and(
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, AccountType), LedgerJournalACType::Bank),
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, OffsetAccountType), LedgerJournalACType::Ledger));

        return _lineFilterBuilder.and(_lineFilter, lineFilter);
    }
}

As you can see, code is pretty straightforward and there is only one interesting method appendLineEntityFilters(). It uses FilterCollectionNode to create filters for template, so this template will show only journal lines that has account type bank and offset account type ledger.

When development is done, we need to reload system templates. Go to Common -> Office integration -> Document templates and click “Reload system templates”.

ReloadSystemTemplates.jpg

After that, our new template should appear under Open lines in Excel button.

OpenLinesInExsel.jpg

Advertisements

10 thoughts on “AX 7. How to create new excel template for general journal lines.

  1. Preben Mortensen August 26, 2016 / 11:20 am

    How do you add or remove fields on an existing General journal. I’m not able to accss the new template created. It is not visible in the “open in excel” menu, and when modifing the filter to show the new template, the actual template opening is a project invoice template, not the modfied templated I created. Any tips ?

    • ievgensaxblog August 26, 2016 / 7:48 pm

      If you want to modify existing template you can go to Common -> Office integration -> Document templates, find excel template, modify it and upload with the same name. AX will pick it up and will work with it.

  2. Frank Bruemmer October 24, 2016 / 2:13 pm

    Hello, I’m trying to get the offset account (display value) to show on my form but it stays blank. The only thing I seem to be able to display is the offset main account number, but not gets attached to it. Any idea what I’m missing ?

    • ievgensaxblog October 25, 2016 / 7:28 am

      Hi Frank,
      Could you please share some details, what code do you have or what exactly are you doing ? Because this post was about Excel and you are asking about a form.

      • Frank Bruemmer October 25, 2016 / 2:27 pm

        Sorry, with form I actually meant the excel sheet. There’s a field ‘Offset account’ which is populated by the DisplayValue method on the OffsetAccountDAVC datasource on the VendInvoiceJournalLineEntity with the combination of “MainAccount-BankAccount”. For some reason I can’t get a value to show in excel for this field/method. I can show the offsetaccount.mainaccount field which is a different field and I was able to add a offsetAccount.bankaccount method as a customization. However, I just can’t seem to get any values displayed in offsetAccount.displayValue. Thanks.

  3. ievgensaxblog October 26, 2016 / 7:02 am

    It’s hard to say what exactly is wrong but what I can recommend you is to go SSMS and check the view (each Data Entity has a view behind), if this field is empty there you need to look at view statement and code that generates it.

    • Frank Bruemmer October 28, 2016 / 6:04 pm

      Yes, that’s why I asked. Microsoft support was unable to reproduce the issue. We will be upgrading to U2 soon, so maybe this will go away. For now I added an additional field to pass the bank account to Excel. Thanks for you help.

  4. Wilnard November 7, 2016 / 5:50 am

    Hi.

    Thank you for sharing this one for I’m on the same path on creating an entirely new data entity and excel template to upload General journal entry. My only question here is how were you able to come-up with the same design of the header in the excel template. I have been trying and I can’t figure out how to make it look exactly as the Standard AX.

    Thank you in advance for your help.

    • ievgensaxblog November 7, 2016 / 6:18 am

      Hi Wilnard,
      Actually nothing spacial, I just moved fields around and copied styles from standard spreadsheet using “Format Painter” tool.

      • Frank Bruemmer November 7, 2016 / 10:52 am

        Hi there, I actually figured out the problem. I had a ‘default’ dimension format set up for application integration (GL/Chart of Accounts/Setup/Dimensions/..) but I was missing one of type ‘ledger’. After adding that it works now.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s