Hi There,
As we all know due to variety of localization settings SAP Business One doesn't have any automatic exchange rate update function within SAP Business One Client and the rates should be entered manually by user. This may not be any big deal however, it eventually becomes a problem when you start working with customers who has running worldwide business. I came up with couple of alternative methods to deal with this situation and I would like to share them with you.
There are two important part you need to decide before developing your project. First thing is deciding how you will fetch the daily currency rates. There are variety of options such as webservice links, xml urls or csv sources. I will use the daily xml link provided by European Central Bank. You can use this source to derive your local currency rates by calculating parity. Also you can find more detailed and localized sources on the internet both free and paid. Second thing you need to decide is how you will execute your application. At his point you have two option. You can create a windows service application and configure trigger and schedule settings after installing it. Or you can create a windows console application and use Windows Task Scheduler to execute your program regularly.
In this example I will explain you how to update exchange rates for "USD", "AUD", "GBP" and "TRY" in a system with "EUR" as a local currency. And then I will explain how to use Windows Task Scheduler to run your console application.
Notes before proceeding;
- First we will connect to the company database.
- We will use the "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml" url to get the exchange rates in a "xml" format and parse the tags.
- Finally we will use "SetCurrencyRate()" function to update the exchange rates in the company database.
- Please check "Currency Functions" and "SetCurrencyRate Method" topics in SDK Help Center to get more information about the subject.
- Makes sure your debug configuration is right. (x86, x64 etc.)
static void Main(string[] args)
{
try
{
//Define Required SAP Business One Objects
SAPbobsCOM.Company oCompany;
SAPbobsCOM.SBObob oSBObob;
SAPbobsCOM.Recordset oRecordSet;
oCompany = new SAPbobsCOM.Company();
//Enter server connection info and establish the connection
oCompany.Server = "ServerName";
oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012;
oCompany.CompanyDB = "CompanyDatabaseName";
oCompany.UserName = "UserName";
oCompany.Password = "Password";
int ret = oCompany.Connect();
//Get Currencies from the source and read it
//Note that how you parse the currency rates depends on the data source
// Define the link and the namespaces in the xml file
XDocument xmlDoc = XDocument.Load("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");
XNamespace gesmes = "http://www.gesmes.org/xml/2002-08-01";
XNamespace ns = "http://www.ecb.int/vocabulary/2002-08-01/eurofxref";
//Read the tags that contains currency codes and rates
var tags = xmlDoc.Descendants(ns + "Cube")
.Where(x => x.Attribute("currency") != null)
.Select(x => new
{
Currency = (string)x.Attribute("currency"),
Rate = (decimal)x.Attribute("rate")
});
//Update currency rates in the SAP Business One Database
oSBObob = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge);
oRecordSet = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecordSet = oSBObob.GetLocalCurrency();
oRecordSet = oSBObob.GetSystemCurrency();
// Read the tags you have fetch from the xml link to get the specific rates
foreach (var result in tags)
{
if (result.Currency.ToString() == "USD")
oSBObob.SetCurrencyRate("USD", DateTime.Now, Convert.ToDouble(result.Rate), true);
if (result.Currency.ToString() == "AUD")
oSBObob.SetCurrencyRate("AUD", DateTime.Now, Convert.ToDouble(result.Rate), true);
if (result.Currency.ToString() == "GBP")
oSBObob.SetCurrencyRate("GBP", DateTime.Now, Convert.ToDouble(result.Rate), true);
if (result.Currency.ToString() == "TRY")
oSBObob.SetCurrencyRate("TRY", DateTime.Now, Convert.ToDouble(result.Rate), true);
}
if (oCompany.Connected == true)
oCompany.Disconnect();
}
catch (Exception e)
{
Console.WriteLine(e.ToString()); // Display on the console application if anny error occurs
Environment.Exit(0); // Optionally use this function to close console application window if any error occurs
}
}
static void CurrentDomain_ProcessExit(object sender, EventArgs e)
{
Environment.Exit(0); // Optionally use this function to close console application window after it runs
}
After you build your application put your ".b1s" and ".exe" file to a folder. Then go to "Control Panel\System and Security\Administrative Tools" and open "Task Scheduler". Click "Create Task" from "Actions" pane. There you'll have five tabs to configure your task. Name your task in the "General" tab. Go the "Actions" tab to add your program to the task and go to the "Trigger" tab to set task schedule. Also check "Settings" and "Conditions" tabs for additional setting that may be needed in particular situations. Finally click "Ok" and you will have your task created.
Also I would like to mention other possible ways to update exchange rate. If you don't want to use an application with "Windows Task Scheduler". Check "How to Create a Windows Service" tutorials on youtube and use the same code above inside your project. Also if you just want to simplify updating exchange rates in SAP Business One Client you can use same code inside the "Exchange Rates and Indexes" system form and a button that updates the specified rows in the matrix.
Method I used for fetching the currency rates might be complex compared to other available methods. Therefore feel free to ask if you have any questions about the subject. Do not forget to like to post and give feedback if you find it helpful.
Regards,
Atilla
