Remove zeros and formating decimal

[Lazim]

Hi folks.
I am an apprentice on the tool.
I have some fields extracted from a ‘txt’ data file that have zeros originally added to the beginning of the data. Example: for $ 1,599.49 the extracted data is: 0000159949.
I need to remove the zeros and add: (.) in the thousands and (,) in the decimals.

[the correct data should be $1,599.49]

What is the best way to achieve this? Will I need to add new fields with javascript formatting each one? (I have 525 fields)
Where would it be better to do this, in the Template or in the Datamapper?
BR

[jchamel]

To extract the value without the leading 0, you can do this in your Datamapper extraction as below:

var str = data.extract(70,80,0,1).trim();
var idx = /[^0]/g.exec(str);
str = str.slice(idx.index);

if(str.indexOf('.') < 0){
	str = str.substr(0,str.length-2)+'.'+str.slice(-2);
}

1. You extract the string
2. You search for the 1st occurence of a character that is not 0 and get its index in the string
3. You grab the rest of the string starting at that 1st non-zero character.
4. You check if there is no decimal period and if not, add one befoe the last 2 characters
5. Set your field as currency as below:
   
   

The formatting would need be done in the Template for the thousand separator. the currency field will display based on what you have setup as locale in your Template:
Edit menu->Locale…

[Phil]

@jchamel’s solution works fine, but just for fun, I thought I could do better by using the post function instead of using a full javascript extraction.

The post function is executed just after the DataMapper has read the raw data and just before it applies the Data format mask to it. So the trick is to have the post function modify the data is such a way that it appears to be written that way in the original data file.

The replace(/(\d{2}$)/,".$1") post function uses a Regular Expression to target the last two digits of the extracted data and to replace them with a dot, followed by those same two digits.

So while the original data contains 0006984913, once the post function has executed, it appears as though the data was in fact read as 00069849.13. Since we also define the Data format for that field with no Thousand separator and a dot as the Decimal separator, the extraction step seamlessly converts the result to a Currency, as seen in the data model on the far right.

The three 0s get automatically discarded by the string-to-number conversion, so you don’t have to do anything for that.

And now that the value is stored as a Currency, you can display it pretty much any way you like in your template using the format helpers.

[Lazim]

Perfectly, these two ways are great and worked like a glove.

If I understand correctly, for the first solution, I have to create new fields using javascript to handle the data from another field already created and extracted.

It’s not that it doesn’t work, but it requires more attention to map and bring the desired result.
In the second solution, the result is handled in the extraction, ensuring that there is no need to create new fields, rename them and parameterize them.

Once that’s done, I’ll think of something similar to handle zip-code data by adding hyphens in these cases: (0000078701378 & 78701378) for 78701-378 where the last 3 digits are hyphenated.

Many thanks to everyone involved!
Best Regards from Brazil

[Phil]

Yes, to convert both 0000078701378 and 78701378 to 78701-378, you would use the following post function:

trim().replace(/^(?:[0]*)(\d*)(\d{3}$)/,"$1-$2")

[Lazim]

@Phil

trim().replace(/^(?:[0])(\d)(\d{3}$)/,“$1-$2”)

Before I was creating a new field with the following expression:

var field, result = “”;
field = record.fields[“zipCode”].replace(‘-’,‘’);
if (field !== “”) result += field.substring(0,5) + “-” + field.substring(5,8);

With this new regex I’ll save time creating LOL

My sincere thanks. You saved my week of hard work.

[Lazim]

@Lazim

0000159949

I come back to the point of formatting monetary values…
Is there any way of adding the dot in the thousands?
E.g. from “0000159949” to “1,599.49”
The solution of adding the comma in the decimal and excluding the leading zeros has worked correctly.

BR

[Phil]

My original regular expression in the Post function already adds a decimal point. There are no commas to add because that’s a display preference, not a data storage preference.
So, make sure that your field is set to the Currency type. In the data model, it will not display any commas, but that’s normal. It’s when you insert that value on the template that you apply the proper visual format.

[Lazim]

@Phil

It’s when you insert that value on the template that you apply the proper visual format.

How do I do this on the Phil model?

[Phil]

In your template, you drag/drop the field where you want it. Then you modify it with a handlebar expression with a format helper.

When you preview this, it shows:

[Sander]

If ‘total’ is a currency field you’ll get {{currency total}}. There will be two curly braces on each side, and the ‘currency’ format helper is added automatically.

In those screenshots the ‘total’ field is of type ‘HTML string’, which messes up the logic. You’ll get {{{total}}}, and you then need to add the ‘currency’ format helper manually. The three curly braces on each side mean the content is unsanitized (i.e. not HTML-escaped), which only makes sense if it contains HTML tags.

[Phil]

Oops… rookie mistake on my end! Thanks for pointing it out, Sander.

I’ll leave my original post as-is because I think your explanation adds some valuable information.

So obviously, my example should have used a Currency data type, and Sander is right: drag&dropping it onto the page automatically adds the currency helper: One less step!