@SETFIELD from LOOKUP showing incorrect decimal number dependant on region.

Using the below code, I am performing a lookup on a view.
The ConversionRate field is set to the rate found in the view.
Dependant on region this figure appears differently.

Selection := @Prompt([OkCancelList]; "Select Conversion"; "Click the Required Conversion"; "1" ; @DbLookup("Notes" : "Recache"; "" : ""; "(Validman1)"; "Conversion Rates"; 2) + " / " + @DbLookup("Notes" : "Recache"; "" : ""; "(Validman1)"; "Conversion Rates"; 3));

@SetField("LocalCur"; @LeftBack(Selection; " / "));
@SetField("ConversionCur"; @RightBack(Selection; " / "));

@SetField("ConversionRate"; @TextToNumber(@DbLookup( "Notes" : "ReCache" ; "" ; "(Validman3)" ; "Conversion Rates**/**"+LocalCur+"**/**"+ConversionCur;4)));

@SetField("LocCurSym"; @DbLookup( "Notes" : "ReCache" ; "" ; "(Validman3)" ; "Conversion Rates**/**"+LocalCur+"**/**"+ConversionCur;2));
@SetField("ConCurSym"; @DbLookup( "Notes" : "ReCache" ; "" ; "(Validman3)" ; "Conversion Rates**/**"+LocalCur+"**/**"+ConversionCur;3));


Open in new window

I have check that the view displays in the exact same format for each region and it does.
I have tried changing the field properties (Number, decimal, user setting) but it seems to make no difference.
Tried custom display, and scientific the scientific shows the correct value but with +E4 on the end.

Anyone know how to adjust the field to always set the field to the same format.

FYI the regions where UK vs. Brazil.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you want the number to be shown differently depending on region, use Numbers If you want them to be the same everywhere use Text. But then you need to take extra care if you need to use the number in calculations. And if the text is the same everywhere, you need to use special code for converting text to numbers, since @TextToNumber changes with the region.

Since you mention a Lookup, this also has consequences for the view design.  So if you want the view lookup to always return field aantalUren conaing the result of 123/100 to always show up as 1.23, the column formula needs to be
@ReplaceSubstring(@Text(AantalUren; "F2");",";".")

Open in new window

This will convert the number AantalUren to a text formatted with a two decimals, not punctuated at the thousands.  Then in case a comma was used as decimal separator this will be substitued with a period.

Then if you need this number you'd need to something like
dSep := @Middle(@Text(0; "F1"); 2; 1);
aNum:=@TextToNum(@ReplaceSubstring(tAantalUren; "."; dSep))

Open in new window

or LotusScript:
Dim ses as New NotesSession ' for NotesInternational
aNum = CDbl(Replace(tAantalUren, ".", ses.International.DecimalSep)

Open in new window

Same with @TextToTime:  If a date is always stored as a text value formatted like dd/mm/yyyy, using @TextToTime will NOT always return the correct date, but vary according to region settings. So to have a view show a Date, for a field dtImp with contents "01/11/2016" you need
d:=@ToNumber(@Explode(dtImp; "/")); @Date(d[3]; d[2]; d[1])

Open in new window

In my case I needed a Notes Date from the text, and the previous code using @TextToTime would show a date varying between 1 nov 2016 and 11 jan 2016, which made calculations based on those dates unreliable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sjef BosmanGroupware ConsultantCommented:
Apparently, ConversionRates is not a number field. Why is that? IMHO that's the root of all evil.
Sjef BosmanGroupware ConsultantCommented:
Your reaction, please...
UnifraxAuthor Commented:
The application uses one generic view with two levels to contain all lookup data.
This means that one record may display a text record while others display numeric values.

Using the advice from the top of this thread we have modified the code so as not to use the @Texttonumber function which is region specific.
It seems to have resolved our issue.
Sjef BosmanGroupware ConsultantCommented:
Ok! So you can close the question...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.