Group array of objects by keys for Summary

[Joe]

Hi, I’m trying to produce a summary using a product field and a quantity field.

Data example

So would like to have a total quantity for each Product i.e.

‘Gods Smuggler Book’ 7
‘World Watch List 2024 Top 50’ 90
‘Counting the Cost Portraits and Prayers’ 12

Preferable using a Java script within DataMapper to achieve this.

I’ve looked through the Forum but could find anything on this topic.

Any help or direction would be great.

Many thanks
Joe

[Phil]

Something like this would do the trick:

let summary = {};
for(let i=0;i<steps.lines;i++){
  let title = data.extract("Product",i);
  let qty = data.extract("Quantity",i)*1;
  summary[title] = (summary[title] || 0) + qty;
}
logger.info(JSON.stringify(summary))

After this code runs, the summary object contains:

{
   "Gods Smuggler Book": 7,
   "World Watch List 2024 Top 50": 90,
   "Counting the Cost Portraits and Prayers": 12
}

[Joe]

Hi Phil,

Thanks for this.
Should I use this script in DataMapper or can I use it directly within my Template.

Just not sure how to implement the script.

Cheers
Joe

[Phil]

It was written for the DataMapper.

It simply creates a summary object, you can store that object in a JSON field and use that JSON field in your template. Just add something like record.fields.myObject = JSON.stringify(summary); to the script above (if the field in your datamodel is named myObject, obviously).

[Joe]

Okay, makes complete sense. I’ve done that but I’m not getting any results.

see highlighted field:

Appreciate your help on this.

[Joe]

Also, this solution would give me a ‘summary’ result for every record! Wouldn’t I need a way to ‘Trigger’ the data on ‘Products’ which I could do using the ‘On Change’ rule but my data isn’t ordered correctly for this.

[Phil]

It all really depends on what your document expects. If you only want a summary of your entire data, without extracting individual records, then set your document boundaries to a number of lines that’s high enough so that all lines are included in a single record, and then the script will give you a summary for the entire data set.

If you want to extract each Product individually (i.e. one product per document), then set your input data’s “Sort on” field to “Product”, and set the Boundaries trigger to “On change”, specifying the Product column. The script will then provide you with a summary of the total for each product, in individual documents.

Also, if the script I provided does not return any values, it’s probably because the column names in your data are different than those specified in my script, which was just an example. Adjust the column names in the script accordingly.

[Joe]

Thanks Phil,

I’m trying to achieve the first you described i.e. summary over the entire data.

I’ve changed the number of lines and it looks like it will be perfect, just still not getting return on the script. I’ve checked the field names and they are correct. Could you have a look at the below screen grab to see if I’m missing something?

Thanks
Joe

[Marten]

Can you enclose something like the following inside the for-loop* on line 2 and check whether there has any “Hello World!” lines being logged in the Messages pane?

logger.info("Hello World!");

The reason why I am asking is because I assume it just applies the empty object, as assigned to the variable summary on line 1.

*That is: The for-loop “for (let i = 0; i < steps.lines; i++) {}”

[Joe]

Hi Marten

I’ve replaced line two with the suggested but get the error " ‘i’ is not defined " on line 3.

[Marten]

Please apply it as follows instead:

let summary = {};

for (let i = 0; i < steps.lines; i++) {
	let title = data.extract("Product", i);
	let qty = data.extract("Quantity", i);
	
	logger.info("Hello World!");
	
	summary[title] = (summary[title] || 0) + qty;
}

record.fields.myObject = JSON.stringify(summary);

[Joe]

Hi Marten,

I’ve used the script above and the result is as the screen shot below

Result is still {}

[Marten]

The reason why the result is still an empty object ({}) is because it doesn’t execute the for-loop as steps.lines is most certainly equal to 0.

Would it be possible for you to share the Data Mapper configuration, from which all sensitive information has been removed and which doesn’t contain any data sample files with sensitive information? The reason why I am asking is because this will make it easier for us to check why the applied JavaScript code doesn’t provide the expected result.

[Joe]

Hi Marten,

Yes no problem, I’ve uploaded my DataMapper with the sensitive information removed.

Summary test.OL-datamapper (29.3 KB)

Thank you for your help on this, much appreciated.
Joe

[jchamel]

Hello Joe,

Although Marten did most of the work in here, I couldn’t help but get involved by curiosity

The script need to be added as part of an Extraction step. Add a field and set the Based on: field to JavaScript. Also set the Type: to JSON.

Here is the updated script to use:

let summary = {};
for (let i = 0; i < steps.lines; i++) {
let title = data.extract(“Product”, i);
let qty = data.extract(“Quantity”, i);

logger.info("Hello World!");

summary[title] = (parseInt(summary[title]) || 0) + parseInt(qty);

}

record.fields.myObject = JSON.stringify(summary);

[Joe]

Hi Jchamel,

More the merrier as they say

I’ve added a new Extraction with the script you given me. But when selecting TYPE I don’t have the option of selecting JSON only the following:
Boolean
String
HTML String
Integer
Float
Currency
Date

Not sure why this is.

No result just ‘{}’ as before.

Thanks
Joe

[Marten]

Can you please confirm if you have installed PReS Connect version 2018.2.1? The reason why I am asking is because I just opened the shared DataMapper configuration in the OL Connect Enterprise version 2024.1.1 Designer application and I see that a correct-looking value has been assigned to the field myObject. So I would recommend you to upgrade OL Connect Enterprise version 2024.1.1, if possible. The reason why I am recommending this is because version 2018.2.1 is a pretty old version and I don’t think that the example provided by Phil works properly in version 2018.2.1.

By the way, I noticed that the JavaScript code needs to be changed back to the following, as shown in this reply:

let summary = {};

for (let i = 0; i < steps.lines; i++) {
	let title = data.extract("Product", i);
	let qty = data.extract("Quantity", i) * 1;

	summary[title] = (summary[title] || 0) + qty;
}

record.fields.myObject = JSON.stringify(summary);

[Joe]

Hi Marten,

Yes, you’re correct, we are still on version 2018.2.1. I will have to talk to my boss about an upgrade to 2024.1.1.

Thanks again to all.
Joe

[Marten]

For your information: The reason why the shared JavaScript code doesn’t work in PReS Connect version 2018.2.1 is because the property steps.lines has been introduced in version 2021.2.1. See OL PReS Connect Release Notes 2021.2.1 for more information.

[Joe]

Hi Marten,

I’ve spoken to my CO and he’s going to look at upgrading the Software as we are well behind on versions.

Would you be able to send a contact for software upgrades?

And finally a big thanks to you all for your help with this.