I’m new here and I don’t have the greatest C# knowledge. Using custom code, I’m trying to generate a list of serial numbers (concatenated) based on critera for use in a variable. For example, in a show message. I found a great post on here that I think is getting me close to what I’m trying to achieve: How to Concatenate in BPM Custom Code - #2 by timshuwy
The code I have so far:
foreach(var i in Db.SerialNo.Where(r => r.Company == "COMPID")){
var fulllist = Db.SerialNo.Where(x =>
x.Company == "COMPID").Select(x=>x.SerialNumber).ToList();
var concats = string.Join(";",fulllist);
}
Perhaps a dumb question but any help would be greatly appreciated!
Welcome Laura! I think I missed your question. Looking at the code I am leaning towards thinking that you need to define some of those variables outside of the loop, otherwise they get defined over and over again. Otherwise, ask a question!
Sorry perhaps I skirted over the question a little!
I’m wanting to retrieve rows from the SerialNo table (only the SerialNumber field) but have these concatenated into a single string that I can store in a variable for use in something like a message box.
For example:
Results from the SerialNo table:
Serial1
Serial2
Serial3
I’m wanting these combining into a single string:
Serial1;Serial2;Serial3
Then storing into a variable so I can use the variable in message box
I think this might get you close. I still think you would need to move the (var concats) outside of the loop, then just use (concats = string…) inside the loop.
Then just MsgBox.Show(concats); to see what you have. It is always tricky working in directives, so make sure you really want to do that. If you just need a one off list of concatenated data, then do it all in a BAQ and calculated fields. You can use String_Agg(concats,', ') or something like that to smush all the data into a single calcualted field separated by the delimiter you put int he expression.
That code is perfect and gets me so close to what I’m trying to achieve. I am using extra criteria in the where statement but for ease I just didn’t include them in my code.
I’ve tested your code in a MsgBox and it gives me my desired output!
I’m needing to use the var AllSerialNumbers in an Update Table By Query. The concatenated serial numbers I’m producing will be inserted into a custom field on QuoteDtl (I’m generating a new quote).
Hope the above give a little more context into what I’m trying to achieve?
Ah, that does make more sense. I’m not sure what you are developing, but keep in mind when you consolidate data like that its not going to be kept up to date, rather it will reflect what the data was when the BPM ran. So that custom field will only have the serial numbers that matched at that time, and not reflect any deletions/additions since then.
Need any more help or was that the solution you needed?
Edit: Just make the AllSerialNumbers a variable in the BPM editor and you can use it in your update Table block.
Thanks for the info, the data will always be accurate. What I’m doing is at the point of an order being invoiced for serial tracked items, I’m generating a quote with a due date of 12 months in the future for servicing purposes so the serial numbers need entering against the quote at the time of being invoiced. Hopefully that makes sense!
I don’t suppose you’d be able to help with me using the custom code variable within the update table by query or would you recommend running another update on QuoteDtl within the custom code? If the latter then you’ve been a great help and thank you!
Oh, and perhaps this will trip you up. The variables you declare in a custom code block are local variables and only accessible in that code block. You’ll want to define AllSerialNumbers in the Variables tab at the bottom of the BPM window, and then in your code delete the ‘var’ in front of AllSerialNumbers in your code block.
