Eliminating Commas Between Rollup Field Items

Alba_Machado2 · ‎Aug 28, 2022

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 · ‎Aug 29, 2022

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 · ‎Aug 29, 2022

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 · ‎Aug 29, 2022

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 · ‎Aug 29, 2022

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 · ‎Aug 30, 2022

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 · ‎Aug 30, 2022

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 · ‎Aug 30, 2022

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:

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.