Substitute returns #ERROR! in HTML

[Daniela_Hecht]

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!

[kuovonne]

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

[Daniela_Hecht]

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:

[kuovonne]

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.

[Daniela_Hecht]

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

[kuovonne · Accepted solution]

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

Does this formula product an error?

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

[Daniela_Hecht]

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?

[kuovonne]

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.

[Daniela_Hecht]

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

[kuovonne]

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.