I want to convert this SQL Query into LINQ so I can populate a field on InvoiceDtl with Total Freight - Fieldname: TotalFreight_c from a BPM on ARInvoice.Update/Post-Processing
SQL:
SELECT InvoiceNum, SUM(InvoiceLine), SUM(DocTotalMiscChrg) AS [Total Freight] FROM Erp.InvcDtl
WHERE InvoiceNum = 134786
GROUP BY InvoiceNum
The easier and more readable way to do that is to query for each row inside your foreach. As long as you query per invoice and return only the field you want, you can sum the entire query.
If this is a big loop then you could sum outside it, within your select clause, and return all the values at once, something like
group invcDtl by InvoiceNum into allInvcDtl
select new {InvoiceNum = allInvcDtl.Key, miscTotal = allInvcDtl.Sum(c => c.DocTotalMiscCharge}
I usually end up playing around with LINQ in LINQpad before committing anything to a BPM. I find it’s very difficult to be fluent in both SQL and LINQ.
If you put it within the foreach, you’ll need a different variable for the row, so the two don’t clash, and you’ll need to sum the charge field not the invoice number.
It looks like you’re only doing this for a single invoice each time? In which case you only have a single value to write to all the lines? If that is the case then it won’t matter anyway and you can do it outside the foreach.
And you can choose one or the other of the two ways to set the field, you don’t need both.
Unfortunately, it’s not updating the InvcDtl.TotalFreight_c
I put this back into SQL with a dummy invoice number which has £100 on Line 1 - £50 on Line 2, - £20 on Line 3 and SQL pulls back £170 but the TotalFreight_c is blank
SELECT Erp.InvcDtl.InvoiceNum, Erp.InvcDtl_UD.TotalFreight_c, SUM(DocTotalMiscChrg)
FROM Erp.InvcDtl INNER JOIN
Erp.InvcDtl_UD ON Erp.InvcDtl.SysRowID = Erp.InvcDtl_UD.ForeignSysRowID
WHERE (Erp.InvcDtl.InvoiceNum = 134824)
GROUP BY InvoiceNum, TotalFreight_c
Over to @aarong on that. It’s just a slightly altered version of his code, and I assumed there was more of it and InvoiceNum was already a set variable.
I would use UpdateMaster and then you will have access to a built in pre-populated variable ipInvoiceNum, ipInvoiceLine, cTableName, cGroupID
The reason yours does not work probably is that you must be updating InvcDtl for the ttInvcDtlRow to have something populated, if you update a Misc Charge the BPM would only have ttInvcChrg or so.