Well I’ve hit a wall with my code. I just figured out how to select a record from another table but now I need to select a bunch of records and iterate through them I’ve looked at other code examples and think I’m close. I’m getting the error that the name PartBin_iterator doesn’t exist in the current context. I believe that’s from the line var PartBinRow = PartBin_iterator. I have seen that used in other’s code and I’m not sure what I’m doing wrong.
//Calculate hours between ud10.PullDateTime and ud10.ReturnDateTime and record in ud10.Number01
//Get PartLot.Firmware (string) from all PartLot records in warehouse (ud10.ShortChar01) and bin (ud10.ShortChar02)
//subtract ud10.Number01 from each PartLot.Firmware and set PartLot.Firmware to the new number
// get Tables
Erp.Tables.PartLot LotTable;
Erp.Tables.PartBin BinTable;
//get temp table for updated UD10 record
var NewRecord = ttUD10.FirstOrDefault();
//calculate hours out
TimeSpan diff = NewRecord.ReturnDateTime_c.GetValueOrDefault() - NewRecord.PullDateTime_c.GetValueOrDefault();
NewRecord.Number01 = (decimal)diff.TotalHours;
//get each part and lot in inventory location
foreach (var PartBin_iterator in (from PartBin_row in Db.PartBin
where PartBin_row.Company == Session.CompanyID
&& PartBin_row.WarehouseCode == NewRecord.ShortChar01
&& PartBin_row.BinNum == NewRecord.ShortChar02
select PartBin_row));
// for each row get the PartLot.Firmware field
{var PartBinRow = PartBin_iterator;
LotTable = (from PartLot_row in Db.PartLot
where PartLot_row.Company == Session.CompanyID
&& PartLot_row.PartNum == PartBinRow.PartNum
&& PartLot_row.LotNum == PartBinRow.LotNum
select PartLot_row).FirstOrDefault();
//subtract NewRecord.Number01 and set that answer as PartLot.Firmware
if (LotTable.FirmWare != null)
{decimal NewFirmware = decimal.Parse(LotTable.FirmWare)-NewRecord.Number01;
LotTable.FirmWare = NewFirmware.ToString();
}
}
i restructured the code you showed into llambda statements (they are easier for me to understand)…
ALSO changed them so that we only gather the fields you actually need which makes them more efficient. This is done with the “.Select” statement listing the fields you need.
The other change was to do the select first, and then after it is selected into a table (PartBinsTble) then we itterate through the records it found. This also makes things more efficient.
//Calculate hours between ud10.PullDateTime and ud10.ReturnDateTime and record in ud10.Number01
//Get PartLot.Firmware (string) from all PartLot records in warehouse (ud10.ShortChar01) and bin (ud10.ShortChar02)
//subtract ud10.Number01 from each PartLot.Firmware and set PartLot.Firmware to the new number
//get temp table for updated UD10 record
var NewRecord = ttUD10.FirstOrDefault();
//calculate hours out
TimeSpan diff = NewRecord.ReturnDateTime_c.GetValueOrDefault() - NewRecord.PullDateTime_c.GetValueOrDefault();
NewRecord.Number01 = (decimal)diff.TotalHours;
//get list of bins
var PartBinsTable = Db.PartBin.where(x=>
x.Company == Session.CompanyID &&
x.WarehouseCode == NewRecord.ShortChar01 &&
x.BinNum == NewRecord.ShortChar02).Select(x=>x.PartNum,x.LotNum);
foreach (var PartBinRow in PartBinsTable) {
var LotTable = Db.PartLot.Where(x=>
x.Company == Session.CompanyID &&
x.PartNum == PartBinRow.PartNum &&
x.LotNum == PartBinRow.LotNum).Select(x=>x.FirmWare)
//subtract NewRecord.Number01 and set that answer as PartLot.Firmware
if (LotTable.FirmWare != null){
decimal NewFirmware = decimal.Parse(LotTable.FirmWare)-NewRecord.Number01;
LotTable.FirmWare = NewFirmware.ToString();
}
}
OK… sometimes i just can’t help it… here is a set of (UNTESTED) code that just might be even more efficient.
what I did was combine the two queries into one. reason this could be more efficient is because the first query could return 100 records… then you loop through and create 100 additional queries to get the additional data.
Instead, i created one query that gathers all the data at once with one query. then we itterate throught that one dataset updating the data.
//Calculate hours between ud10.PullDateTime and ud10.ReturnDateTime and record in ud10.Number01
//Get PartLot.Firmware (string) from all PartLot records in warehouse (ud10.ShortChar01) and bin (ud10.ShortChar02)
//subtract ud10.Number01 from each PartLot.Firmware and set PartLot.Firmware to the new number
//get temp table for updated UD10 record
var NewRecord = ttUD10.FirstOrDefault();
//calculate hours out
TimeSpan diff = NewRecord.ReturnDateTime_c.GetValueOrDefault() - NewRecord.PullDateTime_c.GetValueOrDefault();
NewRecord.Number01 = (decimal)diff.TotalHours;
//get list of partLot records into a working table
var PartLotTable = Db.PartLot.Where(pl=>
pl.Company == Session.CompanyID &&
pl.FirmWare != null &&
(Db.PartBin.Where(b=>
b.Company == Session.Company &&
b.WarehouseCode == NewRecord.ShortChar01 &&
b.BinNum == NewRecord.ShortChar02 &&
b.PartNum == pl.PartNum &&
b.LotNum == pl.LotNum))).Select(pl=>pl.FirmWare)
foreach (var lotTable in PartLotTable) {
//subtract NewRecord.Number01 and set that answer as PartLot.Firmware
decimal NewFirmware = decimal.Parse(LotTable.FirmWare)-NewRecord.Number01;
LotTable.FirmWare = NewFirmware.ToString();
}
The first one takes me to this line: x.BinNum == NewRecord.ShortChar02).Select(x=>x.PartNum,x.LotNum); and has the cursor on the x.LotNum
The second one takes me to the PartBinsTable in: foreach (var PartBinRow in PartBinsTable) {
And all the Firmware ones are in: if (LotTable.FirmWare != null){
decimal NewFirmware = decimal.Parse(LotTable.FirmWare)-NewRecord.Number01;
LotTable.FirmWare = NewFirmware.ToString();
Hi Melissa, can you post the full script you’re using? Based on these errors, I think there’s a method missing somewhere that would specify the object type, and it’s causing all the errors.
@MelissaC I think @jwphillips solution will fix all of your errors. Since it isn’t recognizing your select method, the reference to PartBinsTable is giving you additional errors.
Adding new fixed the x error I was getting. I’m still getting an error on the LotTable.FirmWare. It looks like it doesn’t recognize it’s supposed to be a table? When I pull up the context menu (if that’s what it’s called) FirmWare isn’t an option. It only has function stuff, not table fields.
You selected a table. A table has rows. You have to pick a Row before you can get to a column.
In the PartBins, you are looping through each row. Then in the loop, you are selecting a LotTable, but you aren’t looping through that table. You either need to loop through it, or pick the first one or something.
This is because your LotTable is iterable. You would need to put a FirstOrDefault() after your select if you are certain there will only ever be one row returned. Right now LotTable is not a row but a row set.
I apologize for not looking more thoroughly at your code.
Your LotTable variable will essentially be a list of FirmWare values, because this part of the phrase:
returns just the FirmWare value of each item that matches your criteria (whether none, or one, or all of them match) - so, as others have mentioned, add a FirstOrDefault() to the end of that statement. At that point, your LotTable with be set to a single FirmWare value (basically, LotTable = x.FirmWare, where x is the first PartLot that matches your criteria). You could forego the Select and just take the first PartLot that matches your criteria this way:
Also, it looks like you’re attempting to update the value in the database. Once you Select anything from the database, you are working with the results of your query, and not the actual values in the database … so you can update them, but they will not save to the DB.
I have not been brave enough to directly update database values, so I have no experience to help with that part of your code.
I’ve looked at other code examples and think I’m close. I’m getting the error that the name PartBin_iterator doesn’t exist in the current context. I believe that’s from the line var PartBinRow = PartBin_iterator. I have seen that used in other’s code and I’m not sure what I’m doing wrong.
