Import bank statements (MT940)

The purpose of importing bank statements is to perform reconciliation of payments sent to the bank, and payments received versus the cash amounts shown on the bank statement. The bank reconciliation is an important part of a company’s internal controls over its assets.

The most common formats of electronic bank statements are MT940, BAI2 and CAMT.053.

The following steps to import electronic bank statements will be required: 

  1.  The TMS(*) system picks up the bank statement file 
  2.  The TMS(*) system imports, and transfers the bank statement file to the SFTP repository  
  3.  Logic Apps picks up the file, works out the encryption, and transfers the file
  4.  Logic Apps transfers the file to Azure blob storage and a SharePoint repository   
  5.  D365 F&O picks up the file and imports it through the data management framework

Setting up bank statement import using format MT940 

Export resource files 

  1. Open Application explorer in Visual studio  
  2. Locate Resources and filter by BankStmtImport 
  3. Copy following resources to a local folder 
Resource 
SampleBankCompositeEntity.xml
MT940TXT-to-MT940XML.xslt
MT940XML-to-Reconciliation.xslt
BankReconciliation-to-Composite.xslt

Example:

Select SampleBankCompositeEntity.xml, right click and choose Open designer

Let the cursor on the name of the opened file to see where the resource file is stored

Copy the file to the local folder

Create the data project Bank statement MT940

  1. Navigate to the Data management workspace, then select Import  

  1. Enter the name of the data project 
  2. Select Add file 

  1. Select XML-Element in the Source data format field 
  2. Select Bank statements (target entity: BankStatementImportEntity) in the Entity name field 

  1. Click Upload and select the SampleBankCompositeEntity.xml file 
  2. Click View map 
  3. Click View map again 
  4. On the Map source to staging page, choose TRANSFORMATIONS
  5. Choose New and Upload file
  6. Choose MT940TXT-toMT940XML.xslt
  7. Repeat step until you have all the three resources in following order
Order
MT940TXT-to-MT940XML.xslt
MT940XML-to-Reconciliation.xslt
BankReconciliation-to-Composite.xslt
  1. Click on Apply transforms 

Setup Bank statement format MT940

  1. Navigate to Cash and bank management > Set up > Advanced bank reconciliation setup > Bank statement format 
  2. Click New 
  3. Enter a Name et Description
  4. Add the processing group setup in the previous steps

Configure the bank account to use Advance reconciliation option 

  1. Navigate to Cash and Bank management > Bank accounts, then select a Bank account to view details. 
  2. Under Reconciliation tab, set the Advance bank reconciliation option to ‘yes‘. This is a one-time setup, the system doesn’t allow to undo/change once set to yes.

TMS = Treasury Management Systems

Consolidation in D365 F&O

Consolidations in D365 F&O comes with two features to accomplish consolidations. One of the functionalities that can be used to consolidate is what is now called Financial Reporter.  Your second option in D365 F&O is a process called “Consolidate online.”

I explain how to use the “Consolidate online” feature in our case.

Consolidation company (Reporting entity)

Navigate to Organization administration  > Organizations >  Legal entities 

  • Create a new legal entity and enter the name of the reporting entity
  • Activate Use for financial consolidation process 

The reporting entity will be a non-transactional company. That means that you cannot post a transaction in it. 

  • Add the entity into an organization hierarchy that corresponds to the corporate structure

Navigate to Organization administration  > Organizations > Organization hierarchies

  • Assign the Organization chart purpose 
  • Select View to open the hierarchy designer 
  • You can edit the hierarchy and build the corporate structure 

Accounts for automatic transactions

Navigate to General ledger > Posting setup > Accounts for automatic transactions and select the appropriate main accounts for the two posting types: 

  • Balance account for consolidation differences
  • Profit and loss account for consolidation differences 

Ledger set up 

Navigate to General ledger > Ledger setup > Ledger 

In the Accounts for reconciliation tab, select the main accounts for unrealized gain and loss postings using the main account defined in the setup form “Account for automatic transactions” as “Balance sheet account for consolidation differences”. 

Chart of accounts 

Mark Foreign currency revaluation for all main accounts you need to adjust/translate. 

When you originally consolidate the data, you will use the Currency translation tab of Consolidate Online feature to select the initial exchange rates for translation during the consolidation process. After a new exchange rate is entered next month, you must revalue the account balances as you do in a transactional company. 

Navigate to General ledger > Chart of accounts > Accounts > Additional consolidation accounts 

Map the source main accounts to the group account, also called Consolidation account in D365 F&O. 

Navigate to General ledger > Chart of accounts > Dimensions > Financial dimensions 

For the financial dimensions that are used for group reporting, map the financial dimension values to the group dimension. 

Warning: Leave the Group dimension blank for dummy financial dimensions that may be used. 

Exchange rate types

Set up the exchange rates that are used in the consolidation / currency adjustment run. This should be at least Average and Closing. 

Exchange rates

Navigate to General Ledger > Currencies > Currency exchange rates 

Fill in the exchange rate pairs that you need (Local currency <-> Package currency). 

Run the consolidation 

Navigate to Consolidations > Consolidate Online 

Consolidate online – Criteria

You define the accounts, the periods, and the type of data that is being consolidated. 

Set the Include actual amounts option to Yes

Set the Rebuild balances during consolidation to No and set up a separate batch job for that task

Do not include budget amounts  

In the Select consolidation account from field, select Consolidation account group

Consolidate online – Financial dimensions 

You need to define which financial dimensions will be populated in the group ledger, i.e. the ledger of the consolidation company.   

The legal entity dimension enables the option to see the source company of each transaction in the group ledger. 

Example: 

Local ledger (GOLD)

VoucherDate Ledger account Account name TransactionCurrencyAmount in transaction currencyAmount in Accounting currency (USD)
APPS0000000018/19/202065030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120Travel ExpensesEUR1000890
APPS0000000028/20/202065030000-D30301- NORTH-HO-SB4106-P010110—CR10900-VEND000011-625120Travel ExpensesEUR1000880

Group ledger (Reporting entity) 

VoucherConsolidation DateLedger account Account name CurrencyAmount in transaction currencyAmount 
IRIS0000018/30/20206503.00-GOLD-SB41-P0101-D30-CANADATravel ExpensesUSD17701770

The transaction currency of the group ledger is either the accounting currency of the source company or the reporting currency of the source company. 

The following parameters will be used: 

Consolidate online – Legal entities 

On the Legal entities tab, you define the companies that should be included in the reporting entity/package. 

Consolidate online – Currency translation

There are two methods for currency translation, i.e. the current-rate method and the temporal method. The configuration depends on which method is used. 

In the Currency translation tab, you need to set up the main account ranges and rates to translate from the accounting currency (Local currency) of the source company to the accounting currency of the reporting entity. You need to also define the Exchange rate type and Exchange rate date for a range of main accounts in each source company.

The exchange rate type determines the currencies and exchange rates you want to apply to the account and the exchange rate date determines which exchange rate to use for each account range. 

In the ‘Apply exchange rate from’ field, there are three options: Consolidation date, Transaction date, and User-defined. 

Based on the following table, use Consolidation date for any accounts that should use the Current exchange rate and Transaction date for any accounts that should use the Historical or Average exchange rate. 

Current-rate method exchange rateTemporal method exchange rate
Balance sheet
Monetary accountsCurrentCurrent
Cash, AR, AP, debt
Non-monetary accountsCurrentHistorical
Inventory, Fixed assets, intangible assets…
EquityHistoricalHistorical
Profit & Loss
Revenues and expenses not related to non monetary assetsAverageAverage
Revenues and expenses related to non monetary assetsAverageHistorical

When you choose a User-Defined exchange rate, you can also force the exchange rate. However, we don’t recommend that as the history is lost each time you run the consolidation.

When you translate Balance sheet accounts & Profit & Loss accounts with different exchange rates, you end up with a Currency Translation Adjustment (CTA). That’s why you need to define which accounts to use for the consolidation differences in the Accounts for automatic transactions (2.1.2).

Re-running consolidations and/or Re-running exchange adjustments 

Non consolidated data will be aggregated monthly in the reporting entity. The batch processing recurrence needs to be set up

That’s the end of the article. Please comment if you have any questions.

How to apply cost allocation rules in the cost accounting module ?

In my previous article, I showed you how to configure the basics of cost accounting in D365 F&O. It is time to move to the next steps and apply some allocation rules. 

I would like to apply allocations based on statistical dimensions. Some costs have been accumulated on non allocated business units and need to be allocated to all operating business units based on the volume of oxygen produced per business unit. 

Statistical dimensions

Navigate to Cost accounting > Dimensions > Statistical dimensions 

Select Imported dimension members in the Data provider field as the statistical values are not inherited from D365 F&O but from an external source. 

Click Manage dimension members 

In our case we will use ‘Volume stat data’ and the unit of measure is m3 

We need to import the statistical data and use the data management framework. 

The Excel spreadsheet looks like follow: 

💬 : the MAGNITUDE column is the volume (m3) of oxygen produced per business unit (DIMENSIONMEMBERNAMES).  

Go to the data management workspace, then create an import project.

Add the data entity Imported statistical measures, upload the excel spreadsheet and click Import.  

Navigate to Cost accounting > Cost accounting ledger, then select Actual versions > Manage > Statistical measures.

Click New 

Select the statistical dimension member and the data provider 

Click Configure data provider 

The Import source identifier field is associated with the column SOURCEIDENTIFIER in the imported Excel spreadsheet.  

Close the dialog and the form.  

Select Actual versions, then Source data processing and Statistical measures in the Process data sources group.  

A dialog form pops up

Import the statistical data for a specific period 

Click All processing journals and you can view statistical entry transfer journals that contain statistical data imported into the cost accounting module. 

Cost allocation policies

Let’s configure an allocation policy based on statistical values. 

Navigate to Cost accounting > Policies > Cost allocation policies 

Select the Cost object dimension hierarchy, the cost element dimension, and the statistical dimensions.  

I would like to allocate the costs accumulated on the unallocated business units and that’s why I selected the node ‘Unallocated’ in the cost object dimension hierarchy. The allocation base is those statistical data I imported earlier. 

Now we need to link that rule to a cost control unit and a cost accounting ledger.

Click Policy assignments for cost unit 

The cost control unit is the business unit dimension for the Cost accounting ledger V2_Ledger. 

Let’s run that allocation rule

Go to Cost accounting > Cost accounting ledger, then in the Period calculations tab, select Overhead calculation 

Select the period for which you want to run the allocation rule 

Click OK

You can see the results in a calculation journal of the type cost allocation journal 

As a matter of fact, you can also apply other types of allocations.

In my next article, I will show how to split costs into a fixed amount and a variable amount and how to apply separate allocation rules for each amount type (Fixed/Variable).

Cost accounting – Configuration basics

Cost accounting is the process of recording, analyzing and reporting all of a company’s costs. It classifies a company’s costs by behaviour (fixed, variable, etc), by nature (direct, indirect),  by function (administration, production, etc)…

The following example shows the difference between direct and indirect costs. 

The cost accounting module in D365 F&O has been dramatically refined, and no piece of code has been reused. That means that it is not a well-known module at the moment. 

I would like to demystify that module and explain how it works in detail. 

First there are several elements that need to be configured:

  • Cost element dimensions
  • Cost object dimensions 
  • Statistical dimensions
  • Dimension hierarchies
  • Cost accounting ledger 

Cost elements 

Navigate to Cost accounting > Dimensions > Cost element dimensions 

Add a new cost element dimension

The data provider needs to be ‘Dynamics 365 for Finance and Operations – Main accounts’ when you don’t import main accounts from an external source 

Click Configure data provider 

Select the chart of accounts 

In the Main account rules section, select the main accounts you would like to import into the cost accounting module. You can define the rules by main account type as well. 

Close the dialog and click on Import dimensions members 

Then, click View dimension members 

All cost elements have the type Primary 

When you manually add a new cost element, the type will be Secondary

Navigate to Cost accounting > Dimensions > Dimension hierarchies

Click New 

Select Dimension classification hierarchy 

In the Dimension field, select the cost element dimension you created above. 

Click View hierarchy 

Build your hierarchy depending on the level of details you want to display in the module 

For each node, define the range of main accounts 

Cost objects 

Navigate to Cost accounting > Dimensions > Cost object dimensions 

Add a new cost object dimension

💬: A cost object is a type of object that you want to allocate costs to. It is most of a time a financial dimension in the General ledger. 

The data provider needs to be ‘Dynamics 365 for Finance and Operations – Financial dimensions’’ when you don’t import cost objects from an external source 

Click Configure data provider 

Select the financial dimension associated with that cost object. 

Close the dialog and click on Import dimensions members 

Repeat those steps for each cost object 

Navigate to Cost accounting > Dimensions > Dimension hierarchies

Click New 

Select Dimension classification hierarchy 

In the Dimension field, select the cost object dimension you created above. 

Click View hierarchy 

Build your hierarchy depending on the level of details you want to display in the module

I separated direct and indirect SSBL which means Business unit. 

I did that for allocation purposes. It will be explained later in that article. 

For each node, define the range of SSBLs. 

Cost accounting ledger

Navigate to Cost accounting > Ledger set up > Cost accounting ledgers

Enter a Name

Enter a cost element dimension, a fiscal calendar, an accounting currency, an exchange rate type 

Select Actual Versions, then Manage > General ledger 

Click New and select the cost element dimension

The data provider should be ‘Dynamics 365 for Finance and Operations – General ledger entries’ if you don’t import financial data from an external system. 

Click Configure data provider 

Select the source of the general ledger transactions and the posting layer 

Close the dialog and the form 

Click Control units 

Add a new cost control unit to import the general ledger entries by cost object (= financial dimension)

Close the form

Select Actual versions, then Source data processing and General ledger in the Process data sources group   

A dialog form pops up

Import the general ledger transactions for a specific period 

Click All processing journals and you can view all GL journals that contain GL transactions imported into the cost accounting module. 

All is set to start designing allocation rules and I will show that in my next article.

Foreign exchange rate exposure – Intro

International companies sell goods to overseas customers and they receive payments in the customer’s currency. For instance, a company that operates and reports in EUR made a sale to a customer in the United States for 1 000 USD. That company is subject to the risks resulting from fluctuations in the USD/EUR exchange rate.

However, I would say it is a mistake to think that currency risk is limited to multinational companies.

As a result, they need to cover the financial risk on the exchange rate from order date to payment date.

They can use Forex contracts but they are several types of them :

  • Spot contracts: you are quoted an exchange rate and have a couple of days to send the funds to the broker
  • Forward contracts: It is an agreement to exchange currencies at a specified rate (forward rate) on a stipulated future date (settlement date or payment date).

The forward contract is the most commonly used to hedge purchase or sales orders.

Sometimes you need to roll over a forward contract which means that the issuer closes the current position and simultaneously opens another one with the new maturity date. It may occur when you agree to expand the payment terms on the order after it is invoiced or when you have an early settlement but in that case the payment is settled before its due date.

FireApps is a system specialised in reducing Fx risks and it can be integrated with D365 F&O. An out of the box interface will be available soon.

If you have any questions, please contact us.

https://www.linkedin.com/company/dynagileconsulting/

Credit management in D365 F&O

In the previous versions of D365 F&O, there were crucial features missing around credit management. That’s why I have seen many developments in that area of the ERP.  

Fortunately, Microsoft released a brand new feature called credit management that covers most business cases that I have encountered. However, there is a big limitation in some industries as project invoices are not covered by the credit management feature yet.  

First you need to activate that feature in the Feature management workspace. 

The credit management functionality is embedded in the Credit and collection module. The credit management process can include any of the following steps :

  • Create/update credit limits for customers 
  • Create temporary credit limits to temporarily increase or decrease customer credit limit  
  • Add information about insurance and guarantees 
  • Create customer credit groups that link customers together so that they share a single credit limit.
  • Assign risk scores to customers
  • Set up a workflow to manage the approval of credit limit changes and sales order releases
  • Set up  advanced blocking rules for sales order 
  • Put sales order automatically on hold

Customers 

Navigate to Accounts receivable > Customers > All Customers, then select a customer
Extend the Credit and collections tab

I would like to focus on some fields. 

A] Account status: it shows when a customer is blocked and for which reason. That field is linked to the Invoicing and delivery on hold field. 

When you update the account status, it updates automatically the Invoicing and delivery on hold field. 

B] Customer since: That information helps you identify long term relationships you have with some customers. It might also be used to calculate the risk score.  

C] Last review date: Credit limits need to be reviewed every month/quarter/year and that field helps the credit team to generate reports that list the customers that need to be reviewed. 

D] Credit limit in customer’s currency: it is a game changer for companies that sell internationally. The credit limit can now be maintained in the customer’s currency. 

E] Insurance and guarantees: That field cannot be populated manually. The amount insured is inherited from the insurance stored in the insurance and Guarantees form. It is explained later in that article.   

The credit limit is detailed in the following chapter. 

Create/update credit limits

The credit and collections parameters drive the logic how to manage credit limits in D365 F&O 

  • Do you agree to let users adjust credit limits manually ?
  • Which exchange rate type do you want to use when the credit limit is converted in the customer’s currency? 
  • Do you want to use the Credit limit used rule ? 

Navigate to Credit and collections > Set up > Credit and collections parameters, then go to the Credit tab

Extend the Credit limits fasttab 

If Allow manual editing of credit limits is not marked,  a credit adjustment journal is needed to populate the credit limit of a customer. A workflow can be activated to approve credit limit requests, credit limit adjustments, and temporary credit limits.  

Navigate to Credit and collections > Credit limit adjustments > Credit limit adjustments

There are two types of adjustment that you can select as shown in the following screenshot. 

💬: A temporary credit limit is only valid for a period of time (From date.. To Date), especially when there is a significant rise in sales in a specific month for instance. 

We select “Credit limit” as we want to create a credit request to populate the credit limit of a customer. 

Select the customer, enter the new credit limit, and select the expiration date.  

Click Post 

In our case, there is no workflow for approval. It depends on the size of your company as in international companies the segregation of duties forces us to activate an approval workflow.

In addition, you can develop an interface with your credit insurance company to automate the population of credit limits on customers. For instance, Euler Hermes provides SmartLink that  integrates their business intelligence into your ERP systems. You can get Euler Hermes data (Customer data, credit requests answers…) in real time. 

Please open that URL if you would like to deep dive: https://www.eulerhermes.co.uk/support/services/smartlink.html

Create insurance/guarantee

Credit insurance companies protect companies against potential non-payment by their customers. Credit insurances are subscribed by companies to protect themselves from that risk.

Two types of contracts are available: 

  • Guarantee 
  • Insurance 

Navigate to Accounts receivable > Customers > All Customers, then select a customer and extend the Credit management tab in the action pane

Click Insurance and guarantees  

Select the type of contract, the insurer/Guarantor, the coverage type, the contract dates, the value insured or guaranteed. 

You can also choose to increase the credit limit by a certain amount when you sign a coverage contract with your credit insurance company. 

Credit Holds

A sales order is put on hold automatically based on blocking rules. 

Indeed, rules can be used to place a sales order on credit hold. There are two rule types: 

  • Blocking 
  • Exclusions 

Navigate to Credit and collections > Setup > Credit management setup > Blocking rules

For instance, as soon as one invoice is overdue for the customer US-001, all sales orders raised for that customer will be put on hold automatically. 

There are two additional settings that control scenarios that will block a sales order: 

  1. Change in payment terms
  2. Change in settlement discounts

Blocked sales orders are sent to credit management. 

Navigate to Credit and collections > Credit management hold list > Open credit holds

Select Blocking reasons to check why this sales order was put on hold 

💬: One parameter is crucial to understand if you want to reduce the number of sales orders put on hold because of ‘credit limit expired’ reason. 

I recommend to mark Ignore credit limit expired rule when expiration date is blank in the Credit holds tab of the Credit and collections parameters form  

Once a sales order is on hold, you need to evaluate that there are still reasons to keep it on hold any longer. 

Example: 

On Tuesday a sales order is put on hold because the customer has an overdue balance. 

On Wednesday, you receive a payment from that customer and you book it in D365 F&O through a customer payment journal. That payment settles all overdue invoices and that means there are no appropriate reasons to keep that sales order on hold anymore. That’s why, I recommend you to set up a batch job to run that evaluation every day. 

Select Evaluate blocking rules, then click on Process blocking rules

It opens that dialog

Mark Batch processing and set up the recurrence 

The credit controller can also release or reject manually a sales order or multiple sales orders (by selecting more than one line).

Once released, you keep a track of those released sales orders which is a good thing. You can identify who released it as well. 

Navigate to Credit and collections > Credit management hold list > Closed credit holds

That’s it for today. I will cover other credit management topics in another article.

I hope it helps you understand a little more how you could leverage those functionalities.  

Vendor payments in D365 F&O

In the previous versions of D365 F&O, the usual approach to processing payments for a vendor involves creating a payment journal, and then processing a payment proposal. To provide more automation capabilities, a new feature for payment processing has been introduced lately.

You can create a predefined schedule with a combination of payment proposal criteria to be able to create payment journals automatically.

In our case, I would like to share the preliminary steps before being able to pay vendors and show the standard process to generate a payment file. That post details a little bit more the partnership between Microsoft and Kyriba as well.

Payment methods need to be set up first by selecting the electronic format to be used.

  • Methods of payment

Navigate to Accounts Payable > Payment set up > Methods of payment

The method of payment SEPA credit transfer is configured as follow:  

Those fields drive the creation of the electronic payment file. 

Field Value Type
Method of payment SEPA String 
Payment status Sent Enum
Payment typeElectronic payment Enum
Category purposeSUPP – Supplier payment Drop down list
Charge bearerSLEV – Following service levelDrop down list
Local instrument NA Drop down list
Service level SEPA Drop down list

The checkbox Generic electronic Export format needs to be marked to be able to select a file format. Then, in the Export format configuration field, select the format “ISO20022 Credit transfer (FR)” for France. That electronic reporting format is often adjusted to fit the bank requirements. Don’t forget to take those adjustments into accounts in your deployment project.

The method of payment SEPA intercompany credit transfer is configured as follow:  

Those fields drive the creation of the electronic payment file. 

Field Value Type
Method of payment SEPA INTRA String 
Payment status Sent Enum
Payment typeElectronic payment Enum
Category purposeINTC – Intra company payment  Drop down list
Charge bearerSLEV – Following service levelDrop down list
Local instrument NA Drop down list
Service level SEPA Drop down list

The checkbox Generic electronic Export format needs to be marked to be able to select a file format. Then, in the Export format configuration field, select the format “ISO20022 Credit transfer (FR)” for France.

The method of payment International transfers (Non urgent) is configured as follow:  

Those fields drive the creation of the electronic payment file. 


Field 
Value Type
Method of payment SWIFT NURG String 
Payment status Sent Enum
Payment typeElectronic payment Enum
Category purposeSUPP – Supplier payment Drop down list
Charge bearerSLEV – Following service levelDrop down list
Local instrument IN – Cross border paymentDrop down list
Service level NURG – Non urgent Drop down list

The checkbox Generic electronic Export format needs to be marked to be able to select a file format. Then, in the Export format configuration field, select the format “ISO20022 Credit transfer (FR)” for France.

  • File name in Electronic reporting 

Navigate to the workspace Electronic Reporting, then select Reporting configuration 

Select ISO20022 Credit transfer (FR) for the French format

Click Designer 

Select XMLHeader, then in the Mapping tab, edit the File name 

In the formula, insert “DebtorBankName”-“DateTime”-“JournalNumber” or use the CONATENATE function.

  • Generate a vendor payment in D365 F&O

Navigate to Accounts Payable > Payments > Vendor payment journal, then click New to create a payment journal.    

Select the Journal Name 

Go to Lines > Payment proposal, then click Create payment proposal. 

Select the criteria of the payment proposal 

Review the proposal, then select Create payments

Payment transactions are transferred to the payment journal.

The method of payment is inherited from the invoice transactions by default.

The status of each payment transaction is “None” as no electronic payment file has been generated. 

The user clicks on the Generate payments button on the action pane 

Select the Bank account and the Method of payment 

Note: only one bank account can be selected

Click OK

The Electronic report parameters form pops up  

Mark Print covering letter to generate the remittance advice

Click OK 

The electronic payment file “ISO20022 Credit transfer” is generated by the Electronic reporting feature. The name and the destination of the file need to be setup as previously in this post.

  • Interface with Kyriba

Microsoft and Kyriba, one of the world leader in Treasury Management Systems (TMS) announced a cooperation last year.

It is now a reality and the seamless integration is a time acceleration.

Four interfaces will be built and provided out of the box : 

  • Payments 
  • Cash management
  • GL reconciliation 
  • Risk management 

Payments orders will be transferred and integrated to Kyriba through configuration (Kyriba extension). Digital signatures will still occur in Kyriba.

The technical architecture is based on APIs.

Connect to the Microsoft App source and contact Kyriba for more information.

How to deal with enums in Electronic Reporting ?

Enum fields cannot be exported as it is in Electronic reporting. We need to use a workaround that is detailed in that post. 

In our case I would like to export the item type stored on a purchase order line.  

In the Model mapping designer, select Enumeration and click Add root 

Type in a Name 

Select the enum in the Enumeration drop down field 

In our case the enum is ItemType

Click OK

In the Data model section, select the node and click Edit 

We need to use the CASE function. 

Select the data method getItemType() in the PurchLine table to retrieve the item type field. 

Then, for each enum value, type in the value you want to display in your report. 

The formula is: 

CASE(@.’purchLine()’.’getItemType()’,

Po_Line_Type.Item, “Stock”,

Po_Line_Type.Service, “Service”)

That’s it you can now export enum fields in Electronic Reporting ! 

Security in D365 F&O (III)

I would like to address briefly the last topic about Security in D365 F&O, i.e. field level access. Granting update access to a limited number of fields or makes a field invisible can be performed in D365 F&O.

In our case, an organisation asks you to give access to raise a sales order but they don’t allow the user to amend the sales price, or the discount amount provided as they are inherited from the sales contracts.

You can of course cover that requirement in D365 F&O. The first step is to identify the menu item and the fields that you need to restrict access to.

Preliminary steps

Navigate to Accounts Receivable > Orders > All sales orders

Open a sales order

Right click on the Unit price field

Click on Form Name: SalesTable

Extend the Administration tab

The menu item is SalesTableDetails in our case

The field to restrict access to is SalesPrice stored in the SalesLine table

Do the same steps for those fields:

  • Discount (LineDisc)
  • Discount percent (LinePercent)
  • Net amount (LineAmount)

Development

Open Visual Studio on your VM and open your project

Navigate to User Interface > Menu items > Display

Select SalesTableDetails

Click Open designer

Right click and select Find references

You can see that this menu item is included in those following privileges:

Select SalesTableDetailsMaintain

Right click and select Find references

You can see that this privilege is included in those following duties:

Select SalesOrderMaintain

Right click and select Find references

You can see that this duty is included in those following roles:

It is a little bit cumbersome to identify this way the security role to adjust.

You can also select the menu item and click Addins > View Related roles

Then, open the excel spreadsheet.

Duplicate in project the role TradeSalesClerk we would like to adjust and remove the standard duty SalesOrderMaintain

Duplicate in project the duty SalesOrderMaintain we would like to adjust and assign the custom duty CustSalesOrderMaintain to the custom security role CustTradeSalesClerk

Remove the standard privilege SalesTableDetailsMaintain from the custom duty CustSalesOrderMaintain

Duplicate in project the privilege SalesTableDetailsMaintain we would like to adjust and assign the custom privilege CustSalesTableDetailsMaintain to the custom duty CustSalesOrderMaintain

Find the entry point SalesTableDetails and add the data source SalesLine 

Then right click on the SalesLine entry and select Add a new data source field for those fields:

  • SalesPrice
  • LineDisc
  • LinePercent
  • LineAmount

 Give a read access to those fields:

Build your project.

Now you can test the new role. You can raise a sales order but if you try to edit the unit price, the field is greyed out.

You can check the first article about security to see how to test your roles.

Another case is a little bit tricky. An organisation asks you to grant update access to credit fields on the customer account.

Perform similar preliminary steps

Add the CustTable to the CustTableListPage menu item

Set the access level of the menu item to Read

Set the access level of the CustTable to Update

It gives update access to all fields on the CustTable. That’s why, we finally need to add new data source fields  (Click add a new data source field) to the CustTable for all fields that need to have read access.

It can be quite cumbersome for big tables like this one.

That’s the last article about Security. I will change topics and focus on Electronic Reporting moving forward.