How to sum invoices on a salesorder and record the 'fully invoiced' date

[Guido1982]

Say you have the following use case:

You create salesorders to keep track of you sales, but your company has the habit of not invoicing the salesorder in one invoice. You want to record the date that a SalesOrder is fully invoiced, meaning: when did I create the invoice that totally invoiced this SalesOrder?

Well, here is what you would do.

First off, update your coreBOS! We discovered a small bug that we fixed here. Without this change, this will not work.

Next, create some fields on the salesorder module:

• One for the sum of all invoices
  
• One to record the date we're looking for in the first place
  

Of course create the fields with the right types.

Next, you need to create a text field on invoices that will hold the SalesOrder no. that the Invoice was created from or related to. We need that later to match the invoices to the salesorder. Call it anything you like.

Now we create the workflow on the Invoice module. I created the workflow to check on every save, but you can adjust that if your needs are different, but do understand that it may not work exactly as described here if you do.

First you create a task that gets the salesorder no. on the invoice field you created. Use an 'update field' task type and don't let it evaluate conditions on execution: we want this to happen always. Add a field to fill (the one you just created on invoices) and set it to 'use field'. use

Code:

$(salesorder_id : (SalesOrder) salesorder_no)

(or just select the Salesorder no. field from the dropdown, will do the same thing).

We now have a task that gets the salesorder no. and fills that into our custom field on the invoice, every time the invoice is saved. Now let's create the second task that will use that field to update the sum of all invoices on the salesorder based on that Salesorder no.

Create a second task in the workflow that you call 'sum all invoices on salesorder field' (or something like that in your language). Select the 'update field' tasktype again and don't evaluate conditions. Again, we need this to happen always. Select the 'sum invoices' field you created in the beginning as the field to update. Now use this code to sum all the invoice amounts into the field (Beware, adjust the custom field name here):

Code:

aggregation('sum','Invoice','sum_nettotal','[cf_1248,e,$(salesorder_id : (SalesOrder) salesorder_no),or]')

You need to replace 'cf_1248' for your own custom field no. That will be the field no. that you save the SalesOrder no. on in your Invoices. So again, this is the custom field on Invoices that you filled one task earlier.

What this does is: it uses the 'sum' method of the 'aggregation' function to get all the 'sum_nettotal' values of the 'Invoice' records. In the second to last parameter we say: 'cf_1248' (should be your custom field name) needs to be equal (the 'e') to the 'salesorder_no' field of the related SalesOrder. The 'or' is mandatory to glue more conditions, even if we don't have any. So this is how we match the invoices to the salesorder or: get only the invoices that belong to the salesorder we are updating.

We now have a mechanism in place that updates a field on salesorders with the totally invoiced amount. Beware, I use the sum_nettotal field here. If you need some other total, replace all instances so far (maybe you want the value with taxes, or with grand discount deducted).

Now we want to record the date that the salesorder is totally invoiced. So we want to prevent updating the date field when the 'sum' field on salesorders is lower that the salesorder total, and we want to prevent updating the date field after is has been recorded once. Say we edit an old invoice to change some date field or mark it as Paid, we don't want to affect the date field on SalesOrders when we do that (at least in this example, your needs may vary).

So we create a third task on the workflow we've been working on. This will again be an 'update field' task type. This time, we need to check the 'evaluate conditions' box and create some conditions. The first condition will prevent us from altering the date field once it has been filled once. Create the condition and select the date field we created on salesorders, choose the 'is' condition and leave the field right to it empty. Essentially we are saying "only do this if the date field is empty". Now create a second condition and leave the 'glue' to 'and' (the default). Select the 'sum' field we filled in the second task and select the condition 'greater than or equal to' (or just 'equal to' if you only want to do this when it equals but want to prevent filling the date when you over-invoice). Now in the right-most field you can select the field you want to match against. In my case this resulted in

Code:

$(salesorder_id : (SalesOrder) sum_nettotal)

but again, your needs may be different. Anyway, this matches against the sum_nettotal field of the salesorder. Since that's also the field we used on Invoices I would advise you to use the same fields on the entire worflow, otherwise you would be comparing pears to apples.

Now add a field to update and select 'SalesOrder' from the first dropdown. Because this is a reference field, a new dropdown will appear with all the fields from salesorders. There you can select the date field you created in the beginning. Now just select 'formula' when you click the rightmost field and enter

Code:

get_date('today')

in the box. This will get the current date for you.

So there you have it. Whenever invoices are created or edited, they will update the salesorder with the totally invoiced amount and record the date of the first 'totally invoice' occurance for you. Any questions or remarks: please respond below.