Hi Group,
I am writing a Epicor function where I am passing a EmpID, StartDate and EndDate as a parameter. I need to return the SUM value of the LaborHrs column from the LaborDtl table by ranging on the EmpID and Date range. Please see the attached picture. Can someone help me writing the C# code or if any other way of achieving this.
Thanks
Why wouldn’t you just use a dashboard?
Are you using the function for external purposes or customisation?
If it’s just to get the value of the employees weekly hours. I would suggest a dashboard with paramaters to the BAQ.
I need to popup a message with the return value of labor hours on the time entry screen. I don’t know if dashboard/baq can return the value.
You could still use a BAQ like @aarong suggested and call it in the Function. There are some examples of that on the site.
I’m laughing inside because I believe the answer - if you were to do this as was the original intent - is to use lambda notation.
And DO NOT ask me for advice. 
something like this will have sql do all of the work and send back just the sum. It is a double since sql sends back a double zero if no records are found, but you could change that and do a null check.
there is a long discussion of summing in the thread that @JasonMcD posted.
double laborHrs = Db.LaborDtl.Where(ld=> ld.Company == CompanyID && inputEmpNum == ld.EmployeeNum && ld.LaborDate >= inputDateFrom && inputDateThru <= ld.LaborDate ).Sum(x=> (double?) x.LaborHrs) ?? 0;
I can write the SQL but again writing s SQL within C# would be a challenge for me. Currently I am investigating how to achieve this through BAQ. So far didn’t found a direct example.
That is the linq code you would put in the expression you show in your screen shot. I don’t think you need the assignment
That is true - the widget you have (Set Argument…) is a one-liner.
For a block of C#, you’d need the Custom Code Block widget.
(I think that’s the name.)
Also, You have to set up the Epicor Function Library to allow custom code widgets, and then you have to create that specific kind of Function.
Now, whether that syntax works or not, that is not my forte. Someone else will have to weigh in on that.
Well, OK, I’ll weigh in a little.
So, if you want the compiler to put the values of WeekStartDate (etc.) into the string, then you have to concatenate that together. As is, it’s going to read that as the string literal of “WeekStartDate”.
@JasonMcD That is how you would execute a sql command in a standalone program which is not how Epicor needs it.
I made a function similar to yours and put this in a code block.
EDIT: The code I posted first worked if there were hours, but failed if there were no hours o r the dates were inverted. The new code works for those conditions.
/* sum hours */
Ice.Diagnostics.Log.WriteEntry($"Employee ID {EmpID} From {dateFrom} thru {dateThru}");
double? empSumHours = 0;
empSumHours = Db.LaborDtl.Where(ld => ld.Company == CompanyID && EmpID == ld.EmployeeNum && ld.ClockInDate >= dateFrom && ld.ClockInDate <= dateThru ).Sum(x=> (double?) x.LaborHrs) ?? 0;
Ice.Diagnostics.Log.WriteEntry($"Employee ID {EmpID} From {dateFrom} thru {dateThru} Hours are {empSumHours}");
empHours = (decimal?) empSumHours;
As Jason mentions you need to check custom code widgets and yo will need read db access.
IMO you should always pick Add widget with Code, so you can use either as needed.
As @JasonMcD pointed out you need to use a custom code block. Expressions can only be a one statement.
You also took out the null checking ?s which will be needed when sql returns nothing.
