BUG - Custom List delimiter, in SUBSTITUTE(), not treated the same as default comma delimiter

[WillowMobileSys]

I have an EnumList that needs to use ";" as the delimiter to allow for commas in the list items.  I also need to be able to modify this list by adding additional text AFTER the original list is saved.

I am trying to use the following expression to do this:

"(" &[User] & ") " & SUBSTITUTE([Test2], ";", CONCATENATE(", ("; [User], ") ")) 

However, the resulting list look like this

If I simply modify the expression by replacing the ";" with "," (red commas),  

"(" &[User] & ") " & SUBSTITUTE([Test2], ",", CONCATENATE(", (", [User], ") ")) 

 it seems to work just fine - even including the semi-colon delimiter

HOWEVER....if I add a list item that also includes a comma...NOT so good!!  This shows that list splitting is still being performed using the comma and that should not be the case if the delimiter is defined as something else - which it is in this example.

[Marc_Dillon]

I'm 50/50 on calling this a bug per se. Kind of just an implementation quirk.

I'd say it's always better to explicitly take account for things like type conversions, than to allow the inbuilt systems to make its own best-guess as to what you need.

If you look at the help article on SPLIT(), you'll find this section:

I've found places where this doesn't work as described in the help article. So I never use it. I always manually convert with TEXT(), even if I'll be using SPLIT() on it.

It appears that SUBSTITUTE() is doing a similar thing, where it requires a textual value, and if you don't give it one, it will try to convert it to text. But the inbuilt text conversion that it is using is not the same as using TEXT().

Here are my own tests. 

Definitely something funky going on, and I definitely still recommend explicitly using TEXT() when you need to convert something to text for another expression to use.

-----

As an aside, I think you might also have a typo in your first expression.

Your concat has 3 arguments in it. They should all be separated by a comma. And any semicolons should be within a quoted string. Right?

[WillowMobileSys]

Ah, absolutely correct!!  I was treating this as a List delimiter problem when it's really a SUBSTITUTE() problem.

First, there was a typo,  copied wrong version of expression where I had changed the wrong character.

To resolve my issue I needed to wrap the EnumList column with TEXT() - as you suggested.  There is still some funky stuff going on but it is working expectably now.

I still consider this a bug.  If I write a SUBSTITUTE() function to replace a semi-colon with something, I should NOT see commas being replaced as well.  

I do agree with you that it's something wrong with the internal conversion of a List to text.  Likely, once support for custom List delimiters was added, the internal conversion wasn't properly updated.    

[Marc_Dillon]

---

@WillowMobileSys wrote:

If I write a SUBSTITUTE() function to replace a semi-colon with something, I should NOT see commas being replaced as well.  

---

I don't see that happening anywhere.

[WillowMobileSys]

You're right!  The problem was the reverse - sem-colons, as delimiters, were being replaced when the expression stipulated to replace commas.  But I should re-test since typo was there to make sure I am using the correct expression.

@Marc_Dillon 

EDITED:  Re-ran examples to show the bug, hopefully more clearly.  Semi-colon delimited list is not updated at all (first image).  In an expression which specifies  to replace commas, the semi-colon delimiter is ALSO replaced.  The delimiter is not being honored.  In fact, it seems the delimiter is be treated as a comma.

Since the purpose of a delimiter it to separate list items in a text string -  The SUBSTITUTE() function should honor the specified delimiter OR not support lists at all.

Replace semi-colon in a semi-colon delimited list - no replacement done

Replace comma in a semi-colon delimited list - original list

Result of SUBSTITUTE() to replace comma - semi-colon also replaced

[SkrOYC]

What about:

CONCATENATE(
  "(",
  [User],
  ") ",
  SUBSTITUTE(
    CONCATENATE(
      [Test2]
    ),
    " ,",
    CONCATENATE(
      "; (",
      [User],
      ")"
    )
  )
)

[WillowMobileSys]

---

@WillowMobileSys wrote:

To resolve my issue I needed to wrap the EnumList column with TEXT() - as you suggested.  There is still some funky stuff going on but it is working expectably now.

---

Thanks @SkrOYC !

To be clear I have managed to resolve it by simply wrapping the list with a TEXT() function.  But I am not the kind of person who just settles for the work around - maybe in a corporate setting but this is a retail platform. 

So I am advocating that if the platform allows for lists to be used in a SUBSTITUTE() function along with a custom delimiter, the platform should should do the heavy lifting of properly preparing the list so the substitute can be performed correctly.  To leave it as is means new users going forward will have to figure this issue out - just like I did - over and over and over again.  There is no need for that.  Just fix it!! 

[SkrOYC]

---

@WillowMobileSys wrote:

To resolve my issue I needed to wrap the EnumList column with TEXT() - as you suggested

---

Sorry for not seeing this!

My solution is basically the same, although I prefer CONCATENATE() since I feel like it's more predictable than TEXT() since the later have some magic behaviours based on the column type you throw to it, but it's a matter of taste