I’ve been tasked in creating a tax workflow in DocStar ECM for one of our customers. The workflow is designed to calculate appropriate tax on each line item (some items are tax exempt, some have one tax, some two, etc).
That part works great - I’m able to calculate the taxes per item. Now I need to sum up all the taxes and compare it to the tax shown on the invoice, in order to check its accuracy. But, I can’t find an appropriate workflow task that will total up all the taxes for line item 1, item 2, item 3 etc. All the workflow tasks seem to work on the line-level
Referring to the image above - I want to total up all values in the “Extended GST” column, and put that value in a new field (total tax)
There is an “Aggregate” task that sounds like the one you are looking for. Within there is the option to “Sum” the values of the field you choose. This should likely be either the $Field.YOURFIELDNAME or $FieldGroup.Line.YOURFIELDNAME variable. With that summed up into a local variable, such as zExtCostGST, you can then use a “Compare Verify” or “Advanced Math” task to evaluate if the summed value from the lines matches the field value you brought in.
Thank you so much. I’d looked at the Aggregate task already but dismissed it as it didn’t have the Field Group checkbox like the Math task does. I didn’t realize you could specify field group fields without that.
My aggregate task works - sort of. I have two field group fields to add up, a value of $22.50 and a value of $2.00. The calculation works, and spits out $24.50 as expected. However, when I click on the field the output is put into, the actual value is 24.4995. This then breaks my workflow as the value doesn’t technically match.
I assumed this was due to a data type mismatch, but all my currency values are set to Decimal, with c2 format (currency, 2 decimal places). I triple checked that
The format you choose in the Admin > Fields area is simply how the value will display and not what the value actually is. Ie. $24.4995 is the actual value, but the Decimal type using c2 format for currency will display it as $24.50 in the field.
@swilliasc111 has the right idea in that you will need to format the value before comparing to another.
OK sounds good, I will do that - @swilliasc111’s idea sounds good.
Why does it happen in the first place?
And secondly - earlier in my workflow I had another aggregate task that adds up all the values captured from an invoice in the IDC to validate the subtotal, and I didn’t have an issue there.
It functions the same as an Excel field and how you can hide the decimals as much as you like. It will “round” the displayed value, but it keeps the actual value as the data within the field. Unfortunately, there is no inherent method of rounding values in ECM, but what @swilliasc111 has mentioned is a workaround. I’m sure there may be other ways of accomplishing the same thing, but this seems to work for us.
As for why it didn’t have an issue in some instances, it could very well be that the values did match in that case and rounding wasn’t an issue. This is the crux of developing and troubleshooting - when it works, it can be a false positive simply because you can’t always test for every scenario.
I gave that a shot - rounded the GST and PST values with advanced format, but now when I go to sum them up with Math, I get this error: “Input string was not in a correct format”
I get that it doesn’t seem to like the format of the input - does the Math task not play well with adding currencies? Should I use Advanced Math? (I did try this and got a bunch more errors so I reverted).
Sorry for all the questions hah, I have to get this working by the end of the day.
Edit: I initially tried to round the calculated “total” value, but it didn’t change. So I tried to round the values that are being added, and got the above problem.
The Advanced Format task will provide a string value while the Math task is looking to use a number format (decimal, integer, etc.). You can use Advanced Math to convert from string to decimal or simply do your comparisons in that task by using the ’ qualifier around your input variables in the expression builder [Eg. IIF(‘{0}’=‘{1}’,‘True’,‘False’)]
Wait - even when using a format like {0:C} to format it into a currency, it still outputs a string? That seems…weird.
I guess that might explain my new issue - I did get the rounding to work using Advanced format - it is now comparing [Total GST] @ 24.50 to $Field.GST @ 24.50, but is still outputting False - probably because one is a decimal and one is a string?
The Advanced Math task is formatting it into a string with the format style you’ve chosen. For the currency format, this would include the “$” character.
Do you have a screenshot of the output from the Advanced Format task? I ask as you may just need to remove the “$” character from the output using a Replace Text task and then do the Math task.
This is my advanced format. zTotalGST was obtained in an earlier Aggregate task (summing up the individual line item GSTs which this post was initialy about). I format it and output it back into the same variable
Next up is my advanced math, where I compare zTotalGST to $Field.GST:
Here i used that expression you gave in the last comment. I think I understand it - it’s basically an if statement, right? e.g. if {0} = {1}, then true, else false. The output zGSTCompareResult is a true/false that a branch condition later uses to route the workflow. It is ALWAYS returning False, even though the two values I’m comparing are now the same
I figured out the rounding issue, so the $24.50 is in fact $24.50, not $24.4995
Would it make the whole thing easier if I just handled currency amounts as regular decimal to begin with (so instead of $24.50 it’d just be 24.50, then format them for currency at the very end?
I believe all you may need is the Replace Text task just before the Advanced Math task. You can use the same variables and you’re looking to replace “$” with nothing.
If you could provide a screenshot of the recording, that would be extremely helpful, too.
To answer your follow-up question, the entire idea is to get the values to round the same way for comparison purposes so however you achieve that is what is needed. The suggestions @swilliasc111 and I have provided are just how we have worked through the issue.
Lol That may certainly be a portion of the issue, but it does also look like the “zTotalGST” value is “$24.50” and not “24.50” which is the original issue. The Replace Text task should be able to remove that “$” character so that it can properly compare with the GST field, once the others are removed. If you could, providing more of the recording for all of these tasks will likely give us more to evaluate as to what the issue(s) may be.
