
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 27, 2021 08:51 AM
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!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 06:37 AM
What is the field type for NCLIENTE? Can you post a screen capture?
Does this formula product an error?
SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE & "")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 27, 2021 09:02 AM
Can you post the formula that you are using with the nested substitutes?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 27, 2021 09:27 AM
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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 27, 2021 10:46 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 06:10 AM
Thanks, I tried that, but I still get that error :pensive:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 06:37 AM
What is the field type for NCLIENTE? Can you post a screen capture?
Does this formula product an error?
SUBSTITUTE(HTML,"[NCLIENTE]",NCLIENTE & "")

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 06:49 AM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 06:58 AM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 07:03 AM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jan 28, 2021 07:42 AM
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.
