Code to capture data in a data directive

Epicor ERP 10
1

I have a Data Directive that sums some fields and saves in UD fields. With lots of help I was able to work through a basic ‘select sum(x) group by A’ type scenario.

SQL Statement
SELECT Company, ProjectID, LaborType, SUM((LaborRate + BurdenRate) * LaborHrs) AS LaborSum
FROM Erp.LaborDtl
GROUP BY Company, ProjectID, LaborType
HAVING (ProjectID = N’xxx’) AND (Company = N’xxx’)

Directive SET FIELD Statement
(from laborDtlRows in Db.LaborDtl
where laborDtlRows.Company == callContextClient.CurrentCompany && laborDtlRows.ProjectID == ttProjectCstRow.ProjectID && laborDtlRows.LaborType == “P”
select laborDtlRows.LaborHrs*(laborDtlRows.LaborRate + laborDtlRows.BurdenRate)).DefaultIfEmpty(0).Sum()

Now I have a different scenario with table joins and I can’t seem to get it right! How do I get the below into proper format as above?

SELECT DISTINCT Erp.JobHead.Company, Erp.JobHead.ProjectID, Erp.Part.TypeCode, SUM(Erp.JobMtl.TotalCost) AS Total
FROM Erp.Part INNER JOIN Erp.JobMtl ON Erp.Part.Company = Erp.JobMtl.Company AND Erp.Part.PartNum = Erp.JobMtl.PartNum INNER JOIN Erp.JobHead ON Erp.JobMtl.Company = Erp.JobHead.Company AND Erp.JobMtl.JobNum = Erp.JobHead.JobNum
GROUP BY Erp.JobHead.ProjectID, Erp.Part.TypeCode, Erp.JobHead.Company
HAVING (Erp.JobHead.ProjectID = N’xxx’) AND (Erp.JobHead.Company = N’xxx’)

2

Not the finished article I’m afraid as I’m busy on something, but it is a working example from a data directive of joining tables in Linq. Took me a while to get it working! My examples also includes SUM, so should be all that you need.

var jobsList = from jh in Db.JobHead 
               join p in Db.Part
               on new { jh.Company, jh.PartNum }  
               equals new { p.Company, p.PartNum }
               where jh.ReqDueDate == dProdDate && jh.JobFirm && p.MtlAnalysisCode == strDeptCode
               group jh by new {jh.PartNum, p.ClassID, p.AnalysisCode, jh.PalleconRoom_c} into partsMade
               select new {PartNum = partsMade.Key.PartNum, 
                           PlanQty = partsMade.Sum(x => x.ProdQty),
                           ActQty = partsMade.Sum(x => x.QtyCompleted),
                           ClassID = partsMade.Key.ClassID,
                           AnalysisCode = partsMade.Key.AnalysisCode,
                           PalleconRoom = partsMade.Key.PalleconRoom_c};
3

Since I brought you down this rabbit hole…this statement worked for me:

(from jobMtlRow in Db.JobMtl
join partRow in Db.Part
on
new {Company = jobMtlRow.Company, PartNum = jobMtlRow.PartNum}
equals
new {Company = partRow.Company, PartNum = partRow.PartNum}
join jobHeadRow in Db.JobHead
on
new {Company = jobMtlRow.Company, JobNum = jobMtlRow.JobNum}
equals 
new {Company = jobHeadRow.Company, JobNum = jobHeadRow.JobNum}
where jobHeadRow.ProjectID == ttProjectCstRow.ProjectID && partRow.TypeCode == "P"
select new {jobMtlRow.TotalCost}
).AsEnumerable().Sum(jobMtlRow=>jobMtlRow.TotalCost)