Hello!
We’re on version 11.1.200.0 or 2021.2.20.
For some reason the Act Scrap Qty doesn’t display the sum of labor detail scrap like its supposed to in our Job Tracker. It just shows 0 for each operation.
I created a real basic post-processing method directive on Erp.BO.JobEntry.GetDatasetForTreeView so that the grid will display the actual value.
foreach(var jo in result.JobOper)
{
var query = from ld in Db.LaborDtl
where ld.Company == jo.Company
&& ld.JobNum == jo.JobNum
&& ld.AssemblySeq == jo.AssemblySeq
&& ld.OprSeq == jo.OprSeq
select ld.ScrapQty;
jo.ActScrapQty = query.Sum();
}
It seems to work just fine, but is this a bad idea? What risks should I be aware of? This wouldn’t update the database would it? And how can I tell where else the system calls this method besides Job Tracker?
The only thing that might bite you is that you’re querying the database once for every row in the grid - if that grid gets very big, it might bog down a bit.
If you can, write a single query that pulls all the data you will need, then pull in your data from that result set inside your foreach.
If that is not possible or practical, then it’s not the end of the world - your repeated query looks small.
This. Someone checks the return all records and the overhead cost of a thousand separate queries might be nasty. I’d try to refactor your code to run the query once, then join it to the canned dataset, then loop through the canned dataset and update values.
I actually started out that way but I’m not very familiar with LINQ when it comes to using “group by”. It just seems very different than what I’m used to with SQL. My first attempt turned out to be pretty nasty, so I went with the cleaner approach. But I’ll try this approach again and will post what I come up with. Maybe you can take a look.
Try something like this (note I threw it together so it may not be 100% accurate I didn’t test it
// Get the JobNum from the first result row
var jobNum = result.JobOper.FirstOrDefault()?.JobNum;
if (jobNum != null)
{
// Query and sum all labor operations for the job (that have scrap) for the current Job and Company
var query = from ld in Db.LaborDtl
where ld.JobNum == jobNum && ld.Company == this.Session.Company && ld.ScrapQty > 0
group ld by new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into g
select new
{
g.Key.Company,
g.Key.JobNum,
g.Key.AssemblySeq,
g.Key.OprSeq,
ActScrapQty = g.Sum(ld => ld.ScrapQty)
};
var jobOperScrapQuantities = query.ToList(); // This is important it queries all at once and closes the Db Context nor leaving an open cursor.
// Loop through the operations (now in memory) and update scrap as needed.
foreach (var jo in result.JobOper)
{
var scrapQuantity = jobOperScrapQuantities
.FirstOrDefault(q => q.Company == jo.Company
&& q.JobNum == jo.JobNum
&& q.AssemblySeq == jo.AssemblySeq
&& q.OprSeq == jo.OprSeq);
jo.ActScrapQty = scrapQuantity?.ActScrapQty ?? 0;
}
}
I could not figure out the group by so I tried different approach. For your var query, is it is it different at all from my var inMem below?
var inMem = from jo in result.JobOper
join ld in Db.LaborDtl
on new { jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq }
equals new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into gj
from grp in gj.DefaultIfEmpty()
select new
{
jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq,
grp.ScrapQty
};
foreach(var jo in result.JobOper)
{
var query = from ld in inMem
where ld.Company == jo.Company
&& ld.JobNum == jo.JobNum
&& ld.AssemblySeq == jo.AssemblySeq
&& ld.OprSeq == jo.OprSeq
select ld.ScrapQty;
/*
var query = from ld in Db.LaborDtl
where ld.Company == jo.Company
&& ld.JobNum == jo.JobNum
&& ld.AssemblySeq == jo.AssemblySeq
&& ld.OprSeq == jo.OprSeq
select ld.ScrapQty;
*/
jo.ActScrapQty = query.Sum();
}
I will note I haven’t tested this but it just occured to me that you altering the data set in transit may result in a dirty row in the screen. You may get some pushback from epicor about changes to a record by another user and or a dirty row (in a job tracker)
Do some testing.
IE Open a Job and load it and leave etc to make sure you don’t get a dirty row message.
var inMem = from jo in result.JobOper
join ld in Db.LaborDtl
on new { jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq }
equals new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into gj
from grp in gj.DefaultIfEmpty()
select new
{
jo.Company, jo.JobNum, jo.AssemblySeq, jo.OprSeq,
grp.ScrapQty
};
Is joining an in memory object (result.JobOper) to a Db Context. Now for very long reasons what Linq to SQL does is query (or bring back) the entire SQL Context and does the join in memory. So this query that you did will query out the entire LaborDtl table (ALL OF IT NO FILTERS) bring that into memory and then do the join in memory.
This will cause a massive amount of time and memory to be consumed. You should NEVER join to ttTables (using a Join statement)
here’s an entire long thread about it
Always use a whereClause to pull records back from a Db Context if your source is a tt (temporary) in memory table.
My query below (which I had to correct cause I just noticed that result was in memory)
var query = from ld in Db.LaborDtl
where ld.JobNum == jobNum && ld.Company == this.Session.Company && ld.ScrapQty > 0
group ld by new { ld.Company, ld.JobNum, ld.AssemblySeq, ld.OprSeq } into g
select new
{
g.Key.Company,
g.Key.JobNum,
g.Key.AssemblySeq,
g.Key.OprSeq,
ActScrapQty = g.Sum(ld => ld.ScrapQty)
};
Simply uses the jobNumber and Company to return all relevant LaborDtl records and then the in memory lookup should be much more efficient.
Thank you for explaining that! I was suspicious of my approach to say the least. I went with yours and have been testing. Everything seems okay. I had Job Tracker open and loaded with a job, tried making changes in Job Entry simultaneously. I don’t get the message “changed by another user” at all. I can close out of both without errors. I also started / ended production activity in office MES for the job without any sort of errors. And I tried including multiple jobs in the tracker search, and it pulled in the quantities correctly for each job when I used the little navigator thing.
I’m not sure how else to test it, but seems to work.
