How to concatenate values from Collection

Hi Heather, 

I usually do this in a script task with an ESQ, however, if you can set a field to flag the records you can do it in a process without coding. You can see this article for some details on how to loop in a process by flagging records: https://customerfx.com/article/how-to-loop-through-records-in-a-process…

The basic idea is:

1. Create a process param that will hold the concatenated value
2. Use a modify data to flag all the records you want to work with
3. Now, read the flagged records
4. Check if you got a record by checking if the Id != Guid.Empty
5. Concatenate the value in the process param
6. Unflag the record by using a modify data to un-set the field
7. Loop back to #3

Make sure the maximum number of repetitions setting for the process is larger than the typical set of data you'd be working with. Anyway, hope this helps. Of course, doing it in code is pretty straight forward as well if you're interested in going that route I'd be happy to help.

Ryan

Ryan Farley,

Thanks for your response. I have been trying to get away from using loop flags because it causes all the records to be marked as "modified", which causes confusion about when the records were actually last modified by a user. Could you give me an example of how you do it with a script task? I have never worked with script tasks before.

Heather,

Here's an example that uses an EntitySchemaQuery to read all accounts with a type of Customer and creates a comma delimited string of the account names. When done, it puts the value in a process parameter named TextParam.

var text = "";
 
var esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Account");
esq.AddColumn("Name");
 
var customerFilter = esq.CreateFilterWithParameters(FilterComparisonType.Equal, "Type.Name", "Customer");
esq.Filters.Add(customerFilter);
 
var accounts = esq.GetEntityCollection(UserConnection);
foreach (var account in accounts)
{
	var name = account.GetTypedColumnValue<string>("Name");
	text += name + ", ";
}
text = text.Trim().TrimEnd(',');
 
Set("TextParam", text);
return true;

Hope this helps get you started.

Ryan

Ryan Farley,

Thanks so much, I was able to get that to work. Do you happen to know how to get the value from a non-Typed column? I'm assuming you have to get the GUID and then do a separate lookup from that table?

Heather,

If I am understanding you correctly, you can do that all in the same query. For example, in the example I posted, let's say you want to get the Account Owner's email address (Owner is a lookup to contact). I would add a column like this:

esq.AddColumn("Owner.Email");

Then, I could read that from the results like this (the dots are replaced with underscores):

var ownerEmail = account.GetTypedColumnValue<string>("Owner_Email");

You can go through many object relationships like this, for example Owner.City.Name, etc.

Ryan

Ryan Farley,

Thank you for all your help so far. I will check out the method you suggest. I got this almost working using a different method and I'm wondering if you can help with the last step. I just need to get the value of the record that I want to filter on in my script. I have tried a number of different syntax but just keep getting errors. I'm tryin gto get the UsrContact, which is a Contact Lookup record, and add that value to my SQL as the "user". I have tried it as a Guid and a string and get different errors depending on what way I do it.

var text = "";

var user = Get<string>("UsrContact");

var sql = "select case when cc.Name is null then ' ' else cc.Name end, "+ 

                 "case when org.Name is null then ' ' else org.Name end, "+ 

                 "case when cc.Phone is null then ' ' else cc.Phone end, "+ 

                 "case when cc.MobilePhone is null then ' ' else cc.MobilePhone end, "+ 

                 "case when freq.Name is null then ' ' else freq.Name end, "+ 

                 "case when rm. UsrLastCPIContact is null then ' ' else rm.UsrLastCPIContact end "+

            "from kwlcustomerMatrix rm "+ 

            "left outer join Contact cc on rm.KwlRCustomerContact1Id=cc.ID "+ 

            "left outer join Account org on rm.KwlCMAccountId=org.ID "+

            "left outer join KwlFrequency freq on rm.KwlExpectedMinFrequencyId=freq.ID "+

            "where rm.KwlROurcontact1Id='"+user+"'";

Actually, that issue was a silly mistake on my part. I got this working. Thank you so much for your help in this. I will post my code in case this is useful for someone else in the future.

var text = "";
Guid user = Get<Guid>("UsrContactGuid");
var sql = "select case when cc.Name is null then ' ' else cc.Name end, "+ 
                 "case when org.Name is null then ' ' else org.Name end, "+ 
                 "case when cc.Phone is null then ' ' else cc.Phone end, "+ 
                 "case when cc.MobilePhone is null then ' ' else cc.MobilePhone end, "+ 
                 "case when freq.Name is null then ' ' else freq.Name end, "+ 
                 "case when rm. UsrLastCPIContact is null then ' ' else convert(varchar, rm.UsrLastCPIContact, 23) end "+ 
            "from kwlcustomerMatrix rm "+ 
            "left outer join Contact cc on rm.KwlRCustomerContact1Id=cc.ID "+ 
            "left outer join Account org on rm.KwlCMAccountId=org.ID "+
            "left outer join KwlFrequency freq on rm.KwlExpectedMinFrequencyId=freq.ID "+
            "where rm.KwlROurcontact1Id='"+user+"'"+
            "ORDER BY CASE WHEN freq.Name = 'Daily' THEN '1'"+
                          "WHEN freq.Name = 'Semi-Weekly' THEN '2'"+
                          "WHEN freq.Name = 'Weekly' THEN '3'"+
                          "WHEN freq.Name = 'Monthly' THEN '4'"+
                          "WHEN freq.Name = 'Quarterly' THEN '5'"+
                          "WHEN freq.Name = 'Semi-Annually' THEN '6'"+
                          "WHEN freq.Name = 'Annually' THEN '7'"+
                          "ELSE freq.Name END ASC";
 
 
var query = new CustomQuery(UserConnection, sql);
using (var db = UserConnection.EnsureDBConnection())
{
    using (var reader = query.ExecuteReader(db))
    {
        while (reader.Read())
        {
 
            text += "<tr><td>"+reader.GetString(0)+"   </td>"+
                        "<td>"+reader.GetString(1)+"   </td>"+
                        "<td>"+reader.GetString(2)+"   </td>"+
                        "<td>"+reader.GetString(3)+"   </td>"+
                        "<td>"+reader.GetString(4)+"   </td>"+
                        "<td>"+reader.GetString(5)+"   </td></tr>";
 
 
 
 
 
        }
    }
}
 
text = text.Trim().TrimEnd(',');
Set("UsrEmailContentTableRows", text);
return true;