[afinke@focusedenergy.net]

I’d like to find a way to replace the last comma in a string with ‘&’ or ‘and’.

We send sales order communication to people other than the primary contact, and a list of those contacts is stored on each transaction in a custom multi-select field, which is sourced from a default list on the customer card. This is kind of a clunky (I don’t know scripting) set up, but it allows the sales reps the freedom to choose different contacts on a per-transaction basis, while also preserving the ‘default’ selection that the sales rep sets on the customer card so that they don’t have to choose contacts every time they create a new order. Having the contact information available on the transaction then allows for scheduled automatic emails via workflow.

Since I can’t access the order communication contact first name info through the email template fields (it only populates if there is only one contact listed, does not work for multiple), I have another custom field for email greeting names, populated by a saved search. The saved search is taking the first name of each contact and putting them together so that we can use that field as part of the email greeting using this formula: REPLACE(NS_CONCAT({communicationcontact.firstname} ),’,’,’, ‘)

Here is sample info from the custom fields:

Order Communication Contacts:

BayWa r.e. Solar Systems : Amanda Finke

BayWa r.e. Solar Systems : Ajai Khalsa

BayWa r.e. Solar Systems : Hersch Wilson

Email Greeting Names (output from saved search):

Amanda, Ajai, Hersch

I would like to be able to replace the last comma in this scenario with the symbol &. I think there is a way to get this using regexp, but I can’t get there. I suppose it might be possible to get the info into the email greeting using a freemarker expression also? Any help with this is greatly appreciated!
This is a cached copy. Click here to see the original post.

[michoel]

If you want to go the regex route, something like this should work:

Code:
REGEXP_REPLACE(
REPLACE(NS_CONCAT({communicationcontact.firstname}), ‘,’, ‘, ‘),
‘^(.+), ([^,]+)$’,
‘1 & 2’
)

[afinke@focusedenergy.net]

Like a charm. Thanks so much for the help!

[Author]

Posts