We have a dashboard which contains a BAQ based on the JobHead table. In that table, we have a custom field for estimated hours, which is typically updated manually, one job at a time. What I would like is to build in a button in the dashboard such that when it’s clicked, it takes the LaborHours data for each Job in the LaborDtl table and copies it to the custom field in the JobHead. I’m still very green on BPMs, so would a BPM be the best way to handle this? Method directive vs. data directive? And should the BAQs themselves be updatable? Any advice is appreciated.
And I only realized after submitting the post that the LaborDtl can have many rows for a single Job Number, in which case I’d need to aggregate the LaborHrs by JobNum and write that data to the JobHead…
This is pretty trivial with some custom code, but can be tricky if you try to use the base stuff (widgets). The JobEntry BO is real dog and you don’t really need it for what you are trying to do, but that means using custom code.
When do you want this field updated? Only when someone clicks the button? Or would you rather the system do the update whenever labor is added? I’m assuming the latter, and to do that, I would put a method directive on the labor.update method. What you would be doing is every time a record is added go and get the job head record, then add the new labor hours to the job header. Also, you would want to handle any changes as well, so that gets trickier.
If you want to do it with the BAQ, you can make the BAQ gather all of the data up, then make a custom action to update the job head record. A custom action is just a BPM within a BAQ. That’s probably going to be simpler to start with, so maybe start with that approach first.
Read up on Updateable BAQ’s and custom actions, then get as far as you can and come back with questions.
@Matthew_Morgan LaborDtl records are actual time. What are you building an estimate of?
The routine below I got the pieces from this site, so I will pass it on. If you add a calculated field called Select and make the baq updatable and select advanced bpm update only. Then put something like this in base processing of update. Check the checkbox on the jobs you want calculated and save to run the update.
If you want it to be fancy they you can make it a custom action rather than on save.
If you want to do all jobs in the query then you can make the baq updateable and in post processing of GetList sum up the hours. Same as below without the where clause
foreach (var ttr in ttResults.Where(r=> r.Calculated_Select == true ))
{
double estHours = Db.LaborDtl.Where(ld=> ld.Company == CompanyID && ttr.JobHead_JobNum == ld.JobNum).Sum(x=> (double?) x.EarnedHrs) ?? 0; // has to be double in case a null is returned from sql
if(estHours != 0)
{
var jh = Db.JobHead.Where(h=> h.Company == Session.CompanyID && h.JobNum == ttr.JobHead_JobNum).FirstOrDefault() ;
if (jh != null)
{
jh.Number01 = (decimal)estHours ;
}
}
ttr.Calculated_Select = false ;
ttr.RowMod = "U" ;
}