Interpolate field reference when looking up from a table

We have a process with an email step where the email body must vary by client. We use a configuration table like this:

The form fields companyname and manager-name are assigned values when the workflow is started.

I have a field formula step like this:
result = fields["lookup-client--ic-email-intro-body"]
The output field name is interpolated-ic-body.
I then configure my email step to refer to {{interpolated-ic-body}}.

In the email step, when viewed in Catalytic, the field references are interpolated:

However, in the actual email, the field references are not interpolated:

Why is this?

Best Answers

  • Sean_510793
    Sean_510793 Posts: 69 admin
    Answer ✓

    Let me post the code here:

    var str = fields["web-form-step--appstep--params--subject"];
    const regex = /\{\{.*\}\}/gm;
    if (str) {
      var handlebars = str.match(regex);
      if (handlebars) {
        var i;
        for (i = 0; i < handlebars.length; i++) {
          var fieldName = handlebars[i].substring(2, handlebars[i].length - 2);      
          if (fieldName.startsWith("run.")) {
            fieldName = fieldName.substring(4, fieldName.length);
            str = str.replace(handlebars[i],run[fieldName]);
          } else {  
            str = str.replace(handlebars[i],fields[fieldName]);
          }
        }
      }
      result = str;
    } else {
      result = " ";
    }
    

Answers

  • Sean_510793
    Sean_510793 Posts: 69 admin

    Hi Tom, I bumped into something similar when putting together the CMS. It is because body field is interpolated and replaced with a string. The fields in the email body are strings and not interpolated. Luckily, it’s still easy to manually interpolate as shown in this response => https://community.pushbot.com/discussion/1452/resolve-nested-handlebars

    Put that script into your field formula to interpolate the result variable. Let me know if that works.

  • Hi @Sean_510793, glad to hear there's a potential solution! Unfortunately, that link doesn't work for me -- I get this "Permission Problem" error. Can you please take a look and re-send? Thanks!

  • That worked. Thanks, @Sean_510793!

  • @Sean_510793 Your code is not always correctly interpolating some of the fields in str. I believe I've narrowed down the issue to cases where str contains a handlebar reference two dashes in it, e.g., {{lookup-client--background-vendor}}.

    This will be quite common for our use case. Is there any way to modify your code so that it properly handles field references like this?

    For example, when
    str = 'Please initiate the background check for {{ic-name}} with vendor {{lookup-client--background-vendor}}.'
    My result is
    Please initiate the background check for undefined.
    I believe it's trying to erroneously interpolate ic-name}} with vendor {{lookup-client--background-vendor, as if that were the name of the field.

  • Tom_944137
    Tom_944137 Posts: 40
    edited November 2020

    @Sean_510793 I take that back about the dashes. The culprit seems to be when there is more than one {{ }} reference in one paragraph.

    Is there a way to modify the REGEX expression so that it picks up each set of {{ }} individually?

    It looks like I can add a U to end of the REGEX expression (i.e., /\{\{.*\}\}/gmU) making the search "non-greedy." This now seems to work.

    Any thoughts on this? Do you think it will capture all cases now?

    Researching more...the REGEX tester I'm using allows /U. I'm not sure Javascript does. It looks like I must use ? after the .*, making my REGEX expression /\{\{.*?\}\}/gm

  • Tom_944137
    Tom_944137 Posts: 40
    edited November 2020

    Thanks, @Sean_510793.

    Another complexity to our use case: we need to use {{#if field-name}} {{/if}} statements in some of the looked up text. My guess is that these will not play nice with your code. Any idea how we could work in some conditional statements into the interpolations we're doing?

    If more context helps: We have an intro email that goes out to outline the onboarding process. Some folks require a background check, others don't. When a background check is required, we want to display a certain paragraph in that email. Otherwise, we want to omit it. Right now, we have the email body hardcoded, so {{#if field-name}} {{/if}} works fine. But once we migrate to the new lookup method, I don't think we'll be able to embed these if statements in the configuration table that we're reference in the lookup.

    Any thoughts?

  • Hey @Tom_944137 !
    So I think if I understand this correctly, that code as-is won't work well with any {{#if field-name}}{{/if}} stuff, just as you said.

    I think it would just take a few minor tweaks to the code to get it in the right format to pull out/interpolate the field like that though. Basically just two changes:
    1) adding in an additional check for if the handlebar reference has "#if " in it, then replace it with the field (if it exists)
    2) if the handlebar reference is the end to an if statement (e.g. {{/if}}), ignore it

    You should be able to handle those with two extra else if statements:

         else if (fieldName.startsWith("#if")) {
            fieldName = fieldName.substring(4, fieldName.length);
            if (fields[fieldName]) {
              str = str.replace(handlebars[i],fields[fieldName]);
            }
          }
          else if (fieldName.startsWith("/if")) {
            continue;
          }
    

    So then your final resulting code should be:

    var str = fields["web-form-step--appstep--params--subject"];
    const regex = /\{\{.*?\}\}/gm;
    if (str) {
      var handlebars = str.match(regex);
      if (handlebars) {
        var i;
        for (i = 0; i < handlebars.length; i++) {
          var fieldName = handlebars[i].substring(2, handlebars[i].length - 2);      
          if (fieldName.startsWith("run.")) {
            fieldName = fieldName.substring(4, fieldName.length);
            str = str.replace(handlebars[i],run[fieldName]);
          } 
          else if (fieldName.startsWith("#if")) {
            fieldName = fieldName.substring(4, fieldName.length);
            if (fields[fieldName]) {
              str = str.replace(handlebars[i],fields[fieldName]);
            }
          }
          else if (fieldName.startsWith("/if")) {
            continue;
          }
          else {  
            str = str.replace(handlebars[i],fields[fieldName]);
          }
        }
      }
      result = str;
    } else {
      result = " ";
    }
    

    This should work if you only have if statements, not if there are any else statements. If you want to include those, you can add an additional parameter in the if statements like:

    else if (fieldName.startsWith("#if") || fieldName.startsWith("else")) {
    

    Hope this helps, let us know if this works!

  • Thanks @Kevin_594290 for taking a look. This isn't doing quite what I need.

    I want to be able to include something like {{#if my-field}}This text only shows if my-field has a value.{{/if}} in str, and have it only display when my-field has a value. Just like we would conditionally display text in an email body.

    It seems like your code just replaces the contents of {{#if my-field}} with the value of my-field, which is not what I'm looking to do.

  • Brilliant! Thank you so much, @Kevin_594290.

  • Sean_510793
    Sean_510793 Posts: 69 admin

    This is awesome @Kevin_594290 !

  • Tom_944137
    Tom_944137 Posts: 40
    edited November 2020

    One last complexity that I'm wondering if @Kevin_594290 or @Sean_510793 have any ideas on. Some of our looked up text will have {{ }} references within the {{#if}} {{/if}} tag. For example:

    {{#if payrate}}
    **Pay Rate:** ${{payrate}}
    **Markup Factor:** {{lookup-client--markup-factor}}
    **Bill Rate:** ${{calculated-bill-rate--output}}
    {{/if}}
    

    Because the code above assumes that the {{/if}} is the next handlebar reference following the {{#if}}, this type of structure won't work. Is there a way to modify the code further to account for cases like this?

  • @Kevin_594290 / @Sean_510793 -- I think I came up with something that's a bit more robust for my use case:

    const regex = /(\{\{#if[\s\S]*?\/if\}\}|\{\{.*?\}\})/gm;
    function interpolateFieldsInString(str) {
      var handlebars = str.match(regex);
      if (handlebars) {
        var i;
        for (i = 0; i < handlebars.length; i++) {
          if (handlebars[i].startsWith("{{#if")) {
            var testField = handlebars[i].substring(6,handlebars[i].indexOf('}}',6));
            if (fields[testField]) {
              var innerStr = handlebars[i].substring(handlebars[i].indexOf('}}') + 2,handlebars[i].length - 7);
              str = str.replace(handlebars[i],interpolateFieldsInString(innerStr));
            } else {
              str = str.replace(handlebars[i],"")
            }
          } else if (handlebars[i].startsWith("{{run.")) {
            var fieldName = handlebars[i].substring(6,handlebars[i].length - 2);
            if (run[fieldName]) {
              str = str.replace(handlebars[i],run[fieldName])
            } else {
              str = str.replace(handlebars[i],"")
            }
          } else {
            var fieldName = handlebars[i].substring(2,handlebars[i].length - 2);
            if (fields[fieldName]) {
              str = str.replace(handlebars[i],fields[fieldName])
            } else {
              str = str.replace(handlebars[i],"")
            }
          }
        }
      }
      return str;
    }
    
    var strIn= fields['lookup-client--task-body-vms-1'];
    result = interpolateFieldsInString(strIn);
    

    I've decided to define a function so that I can use it recursively to populate fields within {{#if }} {{/if}} tags. Note that this will not handle {{else}}.