Eliminating Commas Between Rollup Field Items

[Alba_Machado2]

Hi All,

I’m in the process of creating a catalog of related books that I can hopefully email out to customers, and, so far, that’s involved creating an Entries rollup field in my Catalogs table that pulls from my Books table using the ARRAYUNIQUE(values) aggregation formula. I did as much formatting as possible in the Books table, adding caps with UPPER() and spaces with \n, but I can’t figure out how to eliminate the comma after each rollup field item. I tried this:

SUBSTITUTE (ARRAYUNIQUE (values), “,”, " + ")

But I kept getting “Sorry, there was a problem saving this field. Please check your formula text.”

I’ll add a screenshot here so you can see what I’m talking about and why I’d want to eliminate the comma . . .

Thanks in advance for your suggestions!

[Alba_Machado2]

Well, now I feel like I’m diffusing a bomb. :grinning_face_with_sweat:

I used your all-together-now full formula solution, but that returned errors. So I opted, instead, to go to the Books table and create a Price Reformatted field using just your penultimate solution:

REGEX_REPLACE(
    IF(
        {Title},
        TRIM({Title}) & "line"
    )
    &
    IF(
        {Author},
        TRIM({Author}) & IF(
            {Year},
            ", " & TRIM({Year}) & "line",
            "line"
        ),
        IF(
            {Year},
            TRIM({Year}) & "line"
        )
    )
    &
    IF(
        {Description},
        TRIM({Description}) & "line"
    )
    &
    IF(
        {Price},
        IF(
            FIND(
                ".",
                {Price} & ""
            ),
            IF(
                REGEX_MATCH(
                    {Price} & "",
                    "\.[^0][^0]"
                ),
                "$" & {Price},
                IF(
                    REGEX_MATCH(
                        {Price} & "",
                        "\.[^0]$"
                    ),
                    "$" & {Price} & "0"
                )
            ),
            "$" & {Price} & ".00"
        )
    ),
    "line",
    "\n"
)

That worked in giving me two digits to the right of the price decimal, but now I’ve got two dollar signs per entry like this:

EDWARD GOREY. The Other Statue. New York: Simon & Schuster, 1968. Stated first printing. A fine, clean and unmarked copy in a fine if price clipped jacket with a protective Mylar plastic cover. First Edition. $$60.00.

I’m scared to go tinkering with your formula here. How do I change it to eliminate the extra dollar sign?

[Ben_Young1]

Alba_Machado2:

Well, now I feel like I’m diffusing a bomb. :grinning_face_with_sweat:

lol I totally get it!
It’s a lot to take in.

It’s probably giving you errors because your field names don’t exactly match, but I could be wrong.
You’ll also want to make sure that when you’re pasting in the formula, the quotation marks aren’t formatted incorrectly into curly quotation marks.

This is a common problem when copying and pasting formulas every once in a while.

---

If that doesn’t quite work, take a look at your other formulas and confirm that you don’t have a floating “$” somewhere that is inserting it into the final text.

[kuovonne]

Alba_Machado2:

So I opted, instead, to go to the Books table and create a Price Reformatted field using just your penultimate solution:

Warning: this formula will replace every occurrence of the word “line” with a line break. This means if your copy has the word “line” in the middle of a paragraph you will have an unexpected line break in the final result.

Instead of concatenating the word "line" at the end up each field, concatenate the new line character "\n". Then also remove the corresponding REGEX_REPLACE.

I also suggest that you format your currency value as a text string in a separate helper formula.

[Alba_Machado2]

Hi kuovonne,

It seems like you may be thinking I’m using another formula Ben mentioned. This is the one I use in the Price Reformatted field and it doesn’t use the word “line” at all . . .

IF(
{Price},
IF(
FIND(
“.”,
{Price} & “”
),
IF(
REGEX_MATCH(
{Price} & “”,
“.[^0][^0]”
),
“$” & {Price},
IF(
REGEX_MATCH(
{Price} & “”,
“.[^0]$”
),
“$” & {Price} & “0”
)
),
“$” & {Price} & “.00”
)
)

Still, while the price comes across perfectly in the Price Reformatted field, it gains an extra dollar sign ($) when it’s pulled into the Entry field formula, which is:

UPPER({Author}) & ". " & {Title} & ". " & {Place} & ": " & {Publisher} & ", " & {Year} & ". " & {Description} & " " & {Book Edition} & “. $” & {Price Reformatted} & “.”

Thanks for the warning, though, kuovonne!

[kuovonne]

Alba_Machado2:

It seems like you may be thinking I’m using another formula Ben mentioned.

Ah, I must have gotten confused. I was looking at the formula following the text I quoted.

Alba_Machado2:

REGEX_REPLACE(
    IF(
        {Title},
        TRIM({Title}) & "line"
    )
    &
    IF(
        {Author},
        TRIM({Author}) & IF(
            {Year},
            ", " & TRIM({Year}) & "line",
            "line"
        ),
…

[Alba_Machado2]

Thank you! Again, you’re right: I did have a floating “$” in the Entry formula. I was so focused on the Price Reformatted formula that I forgot to re-check the Entry formula.

That brings this project to a close, and I’m so grateful for your help. Thanks for explaining everything so clearly!

[Ben_Young1]

I feel bad about bumping this thread, lol.
I spent like 30 minutes trying to remember why I deliberately made the decision to go with the regex solution instead of concatenating it all since we talked about it briefly here:

Combining results of IF() Formulas

Here is a slight variation on Ben’s formula. This style is slightly more compact. I use this pattern a lot. The use of CONCATENATE() removes the need for the & between IF functions. The '\n' new line characters are inserted directly instead of being replaced with a regex. The addition of TRIM() remove any trailing newline character. TRIM(CONCATENATE( IF( {Binding Date Fx}, "Binding Date: " & DATETIME_FORMAT({Binding Date Fx}, 'L') & '\n' ), IF( {Earnest $ Fx}…

The result of this meditation is that I don’t remember :upside_down_face:

But @Alba_Machado2 wanted to give you that thread link so you can take a look at the implementation of the concatenation formula @kuovonne explained.