How to update UD fields with Data Directive

[SusanM (Susan M Mocine)]

I am trying to update some _UD fields with summed data by using a Data Directive. I believe it should be In-Transaction. Do I HAVE to use custom code (which I have no idea where to start)? Or is there some way to run SQL code which i am waaay more proficient and knowledgeable with?
Or is there some other option I am missing?

I have a BAQ which pulls the summed data - is there anyway to use that???

[Asz0ka (Adam Szoka)]

In-trans is the way to go. You can use the “Set field” widget.

[Asz0ka (Adam Szoka)]

I think you would need some custom code to pull in the BAQ’s data. There are several posts on here that outline how you could do it.

[SusanM (Susan M Mocine)]

I see where I can set the summed data in the ‘Set Field’, but my _UD fields are not available to select to update.

[Asz0ka (Adam Szoka)]

What version are you on? You may need to select the _UD table, or check the “show custom fields” checkbox. Ours show at the very bottom.

[SusanM (Susan M Mocine)]

I had closed / reopened Epicor after creating fields - when i restarted my computer the fields showed up.

However, the Set Field won’t work because I need to sum data from the LaborDtl - grouping by LaborType and JobMatl.TotalCost grouping by Part.TypeCode. SO. Custom code - ugh!!! wish me luck. i had poked around in here to find a scenerio such as I needed and didn’t find any. I’ll look again.
Thanks!

[Asz0ka (Adam Szoka)]

What’s the summed data equation? If it’s not too complex, a LINQ statement could be another simpler option.

[SusanM (Susan M Mocine)]

Basic sum / group by queries

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

SELECT 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’)

[Asz0ka (Adam Szoka)]

Also what table are you trying to update?

[SusanM (Susan M Mocine)]

ProjectCst UD fields: (cased on returned Codes)
Labor_Indirect
Labor_Production
Labor_Setup
JobMatl_Raw
JobMatl_Purch

[Asz0ka (Adam Szoka)]

LINQ may be another option then. It’s pretty similar to SQL, and can be used in a Set Argument / Variable widget. You could set up 5 LINQ statements for each UD field to use in a set-field widget. Here’s a statement I previously used for labor quantity sums. I changed it to calculate indirect labor costs:

(from laborDtlRows in Db.LaborDtl 
where laborDtlRows.Company == callContextClient.CurrentCompany && laborDtlRows.ProjectID == ttProjCostRow.ProjectID && laborDtlRows.LaborType == "I"
select laborDtlRows.LaborHrs*(laborDtlRows.LaborRate + laborDtlRows.BurdenRate)).DefaultIfEmpty(0).Sum()

I haven’t tested it, we don’t really use the projects functionality here. But you could give it a shot, the syntax is OK. For the production and setup costs, you could do the same thing but change the laborType from I to S/P. A similar statement could be used for the material costs.

[SusanM (Susan M Mocine)]

How did you declare your variable? I just took what you had and pasted into the Editor and it doesn’t work. But, I don’t have the ‘IndirectLabor’ variable that you do. I missed a step somewhere.

[SusanM (Susan M Mocine)]

Thanks for your help! I may be using the wrong directive. I didn’t get an error with your code, and it seems to be on the right path!

[ckrusen (Calvin Krusen)]

UD Field not showing up in Data Directive field listing

I had it show up, [image] but I couldn’t reference it by using ttOrderHedRow.BidMargin_c I had to use the following code in expressions. ttOrderHedRow.UDField<System.Decimal>("BidMargin_c") EDIT: I’m on version 10.1.400.23

And this…

Updating User Defined Fields through BPM Method Directive

What is the code you are using to set the UD field on the tt table? From the ICE guide … Method directives work with temporary (tt) tables using tablesets, and so you access them through different syntax. These tablesets are defined in the business object’s (BO) contract .dll file, so their format is fixed and they are not regenerated with the data model. When building a method directive that refers to user-defined fields in temporary tables, reference them using the [“UDField<>()”] syntax. …

Note that the ICE manual shows the different ways for DD’s and MD’s, but has the swapped.

[Asz0ka (Adam Szoka)]

Sorry for the confusion, I didn’t actually use that IndirectLaborCost variable in the screen shot. I just directly set a field (didn’t have your UD fields) to the LINQ result in that “set field” widget. You could also use the LINQ statement to set a variable (that you create).

This is in an in-trans data directive on the ProjCost table. I didn’t see the “ProjectCst” table until I looked a second time. Here’s how the LINQ statement would change for that table:

(from laborDtlRows in Db.LaborDtl 
where laborDtlRows.Company == callContextClient.CurrentCompany && laborDtlRows.ProjectID == ttProjectCstRow.ProjectID && laborDtlRows.LaborType == "I"
select laborDtlRows.LaborHrs*(laborDtlRows.LaborRate + laborDtlRows.BurdenRate)).DefaultIfEmpty(0).Sum()