Case formula feature missing from new JO query builder?
Hi,
In the old JO query builder, we can configure case formula fields as follows to say if there was a CSM assigned to the company, populate a sender email field with the CSM email, else populate the field with a custom string “success@onetrust.com”.
We can do the same thing in the new JO builder, however the field defaults to type Text and cannot be set as the sender address of an email step (it could in the old JO builder).
Gainsight support confirmed on May 31st: “we gave an option to add custom field with email type in the Old JO builder but in new JO builder we don't have that feasibility to use a formula field with email type. The only option is having a custom field with email datatype in the source object and have data in it.”
Will the new builder be enhanced to include this feature previously available?
Page 1 / 1
Here’s an idea along those lines, @silasramsden!
Case expressions can *TECHNICALLY* be accomplished in the query, but it’s very convoluted. Essentially, once the case expression is created, it needs to be re-mapped to the original email field through a union to convert it back to an email data type.
This is yet another older (advanced JO) feature that was not brought forward into the new dynamic JO in the interest of removing the ability to do custom field mapping.
Thanks @dayn.johnson, have voted for that feature.
I understand how mapping it back to the original email field would allow you to source the CSM’s email to use, but I don’t think that solves for email addresses hard-coded into a case expression custom field?
E.g. this X from @sarahmiracle post in the thread you linked to:
If CSM is Null, use Email X. If CSM !=Null, use CSM Email
@silasramsden -- so in this case, where we did the case expression to fix a null CSM email, screenshots below.
Bottom line: when you perform a union (original email data-type field and the “fixed” case-expression with the string data-type field), the unified field “inherits the properties of the master field”.
So, by doing a union and essentially merging the string data back to the original data, the string “X@domain” can be used as an email address (and will show up in the email field).
Case expression setup (string data type) on company info pull
Query setup (shows where case expression, union are)
Union (with popup)
Note -- we’re not using this method in our programs. Spoke to our CSM yesterday, and will be continuing to build programs that require a case expression in the old (advanced) JO where we have the ability to map those strings to custom fields in the program setup until there are more updates to the dynamic JO builder. Sounds like this should hopefully be getting passed along to the product team, since there’s more than a bit of angst about not being able to easily do these case expressions.
1: fixing null CSM (string data type)2: query setup
3: Union (with popup)
Thanks @dayn.johnson, I’ve got that working. One (hopefully!) final question. If, in the union below, the same company has a different value in the Sender Email field to the CSM Email field, how is the output of the final Sender Email field decided? It seems random in the testing I’ve been doing.
Example:
CSM email field contains “john.doe@onetrust.com”
Country field = USA
Case expression logic states IF Country = USA, Sender Email = “USA@onetrust.com”, else Sender Email = CSM email
So the union is combining “USA@onetrust.com” and “john.doe@onetrust.com”. What is the field output?
I wish I could help you more @silasramsden! This is one of the reasons we’re waiting. It worked in most cases, but there were still instances where it should have pulled in a string (and didn’t).
We did a second transform on the result of the union prior to the final merge, that might have been what consolidated the list for us… not 100%.
If you ever had a profile with us, there's no need to create another one. Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.