European currency validation Issue IsNumber(21.345,00) should be true globally

[KPrasanth9]

Hi Team,

I got an issue in the Blue Prism i want to check the European currency it's reverse to the Indian formate

dot'.' and Comma ',' are inverse in the European currency but when i try to validate the value by using IsNumber("Text") function it's not getting True.

INR Value : 21,345.00
European Value : 21.345,00

Can you guys please help me with a resolution.

Regards,
Prasanth

 

[VJR]

Hi KPrasanth9,

You are right, the IsNumber on INR Value : 21,345.00 is showing up as True,
but the European Value : 21.345,00 is retrieving False.

I tried changing the Currency format in the Region & Language settings of the Control Panel but it did not have any effect. Probably it has got to do with the locale settings that Blue Prism uses to validate the IsNumber function. I do not know where this setting is but I can think of the below options.

1. From the European format value, replace the dot with a comma and a comma with a dot to bring it to a INR format.
You can use the Replace function of BP. But you might have to use a temporary dummy character to replace because the character replaced at first is again the next character to be replaced still retaining the original replacement character. You will find out while doing so. See if you are able to do it without the dummy character.

You can do this in a single multi Calc stage which has three separate functions as follows:

Replace([Data1], ".", "#")
Replace([Data1], ",", ".")
Replace([Data1], "#", ",")

If Input is 21.345,00 then their internal outputs will be as follows:
Replace([Data1], ".", "#") -> 21#345,00
Replace([Data1], ",", ".") -> 21#345.00
Replace([Data1], "#", ",") -> 21,345.00

Now you have the final output converted to its INR equivalent format -> 21,345.00
And you can now apply the IsNumber function to check whether the European currency format was right or not.

I do not know all the possible permutations and combinations of the European currency format.
So you can use this approach only if the replacement formats are as straight forward as this.

2. The second approach is by using Regular expressions:
You can find the appropriate Regular Expression for the European currency format.
As per this link it is /^\d+(\.\d{3})*(,\d{2})?$/; but you need to test that out or make amendments to it.

Then validate it by using either the 'Extract Regex Values' or 'Test Regex Match' actions of the 'Utility - Strings' VBO.

You can post back what solution you came up with so that it would be beneficial for anyone else with a similar issue.

 

[KPrasanth9]

Here the problem is IsNumeric function is working good when running from the excel but when it comes to the Blue Prism it's not working your first approach will create some issues as we are having a missed currency values as per the below example

If the currency has given is in the European currency format like 21.023,453 the cal replace function will work superbly but if we got a number like
21,0234.00 then it won't work but thanks for that input.

Can you please help me if there is any predefined dll.system packages for global currency validation or global number validation?

Your inputs will help for the future investigation.

Regards,
Prasanth

 

[VJR]

Hi KPrasanth9,

You can check to see if the TryParse method is suitable for your purpose.
https://msdn.microsoft.com/en-us/library/ew0seb73.aspx
In this link, scroll down to the Examples section. In the VB tab there appears to be culture specific code.

You can see which setting works for Euro currency by doing something like this in the Code stage. (This is just a working sample but not specific to your conversion requirements). Add the System.Globalization namespace in the Initialization tab.
Put this in an object action with just the Start, Code stage and End stage and check the values of the output parameters.


InputValue is the input currency value eg; 21.345,00
Result is a True/False Output parameter of the Code stage of type Flag.
RetNumber is a number data type Output parameter.

In line with the above method also check these links-
Formatting Numeric Data for a Specific Culture:
https://msdn.microsoft.com/en-us/library/syy068tk(v=vs.71).aspx

CultureInfo.CreateSpecificCulture Method
In this link scroll below to see a list of SPECIFIC CULTURE codes.
https://msdn.microsoft.com/en-us/li...ltureinfo.createspecificculture(v=vs.90).aspx

 

[VJR]

If the above doesn't work for you then I still think you can find out a suitable Regular expression for all possible input combinations and then validate as mentioned above.
Or if you say that the excel function works correctly as expected then you can paste the input value to excel, validate it and then return back the result to BP if that works with you.