Transactional email template with CRM Object data - access problem

Develop, Build & Integrate APIs & Integrations
1

Hi,

I try to solve an issue for a custom module as base for a single send email template for transactional emails. So the base idea is to pass on an identifier for a custom object and us on the object stored data to create the email content. Basically its a reminder service for upcomming events.

I tried out three different scenarios:

  1. Query the custom object with a hardcoded query
    Everything works fine
  2. Query the custom object with the id on a field in the module
    I can fetch the object but not access the properties.
  3. Query the custom object by sending the id on API side as
    custom.property.
    I can not even fetch the object here.

The three different versions:

Version 1

{% set query_result_without_string_build = crm_object("fomf_content", "ub_event_id=7008", "ub_event_id, brand, id") %}

Version 2

{% set QueryFromField = "ub_event_id=" + module.test_text | string | trim %}

{% set query_result_string_built_from_field = crm_objects('p_fomf_content', "{{ QueryFromField }}", "ub_event_id, brand, id") %} 

Version 3

{% set QueryFromSendAPI = "ub_event_id=" ~ custom.ub_event_id | string | trim | replace('\n', '') | replace('\r', '') %}

{% set query_result_string_built_from_custom_parameter = crm_objects('p_fomf_content', "{{ QueryFromSendAPI }}", "ub_event_id, brand, id") %}

Then I try to output it:

<h2 style="text-align:left;">
 Query with ub_event_id hardcoded
 </h2>
 <table style="text-align:left;">
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Query string:</b> </td>
 <td style="border: 1px dotted black; width:800px;"><b>"ub_event_id=7008" (hardcoded)</b></td>
 </tr>
 <td style="border: 1px dotted black; width:120px;"><b>Result:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ query_result_without_string_build }}</b></td>
 </tr>
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Property access:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ query_result_without_string_build.brand }}</b></td>
 </tr>
 </table>

 <h2 style="text-align:left;">
 Query with ub_event_id from field in module
 </h2>
 <table style="text-align:left;">
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Query string:</b> </td>
 <td style="border: 1px dotted black; width:800px;"><b>{{ QueryFromField }}</b></td>
 </tr>
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Result:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ query_result_string_built_from_field }}</b></td>
 </tr>
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Property access:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ pquery_result_string_built_from_field.brand }}</b></td> 
 </tr>
 </table>

 <h2 style="text-align:left;">
 Query with ub_event_id from custome property via api
 </h2>
 <table style="text-align:left;">
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Query string:</b> </td>
 <td style="border: 1px dotted black; width:800px;"><b>{{ QueryFromField }}</b></td>
 </tr>
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Result:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ query_result_string_built_from_custom_parameter }}</b></td>
 </tr>
 <tr>
 <td style="border: 1px dotted black; width:120px;"><b>Property access:</b> </td>
 <td style="border: 1px dotted black;"><b>{{ query_result_string_built_from_custom_parameter.brand }}</b></td>
 </tr>
 </table>

Which results in the following output when I send the email via postman.

alanger_fomf_0-1716895493057.png
alanger_fomf_0-1716895493057.png611×588 31 KB

And I have no idea why the behavior is different on the three versions. Any idea?
Thx in advance.
Andreas

2

Hi @alanger_fomf ,

Is there a specific reason why you are using `crm_object` in your first example and `crm_objects` in your second and third example?

3

Hi Teun,

no not really this was just one attempt to find another solution. The issue is the same when I change it to:

{% set query_result_string_built_from_field = crm_object('p_fomf_content', "{{ QueryFromField }}", "ub_event_id, brand, id") %} 

{% set query_result_string_built_from_custom_parameter = crm_object('p_fomf_content', "{{ QueryFromSendAPI }}", "ub_event_id, brand, id") %}
4

Thank you for the explanation.

Could you try your function like this:

{% set query_result_string_built_from_field = crm_object('p_fomf_content', QueryFromField|string, "ub_event_id, brand, id") %} 

{% set query_result_string_built_from_custom_parameter = crm_object('p_fomf_content', QueryFromSendAPI|string, "ub_event_id, brand, id") %}

I’ve had quite a few issues in the past using the crm_object and

crm_associations functions, so it might not make sense, but I got it working this way.

5

I had already a similar thought. I tried out different conversions like this

{% set QueryFromField = "ub_event_id=" ~ module.test_text | string | trim %}
{% set QueryFromSendAPI = "ub_event_id=" ~ custom.ub_event_id | string | trim | replace('\n', '') | replace('\r', '') %}

Now I tried these two

{% set QueryFromField = "ub_event_id=" ~ module.test_text %}
{% set QueryFromSendAPI = "ub_event_id=" ~ custom.ub_event_id %}

in combination with you suggestion

{% set query_result_string_built_from_field = crm_object('p_fomf_content', QueryFromField|string, "ub_event_id, brand, id") %} 
{% set query_result_string_built_from_custom_parameter = crm_object('p_fomf_content', QueryFromSendAPI|string, "ub_event_id, brand, id") %}

Unfortunately it did not work in both versions.

6

It’s an odd issue.

Is the ‘7008’ the HubSpot object ID or another property you are using to query the record? If it is the record ID, can you pass it without the query?

Also, can you pass the 4th parameter in the function for formatting? That has fixed an issue for me in the past:

crm_object("p_fomf_content", QueryFromSendAPI, "ub_event_id,brand,id", false)

I’m just throwing with possible fixes, as it isn’t making any sense.
Also, could you add double quotes around the object_type? That is the only difference between version 1 and version 2 and 3.

7

Yes it is. 7008 ist just a different id but not the hs record id. I will know the record id on the requesting platform. But this will be achieved later this year - so for now I need to go with the event id.
When I pass on the 4th parameter I get uppercase result on the first result and still nothing on the second result. Quotes do not bring a change.
The issue is somehow based in the query string. When I compare the different query strings the result is:

{% if QueryFromField == QueryFromSendAPI %}
--> NOT EQUAL
{% if QueryFromField == "ub_event_id=7008" %} 
--> EQUAL
{% if QueryFromSendAPI == "ub_event_id=7008" %} 
--> NOT EQUAL
8

I just got a possible reason: the value from the custom.template_ub_event_id has not yet been replaced when the crm_object function is called. When you then send the mail, the value is replaced, but this happens later.
To verify this, we have output both values as a base64 string and then calculated them back:

Buffer.from(‘dWJfZXZlbnRfaWQ9NzAwOA==’, ‘base64’).toString(‘utf-8’)
‘ub_event_id=7008’
Buffer.from(‘dWJfZXZlbnRfaWQ9e3tjdXN0b20udGVtcGxhdGVfdWJfZXZlbnRfaWR9fQ==’, ‘base64’).toString(‘utf-8’)
‘ub_event_id={{custom.template_ub_event_id}}’

Any suggestions how to solve the issue based on the new infos?

Regards,
Andreas