Bulk values updating

OL Connect Professional Designer
1

Hi there,

I have extracted over 100 differenrt columns from supplied data, and 26 of them need to be formated as 6 digital.

image
image177×525 3.88 KB

Instead of formating them one by one, is there any way i can formating all these values togething in datamapper?

Many thanks

2

In the DataMapper, you can convert your field extraction to Javascript, with code similar to this:

(data.extract(70,80,0,1,"")*1).toFixed(6).concat("");

That statement extracts the data as a string, multiplies it by 1 to convert it to a number, then uses the toFixed() method to set 6 decimals, and finally concatenates an empty string to the result in order to convert it back to a string.

Obviously, the data.extract() statement will have to be adapted to your data.

3

Hi Phil,

Thanks for your reply.
Apologise for not making this clear in the beginning.

My data is an excel file, so my extraction must be extract(columnName, rowOffset)

image
image1766×840 63.2 KB

However, the thing is I still have to do it one by one field right? Because my live data got Over 400 columns and more than half of them need to be formatted as 6 decimals.

So that i cannot add any script after extraction to make this easier right ?

Thanks
Cain

4

Yep, you can do that in one go with a post-processing script. Something like this should get you started:

var fieldsToNormalize = ["v1A","v2B","v2C","v4D"];   // add all the fields to normalize in the array
for(let i=0;i<data.records.length;i++){
    let rec = {};
    for(let f=0; f<fieldsToNormalize.length;f++){
        let fldName = fieldsToNormalize[f];
        let newValue = (data.records[i].fields[fldName]*1).toFixed(6).concat("");
        rec[fldName] = newValue;
    }
   data.records[i].set(rec);
}

The code uses an array containing the names of all the fields whose decimals you want to set.
Then, for each record that was extracted, it builds an object (rec) that contains only those fields, with the adjusted value. Finally, it uses the set() method to store all these new values into the data record.

That’s the cool thing about the set() method: it is non-destructive, meaning that it will only replace in the data record the fields that are explicitly set in the rec object, leaving the other fields untouched.

EDIT: This code cannot work as initially intended, see how to achieve the same results in this follow-up post.

5

Awesome!!! That’s exactly what I’m looking for!! Thanks a lot!

I have another issue regarding bulk fields renaming.
Almost 200 columns having space in the extract fields name, and as i know space will cause the metadata issue in the workflow
image

So is there any easy way to rename all of them? or is there any set up to ignore space when I do data extraction

Many thanks
Cain

6

You simply have to rename them in your data model.
Click on any field and press F2.

Unfortunately, you can’t rename them after they have been extracted, so you have to make sure your data model contains the final names.

Note that the name in the data model doesn’t have to be the same as the name of your CSV column. You can assign any name to a field (well, subject to best practices, which is something we touched on in one of our recent Tech Blog articles).

7

Thanks so much for your help:)

8

Hi Phil,

Sorry for bother you again. The post -processing was working perfectly at the designer, all the fields have been formatted correctly when i hit the apply button

However when I ran the datamapper plugin at the workflow under debug model, it does not work at all, is there anything special setup i have to make?

Mant thanks
Cain

9

I see you have posted your question twice. Please don’t do that, it becomes confusing for other members of the community who are trying to follow the conversation.

I will answer in the other thread.( Post processing script apply - OL Learn (objectiflune.com))