D365 Electronic Reporting: JOIN records in pairs

D365 Electronic Reporting: JOIN records in pairs

Recently, I came across a really challenging requirement in Electronic Reporting / Configurable Business Documents in Dynamics: in an additional section in the Delivery note, print a flat table where every pair of consecutive records becomes one line:

No	Not like this	But like this	No
1	Left lace	Left lace – Right lace	1
2	Right lace	Left shoe – Right shoe	2
3	Left shoe
4	Right shoe

This required a set of sophisticated techniques I am thrilled to share. The idea is to split into odd and even records and build something like

SELECT *, ROW_NUMBER as Rn1 FROM Records JOIN *, ROW_NUMBER as Rn2 from Records ON Rn2= Rn1+1

Enumerate: make a so called Calculated field at the ER configuration’s root level with a record list and a row Number inside:
ENUMERATE(ALLITEMS(MyRecords))
Add another Calculated field at the row level to check if the current record has an odd or an even row number: LEN(REPLACE("13579",RIGHT(NUMBERFORMAT(@.Number,"#"),1),"",false))<5 I’ll leave the formula uncommented, but I wonder if somebody manages to write a shorter function. I can also imagine using TRANSLATE() or the VALUEIN(SPLIT(“1,3,5,7,9″…) functions. The problem is the missing MOD operator in Electronic Reporting: https://experience.dynamics.com/ideas/idea/?ideaid=09590ad4-70a4-e711-80c0-00155d7cd0b4. They promised, but failed to deliver. Anyway, now we’ve got this:

Next, let’s add another root Calculated field, filter the list by $Odd = true and make a list with only the odd records:
WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.'$Odd')
Finally, attach an ‘even’ record to every ‘odd’ one by placing another Calculated field with a lookup inside of the $OddRecord: FIRSTORNULL(WHERE('$AllRecordsEnumerated', '$AllRecordsEnumerated'.Number=@.Number+1)). Here is the result, a JOIN to itself:

Bingo!

Electronic reporting blog series

Cross-company data sharing vs. Duplication in Dynamics

Cross-company data sharing

Cross-company data sharing in Dynamics 365 for Finance and SCM (see System administration > Setup > Configure cross-company data sharing) supposed to be a valid replacement of the virtual companies in Dynamics AX2012 and earlier. I don’t know how about you, but I get the error “Table … is a Main table as specified by the table group and may not be shared unless its Data Sharing Type is Duplicate” every time I wish to share something useful.

It looks for the TableGroup property in the D365 table metadata and practically rejects any table which is not a Group or does not have a simple primary key. What is left for sharing? Some little configuration tables such as the Delivery terms (INCOTERMS), Payment terms ans so on: only these have the TableGroup = Group. This rules out the most interesting tables in Dynamics such as the InventTable (Product list), CustTable (Customers) or VendTable (Suppliers) (well, this is not entirely true: for the latter there is a dedicated Feature Customer and Vendor master data sharing available for activation). Namely, these have TableGroup = Main.

The TableGroup property of the standard tables is not extendable. The developers left a backdoor: a Main table with the property DataSharingType explicitly set to Single or Duplicate may be eligible too. CustTable is an example. Needless to say, the DataSharingType is not extendable either. There seems to be a yet another backdoor in the form of the SysDataSharingTypeTableConfiguration table where this DataSharingType is supposed to be configured. However, this feature is protected by the flight EnableSysDataSharingTypeTableConfiguration. As with every flight it is very promising and tempting but it remains hard-locked in any production environment.

Dead end? Not quite.

Recurring ‘Copy into legal entity’ data project

In Data management, create a new data project of the Copy into legal entity kind, select the destination company(ies) and the appropriate entity or entities:

It should not grab unfinished work, though. In this example it is obvious to only consider an approved bill of materials. The BOM lines do not have an approved flag, but if there is no BOMId, no line can be copied either, i.e. it is sufficient to only filter the BOM headers. In general, you should take care of the data consistency. In the particular case of the bills of materials, they should not contain any unknown items or refer to any local inventory dimension such as the site, warehouse or the warehouse location: ensure the default sites in all companies in question have the same code “STD” or “DEF”, and set the Resource consumption checkbox in all lines to make them warehouse-agnostic. The Approver is not a problem, since the employee list is technically shared across all legal entities.

Click Copy into legal entity in batch and set a Recurrence e.g. every weekday. The data project may be run over and over again in a cycle copying the same data, but it is not a problem as it works in the UPSERT mode in Dynamics. However, as the data grows the data project runs slower. We should restrict the set and take only the changes of the day, for example. If you are lucky, there will be a ModifiedDateTime column you can use in the Filter:

(here with a Join and an advanced moving date query).

Dynamics 365 tips and tricks

German travel expenses in Dynamics 365 – Part 2

Confinued from German travel expenses in Dynamics 365 / Deutsche Reisekostenabrechnung 2022

Domestic business travel – Case 2 “3 days of training with hotel”

The 2nd case requires a receipt, a bill for the accommodation at a hotel. The VAT from domestic invoices may be recovered, and we need a country-specific setup for the VAT (…> Setup > General > Tax configurations) where the host country is associated with a tax group containing both full (19%) and reduced (7%) tax codes.

Set up an additional category of the type “Hotel”, assign the reduced 7% tax group to this category. Let the user enter a line against his category as an amount including tax, and select the Country/region = DEU. The domestic VAT is derived automatically by the system: €9,58.

The two lunches reduce the per diems by €22,40 down to €33,60 as required:

Domestic business travel – Case 3 “Hotel breakfast”

This time the hotel served Erika breakfasts. The breakfast is presented on the bill separately, since the accommodation and meals are subsidized through the reduced VAT of 7%, but beverages other than water are taxed at the full rate of 19%:

This requires an “Itemization” and at least 2 expense Subcategories under the main expense category Hotel. You can deactivate all standard subcategories but the Daily room rate, Restaurant and Loungebar and apply the full rate to the latter. The VAT amount by the “item” can be reviewed under Itemization details, the total VAT amount from the itemizations is not visible elsewhere.

If this is considered an unreasonable workload, you may abandon the itemization and simply edit the total VAT receivable in the Actual VAT amount field.

The entertainment (de: Bewirtung) may be dealt with similarly: 2 subcategories or even 3, as the tips (de: Trinkgeld) are VAT free. A mandatory list of guests may be enforced with a travel expense policy (Expense management > Setup > Policies > Expense report) where a submission to the workflow is prevented with an error message if the Number of persons entertained = 0 for any expense category of the Entertainment type:

Case 4 “Travelling abroad”

As we have learned already, daily allowances are country-specific. The reduced domestic rate for Germany is 50%, but the reduced foreign rates are close to 66% (but not exactly): here is an example for Switzerland, note the ratio:

	Full 24h day	“First/last day”	Ratio
Switzerland
– Geneva	€66	€44	66,67%
– the rest of Switzerland	€64	€43	67,19%

The reduced rates are rounded by the government to whole euros. To match the reduced foreign rate, I use a tailored percentage. It should approximate the daily rate with a deviation less than €0,01:
44/66 = 0,6666… => 66,00 * 66,67% = 44,0022 ~= 44,00
43/64 = 0,671875 => 64,00 * 67,19% = 43,0016 ~= 43,00

The respective setup of per diems and tiers per location is shown on the following screenshot:

Note that the rates are updated by the BMF every year and have to be regularly re-imported with validity dates. This is also true for the calculated percentage for first/last day.

The meals reduction and per diem calculation now caters for the rates in Geneva:

Switzerland does not belong to the EU, and their foreign VAT on the hotel bill is not recoverable, the selection of the right country code removes the VAT. No itemization is needed, the hotel bill may be entered as a lump sum in Swiss francs. The amount paid in Swiss francs is converted to euro at the intrinsic System exchange rate type, or at a user-defined rate.

Author Archives: Eugen Glasow