When a buyer is working with PO Suggestions and they have selected several records to buy and then selects the ‘Generate PO’s’ Actions Menu I need to aggregate the selected rows OrderQty by Supplier and make sure that exceeds a value defined on the Supplier. However, the only Method I can see to use is the .CheckBuyers which only has the list of SugNum as a tilde delimited list.
I thought I could convert this list to a datatable that I could then use in a query to make my checks and provide the appropriate message to the user, but I can’t work out how to split\convert the tilde delimited SugNum’s into a DataTable.
Or maybe there is a better way to accomplish this.
Maybe you can glean something from what Jose did with a CTE for Sales Reps (also tilde delimited).
1 Like
Is what you are wanting a datatable with each item as a separate column?
I assume this needs to be a dynamic number of columns?
I don’t know exactly. I got this partial solution from someone on here. It uses the ZDataField as a source for a unique sequential number list. Add a subquery with ZDataField table and no fields. Add a calculated field:
Name: RowNum
Type: Int
Editor: ROW_NUMBER() OVER(ORDER BY ZDataField.SysRevID ASC)
By itself this does almost nothing, it just returns a long list of numbers. We want to return the suggestions that go with the SugNums in your tilde list.
At the top level, or next level up query, add your table that pulls in the SugNum list, to that table, join the RowNum subquery. Join on this criteria:
RowNum Sub: Calculated_RowNum <= [Ice].num_entries(YourTable.YourField,'~')
In this case your table and your field need to be replaced by the table you are getting SugNums from.
I know this isn’t a perfect answer, but I think this will get you closer. This is the only way I have ever parsed the tilde lists you are talking about. Good luck!
I’m guessing you’re doing something like making sure you’re hitting a minimum order value or something similar?
The best way I’ve found to do this kinda validation is to ‘cheat’. Replace the the standard button with a custom one. Have it do all your validations and other underhanded deeds, then just call the standard method afterward. (Or not as the case may be.)
Or if you just need the code to split the list:
var crap = "aoeu~aoeu~nth~aoeu~sthsnth";
var arrayOfCrap = crap.Split('~');
arrayOfCrap.Dump();
/* Output:
String[5]:
aoeu
aoeu
nth
aoeu
sthsnth
1 Like
1 Like
No, I don’t need the tilde delimited items in different columns, but I need them in different rows.
My thinking is if I can split the string by Tilde and populate a datatable, I would then use that datatable to query the SugPODtl for those SugNum and summarize OrderQty by Vendor.
Thanks, I’ve been able to Split the list into an array, but now I need to lookup the SuggNums (that’s what the tilde delimited list is) and sum by vendor to make my check.
I’m stumped on how to query SugPODtl.SuggNum with an Array. I was hoping to convert the Array to a DataTable and then use the DataTable and SugPODtl to make the aggregates.
Ok, sorry but I am confused.
Would you mind backing up an explaining exactly what you are doing and what kind of solution
you are looking for?
Are you wanting a BAQ that breaks it out, or can you use custom code with LINQ?
What kind of output etc?
This is not a BAQ, it’s a BPM that needs to evaluate all the PO Suggestions that have been selected for Buy when the Generate PO’s Action menu option is selected.
I ran a trace and what appears to be the only viable Method is the .CheckBuyers,
.CheckBuys includes a few parameters, none of which are DataTables. I was hoping it would pass a DataTable of the rows that were selected for Buy. Instead it’s just a tilde delimited list of the SugNums that were selected.
Just need to break those out and then query the SugPODtl table for those rows and aggregate the OrderQty Sum by Supplier to see if they meet the Supplier’s MinOrderQty (UD Field).
1 Like
So would you like it as a datatable, or to show you how to query an array with a datatable?
List<string> suggestionsList = suggList.Split("~").ToList();
DataTable suggestionsTable = new DataTable("Suggestions");
suggestionsTable.Columns.Add("Buyer", typeof(string));
foreach(string buyer in suggestionsList)
{
DataRow newRow = suggestionsTable.NewRow();
newRow["Buyer"] = buyer;
suggestionsTable.Rows.Add(newRow);
}
//Table is populated
2 Likes
Perhaps you could use the SysCube table to hold the data so you can query it. I have recently started using Executive Queries, and they are really helpful. Not sure exactly how you would implement it for you use case though.
1 Like
I scribbled up a buncha crap. Hopefully it helps. Basically it shows up to find the sugs that match your selections, figure out who is under their min order, etc. Have fun!
// A list of your rows and a DB context
// You don't need this, but it's handy for the example
var ttSugPODtl = new List<SugPoDtlRow>();
var Db = new ErpContext();
// Break your sugs into an array
var sugs = "123~098~897~123123";
var listOfSugs = sugs.Split('~').Cast<int>();
// Get the rows that match your selections
var selectedSugs = ttSugPODtl.Where(p=>listOfSugs.Contains(p.SugNum));
// Group em up by Vendor
var selectedSugsByVendor = selectedSugs.GroupBy(s=>s.VendorNum);
// Get the total by vendor
foreach(var vendorSugs in selectedSugsByVendor){
var vendorNum = vendorSugs.Key;
var orderedQty = vendorSugs.Sum(s=>s.RelQty);
}
// Or do it all at once and just get a list of vendors + ordered quantities
var quantitiesByVendor = ttSugPODtl.Where(p=>listOfSugs.Contains(p.SugNum))
.GroupBy(s=>s.VendorID)
.Select(g=>new{ID=g.Key, OrderedQty=g.Sum(i=>i.RelQty)});
// Loop through em!
foreach (var quantity in quantitiesByVendor){
var vendorID = quantity.ID;
var orderedQty = quantity.OrderedQty;
}
// Or just cut to the chase and find the vendors that don't meet minimum
var shortOrders = quantitiesByVendor.Where(q=> Db.Vendor.Any(v=> v.VendorID==q.ID && v.MinOrderQty_c > q.OrderedQty));
Console.WriteLine($"{shortOrders.Count()} vendors are under their min order qty.");
2 Likes
Thanks to everyone for the ideas and troubleshooting.
I’m testing some of this out and I found a flaw in my approach…
What I found is that in some cases when generating PO’s Epicor will allocate various suggestions to multiple PO’s which then may not meet the Min Order Qty… So I had to check against the generated Purchase Orders… which actually is easier to work with. Going that direction with a Data Directive, I just leave the PO Unapproved with a warning message. Then in PO Entry I have a Method Directive on changing the Approved checkbox to check the Order Qty.
Regardless, this is all very helpful information for anyone that needs to work with delimited lists.
Incidently though, though this I am baffled by the BPM Variable Type ‘DataSet’ you can create, but I’ve never used it and can’t work out it’s usefulness. I had thought I could assign the results of one of the LINQ queries suggested to the DataSet variable… but that’s not the case and befuddles me further.
Has anyone used the DataSet variable in a BPM or Function in any practical use?
I was wondering the same thing when I was looking at trying to help you yesterday.
I’ve used it for passing them around, but beyond that, no.
It’s difficult to work with DataSets/DataTables in LINQ, because they aren’t strongly typed.
There are however some extension methods for it.
They make my brain hurt 
(The extension methods)