24 Comments

1. Thanks for sharing.
   I need to add the vendor account number (Vendor ID), do you have a hint?

   • Try model.Payments.Creditor.Identification.’Source ID’

2. Great job,

   Very clear.

3. Is there an option to separate the address?
   As you described, it is possible to fill the complete address.
   But I want to have the Country in a separate tag.
   The same for the street and number.
   And in a separate tag the postcode and city.
   Is this possible?

   • Oh dear 😉 The answer is twofold:

     1. First of all, in the PAYMENT MODEL MAPPING 1611 every single part of the address is properly mapped to the respective node in the Payment model:
        
        I.e. you don’t have to split the string but just take the model.Payments.Debtor.PostalAddress.Country or model.Payments.Creditor.PostalAddress.Country for the ISO code.
     2. If it was not mapped (and sometimes the MSFT developers do not care or have too little time to bind every node), then you could have used the regular expressions. Here is a crazy example I recently used to extract the delivery note number in the middle of a long string in the invoice, assuming it preceded by a “: ” substring and followed by a space ” ” and a non-digit: REPLACE(REPLACE(model.'$FirstLineWithPackingSlip'.PackingSlipInfo, ".*: (?=\D)", "", true), " .*", "", true)
     • HI EUGEN,

       Is there any documentation available for using REPLACE function by passing different patterns? Few of them are given in above examples- like (?=\D), “\n” etc.
       If you have something around it, please share. It would be very much helpful.

       • Hello Neeraj, these are the standard Regular Expressions in the .NET flavour: https://en.wikipedia.org/wiki/Regular_expression
         I usually test them in a Web interpreter first, than plug them into a ER formula: https://regex101.com/

4. Hi, I have added Vendor Bank account to my format model, however when the template is only picking up the first value on the Vendor Id List.

   Any suggestions on how i can get the Vendor Bank Account for the Vendor ID on the payment Advice?

   • A screenshot may be beneficial. Why would you add anything to the format model if it contains model.Payments.CreditorAccount.Identification.Number already?

5. How can I add the creditors Bank Account Type as a string?

   • Hello, whatever the Bank account type is, it is not bound in the “Payment model mapping 1611”. I.e. you’ll need to derive an own mapping from the standard one and extract the type from the vendor bank account record. Converting an enumeration into a string is a challenge of its own. You can either build a simple CASE statement:
     https://community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/319051/electronic-report-framework-format-designer or a sophisticated chain of formulae and ‘calculated fields’: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/er-functions-text-getenumvaluebyname

6. Hi, to display routing number + bankidentification number in tag CdtrAcct > ID i use the formula
   model.Payments.CreditorAgent.RoutingNumber & @.CreditorAccount.Identification.Number.
   When i run the xml file it seems that for records without routing number in bank setup it gets the routing number of previous record in payment file. e.g bank , what is wrong?

7. I have my fields mapped, but when I save I keep getting the error ” Path ‘model/Payments/CreditorAccount/Identification/Number’ has no binding to any datasource in using model’s mapping “. Do you have an idea why?

   • This is strange, but it is a clear sign that this model node is not populated, i.e. not mapped to any backbone D365 table field. Derive you own mapping from the standard one, map the CreditorAccount/Identification/Number to the creditor’s bank account number, then make your derived mapping the default mapping.

8. Hi,
   In my electronic reporting configuration, I would like to add a “%” after the numbers printed out. For example, instead of just showing “25” or “20”, I would like it to be “25%” or “20%”. Do you have a suggestion on how to formulate the formula?
   I have tried the following, without succeeding:
   Datasource & “%”

   • Hello, when I was solving a similar problem, I used the NUMBERFORMAT (?) function and used a “p” format string. ER implements the .NET formatting, check https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings and specifically the “p” specifier.

     • Thank you for a quick reply! It worked with the formula: NUMBERFORMAT(datasource, “#”)&”%”

9. Knowing that the data model is an abstraction of the actual database design, is there still a most optimal node branch to choose for certain types of data? Does it have much impact other than where you as the analyst find it when doing the mapping work?

   For instance, what is the best node to store HcmWorker from Table CustInvoiceLineInterProj for eventual inclusion into an Intercompany Free Text Invoice?

   My first try would be in a root node called “Invoice line – item(LineItem)” I would choose this since in Visual studio, looking at references I see that the table CustInvoiceLineInterProj is related to CustInvoiceLine.

   • I try to stay pragmatic, and choose ANY not yet mapped node in the existing model. There are usually many unused, unmapped fields. The reason is the ease of upgrade: if you start extending the Model and deriving your own sub-version, then any changes made by Microsoft to the standard model remain invisible in your format, unless you “Rebase” the model first, then your mapping, then your format. It’s very hard to maintain.

     • Thanks Eugen.

10. Hello Eugene, would you be kind enough to share also the screenshots of the ER config/code of the ACH and Batch headers? kinda like what you did here for the detail records. Thanks a bunch.

    • Hello, it will be in your mailbox in a few minutes. All the best to Anchorage, I did not know that people live there, like for real :))

11. Hello Eugene, would you be able to help me provide mapping with line wise VAT amount value in posted invoice.

    • Sorry, no, this is an exotic setup over here.