Save the date! Join us on October 16 for our Product Ops launch event. Register here.
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.
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 & "")
Jan 27, 2021 09:02 AM
Can you post the formula that you are using with the nested substitutes?
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:
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.
Jan 28, 2021 06:10 AM
Thanks, I tried that, but I still get that error :pensive:
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 & "")
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?
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.
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
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.