Currency Exchange Rate Service with Swiss Admin website

Jérémie

good afternoon!
I am trying to setup the Currency Exchange Rate Service on NAV2016with the following xml.
http://www.afd.admin.ch/publicdb/newdb/mwst_kurse/estv/mittelkurse_xml.php
I am struggling to setup this service.
Have you ever done it with this website?
Thanks in advance
Jérémie

archer89

i have developed the needed data exchange definition for that special case.
so create a new empty text file. add following xml text. save it as e.g. dxd-afd.xml.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<root>
<DataExchDef Code="CURRENCY0001" Name="AFD-EXCHANGE-RATES" Type="3" FileType="0" ReadingWritingCodeunit="1203">
<DataExchLineDef LineType="0" Code="CURREXCHANGE" Name="Parent Node for Currency Code" ColumnCount="0" DataLineTag="/monatsmittelkurs/devise">
<DataExchColumnDef ColumnNo="10000" Name="monatsmittelkurs" Show="false" DataType="0" Path="/monatsmittelkurs" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="20000" Name="xmlns" Show="false" DataType="0" Path="/monatsmittelkurs/@xmlns&quot; TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="30000" Name="xmlns:xsi" Show="false" DataType="0" Path="/monatsmittelkurs/@xmlns:xsi" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="40000" Name="xsi:schemaLocation" Show="false" DataType="0" Path="/monatsmittelkurs/@xsi:schemaLocation" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="50000" Name="monat" Show="false" DataType="0" Path="/monatsmittelkurs/monat" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="60000" Name="devise" Show="false" DataType="0" Path="/monatsmittelkurs/devise" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="70000" Name="code" Show="false" DataType="0" Path="/monatsmittelkurs/devise/@code&quot; TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="80000" Name="land_de" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_de" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="90000" Name="land_fr" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_fr" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="100000" Name="land_it" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_it" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="110000" Name="waehrung" Show="false" DataType="0" Path="/monatsmittelkurs/devise/waehrung" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="120000" Name="kurs" Show="false" DataType="0" Path="/monatsmittelkurs/devise/kurs" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="130000" Name="" Show="false" DataType="0" TextPaddingRequired="false" />
<DataExchMapping TableId="330" Name="Mapping for Currency Exchange" MappingCodeunit="1280">
<DataExchFieldMapping ColumnNo="50000" FieldID="2" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="70000" FieldID="1" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="120000" FieldID="3" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="130000" FieldID="6" UseDefaultValue="true" DefaultValue="1" />
</DataExchMapping>
</DataExchLineDef>
</DataExchDef>
</root>

then create a new "currency exchange rate service" record.
Name = "AFD-EXCHANGE-RATES"
Desc. = "AFD-EXCHANGE-RATES"
service url = "http://www.afd.admin.ch/publicdb/newdb/mwst_kurse/estv/mittelkurse_xml.php&quot;

in tab "field mapping" set following:
field "Parent Node for Currency Code" = /monatsmittelkurs/devise
field "Currency Code" = /monatsmittelkurs/devise/@code
field "Starting Date" = /monatsmittelkurs/monat
field "Exchange Rate Amount" = /monatsmittelkurs/devise/kurs
field "Relational Exch. Rate Amount": leave empty, but set default value to 1

then click on button "data exchange definition".
in this page click "New", then import the created dxd file (above text) by clicking "Import data exchange definition file". Close and go back to the exchange service page.

Now click "Preview" to show the imported exchange rates. currencies that are not shown in the list, although delivered by the service, are missing, because the currency is not defined in the nav currency list. to get the exchange rate also, add the needed currency record in nav. You can now use the new exchange rate service in page currency list to update the exchange rates. it's also possible to configure a job queue entry for automatic update.

archer89

please follow
https://msdn.microsoft.com/en-us/library/mt299007(v=nav.90).aspx
https://msdn.microsoft.com/en-us/library/mt299004(v=nav.90).aspx
https://msdn.microsoft.com/en-us/library/dn495312(v=nav.90).aspx
https://msdn.microsoft.com/en-us/library/mt299584(v=nav.90).aspx

Jérémie

Thanks for the links. I have already checked this out but I did not manage to fully setup the currency Exchange rate with the swiss website...

Jérémie

I don't know how to import the Exchange Rate amount from the website. Sometimes the value is 1 EUR, sometime 10000 IDR etc... as shown below in the exemple
Indonesien 10000 IDR 0.76067
Euro 1 EUR 1.09484
http://www.afd.admin.ch/publicdb/newdb/mwst_kurse/estv/mittelkurse_xml.php
Is it possible to take only the Numeric part of 10000 IDR, 1 EUR etc...?
thanks a lot for your help
Jérémie

archer89

i have developed the needed data exchange definition for that special case.
so create a new empty text file. add following xml text. save it as e.g. dxd-afd.xml.

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<root>
<DataExchDef Code="CURRENCY0001" Name="AFD-EXCHANGE-RATES" Type="3" FileType="0" ReadingWritingCodeunit="1203">
<DataExchLineDef LineType="0" Code="CURREXCHANGE" Name="Parent Node for Currency Code" ColumnCount="0" DataLineTag="/monatsmittelkurs/devise">
<DataExchColumnDef ColumnNo="10000" Name="monatsmittelkurs" Show="false" DataType="0" Path="/monatsmittelkurs" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="20000" Name="xmlns" Show="false" DataType="0" Path="/monatsmittelkurs/@xmlns&quot; TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="30000" Name="xmlns:xsi" Show="false" DataType="0" Path="/monatsmittelkurs/@xmlns:xsi" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="40000" Name="xsi:schemaLocation" Show="false" DataType="0" Path="/monatsmittelkurs/@xsi:schemaLocation" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="50000" Name="monat" Show="false" DataType="0" Path="/monatsmittelkurs/monat" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="60000" Name="devise" Show="false" DataType="0" Path="/monatsmittelkurs/devise" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="70000" Name="code" Show="false" DataType="0" Path="/monatsmittelkurs/devise/@code&quot; TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="80000" Name="land_de" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_de" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="90000" Name="land_fr" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_fr" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="100000" Name="land_it" Show="false" DataType="0" Path="/monatsmittelkurs/devise/land_it" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="110000" Name="waehrung" Show="false" DataType="0" Path="/monatsmittelkurs/devise/waehrung" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="120000" Name="kurs" Show="false" DataType="0" Path="/monatsmittelkurs/devise/kurs" TextPaddingRequired="false" />
<DataExchColumnDef ColumnNo="130000" Name="" Show="false" DataType="0" TextPaddingRequired="false" />
<DataExchMapping TableId="330" Name="Mapping for Currency Exchange" MappingCodeunit="1280">
<DataExchFieldMapping ColumnNo="50000" FieldID="2" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="70000" FieldID="1" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="120000" FieldID="3" UseDefaultValue="true" />
<DataExchFieldMapping ColumnNo="130000" FieldID="6" UseDefaultValue="true" DefaultValue="1" />
</DataExchMapping>
</DataExchLineDef>
</DataExchDef>
</root>

then create a new "currency exchange rate service" record.
Name = "AFD-EXCHANGE-RATES"
Desc. = "AFD-EXCHANGE-RATES"
service url = "http://www.afd.admin.ch/publicdb/newdb/mwst_kurse/estv/mittelkurse_xml.php&quot;

in tab "field mapping" set following:
field "Parent Node for Currency Code" = /monatsmittelkurs/devise
field "Currency Code" = /monatsmittelkurs/devise/@code
field "Starting Date" = /monatsmittelkurs/monat
field "Exchange Rate Amount" = /monatsmittelkurs/devise/kurs
field "Relational Exch. Rate Amount": leave empty, but set default value to 1

then click on button "data exchange definition".
in this page click "New", then import the created dxd file (above text) by clicking "Import data exchange definition file". Close and go back to the exchange service page.

Now click "Preview" to show the imported exchange rates. currencies that are not shown in the list, although delivered by the service, are missing, because the currency is not defined in the nav currency list. to get the exchange rate also, add the needed currency record in nav. You can now use the new exchange rate service in page currency list to update the exchange rates. it's also possible to configure a job queue entry for automatic update.

rosietesmen

The availability of current rates cannot be guaranteed 100% for technical reasons. The following applies for the value added tax on imports: In the event of system failure, the exchange rates (sell) are to be obtained from a Swiss or Liechtenstein bank.

kriki

[Topic moved from 'Navision Financials' forum to 'NAV Three Tier' forum]

Jérémie

Thanks Jonathan!

MSchuermann

Jérémie wrote: »

I don't know how to import the Exchange Rate amount from the website. Sometimes the value is 1 EUR, sometime 10000 IDR etc... as shown below in the exemple
Indonesien 10000 IDR 0.76067
Euro 1 EUR 1.09484
http://www.afd.admin.ch/publicdb/newdb/mwst_kurse/estv/mittelkurse_xml.php
Is it possible to take only the Numeric part of 10000 IDR, 1 EUR etc...?
thanks a lot for your help
Jérémie

Is there already a solution for this problem?