Modifying GetList result dataset, joining another table and sorting?

Epicor ERP 10
, , ,
1

So for context I’m setting up Mobile/Kinetic Warehouse on some Zebra TC21 devices with Epicor 10.2.700 to replace our old system with an Intermec scanner scanning a barcoded Job Pick List displayed on a Surface Pro tablet. Simply going through “Issue Material” in Mobile Warehouse seems like a good way of stepping our current process forward, but the material sequence is not the best way to sort the list. We have assigned BinSeq numbers to most of the bins in our main warehouse and I have previously modified the JobPickL report data definition to bring in WhseBin based on the JobMtl calculated bin field, and then the RDL itself to sort the materials within an assembly by the WhseBin.BinSeq then by BinNum.

Now, I know that the Mobile Warehouse app is not as user-customizable as we’re used to with Epicor. Costs some $225+/hr to hire Biscit to customize it? Idk. However, the debug log in the app has been fairly useful, and I was able to at least add a pre-processing directive to JobMtlSearch.GetList to add "by IssuedComplete, PartNum ascending" to the method’s whereClause parameter to at least sort the materials list for an assembly by part number, with completely issued materials at the bottom.

So, with that context out of the way, I would like to know if there’s a good way of modifying the dataset in a BPM before it’s returned to the client, joining/searching another table and either adding a new field to the dataset or modifying/co-opting an existing unused field (like StagingBinNum? That’s a string, though, and I’d like to sort by the integer WhseBin.BinSeq). I’m not sure if “Fill Table by Query” or “Update Table by Query” widgets would be useful, or how to set them up, or if custom code will be required. If custom code is required, I may be able to figure it out on my own (change it to a post-processing directive and modify the tt result dataset? And then how would I go about sorting that tt object?) but any help/examples would be greatly appreciated.

2

This is actually what I ended up doing. I disabled the pre-processing thing I tried with the whereClause and created a post-processing directive on JobMtlSearch.GetList and used the following custom code to construct a sort string from a chosen bin’s BinSeq plus the BinNum itself, and then call result.JobMtlList.Sort() to sort by the fields I wanted.

foreach (var row in result.JobMtlList)
{
  // try to get default bin from PlantWhse
  var thisDfltBin = (from pRow in Db.PlantWhse where pRow.Company == this.Session.CompanyID && pRow.PartNum == row.PartNum && pRow.WarehouseCode == row.WarehouseCode select pRow.PrimBin).FirstOrDefault();
  if (String.IsNullOrEmpty(thisDfltBin))
  {
    // no default, get bin in current whse with max on hand instead
    thisDfltBin = (from pRow in Db.PartBin where pRow.Company == this.Session.CompanyID && pRow.PartNum == row.PartNum && pRow.WarehouseCode == row.WarehouseCode orderby pRow.OnhandQty descending select pRow.BinNum).FirstOrDefault();
  }
  var thisBinSeq = 999;
  if (!String.IsNullOrEmpty(thisDfltBin))
  {
    // get the bin seq of the chosen bin
    thisBinSeq = (from wbRow in Db.WhseBin where wbRow.Company == this.Session.CompanyID && wbRow.WarehouseCode == row.WarehouseCode && wbRow.BinNum == thisDfltBin select wbRow.BinSeq).FirstOrDefault();
  }
  else
  {
    // no bin found, leave the seq at 999 to throw it to the end
  }
  // format bin seq as a zero-padded three digit number so it sorts right
  row.StagingBinNum = thisBinSeq.ToString("D3") + " " + thisDfltBin;
}
result.JobMtlList.Sort("by IssuedComplete by StagingBinNum by PartNum");

I think we’ll adjust some of the BinSeq numbers we have assigned to put certain parts at the end of the list instead of the top, but it seems to be working.

2 Likes