Extra field on PartTranHist data view - 10.0.7

Epicor ERP 10
1

I’m trying to find an efficient method for adding the EmpID to the PartTranHist data view in the ‘Part Transaction Histroy Tracker’ form.

The data is in the PartTran table, it’s just not pulled into the PartTranHist view.

I’ve tried adding the PartTran table as a foreign key view to the PartTranHist view but neither the TranNum or SysRowID seem to be exposed for me to attempt a join

I’ve tried looking for a system BAQ to copy/clone to create my own view to replace the system view. I’ve found a couple I could build from but they’re missing the RunningTotal.

Is there a simple way to do this or do I need to create my own view that mimmicks the system view and then add/calculate the running total manually myself?

2

How many fields are you trying to add?

3

It’s just the one, EmpID, but potentially on thousands of rows.

4

Could you create a POST Method Directive on Erp.PartTranHist on Method RunPartTranHistory with simply adding the EmpID as EntryPerson otherwise UserID?

I have also seen someone just hijack the PCID2 field which they have no intention to use PCID2 and just renaming the Grid Column.

var PartTranHistory = 
		(from ph in ttPartTranHist
		join pt in Db.PartTran.With(LockHint.NoLock) on ph.TranNum equals pt.TranNum
		select new { HistoryRow = ph, EmployeeID = pt.EmpID }
		).ToList();

foreach (var row in PartTranHistory)
{
	 //row.HistoryRow.PCID2 = row.EmployeeID;

   if (row.EmployeeID != string.Empty)
   {
        // Or even splitting it showing both on 1 column
   	row.HistoryRow.EntryPerson = string.Format("{0} / {1}", row.EmployeeID, row.HistoryRow.EntryPerson);
   }
   else 
	 {
     row.HistoryRow.EntryPerson = row.EmployeeID;
   }
}

EDIT: Don’t join on the ttTable - this is an old Query. Just extract it out of there.

5

I hadn’t thought of attacking it from that direction. I’ll have a go.

Thanks!

6

Looks like there’s too much data. Client side freezes and I can see memory usage server side slowly increasing to full. It’s given me a starting point though and I’ll try refining tomorrow/the day after and let you know how I get on.

I’ll probably just return the SysRowID and EmpID and then loop through the already existing ttPartTranHist

7

I went with this in the end which only slows the screen down slightly.

var partNum = ipPartNum;
var tranDate = ipTranDate;

DateTime finalDate = tranDate == null ? new DateTime(8888,1,1) : (DateTime)tranDate;

var partTranEmployee = (from pt in Db.PartTran where pt.PartNum == ipPartNum && pt.TranDate <= tranDate && pt.EmpID != null && pt.EmpID != String.Empty select new { HistoryRow = pt.SysRowID, EmployeeID = pt.EmpID });

if (partTranEmployee == null || partTranEmployee.Count() < 1) return;

foreach (var row in partTranEmployee)
{
	var historyRow = (from ph in ttPartTranHist where ph.SysRowID == row.HistoryRow select ph).FirstOrDefault();
	
	if (historyRow == null) continue;
   
	historyRow.EntryPerson = string.Format("{0} / {1}", row.EmployeeID, historyRow.EntryPerson);
}
8

Yeah my Query up there would be alot slower given that I am doing a join on the ttTable which is a No-No, but I wrote it before the discovery of the ttJoin NO NO =) If you take it out of the JOIN and to a Select on PartTran it should be much much faster.

=) I need to be careful to fix my old Queries.

2 Likes