How to select Min. result in BPM Lookup

Epicor ERP 10
1

I have this pop-up confirming the data im trying to lookup. By default its selecting the last or largest BreakQty. I’m not sure where i can throw a .Min or sort DESC to have it kick out the smallest value for BreakQty.

Erp.Tables.VendPBrk VendPBrk;
foreach (var ttVendPart_iterator in (from ttVendPart_Row in ttVendPart
                                     where ttVendPart_Row.Company == Session.CompanyID
                                     select ttVendPart_Row))
{
    var ttVendPart = ttVendPart_iterator;

    {
        foreach (var VendPBrk_iterator in (from VendPBrk_Row in Db.VendPBrk
                                             where string.Compare(ttVendPart.Company, VendPBrk_Row.Company, true) == 0 && ttVendPart.PartNum == VendPBrk_Row.PartNum && ttVendPart.VendorNum == VendPBrk_Row.VendorNum
                                             select VendPBrk_Row))
        {
            VendPBrk = VendPBrk_iterator;
            MinBreakQty = VendPBrk.BreakQty;

        }

    }
}


// Validate Min Order Qty
string body = "Min Break Qty is " + (MinBreakQty);
this.PublishInfoMessage(body, Ice.Common.BusinessObjectMessageType.Information, Ice.Bpm.InfoMessageDisplayMode.Individual, "FirstVar","SecondVar");
2

Something like this?

string body;
int minqty = 0;
foreach(var row in ttVendPart)
{
	if (minqty = 0 || row.BreakQty < min)
		{ minqty = row.BreakQty; }
}

if (minqty > 0)
	{body = "Min Break Qty is " + minqty;}
else
	{body = "whoops";}

This is probably the dumbest solution to the problem, but it should return the minimal value regardless of sort order. I’ve got an algo textbook on a shelf at home that probably has a more elegant solution.

3

I’ll check that out. Hoping to get the min value though with whatever Min syntax works. As is the code im using is pulling the max (or last row)

image
image841×557 29.2 KB

4

So the BEST way to do this is to let SQL do the work and not iterate through the data that is returned… if you only need the minimum record, then only select the minimum record. The way that you have this structured, you created a query that returns ALL values, and then you proceed to look for the lowest value. Instead, this query below (that is not tested… so it may have some errors) only selects the first record it finds… but before it selects the first one, it sorts it, so that the first record is the minimum one. PLUS this is much smaller code…

var ttVPart = ttVendPart.Where(x=>x.RowMod == "U").FirstOrDefault();
var dbVendPBrk = Db.VendPBrk.Where(x=>
    x.Company == ttVPart.COmpany && 
    x.VendorNumber == ttVPart.VendorNumber && 
    x.PartNum == ttVPart.PartNum).OrderBy(x=>x.BreakQty).FirstOrDefault();

MinBreakQty = dbVendPBrk.BreakQty;
1 Like
5

thank you i’ll check this out. I struggle on the sytax from C# connecting to SQL. I can do SQL by itself most of the time or through SSRS =)

6

Yea, you have to learn a new language instead of SQL… but the results turn to SQL. This is all done using “LINQ” expressions in C#. it takes a while to “get it”… but once you do, it totally makes sense. I find LINQ easier to read than SQL. Check out: Query expression basics (LINQ in C#) | Microsoft Learn

1 Like