Hello, we need to align an amount on the right instead on the left and without the decimal. For instance I have an amount of (1234,56) or (-1234,56) in a dataease table defined as number with 2 decimal and we have to format the field in 16 alphanumeric as follow '            1234' or     '           -1234'. In my DQL I'm using inside an enter records the following item : Value_8 := floor(Amount) but unfortunately the result is correct only in case of positive sign but it's incorrect in case of negative sign '-1234           '. I'm working in Italy and we use comma for decimal separator. The output is txt file. Any suggestion will be appreciated. Thanks .

Sorry, I correct myself. The problem is present also in case of positive amount.

Download Sample

Thank you for giving us such a brilliant opportunity to show of some of the new and sadly not so well known functions in DataEase 8.

In this sample we will use the following to functions:
FixedWidth()
StringTo()

FixedWidth() basically return a fixed string of n length where you can choose if the value should be left, center or right oriented.
StringTo() basically return the value up to but not including a search that can be a character or a string, so in this sample we will use StringTo(Value," ,")="" which="" will="" return="" 19342="" from="" the="" number="" 19342,44.=""


Here we see the data we want to export and the DQL that will export them to the right. 

In this DQL we have taken the problem a little further and also rounded the decimals up and down before stripping the decimals.

The "trick" to do that in DE is simply to add 0.5 and then floor the resulting value i.e.strip the decimals away. In this case we didn't really need to use floor as we were going to strip them anyway with StringTo() but it was a nice opportunity to show how to do this too... ;-)

StringTo() is complimented by StringFrom() and StringBetween() so you can basically strip and get whatever you want from inside a string.

Another complimentary set is of course MemoStringTo(), MemoStringFrom() and MemoStringBetween() 

Great! It works well. I have another questiom. I have to format a field in this way "000000000.00" in a fixed text field within a length 12 char (padded with zeroes) . The input field is simply a number with 2 decimals. Take in mind that I'm in Italy and we use a comma for decimals instead of point. This field must be used in an export context and must have a fixed length. Thanks for all.

Download Sample

This is quite straightforward but as it again showcase some of our new functions we'll happy to showcase it.

We simply added the form ReformatNumber to the application ExportRightWithoutDecimal.

The derivation is as follows:

stringreplace(lastc(stringreplace(concat("000000000000",number),chr(160),""),12),",",".")

What we do is as follows:

1. concatenate the number with a string of 12 0's so blank will return 12 0's.
2. we remove the thousand separator by replacing it with "" nothing.
3. we then truncate the string to only be he last 12 characters.
4. We replace , with . with stringreplace.

FInished!