Calculating Purchase Order Totals in a Workflow

We have a use case where a customer wants to store an invoice, and associated purchase orders in a keyword field type.

We are struggling to find a way, via workflow, to look at the Purchase Order's associated with an invoice, whether that's one or ten, and tally up the Purchase Order Amounts to compare to the Invoice Amount.

I imagine there's a way to query the Purchase Order Keyword field, step through the list of PO #'s and add the PO amount associated with each to a global variable and loop until it gets to the end of the keyword list. 

Any ideas would be appreciated.

Hi Jeff,

so probably you can approach it like this:

1. Set a decimal global variabel "TotalPOAmount" to "0.00"
2. For the loop set a numeric global variable "CurrentPO" initially to "0"
3. Find out how many POs are there to determine the end of the loop. Using a workflow arithmetic expression you can get the count of the PO's from the keyword field, e.g. Count(DW_POKeyword). Assign it to a numeric global variable called "TotalPOs" so you can check later if the end is reached.
4. Start the loop and get the PO number of the current run. Using a workflow arithmetic expression get the PO number from the keyword field. Use the global variable "CurrentPO" to return the appropriate value of this run, e.g. DW_POKeyword(GV_CurrentPO). Assign it to a text global variable called "CurrentPONumber".
5. Get the amount of the appropriate PO by querying the document using the global variable "CurrentPONumber". Assign the amount to a decimal global variable "CurrentPOAmount".
6. Add the amount to the global variable "TotalPOAmount" using a workflow arithmetic expression, e.g. GV_TotalPOAmount + GV_CurrentPOAmount
7. Add a condition to check if you need to repeat the steps 3-5, by comparing the number of "TotalPOs" with the number of "CurrentPO", using a workflow arithmetic expression, e.g. GV_TotalPOs = GV_CurrentPO
8. if the condition is not fulfilled add 1 to the global variable "CurrentPO", using a workflow arithmetic expression, e.g. GV_CurrentPO + 1 and go back to the step 3.
9. if the condition is fulfilled you are done and you can move on, for example by comparing the "TotalPOAmount" with the total amount of the current invoice, using a workflow arithmetic expression, e.g. GV_TotalPOAmount = DW_TotalAmount

Needed global variables:

• TotalPOAmount (decimal)
• CurrentPO (numeric)
• TotalPOs (numeric)
• CurrentPONumber (text)
• CurrentPOAmount (decimal)

Let me know if this helps!

Viele Grüße / With best regards,

--

Gerardo Lisanti
Product Manager  |  DocuWare GmbH

Thank you, Gerardo, for this explanation.  It has been very helpful but I have one question.

In step 5, you say to "Get the amount of the appropriate PO by querying the document".  Would you please explain the syntax needed to query the CurrentPONumber to copy the CurrentPOAmount.  

Hi Randy,

You are welcome. I have attached a screenshot showing how this could be done.
The WHERE is simple SQL, you can query the document in the file cabinet by searching for the "document number" with the global variable "CurrentPONumber", e.g. DW_DOCUMENT_NUMBER = 'GV_CurrentPONumber'  and take over the "(Net) Amount" to "CurrentPOAmount"

Viele Grüße / With best regards,

--

Gerardo Lisanti
Product Manager  |  DocuWare GmbH

Gerardo,

That worked perfectly.  Thank you!

Randy