In-Transaction Foreach Loop going wrong

Hello folks! For a while now I’ve been trying to get a script like this working, which will keep all quote quantities consistent with each other under all circumstances. This script is based off of a different but similar Foreach loop I found on google, since I’m not very familiar with Foreach logic or Epicor syntax.

WHAT THE SCRIPT IS SUPPOSED TO DO:
For each line in a quote, check to see if the QuoteQty.SellingQuantity does not match the QuoteDtl.OrderQty. If they do not match, set the OrderQty and QuoteDtl.SellingExpQty to match the SellingQuantity.

WHAT THE SCRIPT DOES INSTEAD:
Takes the SellingQuantity value of the last line in a Quote and sets every other quantity in every line in the quote to match.

WHAT THE CODE LOOKS LIKE:

Erp.Tables.QuoteDtl QuoteDtl;
Erp.Tables.QuoteQty QuoteQty;

foreach (var ttQuoteHedRow in ttQuoteHed)
{
foreach (var QuoteDtl_iterator in (from QuoteDtl_Row in Db.QuoteDtl
where QuoteDtl_Row.Company == ttQuoteHedRow.Company && QuoteDtl_Row.QuoteNum == ttQuoteHedRow.QuoteNum
select QuoteDtl_Row))
{
foreach (var QuoteQty_iterator in (from QuoteQty_Row in Db.QuoteQty
where QuoteQty_Row.Company == ttQuoteHedRow.Company && QuoteQty_Row.QuoteNum == ttQuoteHedRow.QuoteNum
select QuoteQty_Row))
{
QuoteDtl = QuoteDtl_iterator;
QuoteQty = QuoteQty_iterator;
if (QuoteDtl.OrderQty != QuoteQty.SellingQuantity)
{
QuoteDtl.OrderQty = QuoteQty.SellingQuantity;
QuoteDtl.SellingExpectedQty = QuoteQty.SellingQuantity;
}
}
}
}

Can anyone see what I’m missing here?

I see where’s including company == company but nothing like QuoteNum == QuoteNum.

What I’m getting at it that your QuoteDtls should link to ttQuoteHed on the quotenum

1 Like

Or at least investigate the data dictionary for indexes,. Note you dont have to use them in full, but at certainly in order for best performance

That was… odd. I DO have a line linking to ttQuoteHed on QuoteNum, but when I pasted the code here it excluded that row (and only that row!) Of course linking on Company alone would be anarchy. Fixed the original post’s code.

2 Likes

I think for the last bit of that where clause, I’d add a criteria where QuoteDtl.OrderQty != QuoteQty.SellingQuantity cause that’s the only records you care about.

How about something like this?

foreach (var ttQuoteHedRow in ttQuoteHed)
{
	foreach (var QuoteDtl_iterator in Db.QuoteDtl.Where(QuoteDtl_Row=> QuoteDtl_Row.Company == ttQuoteHedRow.Company && QuoteDtl_Row.QuoteNum == ttQuoteHedRow.QuoteNum))
	{
		foreach (var QuoteQty_iterator in Db.QuoteQty.Where(QuoteQty_Row => QuoteQty_Row.Company == QuoteDtl_iterator.Company 
														&& QuoteQty_Row.QuoteNum == QuoteDtl_iterator.QuoteNum
														&& QuoteQty_Row.QuoteLine == QuoteDtl_iterator.QuoteLine
														&& QuoteQty_Row.SellingQuantity != QuoteDtl_iterator.OrderQty))
		{			
			//this is the code that is needed for when you set data back to the database in a different business object then what you are calling
			using (System.Transactions.TransactionScope txScope = Erp.ErpContext.CreateDefaultTransactionScope())
			{	
				QuoteDtl_iterator.OrderQty = QuoteQty_iterator.SellingQuantity;
				QuoteDtl_iterator.SellingExpectedQty = QuoteQty_iterator.SellingQuantity;
				Db.Validate();	
				txScope.Complete();
			}	
		}
	}
 }

Hmm that code works much better (as does Chris’ suggestion,) but now I’m seeing “Row has been modified by another user” errors. These are most likely caused by the SellingExpQty field, since price fields are re-calculated when that changes. I know that in the form’s Script Editor I can use oTrans.Update() at the right place to save changes before the re-calculation which prevents that error, but I don’t know the BPM syntax to do that in this script.

Also, do you think an in-transaction data directive is the correct place for this? I assumed it would be, but I don’t know the difference between Standard and In-Transaction.

Why not have this be a BPM? Maybe check to see if the Order has been updated as well. Instead of just running the code on all orders.

Pre process?

Your code was very close the big thing you just need to make sure you are getting the tables joined right. You really need to have the company, quote, quoteline. Otherwise you will overwrite what you just did if there are multiple lines per quote. Think that is what you were seeing before I think.

1 Like

I’ll try it as a Quote.Update pre-processing BPM. I’m not sure that’ll keep my quantities from zeroing out when a quote is duplicating, though-- the big perk of the in-transaction directive was that it preserves quantities after duplicating a quote. Maybe I’ll do most of the work in Quote.Update, then a Foreach where OrderQty == 0 script in the data directive.

Also, Ken, I have a question about your code, specifically this line:
var QuoteQty_iterator in Db.QuoteQty.Where(QuoteQty_Row => //insert criteria here
What does that => operator do in this context, specifically?

@josecgomez talks about using the Lambda Expression in this post. Just a different way of writing the code.

https://epiusers.help/t/a-few-little-code-snippets-that-i-have-found/39565/6

1 Like

I dig it! That syntax style actually makes a bit more sense to me. Also, I figured out the core problem of my original in-transaction script: I’m a moron! My Where Clause did not match QuoteDtl and QuoteQty on QuoteLine=QuoteLine. My overall project concerning Quote Quantities isn’t over with, but this part is. Thanks!