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

Unifrax
Unifrax used Ask the Experts™
on
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));

@Command([ViewRefreshFields])

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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
FormulaLanguage
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.
Sjef BosmanGroupware Consultant

Commented:
Apparently, ConversionRates is not a number field. Why is that? IMHO that's the root of all evil.
Sjef BosmanGroupware Consultant

Commented:
Your reaction, please...

Author

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 Consultant

Commented:
Ok! So you can close the question...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial