Custom code group by & contains doesn't seem to work

Kinetic ERP
, , ,
1

The users want a message to show if they are shipping a partial shipment when OrderHed.ShipComplete or OrderDtl.ShipLineComplete is checked and the Shipping Qtys <> the Order Qtys during the Freight Action in Single Packs and Master Packs.

I did this in FreightService.BuildCustFreightRequest Post Process Method Directive for the single packs, which works.

For the Master Pack, I am trying to do the same thing in FreightService.BuildMasterPackFreightRequest. The ttMasterPackInfo_Row.PackIDList is a tilde delimited list of the packs in the MasterPack. My thought was to get all the ShipDtl in that list and group them by OrderNum, OrderLine to sum SellingJobShipQty & SellingInventoryShipQty. Then compare that to the OrderDtl.SellingQuantity to set the exception.

My problem is in sql this code works fine:
select sd.ordernum, sd.orderline, sum(sd.sellingjobshipqty), sum(sd.SellingInventoryShipQty)
from erp.shipdtl sd
join erp.OrderDtl od
on sd.ordernum = od.ordernum and sd.orderline = od.orderline
where sd.packnum in (618962, 618963)
group by sd.ordernum, sd.orderline

But, the C# version does not. I get a random order# and Part# back. What am I doing wrong?

Erp.Tables.ShipDtl ShipDtl;
Erp.Tables.OrderDtl OrderDtl;
Erp.Tables.OrderRel OrderRel;

dspException = false;

foreach (var ttMasterPackInfo_Row in result.MasterPackInfo)
{
packList = ttMasterPackInfo_Row.PackIDList;
var shipdtlrows = from shipdtl in Db.ShipDtl
where shipdtl.Company == ttMasterPackInfo_Row.Company && packList.Contains(shipdtl.PackNum.ToString()) //shipdtl.PackNum == 618962 //packList.Contains(“618962”) //packList.Contains(shipdtl.PackNum.ToString())
group shipdtl by new { shipdtl.Company, shipdtl.OrderNum, shipdtl.OrderLine } into sd
orderby sd.Key.OrderNum, sd.Key.OrderLine
select new
{
sd.Key.Company,
sd.Key.OrderNum,
sd.Key.OrderLine,
sjsq = sd.Sum(shipdtl => shipdtl.SellingJobShipQty),
sisq = sd.Sum(shipdtl => shipdtl.SellingInventoryShipQty)
};

The packList.Contains doesn’t work, it only seems to work when I hardcode it like shipdtl.PackNum = 618962.

Any ideas?

2

You might add your PackNum to the select, just to verify what you’re actually getting.

packList.Contains(shipdtl.PackNum.ToString()) might return a result with a different PackNum - for example, your PackList has 618962 and 618963, but that could return 6189, because the string "618962~618963" contains the string "6189" (I assume this is tilde-delimited)

It might be better to split your list into a collection and check the contents of the collection (Split returns a collection of type Array, but correct me if I’m wrong that does not natively play nice with Linq, so I turn it into a List):

List<string> lstPackList = packList.Split('~').ToList();

and then in your query:

lstPackList.Contains(shipdtl.PackNum.ToString())
3

Here is the corrected code.


Erp.Tables.ShipDtl ShipDtl;
Erp.Tables.OrderDtl OrderDtl;
Erp.Tables.OrderRel OrderRel;

dspException = false;

foreach (var ttMasterPackInfo_Row in result.MasterPackInfo)
{
    packList = ttMasterPackInfo_Row.PackIDList;

    // Parse the packList into a list of integers
    List<int> packNumList = packList.Split('~')
                                       .Where(s => !string.IsNullOrEmpty(s)) //remove empty strings
                                       .Select(int.Parse)
                                       .ToList();

    var shipdtlrows = from shipdtl in Db.ShipDtl
                      where shipdtl.Company == ttMasterPackInfo_Row.Company && packNumList.Contains(shipdtl.PackNum)
                      group shipdtl by new { shipdtl.Company, shipdtl.OrderNum, shipdtl.OrderLine } into sd
                      orderby sd.Key.OrderNum, sd.Key.OrderLine
                      select new
                      {
                          sd.Key.Company,
                          sd.Key.OrderNum,
                          sd.Key.OrderLine,
                          sjsq = sd.Sum(shipdtl => shipdtl.SellingJobShipQty),
                          sisq = sd.Sum(shipdtl => shipdtl.SellingInventoryShipQty)
                      };

    // ... your remaining logic ...
}
2 Likes
4

That is FANTASTIC!! It works. The good thing is that I started to write code to put into a list, but thought I had to put it into an array to loop through and then wasn’t sure how to put the list in the select statement. THANK YOU so much!

2 Likes