Total amount from xml file

OL Connect Professional Workflow
1

I have an xml file. I want to write the total of all lines in variable %1. How can I do ?

image
image771×296 15.1 KB

2

Out of curiosity: Can you let us know please why you would like to get the total number of lines of a XML file and parse this value to the Job Info variable 1 or do you just want to parse the total of 6, 12.56 and 75.36?

Tip: Please make sure to add three backtick characters on a separate new line the line before and after the XML data you want to share.

3

Marten
I get xml invoices from a supplier. Sometimes, invoice total is not good. I want calculate the total ($) to make sure the total in the invoice is good. Sometimes, a discount is missing in the xml file.

4

Dear @nabel1510,

Thank you for answering the asked question.

A solution is to use a Run Script Workflow plugin for this and to apply JavaScript code like the following to this Run Script Workflow plugin:

var quantity = Watch.ExpandString("xmlget('/test[1]/Quantity[1]', Value, KeepCase, NoTrim)");
var perQuantity = Watch.ExpandString("xmlget('/test[1]/PerQuantity[1]', Value, KeepCase, NoTrim)");
var extendedAmount = Watch.ExpandString("xmlget('/test[1]/ExtendedAmount[1]', Value, KeepCase, NoTrim)");
var total = 0;

/* Check if the given variable is not a empty string and if it is a number
 * before adding it to the value of the variable 'total' */

if (quantity != "" && !isNaN(quantity)) {
	total += parseFloat(quantity);
}

if (perQuantity != "" && !isNaN(perQuantity)) {
	total += parseFloat(perQuantity);
}

if (extendedAmount != "" && !isNaN(extendedAmount)) {
	total += parseFloat(extendedAmount);
}

Watch.Log("Total: " + total, 3);

Important: In this JavaScript code example I assume that the values of the element nodes Quantity, PerQuantity and ExtendedAmount do not contain a comma.

In this JavaScript code example I am simply logging the result of the variable total.

Execute the following steps to apply the correct Data Locations to the first three lines of JavaScript code:

  1. Select a sample data file by going to OL Connect Workflow Configuration application > Debug > Select > Data Selector window > Open button
  2. Insert a Run Script Workflow plugin into a Workflow process
  3. Apply JavaScript code like var quantity = Watch.ExpandString(“”); to the inserted Run Script Workflow plugin
  4. Click with the right mouse button between the two quotation marks of the applied JavaScript code and select the option Get Data Location…
  5. Select the appropriate element node in the Data Selector window and click on the OK button
5

Please note that, as in regards the given solution, you still have to think about the question about what you have to do when one or all of the element nodes Quantity, PerQuantity and ExtendedAmount aren’t present. Because in that case the value of the variable total will remain 0.

6

wow thks Marten, I will try this

7

I get error: expected end of statement for line 1. why?

8

The reason why the following error occurs is because you’re executing JavaScript code but the language has not been set to JScript yet.

W3602 : Error 0 on line 1, column 93: Microsoft VBScript compilation error: Expected end of statement

Please click with the right mouse button on the Run Script Workflow plugin and select Properties… to open the Script Editor window and go to Script Editor window > Language and select JScript to change the language from VBScript to JScript.

9

If I want this total in variable %1, how can I do ?

10

This can be done, for example, by replacing the following line of JavaScript code:

Watch.Log("Total: " + total, 3);

With the following line of JavaScript code:

Watch.SetJobInfo(9, total);

Tip: See the web page ‘Watch.SetJobInfo - OL Connect Workflow User Guide (link)’ for more information about the Watch.SetJobInfo method.

11

This would be easier to process in the DataMapper since you could then have totals for each individual invoice (if you have multiple invoices per data file). For instance, the following extraction step sums up all ExtendedAmount elements and extracts the result a field named CalculatedTotal:

image

In Workflow, you can achieve the same thing with the following script (the total is stored in JobInfo 1).

var xmlDoc = new ActiveXObject("Msxml2.DOMDocument.3.0");
xmlDoc.async = false;
xmlDoc.load(Watch.GetJobFileName());
if(!xmlDoc.parseError.errorCode) {
  xmlDoc.setProperty("SelectionLanguage", "XPath");

  var items = xmlDoc.selectNodes("//ExtendedAmount");
  var total = 0
  for(var i=0;i<items.length;i++){
    total += items.item(i).text*1
  }
  Watch.SetJobInfo(1,total);

} else {
  Watch.log("Error parsing XML",1);
}

This is slightly less efficient because MSXML does not support XPATH functions like sum(), whereas the DataMapper does:

12

Hi Phil
I have always used planet press 7 designer. I have tried with connect designer but it does not work. Do I have to create a data model and a template or just datamapper ?

13

@nabel1510,

Well it depends what you want to achieve. If you want to generate new documents, then you’ll first need to create a new data mapping config, which will generate a data model. You can then use that data model to map your variable data onto an OL Connect template.

If you only need the data, then you can just create a new data mapping configuration and retrieve the results of the data mapping operation as an XML or a JSON file.

If you just need the total of all detail lines as you asked in your original post, then you can use the Workflow script I provided above.

14

ok thks Phil I will try