Skip to main content

I’m trying to create HTML code for each record based on a common HTML with placeholders between [ brackets ] that will then be replaced with record data from the corresponding fields, so I have in a long text field something like this:

< h2 style=“text-align: center;”>[ESTADO]< /h2>
< p> < /p>
< table style=“width: 100%; border-collapse: collapse; border-style: none; margin-left: auto; margin-right: auto;” border=“0”>
< tbody>
< tr>
< td style=“width: 25%;”>
< h4>Cliente:
< /td>
< td style=“width: 25%;”>
< p>[NCLIENTE]

And then in a formula field I have

SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE) (that last one is the field name)

Works fine with the [ESTADO] placeholder, but then when I use the [NCLIENTE], either by itself or as a second nested substitute formula I get #ERROR! message.
I have tried changing the placeholder name and/or the field name, I can’t figure out what it is that is causing this, has anyone seen this behaviour before that can help me with this please?
Thanks!

Can you post the formula that you are using with the nested substitutes?

Can you post the formula that you are using with the nested substitutes?


I’ve tried:

SUBSTITUTE(HTML,"[ESTADO]",ESTADO)

SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE)

SUBSTITUTE(SUBSTITUTE(HTML,"[ESTADO]",ESTADO),"[NCLIENTE]",NCLIENTE)

SUBSTITUTE(SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE),"[ESTADO]",ESTADO)

The first one is the only one that works :pensive:

I’ve tried:

SUBSTITUTE(HTML,"[ESTADO]",ESTADO)

SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE)

SUBSTITUTE(SUBSTITUTE(HTML,"[ESTADO]",ESTADO),"[NCLIENTE]",NCLIENTE)

SUBSTITUTE(SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE),"[ESTADO]",ESTADO)

The first one is the only one that works :pensive:


The format of the formulas look fine.
Since SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE) does not work, I suggest that you look into the values and field types for NCLIENTE.

If NCLIENTE is a lookup field, try using a rollup field instead.

The format of the formulas look fine.
Since SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE) does not work, I suggest that you look into the values and field types for NCLIENTE.

If NCLIENTE is a lookup field, try using a rollup field instead.


Thanks, I tried that, but I still get that error :pensive:

Thanks, I tried that, but I still get that error :pensive:


What is the field type for NCLIENTE? Can you post a screen capture?

Does this formula product an error?

SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE & "")

What is the field type for NCLIENTE? Can you post a screen capture?

Does this formula product an error?

SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE & "")

WOW, that did it!!! thanks so much!

works with NCLIENTE field both as lookup and as rollup, so surprising, can you please explain why this works?

WOW, that did it!!! thanks so much!

works with NCLIENTE field both as lookup and as rollup, so surprising, can you please explain why this works?


You NCLIENTE field produces a number, not a text string. The SUBSTITUTE function expects a text string. Concatenation the number with an empty string converts the number into a text string.

It was a bit tricky to figure out because you never said the field type of NCLIENTE.

You NCLIENTE field produces a number, not a text string. The SUBSTITUTE function expects a text string. Concatenation the number with an empty string converts the number into a text string.

It was a bit tricky to figure out because you never said the field type of NCLIENTE.


funny, it’s a lookup for a text field, I still don’t get it, but it worked anyway. Thanks a lot for your help

If it is a lookup of a text field, converting the lookup to a roll up should have worked. That is why I concluded that your field must have been a number field. In any case, the issue is the same. A lookup field is not a text string and needs to be converted to one.

Terms & ConditionsAccessibility statement