D365 - Adding field preview (fast tab summary) to Tab Page on Forms

 This is simple when you know the trick.

There are fields on a tab page (to the right side of the screen) that show a preview of some fields that are on the tab when you open it.

For example, sales order form:

If you add a new tabPage to this form, you may want these field previews for your fields in your group. 

A tabPage has a group under it, with fields in that group.

Go to a field in your group and look at the properties. There is a property called Fast Tab Summary. Set that property to YES and it will show up on your tab page.

D365 Positive pay Designer

 In this example, I had to create a positive pay file for Fifth Third Bank. They require a CSV file and six pieces of data on the file. Each record should be on its own row on the file.

1) ChequeStatus indicator - this will be an "I" if the chequeStatus type is "Payment", it will be a "V" on the file if the type is "Void" or "Rejected" or "Cancelled" and it will be empty if there is any other type on the record in the file.

2) AccountNum

3) ChequeNum

4) TransDate - format should be MM/dd/yyy

5) BankNegInstRecipientName - need to strip out commas since it's a csv file and commas will add columns

6) AmountCur - this should not have any decimal or comma, but should always show the dollars and cents. So 1200.43 should be 120043. 2243.00 should show 224300. Etc.

You must create a new configuration format based on the data model Positive pay model. I am not going to explain how to do that here. There is plenty of Microsoft documentation that explains how to do that part.

Once you get to the configurations page, you should click "Designer" to build your file.

You have to build a hierarchy where you have a Type "File" at the top level. Then you add a Sequence and then you add a second Sequence. Like this:
File
    RowSequence
        FieldSequence

File should have encoding UTF-8. Under FILE EXTENSION SETTINGS you should select a Custom (user defined) way of definition and put "CSV" in the Custom extension box. This will generate a CSV file extension for you. If you wanted a .TXT file, you could put that here as well.

Under Formatting Preferences, I added the MM/DD/YYYY for my date format, although I don't know if this global format works. I ended up adding formatting to my date and didn't try it without the formatting.

Under the format for your RowSequence node, you should have special characters set to "New line - Windows (CR LF)". This will create a new line after each row on your file. This node is at the row level.

Under the format for your FieldSequence node, you should have Custom delimiter set to a comma (just put , in the box). This node is at the field level and putting this custom delimiter here will add a comma after every field for you. If you need a different type of file like pipe delimited, you could put your pipe, |, here.

Now you can start adding your fields to FieldSequence. Make sure you click on FieldSequence and click Add > String. Name it CHEQUESTATUS. No special format properties are required. Click on Mapping and then click on Edit formula. This is where you can write a formula for your field. Because ChequeStatus is an enum, this can be tricky. What I did was, first, create a formula for the field that shows: Text(@.ChequeStatus). Then you can run your file and see what shows in the CHEQUESTATUS field on the file. Once you know the text in the field, you can write an IF statement that does what you need:

IF(Text(@.ChequeStatus) = "ERModelEnumDataSourceHandler#Positive pay model#BankChequeStatus#Payment","I",

IF(Text(@.ChequeStatus) = "ERModelEnumDataSourceHandler#Positive pay model#BankChequeStatus#Void","V",

IF(Text(@.ChequeStatus) = "ERModelEnumDataSourceHandler#Positive pay model#BankChequeStatus#Rejected","V",

IF(Text(@.ChequeStatus) = "ERModelEnumDataSourceHandler#Positive pay model#BankChequeStatus#Cancelled","V",""))))

The next two are easy because they are direct mappings to the fields in the model and no formula or mapping are required.

Make sure you saved your CHEQUESTATUS formula before moving on. Go to FieldSequence and add another string called ACCOUNTNUM. Click on Mapping, choose your field from the Model > Positive pay transactions (field is AccountNum) and then click "Bind". That's it for AccountNum. 

Go back up to FieldSequence and add another string called CHEQUENUM. Click on Mapping, choose your field from the Model > Positive pay transactions (field is ChequeNum) and click "Bind". That's it for ChequeNum.

The next field we want is TransDate. It is a date field, but we want it to be a string on the CSV file, so we go up to FieldSequence and add another string field and call it TRANSDATE. Go ahead and map this to the TransDate field and bind it as well. Then click on Edit formula and you can use this to get the format: DATEFORMAT(@.TransDate,"MM/dd/yyyy") The Ms in the Month hav to be capital and the ds in the day and ys in the year format have to be lowercase. 

The next field is BANKNEGINSTRECIPIENTNAME. Go back up to FieldSequence and add another string. Map and bind it to the BANKNEGINSTRECIPIENTNAME field. Then Edit the formula. We want to remove any commas. The formula should be:
REPLACE(@.BankNegInstRecipientName, ",", "", false)

The last field is the AMOUNTCUR. This is a Real data field but again, we want a string on the file. Go up to FieldSequence and add another string field. You can call it AMOUNTCUR. Map and Bind it to the AmountCur field in the positive pay model and then edit the formula. This formula will strip out the decimal and ensure that the dollars and cents are accounted for on the file:
REPLACE(NUMBERFORMAT(@.AmountCur,"F2", "en-us"),".","",FALSE)

Save your formula and go back and save the whole file formatting. You can mark your draft complete and export the XML format to save it somewhere safe. You can also use this to import into other environments.

D365 - removing new line character from string (note/memo/text) fields

I wrote a post about how to do this in AX 2009, but D365 has made it much easier with the global method strReplace().

It accepts a string, fromStr, toStr and returns the changed string.

So to clear out new line characters:

string StringwithNewline;

stringwithnewline = strReplace(stringwithNewLine,'\n',' ');

This will replace new line characters with a space.

D365 Report as finished error on Route Card Journal

A user was getting the following error when selecting "report as finished" on their production order:

Posting - Route card journal 

    Journal:XXXX 

        Voucher: XXXX 

            You must select a value in the X field in combination with the following dimension values that are valid: 

When I manually created the route card journal, the financial dimension mentioned in the error message was not being populated. So where do the financial dimensions on a route card journal come from? 

The user needed to set up the financial dimensions on their operation. 

Navigation was: 

Production control > All Production orders 

Locate your order 

Go to Production orders tab > Production details section > Route 

Once you are on the Route screen, you need to click on the OPERATION NO to get to the screen where you should enter your financial dimensions for the route card journal to use. 

Hope this helps! Happy DAXing!

Form has outer join on a datasource and user wants to filter for blank value on the outer joined table

 As you may have found, if you have a form with an outer joined datasource and a user wants to filter the form so that a value on the key field in the table is blank (in other words, you want not exists join instead of outer join), the form will show NO records returned.

This occurs because the form adds a queryFilter to the outer joined datasource. My solution was to catch this scenario and change the queryFilter to a queryRange.

Example:

On the executeQuery() method on the main datasource, I added this code.

This code will find the existing queryFilter (this one looks specifically for a blank batch number) and then add the same queryFilter to a range.

SSRS Reports - Showing the first record's data of your group

 If you need to display the first record's data of your group, you can use the following expression:

=First(Fields!FieldName.Value, "GroupName")

Dynamics AX SSRS reports using SubReport

This can be tricky for sure. The most important point to be understood is that there has to be a parameter from the Main report that can be passed to the Subreport in the subreport Parameters.

From the main report design, you Right-click > Insert > Subreport.

Then you Right-click the Subreport to view Subreport Properties.

On the General tab, you must set the report that you want to use as the subreport. This should be in the format: Report.Design.

Here is an example:

The parameters tab is where the real magic happens.

First of all, you need three parameters that are standard for all (most?) ssrs reports. The "Name" column on this parameters screen should be thought of as the parameters from the Sub report. The "Value" column in this screen should be thought of as the parameters from the Main. The value parameters can be an actual parameter field or just a data field from your report. The Name will be the name of the parameter from the sub report. The Name drop-down will not be helpful to you. You will need to type in the name of the parameter to pass. The Value drop-down will also not be helpful to you. You can use the "fx" to build the expression. If you do this, you can choose the "Parameters" category and double click the parameter you want in the Values section:

Or you can choose a field from your dataset as the parameter for the report.

Or you can type them in the Values section of the Subreport properties screen like this:

Name                                        Value

AX_CompanyName                [@AX_CompanyName]
AX_UserContext                     [@AX_UserContext]
AX_ReportContext                 [@AX_ReportContext]

Then you need to pass the parameter(s) that the report needs for it to run. My report was passing the AccountNum. The customer account number in the main report had parameter "CustListReportDS_AccountNum." The customer account number in the subreport had parameter [@DataSet1_Customer_account], so my parameter looked like:

Name                                                            Value
CustListReportDS_AccountNum             [@DataSet1_Customer_account]

It will look like this when you are done:

Hope this helps!

D365 - creating an externalItemId Lookup for Sales Orders, Purchase Orders and Sales Quotations

 For some reason, the external item field on these forms do not have lookups. If you want to set the external item manually, you would need a lookup on the field. Standard AX allows the user to type any string into the externalItemId field, it doesn't actually have to be in the external item table. We had a requirement to keep that functionality. If you wanted to limit the selection to only the items in the table you would need to add a relation on the salesLine, salesquotationline, and purchline tables to the CustVendExternalItem table and make sure validation is set to yes.

Doing this is not all that difficult. First you need to create a lookup on the custVendExternalItem table and then override the lookup on the form(s) you want to use it.

1. Here is the lookup code on the custVendExternalItem table

[ExtensionOf(tableStr(CustVendExternalItem))]
final class ModelCustVendExternalItem_Extension
{
    public static void modelLookupExternalItem(FormControl _formControl, CustVendRel _accountNum, ItemId _itemid)
    {
        Query query = new Query();
        QueryBuildDataSource qbds;
        QueryBuildRange qbr;

        SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustVendExternalItem),_formControl);

        sysTableLookup.addLookupfield(fieldNum(CustVendExternalItem, ExternalItemId),true);

        sysTableLookup.addLookupfield(fieldNum(CustVendExternalItem, ItemId));

        sysTableLookup.addLookupfield(fieldNum(CustVendExternalItem, CustVendRelation));

        qbds = query.addDataSource(tableNum(CustVendExternalItem));

        qbds.addRange(fieldNum(CustVendExternalItem, custvendrelation)).value(_accountNum);

        qbds.addRange(fieldNum(CustVendExternalItem, ItemId)).value(_itemid);

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();        

    }

}

2. Here is the code for the sales order form

[ExtensionOf(formStr(SalesTable))]

final class ModelSalesTableForm_Extension

{

    void init()

    {

        next init();

        Sales_ExternalItemId.registerOverrideMethod(methodStr(FormDataObject, lookup), formMethodStr(SalesTable, modelExternalItemLookup));

    }

    public void modelExternalItemLookup(FormStringControl _callingControl)

    {

        CustVendExternalItem::modelLookupExternalItem(_callingControl,SalesTable.CustAccount,SalesLine.itemId);

    }

}

3. The code for the purchase order form is very similar

[ExtensionOf(formStr(PurchTable))]

final class ModelPurchTableForm_Extension

{

    void init()

    {

        next init();

        PurchLine_ExternalItemId.registerOverrideMethod(methodStr(FormDataObject, lookup), formMethodStr(PurchTable, modelExternalItemLookup));

    }

    public void modelExternalItemLookup(FormStringControl _callingControl)

    {

        CustVendExternalItem::modelLookupExternalItem(_callingControl,PurchTable.OrderAccount,PurchLine.itemId);

    }

}

4. The code for the sales quotation form is also similar:

[ExtensionOf(formStr(SalesQuotationTable))]

final class modelSalesQuotationTableForm_Extension

{

    void init()

    {

        next init();

        Sales_ExternalItemId.registerOverrideMethod(methodStr(FormDataObject, lookup), formMethodStr(SalesQuotationTable, modelExternalItemLookup));

    }

    public void modelExternalItemLookup(FormStringControl _callingControl)

    {

        CustVendExternalItem::modelLookupExternalItem(_callingControl,SalesQuotationTable.custAccount,SalesQuotationLine.itemId);

    }

}

Dynamics 365 Docentric report: formatting negative numbers with parentheses

 I was hoping there was an easier way to do this, but this is how I ended up doing it.
For each numeric field that you want to show parentheses around a negative number, do the following. 
1. Change the field to return the absolute value of your field:
    For example:  abs(@GrossTotal)

2. Make sure you go outside of the "field" definition and add a beginning parenthesis before it and an ending parenthesis after it.

3. Highlight the first parenthesis and go up to the top and choose the Docentric "IF" to put around it. Only show the parenthesis if the value of @GrossTotal is less than zero. @GrossTotal < 0.

4. Highlight the last parenthesis and go up to the top and choose the Docentric "IF" to put around it. Only show the parenthesis if the value of @GrossTotal is less than zero. @GrossTotal < 0.

When you "show tags" on the template, it should look like this:

If anyone finds an easier way to do this, let me know.
I tried using an iif statement in the field value but it didn't give me the decimal places I needed:
iif(@GrossTotal>0,@GrossTotal,concat(concat('(',@GrossTotal*-1.00),')') )
It returned (25) instead of (25.00). 

Changing a string field to match number sequence format

 Our customer is automating their invoice processing with a 3rd party program (AxTension). The invoices they receive from vendors often have only part of the purchase order number in them. Their current PO format is PO#######. Sometimes they just receive the numeric part, '1234567', sometimes the numeric part is missing the leading zeroes, '12345'. This kicks the PO numbers out as errors and the users would have to manually match them to the PO in the system.

I was asked to write logic that will "fix" the PO numbers that come in. First of all, if the PO number is blank, I just want to return (this avoids adding a po number for NON PO invoices):

if(_purchId == strMin())
{
    return _purchId;
}

Then I had to find the number sequence format for the PurchId extended data type.

NumberSequenceReference Ref;
NumberSequenceDataType dataType;
NumberSequenceTable    numSeq;
extendedTypeId                poTypeId;

//Find the EDT id for your extended data type (mine is PurchId)
poTypeId = extendedTypeNum(PurchId);

select firstOnly RecId from dataType
            where dataType.DatatypeId == poTypeId
            join numbersequenceid from Ref
            where Ref.numbersequencedatatype == datatype.recid|
            join Format from numSeq
            where numSeq.recid == Ref.numbersequenceid;

Once I have the numberSequenceTable record and the format field, I can use it to check the purchId that was passed into the system. I also did not want to show the user any errors so I store the length of the info log before and clear it after calling in to the numCheckFormat() method run a check on my format.

When you incorporate this into your class(es), you can have the user pass in the purchId and return the newPurchId you have fixed (public static PurchId validatePurchId(_purchId)). When you run it as a job, you can create the PurchId purchId variable and set it to the purchId you want to "fix."

int lines;
purchid purchid, newpurchid;

//Save current infolog spot
lines = infolog.line();   

// Check to see if purchid matches format
if(!NumberSeq::numCheckFormat(purchID,numSeq))
{  // if check fails, fix purchid
    //Cut any infolog messages that were created
    infolog.cut(lines ? lines : 1);
    //loop through the format
    newpurchid = purchid;

    for(i=1; i <= strLen(numseq.format); i++)
    {
        if(i > strLen(_purchId))
        { // if the purchid is longer than the format, we don't need to modify it (this only adds)
            break;
        }

        f = subStr(numSeq.format,i,1);
        p = substr(purchId,i,1);
        if (f != '#')
        { // if we aren't looking for a number in the format
            if(f != p)
            { // insert the character if it is in the format but not the purchid
                if(strLen(purchId) < strLen(numSeq.Format))
                {
                        //insert character into string
                        purchId = strIns(purchId, f, i);
                }

                //fix issue where readsoft reads the letter O as a numeric 0 
                else if (p == '0')
               { //Overwrite character in the string
                        purchId = strPoke(purchId, f, i);
                }
            }  // END if f!=p     
        } // END if f!= '#'
    } // END for

    // After inserting the characters that exist in the format but not in the purchid, check the length
    // if it's not long enough, we want to insert 0s before the existing numbers in the purchid
    if(strLen(newpurchid) < strLen(numSeq.format))
    {
        zeroPos = strNFind(numSeq.Format,'#',strLen(numSeq.Format),-strLen(numSeq.Format));
        newPurchId = strIns(newpurchid,strRep('0',strLen(numSeq.format)-strLen(newpurchId)),zeroPos+1);
    }

You could also add logic that deletes unwanted characters from the purchid if it's not in the format, but we were assuming that wasn't going to happen. If it did happen, we would actually need user interaction to figure out if it matched with a PO in the system. 

If you want an easy way to test this, you can create a dialog that allows you to enter your PurchId and your Format and ensure that the logic above will modify the PurchId the way you intend.
I added this to the job to give me a dialog:

    Dialog                  dialog = new Dialog("Purchase order number");
    DialogField             dlgpurchid,dlgFormat;

    dlgpurchid = dialog.addField(extendedTypeStr(str60),"Num to validate");
    dlgFormat  = dialog.addField(extendedTypeStr(NumberSequenceFormat));
    dlgFormat.value("PO#######"); // Set my current known number sequence as a starting point    

    if(dialog.run())
    {
        purchid = dlgPurchId.value();
        // code to select the num seq record from the table can go here
        //After selecting the numsequencetable record, you would need to store the format from your dialog
        numSeq.format = dlgFormat.value();
        // code to fix the purchid can go here
    }

This next section specifically references where to put the code in AxTension to adjust the PurchId. It has to be done on the header and the lines (as you can pass invoice number per line with AxTension).

1. Add method to AXTip_ImportPurchInvoices that will receive the purchId, fix it, and return the new purchId

2. Add code in method addInvoiceLine() where it calls the setField() for #Field_Line_PurchId. Replace it with this:
this.setField(#Field_Line_PurchId, this.YOURMETHOD(getFieldorDefaultValue(this.getField(#Field_Line_PurchId, ''), this.getField(#Field_PurchId, ''))));

3. Add code in method handlePurchInvoice(). After the if(importRunId), add this line of code:
this.setfield(#Field_PurchId,this.YOURMETHOD(this.getField(#Field_PurchId)));

4. Add code in class AXTip_ImportInvoices_ReadSoftOnline. This extends a class that extends the earlier class where you put your new method to fix the purchId. Add code in the analyzeFile() method.
Set default Id with your new logic.
defaultPurchId = this.YOURMETHOD(this.getfield(#Field_PurchId, ''));

5. In the readsoftonline class in #4, add code in the setHeaderFields() method:
change the this.setField() for the #Field_PurchId to call your new method instead
this.setfield(#Field_PurchId, this.YOURMETHOD(this.marshalStr(documentData.get_PurchId(), identifierStr(PurchId))));

Hope this helps!

-Amber