How to get the CRM ID of a related record through expression

[Guido1982]

I have a workflow task that I want to use to update a field. I want to sum a field in a SalesOrder with all the Invoice nettotals summed up. Therefor I want to get all invoices that are related to the same salesorder that I am launching the workflow task on. So I'm using

Code:

$(salesorder_id : (SalesOrder) subject)

to do that. Problem is, there are many salesorders that have that exact subject. So the workflow engine gives me all of those and sums up all the invoices that have that text in the 'Related SalesOrder' field. Which is not what I want, I want only the Invoices that have that exact salesorder CRM ID. So basically I'd like to do something like

Code:

$(salesorder_id : (SalesOrder) salesorderid)

, but that's not working for me.

---

I worked around this now by adding a task before the update task that fills a custom field in the invoice with the salesorder_no. The second task (the one that sums) then checks against that salesorder_no on other invoices. This works, but I don't like it since it needs an extra field, task and only works correctly when every invoice has been saved once to update the related salesorder_no (to do the filtering).

[joebordes]

the aggregation functions only work on the related records, so you do not need to add any conditions

Code:

aggregation('sum','Invoice','sum_nettotal')

that should do what you want.

Adding condtions would be for the case where you only wanted to sum the "Paid" invoices, for example

Give that a try

[Guido1982]

That doesn't work sadly. It gives me a total of 8 million euro's, which is probably the sum of many, many invoices that are not at all related to this salesorder

[joebordes]

Unit test don't seem to agree with you:

https://github.com/tsolucio/coreBOSTests/blob/master/modules/com_vtiger_workflow/expression_functions/aggregationTest.php#L31:L37
https://github.com/tsolucio/coreBOSTests/blob/master/modules/com_vtiger_workflow/expression_functions/aggregationTest.php#L51:L61

Two things come to mind:
- the code actually works differently when executed in the unit test (I truly hope not)
- the omnipresent number formatting is getting in the way

[joebordes]

I just did a quick workflow on SO with the expression above and it worked as expected. Are you using that same expression?

Can you try with a user that has standard US decimal formatting?

[joebordes]

I just noticed that the user I tested with has EU formatting!!!

[Guido1982]

Sorry, didn't notice the updates. Will try and let you know

[Guido1982]

Turns out we were not understanding each other correctly here. When you want to create a workflow on Invoices that sums up all the Invoice amounts from all Invoices related to the SalesOrder this Invoice is related to (confusing, yes I know) you can't just use

Code:

aggregation('sum','Invoice','sum_nettotal')

. This will get ALL invoices in the entire system for you. What you need to do is get some unique reference to the salesorder. I would not advise you to just use the reference field, since that will check against all SalesOrders with that subject line meaning: every SalesOrder that has the same subject (this is a free text field so VERY dangerous in this context) will qualify.

If you need to know how to to this: use a two step process to get the salesorder no. on to the invoice record like described here (you don't need the third workflow task there). This will match the invoices to the salesorder based on the salesorder no, which is reliable.