BPM - Remove Duplicates in DataTable Variable

I have a BPM that is getting PartBin records for a given Part Number. However, what I am interested in is the Bin’s, only the distinct Bin’s.
None of the Query Widgets permit that the Join be specified, so I am thinking I might be able to use a bit of C# code to reassign my Tableset Variable to a Distinct version of it… but I’m obviously missing something.
I would prefer to ‘filter’ in place, but I’ve even tried to assign it to another Tableset variable of the same type and it says it’s read only, which is confusing since I can update it with Fill/Update Table by Query.

Among other fails, here is what I’ve tried:

PartBinOnHandDist.PartBinSearch = PartBinOnHand.PartBinSearch.Distinct();

I might need this to be a GroupBy on BinNum, though since other columns have data (like Qty, UOM, Lot) that would make them distinct.

try doing this?

var PartBinOnHandDist = PartBinOnHand.Select.Distinct(x=>x.PartBinSearch);

or this:

var myRecords = PartBinOnHand.GroupBy(x=>x.PartBinSearch);

or something like this?

Perhaps, but neither of those pass syntax check. From what you provided I got to this, but my PartBinOnHandDist appears to be empty when I add it to a Message box.

Syntax OK:
var PartBinOnHandDist = PartBinOnHand.PartBinSearch.Distinct().Select(x => x);

Syntax OK:
var PartBinOnHandDist = PartBinOnHand.PartBinSearch.GroupBy(x => x.BinNum);

Syntax OK:
var PartBinOnHandDist = PartBinOnHand.PartBinSearch.GroupBy(x => new {x.BinNum, x.BinDesc});

I’m pretty sure I need to Group by BinNum & BinDesc, but all of these do not seem to actually set my PartBinOnHandDist variable I created in the BPM.

Ahh… you need to have something more than… I didn’t know that you wanted the sum of the qty… to do that, you need to select and sum the data. Something like: (untested).

var PartBinOnHandDist = PartBinOnHand.PartBinSearch.GroupBy(x => new {x.BinNum, x.BinDesc}).Select(x=> new{x.BinNum, decimal qty = xsum(y=>.OnHand)});

This site has a bunch of examples: LINQ Sum

Actually, I don’t need to sum the qty, I just need a list of the distinct BinNum and their Descriptions.

var PartBinOnHandDist = PartBinOnHand.PartBinSearch.GroupBy(x => new {x.BinNum, x.BinDesc});
yields an empty PartBinOnHanddist TableSet, but there’s no syntax error, not sure what I’m missing.

Try this, it works for me

Select(x => new {x.BinNum, x.BinDesc}).Distinct().ToList();

While the syntax passes, it doesn’t populate the DataTable. Can you provide some context on how you are using this?

Here is my context:
I have a Tableset Variable called ‘PartBinOnHandDist’ that is of Type ‘PartBinSearchTableset’

After populating the Table with the Fill Table by Query Widget I am using a Custom Code Widget with the following code with the intent to eliminate duplicate rows since the source data has multiple Bins because of Lot Tracking.

PartBinOnHandDist.PartBinSearch.Select(x => new {x.BinNum, x.BinDesc}).Distinct().ToList();

Ok, mine is solely used in a Custom Code Widget, I’m not prefilling a table
You may be able to build your query in the Code Widget

  var InvCusts = Db.InvcHead.Where(i => i.Company == Session.CompanyID && i.Posted == true && i.OpenInvoice == true && i.CreditMemo == false && i.UnappliedCash == false && i.InvoiceBal > 1 && i.DueDate < nextsunday && Db.Customer.Any(c => c.CustNum == i.CustNum && c.NoContact == false && c.SendReminders_c == true)).Select(x => x.CustNum).Distinct().ToList();

I got the idea from

I wasn’t able to figure out how to do this the way I wanted to but I got to the result I wanted. If anyone can provide a way to remove duplicates in a Table, please let me know as I think that would be simpler.
What I did is I created another Table (same Type) and used the Fill Table by Query to Fill the new Table with the same records except I concatenated the BinNum & Bin Desc into the Bin Desc field with a few horizontal tabs. Then I used an Set Argument/Variable widget to set a String Variable and format the data:
string.Join("\n", PartBinOnHandDist.PartBinSearch.AsEnumerable().Select(x => x.BinDesc).Distinct())

Get’s me a list in a Message Popup, it just doesn’t feel elegant nor efficient.

I usually use GroupBy

DataTable dtJson = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(callContextBpmData.Character01);
List<UD03> UD03BufferList = new List<UD03>();
 
// We could have the same ECO.PartNum checked out with different Revs
// Group By PartNum, Revision
Dictionary<string, DataRow> dtJsonGroupedByRev =
    (from j in dtJson.AsEnumerable() select j)
        .GroupBy(g => new {
            PartNum = g.Field<string>("PartNum"),
            RevisionNum = g.Field<string>("RevisionNum")
        })
        .ToDictionary(r => string.Concat(r.Key.PartNum, "-", r.Key.RevisionNum),
            r => r.Where(
                x => x.Field<string>("PartNum") == r.Key.PartNum && x.Field<string>("RevisionNum") == r.Key.RevisionNum).First()
            );
 
// Get UD03 Records in Bulk by PartNum, Revision, AltMethod
foreach (var item in dtJsonGroupedByRev)
{
    string sPartNum = item.Value.Field<string>("PartNum");
    string searchString = String.Join("~", new List<string>() {
        item.Value.Field<string>("RevisionNum"),
        item.Value.Field<string>("AltMethod"),
        string.Empty
    });
 
    UD03BufferList.AddRange(
        (from u in Db.UD03
            where
                u.Company == Session.CompanyID
                && u.Key1 == "ENG"
                && u.Key2 == sPartNum
                && u.Key3.StartsWith(searchString)
            select u
        ).ToList()
    );
}
2 Likes