I am working on some automated reporting for our “Certificates of Analysis” when an order is shipped.
We are processing most of this data off of the ShipDtl from the Pack in Customer Shipment Entry.
When the pack is marked shipped, it does the thing, bla bla bla.
Anyway, the ShipDtl has a field for JobNum, and I need it, but the way our system is set up, that field
is not populated.
We populate that in a field on PartLot called V8JobNum_c.
Before you ask, no I didn’t name it. Consultant did on uplift from Vantage.
He didn’t realize we would be using it after that
Anyway, I was conversing with @josecgomez about if there was a nifty way to do the join between the pack’s ShipDtl table, and PartLot, while returning only the ShipDtl fields, with the JobNum filled in correctly.
This was the best we could come up with, and neither one of us are real happy with it.
So does anyone have any better ideas? (It works BTW, just want perfection )
var shipDetailWithJob = (from tblShipDetail in packTS.ShipDtl
join
tblPartLot in Db.PartLot
on new { tblShipDetail.LotNum, tblShipDetail.PartNum } equals new {tblPartLot.LotNum, tblPartLot.PartNum }
into tblJoin
from tblCombined in tblJoin.DefaultIfEmpty()
select new {tblShipDetail, tblCombined.V8JobNum_c})
.ToList()
.Select(x =>
{
x.tblShipDetail.JobNum = x.V8JobNum_c;
return x.tblShipDetail;
});
@klincecum Do you even need the V8JobNum? Our lot matches the JobNum by default and I use that for this join for our CofC statement. I also double check with the jobnum since we will do a make direct even though we are not suppsoed to.
So if you are just trying to get back to the job that created the lot why don’t you just look it up in the Part Tran record?
I’m assuming you are shipping from inventory so you do your job receive to inventory then ship
What we do for our CoC is take the Lot in the Shopment and do a reverse lookup in part tran for the MFG-STK transaction that tells me the job that lot came from and thus get the information that way
Well if me and Jose didn’t find anything better together, and no one is putting up leads, I think I’m gonna have leave this one. I got other stuff to do
I’m not too good at LINQ, why can’t you have the select as part of your first statement, bring back just tblShipDetail and V8JobNum_c and then do the mutation locally? Its my understanding that the “ToList” is the part that brings all the data back that you don’t want.
