So let's say that I have two Lists. An Inventory list which contains an assortment of items, some of which might have the same values:
And an Inventory Totals list, where I want a total of items with matching Kinds, Colors, and Sizes to be tallied:
Both Lists have the same Columns configured in the same way:
To properly calculate the totals of similar items in the Inventor List into the Inventory Totals List, I am using the Attached workflow.
The workflow works by getting all of the relevant columns (in this case [Kind of Dress], [Colour of Dress], [Size of Dress], and [QTY]) from the Inventory List, and then iterating through one of those collections to generate a unique "Key" for each item.
A "Key" is just a combination of the Columns that will determine what constitutes a "unique" filter. In this case it's the [Kind of Dress], [Colour of Dress], and [Size of Dress] columns. It will combine those column values into a single string of text (the key).
Using the first item in the Inventory List as an Example:
Its key would be "Shirt;Red;S"
After the Key is made, the workflow performs a quick evaluation. It checks to see if the NewKey exists in a collection called var_NewKey_Collection, and stores the resulting Index in var_ExistinKey_Index. When you check a collection for a value, if the value is NOT found then the Index will be -1. However, if the value is found, then the index will be Greater Than -1.
If the value was not found, then the QTY value in the var_QTY variable is pushed into a block of XML. The reason I pushed the QTY into a block of XML is because Nintex Workflow Collections have no option to "replace" a value at a given index, which makes it difficult when you'd like to update the value of something that's in a collection. So, instead of worrying about that, I can easily get / replace the value of an XML node with no extra step needed to remove the old bad value that I was trying to update.
The XML generated looks something like this:
<QTYs>
<QTY>1</QTY>
<QTY>2</QTY>
<QTY>3</QTY>
<QTY>4</QTY>
<QTY>5</QTY>
<QTY>6</QTY>
<QTY>7</QTY>
<QTY>8</QTY>
<QTY>9</QTY>
<QTY>10</QTY>
<QTY>11</QTY>
</QTYs>
However if the NewKey is indeed found inside of the NewKey_Collection, then that means we need to Add the current Item's QTY to the QTY that is already stored for a duplicate item in the XML. To do this I Query the XML for the QTY which matches the ExistingKey_Index value + 1. This provides me with the OldQTY value which I can then add to the QTY variable using a Math Action resulting in the NewQTY, that I then use to Update the XML back at the same node (ExistingKey_Index + 1).
(note: xml xpath uses a base 1 starting point for arrays unlike Workflow Collections which start at 0. So any Index position we get from a Collection will need to have 1 added to it in order to correctly match its position in the XML document that was generated to be paired with the Collection data)
That leaves me with a Collection of Unique Keys (NewKeys_Collection) and an XML document of all of the QTY totals. Using the example Inventory List above the Collection would now look like:
Shirt;Red;S;
Shirt;Blue;S;
Pants;Jeans;M;
Pants;Khaki;L;
Shirt;Black;XL
Shirt;Red;M;
Shirt;Red;XL;
Shirt;Blue;XS;
Socks;Black;L;
and the XML would look like:
<QTYs>
<QTY>1</QTY>
<QTY>2</QTY>
<QTY>13</QTY>
<QTY>4</QTY>
<QTY>5</QTY>
<QTY>6</QTY>
<QTY>18</QTY>
<QTY>8</QTY>
<QTY>9</QTY>
</QTYs>
You can see that both contain 9 items, and that the orders are correctly aligned.
At this point it's just a matter of looping through each value in the NewKeys_Collection, splitting the Key back into its individual parts using a Regular Expression Action, getting the QTY value from the XML, and pushing all of those values into a New Item Action to generate a new Item in the Inventory Totals List.
All of that work results with turning this:
Into This:
Again, please see the attached Workflow as it should be fairly straight forward with how it's working once you're able to really look at it. Let me know if you need any additional help or have any questions about why I decided to do one thing instead of a different thing.
