Publié Mon, 06 Mar 2023 21:33:34 GMT par Jeff Slack ECM Specialist
We have a use case where a customer wants to store an invoice, and associated purchase orders in a keyword field type.<br> <br> 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.<br> <br> 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.&#160;<br> <br> Any ideas would be appreciated.
Publié Thu, 09 Mar 2023 12:49:10 GMT par Gerardo Lisanti Team Leader Product Management
Hi Jeff,<br> <br> so probably you can approach it like this: <ol> <li>Set a decimal global variabel &quot;<code>TotalPOAmount</code>&quot; to &quot;<code>0.00</code>&quot;</li> <li>For the loop set a numeric global variable &quot;<code>CurrentPO</code>&quot; initially to &quot;<code>0</code>&quot;</li> <li>Find out how many POs are there to determine the end of the loop. Using a&#160;workflow arithmetic expression you can get the count of the PO's from the keyword field,&#160;e.g.&#160;<code>Count(DW_POKeyword)</code>. Assign it to a numeric global variable called &quot;<code>TotalPOs</code>&quot; so you can check later if the end is reached.</li> <li>Start the loop and get the PO number of the current run.&#160;Using a&#160;workflow arithmetic expression&#160;get the PO&#160;number from the keyword field. Use the global variable &quot;<code>CurrentPO</code>&quot; to return the appropriate value&#160;of this&#160;run, e.g. <code>DW_POKeyword(GV_CurrentPO)</code>. Assign it to a text global variable called &quot;<code>CurrentPONumber</code>&quot;.</li> <li>Get the amount of the appropriate PO by querying the document using the global variable &quot;<code>CurrentPONumber</code>&quot;. Assign the amount to a decimal global variable &quot;<code>CurrentPOAmount</code>&quot;.</li> <li>Add the amount to the global variable &quot;<code>TotalPOAmount</code>&quot; using a&#160;workflow arithmetic expression, e.g. <code>GV_TotalPOAmount + GV_CurrentPOAmount</code></li> <li>Add a condition to check if you need to repeat the steps 3-5, by comparing the number of &quot;<code>TotalPOs</code>&quot; with the number of&#160;&quot;<code>CurrentPO</code>&quot;,&#160;using a&#160;workflow arithmetic expression,&#160;e.g. <code>GV_TotalPOs = GV_CurrentPO</code></li> <li>if the condition is <strong>not </strong>fulfilled add <code>1</code> to the global variable &quot;<code>CurrentPO</code>&quot;, using a&#160;workflow arithmetic expression, e.g. <code>GV_CurrentPO + 1</code> and go back to the step 3.</li> <li>if the condition is fulfilled you are done and you can move on, for example by comparing&#160;the &quot;<code>TotalPOAmount</code>&quot; with the total amount of the current invoice,&#160;using a workflow arithmetic expression, e.g. <code>GV_TotalPOAmount =&#160;DW_TotalAmount</code></li> </ol> <br> Needed global variables: <ul> <li>TotalPOAmount (decimal)</li> <li>CurrentPO (numeric)</li> <li>TotalPOs (numeric)</li> <li>CurrentPONumber (text)</li> <li>CurrentPOAmount (decimal)</li> </ul> <br> Let me know if this helps!<br> <br> Viele Grüße / With best regards,<br> <br> --<br> <br> Gerardo Lisanti<br> Product Manager &#160;| &#160;DocuWare GmbH
Publié Thu, 09 Mar 2023 20:28:01 GMT par Randy Nelson Solutions Architect
Thank you, Gerardo, for this explanation.&#160; It has been very helpful but I have one question.<br> <br> In step 5, you say to &quot;Get the amount of the appropriate PO by querying the document&quot;.&#160; Would you please explain the syntax needed to query the CurrentPONumber to copy the CurrentPOAmount.&#160;&#160;
Publié Fri, 10 Mar 2023 08:56:31 GMT par Gerardo Lisanti Team Leader Product Management
Hi Randy,<br> <br> You are welcome. I have attached a screenshot showing how this could be done.<br> The WHERE is simple SQL, you can query the document in the file cabinet by searching for the &quot;<code>document number</code>&quot; with the global variable &quot;<code>CurrentPONumber</code>&quot;, e.g.&#160;<code>DW_DOCUMENT_NUMBER = 'GV_CurrentPONumber'</code>&#160;&#160;and take over the &quot;<code>(Net) Amount</code>&quot; to &quot;<code>CurrentPOAmount</code>&quot;<br> <br> Viele Grüße / With best regards,<br> <br> --<br> <br> Gerardo Lisanti<br> Product Manager &#160;| &#160;DocuWare GmbH
Publié Fri, 10 Mar 2023 19:10:36 GMT par Randy Nelson Solutions Architect
Gerardo,<br> <br> That worked perfectly.&#160; Thank you!<br> <br> Randy

You must be signed in to post in this forum.