Combine two fields as part of a response in Translation Map

JDunn · May 24, 2024, 12:20pm

Hi,
I've spent way, way too long trying to figure this out and not getting anywhere, reading up on VTL etc, but still stumped.

Situation: We have a series of fields in a Data Action response. I want to add another "custom" field which is a combination of them both, i.e. we return "Name" and "AccountName" and I just want one field "Name_and_AccountName" which would be in format: "Bill Gates (Microsoft)".

I thought this would be straightforward to do in the response but I've tried as much as I can think of. Is there a way to do this?

This is what I have, at the moment it doesn't do what I'd like which is to essentially stuff Name and AccountName into one field.

Any guidance to stop me pulling my hair out would be welcomed.

{
  "translationMap": {
    "Name": "$.records.*.Name",
    "Phone": "$.records.*.Phone",
    "Email": "$.records.*.Email",
    "Contact_Locale__c": "$.records.*.Contact_Locale__c",
    "Contact_Role__c": "$.records.*.Contact_Role__C.*",
    "Okta_ID__c": "$.records.*.Okta_ID__C",
    "Preferred_Language__c": "$.records.*.Preferred_Language__c",
    "Id": "$.records.*.Id",
    "AccountId": "$.records.*.AccountId",
    "AccountName": "$.records.*.Account.Name",
    "AccountGMS_Dealer_Customer__c": "$.records.*.Account.GMS_Dealer_Customer__c",
    "AccountAccountNumber": "$.records.*.Account.AccountNumber",
    "AccountPayment_Method__c": "$.records.*.Account.Payment_Method__c",
    "AccountSupply_order_instructions__c": "$.records.*.Account.Supply_order_instructions__c",
    "Phone_Extension__c": "$.records.*.Phone_Extension__c",
    "Test": "#set( $Test = \"$.records.*.Account.Name $.records.*.Name\" )"
  },
  "translationMapDefaults": {
    "Test": "\"Test default\""
  },
  "successTemplate": "{\r\n \"Name\": ${Name},\"Phone\": ${Phone} , \"Email\": ${Email} , \"Contact_Locale__c\": ${Contact_Locale__c} , \"Contact_Role__c\": ${Contact_Role__c} , \"Okta_ID__c\": ${Okta_ID__c} , \"Preferred_Language__c\": ${Preferred_Language__c} , \"Id\": ${Id} , \"AccountId\": ${AccountId} , \"Account.Name\": ${AccountName} , \"AccountGMS_Dealer_Customer__c\": ${AccountGMS_Dealer_Customer__c} , \"AccountAccountNumber\": ${AccountAccountNumber} ,\"AccountPayment_Method__c\": ${AccountPayment_Method__c} ,\"AccountSupply_order_instructions__c\": ${AccountSupply_order_instructions__c} ,\"Phone_Extension__c\": ${Phone_Extension__c},\"Test\": ${Test} }"
}

Jason_Mathison · May 24, 2024, 12:48pm

I see that your translation map for both "Name" and "AccountName" include '*'s in them, which means that the result is going to be an array of values, even if there is only one value. That could mean that you are going to want to use the firstFromArray macro to get pull the first value out of the array:

If that doesn't get this solved for you please post an example response from your endpoint, with anything sensitive redacted so that others can help you.

--Jason

JDunn · May 24, 2024, 1:21pm

Thanks Jason,

Your are right, it is an array, although generally with one value. The other fields all work fine, I just can't figure out the right way to merge Name and Account Name. Using firstFromArray allows me to get the first value for one of the fields but I'm not sure how to concatenate them - nor could I work out how to use those Java string method macros properly.

This is an example response:

{
  "Email": [
    "james.dunn@XXXXX"
  ],
  "AccountId": [
    "00XXXXXXXXX"
  ],
  "Name": [
    "James Dunn"
  ],
  "AccountSupply_order_instructions__c": [
    "Test SOI"
  ],
  "AccountPayment_Method__c": [
    "XXXXX"
  ],
  "Contact_Locale__c": [
    "en_US"
  ],
  "Phone_Extension__c": [
    "1234"
  ],
  "Test": "Test default", // This is the custom field I was messing about trying to fill, it is pulling the default value from translationMapDefaults at the moment.
  "Phone": [
    "12XXXXXXX"
  ],
  "Preferred_Language__c": [
    "English"
  ],
  "Account.Name": [
    "XXXX EQUIPMENT"
  ],
  "Id": [
    "003D60XXXXXX"
  ],
  "AccountAccountNumber": [
    "0018XXXXXX"
  ],
  "AccountGMS_Dealer_Customer__c": [
    true
  ]
}

Jason_Mathison · May 24, 2024, 1:44pm

Can you post the response from the endpoint (Which should be in the "execute" step of test mode)?

In general you should be able to combine multiple variables in your response template by doing something like

"${val1} ${val2}"

--Jason

JDunn · May 24, 2024, 1:52pm

Ah sorry, yes, this is that:

{
  "totalSize": 1,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v44.0/s"
      },
      "Id": "003D6xxxxx",
      "AccountId": "001D6xxxxxxx",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v44.0/"
        },
        "Name": "xxxxxx equipment",
        "GMS_Dealer_Customer__c": true,
        "AccountNumber": "001xxxxxx",
        "Payment_Method__c": "G",
        "Supply_order_instructions__c": "Test SOI"
      },
      "Name": "James Dunn",
      "Phone": "1xxxxxxx",
      "Phone_Extension__c": "1234",
      "Email": "james.dunn@xxxxxxx",
      "Contact_Locale__c": "en_US",
      "Contact_Role__c": "End User",
      "Okta_ID__c": "xxxxxxxxxxxxxxxxxxxx",
      "Preferred_Language__c": "English"
    }
  ]
}

Thanh_Vo · May 28, 2024, 11:20pm

You could do something like this:

$.concat($.records[0].Name," - ",$.records[0].Account.Name)

This would add a space and dash in between Name and Account Name, so it'd be "James Dunn - XXXX EQUIPMENT" in your example.

I had a hard time figuring out how to add parentheses since they're reserved characters, but that might work if you figure it out.

JDunn · May 29, 2024, 8:58am

Thanks Thanh, this is great, concat has worked

I also can't make it work with parentheses (or even square brackets), none of the escape characters seem to work... If anyone knows that trick, I'd love to know, but for now I'll use " - ".

Thanks!

JDunn · May 29, 2024, 4:52pm

@Thanh_Vo Do you know if this sort of thing is documented anywhere? We're actually having an issue with it where it's merging multiple values when returning an array.

So it's becoming like:

JamesDunnJamesDunn - xyzEquipmentxyzEquipment

Jason_Mathison · June 3, 2024, 3:16am

I was completely unaware of the concat function, thanks Thanh_Vo!

I was able to get characters like ( or ] into the result by specifying the unicode character, like this:

    "Test": "$.concat(\"\\u0028\", $.records[0].Name,\" - \",$.records[0].Account.Name, \"\\u005D\")"

My source of the codes was: List of Unicode characters - Wikipedia

--Jason

Jason_Mathison · June 3, 2024, 3:20am

Can you give an example of the response from the endpoint that is causing this problem?

JDunn · June 4, 2024, 1:22pm

Hi Jason,

This is my response JSON (with translation map):

{
  "translationMap": {
    "Name": "$.records.*.Name",
    "Phone": "$.records.*.Phone",
    "Email": "$.records.*.Email",
    "Contact_Locale__c": "$.records.*.Contact_Locale__c",
    "Contact_Role__c": "$.records.*.Contact_Role__c",
    "Okta_ID__c": "$.records.*.Okta_ID__c",
    "Preferred_Language__c": "$.records.*.Preferred_Language__c",
    "Id": "$.records.*.Id",
    "AccountId": "$.records.*.AccountId",
    "AccountName": "$.records.*.Account.Name",
    "AccountGMS_Dealer_Customer__c": "$.records.*.Account.GMS_Dealer_Customer__c",
    "AccountAccountNumber": "$.records.*.Account.AccountNumber",
    "AccountPayment_Method__c": "$.records.*.Account.Payment_Method__c",
    "AccountSupply_order_instructions__c": "$.records.*.Account.Supply_order_instructions__c",
    "Phone_Extension__c": "$.records.*.Phone_Extension__c",
    "ContactAndCompanyName": "$.*.concat($.records.*.Name,\" - \",$.records.*.Account.Name)",
    "ContactCount": "$.records.size()"
  },
  "translationMapDefaults": {
    "Contact_Locale__c": "[\"\"]",
    "ContactCount": "1"
  },
  "successTemplate": " #set ( $quo = $esc.quote ) #set($newNull = \"${quo}EMPTY${quo}\" ) {\r\n \"Name\": ${Name},\"Phone\": ${Phone} , \"Email\": ${Email} , \"Contact_Locale__c\": ${Contact_Locale__c.replace(\"null\", $newNull)}, \"Contact_Role__c\": ${Contact_Role__c} , \"Okta_ID__c\": ${Okta_ID__c.replace(\"null\", $newNull)} , \"Preferred_Language__c\": ${Preferred_Language__c} , \"Id\": ${Id} , \"AccountId\": ${AccountId} , \"AccountName\": ${AccountName} , \"AccountGMS_Dealer_Customer__c\": ${AccountGMS_Dealer_Customer__c} , \"AccountAccountNumber\": ${AccountAccountNumber} ,\"AccountPayment_Method__c\": ${AccountPayment_Method__c} ,\"AccountSupply_order_instructions__c\": ${AccountSupply_order_instructions__c} ,\"Phone_Extension__c\": ${Phone_Extension__c},\"ContactAndCompanyName\": ${ContactAndCompanyName} ,\"ContactCount\": ${ContactCount}}"
}

Here is the raw response from the API:

{
  "totalSize": 1,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v44.0/sobjects/Contact/003D60000"
      },
      "Id": "003D60000",
      "AccountId": "001D600001k",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v44.0/sobjects/Account/001D600001k"
        },
        "Name": "XXXXXXX EQUIPMENT",
        "GMS_Dealer_Customer__c": true,
        "AccountNumber": "0018XXXXX",
        "Payment_Method__c": "G-Purchase Power",
        "Supply_order_instructions__c": "Test SOI"
      },
      "Name": "James Dunn",
      "Phone": "1XXXXXXX",
      "Phone_Extension__c": "1234",
      "Email": "james.dunn@xxxxxxx.com",
      "Contact_Locale__c": "en_US",
      "Contact_Role__c": "End User",
      "Okta_ID__c": "xxxxxxxxxxxxxxxxxxxx",
      "Preferred_Language__c": "English"
    }
  ]
}

And then here is the response after going through translation. We were adjusting the contract to try and get the concatenated field into an array, and now it seems to give the "ContactAndCompanyName" three times, I'm not certain why it does it three times though. All the fields are in an array now at least.

{
  "Name": [
    "James Dunn"
  ],
  "Phone": [
    "1XXXXXXX"
  ],
  "Email": [
    "james.dunn@XXXXXXX.com"
  ],
  "Contact_Locale__c": [
    "en_US"
  ],
  "Contact_Role__c": [
    "End User"
  ],
  "Okta_ID__c": [
    "xxxxxxxxxxxxxxxxxxxx"
  ],
  "Preferred_Language__c": [
    "English"
  ],
  "Id": [
    "003D60000"
  ],
  "AccountId": [
    "001D600001k"
  ],
  "AccountName": [
    "XXXXXX EQUIPMENT"
  ],
  "AccountGMS_Dealer_Customer__c": [
    true
  ],
  "AccountAccountNumber": [
    "00184XXXXX"
  ],
  "AccountPayment_Method__c": [
    "G-Purchase Power"
  ],
  "AccountSupply_order_instructions__c": [
    "Test SOI"
  ],
  "Phone_Extension__c": [
    "1234"
  ],
  "ContactAndCompanyName": [
    "James Dunn - XXXXX EQUIPMENT",
    "James Dunn - XXXXX EQUIPMENT",
    "James Dunn - XXXXX EQUIPMENT"
  ],
  "ContactCount": 1
}

If I generate a response where there are multiple contacts & companies returned, it's even stranger:

Raw response:

{
  "totalSize": 2,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v44.0/sobjects/Contact/003D600001"
      },
      "Id": "003D600001",
      "AccountId": "001D600001",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v44.0/sobjects/Account/001D600001k"
        },
        "Name": "XXXXXX EQUIPMENT",
        "GMS_Dealer_Customer__c": true,
        "AccountNumber": "001xxxxxxx",
        "Payment_Method__c": "G-Purchase Power",
        "Supply_order_instructions__c": "Test SOI"
      },
      "Name": "First Name",
      "Phone": "1234567890",
      "Phone_Extension__c": "1234",
      "Email": "XXXXYZ@test.com",
      "Contact_Locale__c": "da_DK",
      "Contact_Role__c": "Economic Buyer",
      "Okta_ID__c": "12456",
      "Preferred_Language__c": "English"
    },
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v44.0/sobjects/Contact/003D60000X"
      },
      "Id": "003D600001J",
      "AccountId": "001D600001j",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v44.0/sobjects/Account/001D60002"
        },
        "Name": "XXXXXXX Print Shop",
        "PO_Required__c": "Y",
        "GMS_Dealer_Customer__c": false,
        "AccountNumber": "0018119752",
        "Payment_Method__c": "C-Check"
      },
      "Name": "XXXXX",
      "Phone": "920XXXXX",
      "Phone_Extension__c": "1234",
      "Email": "c@test.com",
      "Contact_Locale__c": "da_DK",
      "Contact_Role__c": "Decision Influencer",
      "Okta_ID__c": "12",
      "Preferred_Language__c": "English"
    }
  ]
}

And response after translation; you can see the concatenated field is merging the Contact & Account name values from both contacts that are returned:

{
  "Name": [
    "Full Name 1",
    "Full Name 2"
  ],
  "Phone": [
    "1234567890",
    "9xxxxxxxx"
  ],
  "Email": [
    "xxxxx@test.com",
    "xxxx@test.com"
  ],
  "Contact_Locale__c": [
    "da_DK",
    "da_DK"
  ],
  "Contact_Role__c": [
    "Economic Buyer",
    "Decision Influencer"
  ],
  "Okta_ID__c": [
    "12456",
    "12"
  ],
  "Preferred_Language__c": [
    "English",
    "English"
  ],
  "Id": [
    "003D600001",
    "003D600001J"
  ],
  "AccountId": [
    "001D600001",
    "001D60002"
  ],
  "AccountName": [
    "XXXXXX EQUIPMENT",
    "XXXXXX Print Shop"
  ],
  "AccountGMS_Dealer_Customer__c": [
    true,
    false
  ],
  "AccountAccountNumber": [
    "00184XXXX",
    "00181XXXX"
  ],
  "AccountPayment_Method__c": [
    "G-Purchase Power",
    "C-Check"
  ],
  "AccountSupply_order_instructions__c": [
    "Test SOI",
    "EMPTY"
  ],
  "Phone_Extension__c": [
    "1234",
    "1234"
  ],
  "ContactAndCompanyName": [
    "FullName1FullName2 - XXXXX EQUIPMENTXXXXX Print Shop",
    "FullName1FullName2 - XXXXX EQUIPMENTXXXXX Print Shop",
    "FullName1FullName2 - XXXXX EQUIPMENTXXXXX Print Shop",
  ],
  "ContactCount": 2
}

Sorry, this is a really long reply; I really appreciate any help anyone can give. Thank you.

Jerome.Saint-Marc · June 4, 2024, 3:31pm

Hello,

I unfortunately don't have an answer for you on how to make this work. I think the fact that you want to manage an array of records with an unknown number of records (in advance) probably makes it too complex for JsonPath and Velocity macros.
A Genesys Cloud Lambda Data Actions Integration or the future Genesys Functions are probably better for this manipulation.

But I can explain why you get these values in your "ContactAndCompanyName".
Reading your translation map, I see: "ContactAndCompanyName": "$.*.concat($.records.*.Name,\" - \",$.records.*.Account.Name)"

The * in $.*.concat (JSONPath expression) means that you are going to execute ($.records.*.Name,\" - \",$.records.*.Account.Name) 3 times.
This because you have 3 attributes/properties at the root of your response - i.e. totalSize, done, and records

And each time, you execute $.records.*.Name,\" - \",$.records.*.Account.Name, this means that you are taking a list of all records Names (not just 1 record - because of the * - JSONPath Expression), that you concatenate with -, and then with the list of all records Account.Names (again the * - not just 1 record).

Hope this clarifies at least the result you get.

Regards,

system · July 5, 2024, 3:31pm

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.