Help

Re: Eliminating Commas Between Rollup Field Items

Solved
Jump to Solution
3188 9
cancel
Showing results for 
Search instead for 
Did you mean: 
Alba_Machado2
7 - App Architect
7 - App Architect

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 . . .

Catalog Entries Rollup Field

Thanks in advance for your suggestions!

1 Solution

Accepted Solutions

Okay, let’s talk about this.

This is actually behaving as it should!

If you’re curious to read a bit about why this happens, you can read below.
Alternatively, if you want to skip to the solution, click here.


I could type out massive chunks to explain why this is the case, but I’ll provide you with some context.

Computers and programs interpret data in ‘types’. We literally call them data types.

For our purposes, here’s what we care about with Airtable’s formulas.

  1. Booleans (True/False)

  2. Strings (Text)

  3. Numbers

  4. Undefined (Literally nothing. Like forgetting the field even existed).

  5. Date/Time (Not actually a data type, but for the sake of Airtable, we’ll toss it in).


Every field type in Airtable holds a certain type of data. (With the exceptions being the linked record and attachment fields, which use a type called an object.)

The currency field accepts numbers.

Despite being able to format data into a readable currency, Airtable only understands and interprets data in that field as being a number.

We see $20.00, but from Airtable’s perspective, it’s just 20.

This is, of course, quite simple.

However, it gets frustrating when we see $20.90 because Airtable sees 20.9.

So, how can we change this?

Well, since Airtable is looking at the currency field as a number (and thus doesn’t care about any formatting), we can take the number value and force Airtable to treat it as text.


Here’s a formula that will format numbers into a USD text format.

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

Here’s what the returned behavior looks like:

image

With this, you can correctly format numbers into a USD format within a text string inside a formula.

Here’s what the fully formatted, final output would look like using the original formula I posted:

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"
)

image

See Solution in Thread

16 Replies 16

Hey @Alba_Machado2!

Go ahead and try this formula and let me know if it works!

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

Hi Ben!

Thanks for the suggestion, but, nope, it doesn’t work. It returns an ERROR to me. :confused:

Okay, one sec.

There isn’t a problem I cannot solve.

Boom.

IF(
    values,
    ARRAYJOIN(
        ARRAYUNIQUE(
            values
        ),
        ' + '
    )
)

image

Also, I just saw this:

You can also make these transformations within the rollup field without doing much in your other table.

Not too sure how you’re using the UPPER function, but here’s a quick example.

Here are my records with the text fields:

image

Here’s the table where I’m looking them up:

image

If I use this formula in the rollup field, I can automatically format and separate everything however I’d like:

IF(
    values,
    ARRAYJOIN(
        ARRAYUNIQUE(
            values
        ),
        '\n' & '\n'
    )
)

image

I apologize for the quick response!
I’m on my laptop and struggling without my main monitors.

Let me know if you have more that you’d like to do, or if you’re having trouble with any of it!

You did it! There really isn’t a problem you can’t solve! :open_mouth:

I was going to post new topics for a couple of other problems with this particular project, but since you offered so nicely…

For some reason, the Year is coming up as {Year} in the Entries rollup field. In the Books table, the Year field will say 1901. But in the Catalogs table’s Entries rollup field, I see {Year} instead.

And the one last problem is that the Price sometimes drops a digit. So, like, $31.50 will become $31.5.

Other than that, I’m pretty happy with the way this is working out and thrilled that you figured out how to solve the random comma problem. Thank you! Thank you! Thank you! :slightly_smiling_face:

Okay, there are a few things to cover.

First, let’s talk about the Year field.


Let’s say this is your Books table:

image

We have the original text field that we were working with before.
Here, it’s the Description field.
I also added a Year field to hold the year of publication.

Let’s hop into our Catalogs table.
Here, we have the Description rollup field I previously walked you through.

image

We now want to get our publication year information into this rollup.
When trying to do this, we quickly encounter a huge deficiency in Airtable’s rollup & formula implementation.
When using Airtable’s rollups (or formulas that reference a linked record field), you cannot reference additional fields from a linked record.

So, if you’re trying to do this:

IF(
    AND(
        values,
        {Year}
    ARRAYJOIN(
        ARRAYUNIQUE(
            values
        ),
        '\n' & '\n'
    )
)

It won’t work, even if you’re referencing a valid field in another table. As far as Airtable is concerned, the object you’re trying to reference is just a text string.

Now we’re left sitting here asking ourselves what we can do next.
I think that are two ways to approach this scenario.

The first method is the way I would personally solve this.

After writing out this first method and starting the second one, I realized just how terrible the second method is. With that in mind, I’m not even going to try and describe the second method.
Leaving this info in to let you (and others) know there are a few ways to approach this puzzle. Not all solutions are worth your time.


Method 1: Transform Before You Look

You’ve actually done this, albeit in a slightly different way.

When using a rollup field, you take specific data from another source and then format it using formulas.

As we’ve just come to find, sometimes we don’t have as much flexibility as we need when doing this.

The solution with this method is to do all of our formatting before it ever touches our rollup.
This allows us to leverage all of the fields on the record and format it however we’d like without worrying about the limitations of the rollup field.

Here’s how…

This might vary for your requirements, but it’ll serve as an example.
To define what our final goal is, let’s say this will be our formatting goal:

{Title}
{Author}, {Year}
{Description}

It should look like this:

Native Son
Richard Wright, 1940
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.
Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.

Here’s a formula that will meet these requirements:

{Title}
& "\n" &
{Author} & ", " & {Year}
& "\n" &
{Description}

And here’s what that looks like:

image

Okay, dope.
We do, however, have a new problem.
What happens if one or more of these fields are blank?

If I delete the Title and Author fields, it’ll give us this:

image

If you’re like me and hate this behavior, we’ll leverage nesting and a few tricks to account for these situations.

One way to do this is to create placeholders that will fill in for blank values.
You’ll see this in this formula:

IF(
    {Title},
    {Title},
    "Missing Title ❗"
)
& "\n" &
IF(
    {Author},
    {Author},
    "Missing Author ❗" & ", "
)
&
IF(
    {Year},
    {Year},
    "Missing Year ❗"
)
& "\n" &
IF(
    {Description},
    {Description}
)

Here’s the formula field with this change:

image

This makes life a little bit easier.
The second way would be to make it fully dynamic. This means that the field will only fill in information it has and nothing else.

This is my preferred way since I am a bit picky about formatting.

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"
    ),
    "line",
    "\n"
)

Here are some examples of the behavior that produces:

image
image
image


Okay, cool.
So what now?

If you hop back into the Catalogs table and change the rollup field we created to point to the new formula field, it will provide you with the formatting we specified. Hence the method’s name, Transform Before You Look.

Here’s our end result:

image

I trimmed some of the sample data to show a more reasonable example. Here’s what it (reasonably) looks like:

image

As you can see, even if data is missing, everything remains neatly formatted and clean.


I will type out the answer to the second part of your question regarding currency formatting.
Gimme a bit to get it out.

Edit:

8/19/2022: I realized that I didn’t quite like how the original formula worked. I changed the behavior of the formula to more accurately account for new line characters using a regex solution I put together a bit ago.

I did not update the screenshots since they still accurately represent the behavior of the new formula, as well as the previous one.

Wow, this is thorough. I still have to look into the trimming trick since, I, too, am picky about formatting—although less so with Airtable, since I’m not a pro like you.

The thing is that I was already taking the “transform before you look” approach, but it didn’t work in pulling the Year into the rollup. In my Books table, I used this formula in the Entry field:

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

Even here, even in this formula field, even before the rollup field in the Catalogs table, {Year} stays {Year} and doesn’t give me the numbers. And the price is missing a digit. I’ll use your Native Son example, since I actually have one of those in my stock . . .

WRIGHT, RICHARD. Native Son. New York: Harper & Brothers, {Year}. A faithful facsimile of the first edition. First Edition Library prospectus laid in. Brand new, never read, and in a Brodart jacket cover. First Edition Facsimile. $41.5.

^ this is what the cell looks like in the Entry field of the Books table, before it’s pulled into the rollup field in the Catalogs table. Did I miss a step in your instructions on how to transform before you look?

Try this:

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

Okay, let’s talk about this.

This is actually behaving as it should!

If you’re curious to read a bit about why this happens, you can read below.
Alternatively, if you want to skip to the solution, click here.


I could type out massive chunks to explain why this is the case, but I’ll provide you with some context.

Computers and programs interpret data in ‘types’. We literally call them data types.

For our purposes, here’s what we care about with Airtable’s formulas.

  1. Booleans (True/False)

  2. Strings (Text)

  3. Numbers

  4. Undefined (Literally nothing. Like forgetting the field even existed).

  5. Date/Time (Not actually a data type, but for the sake of Airtable, we’ll toss it in).


Every field type in Airtable holds a certain type of data. (With the exceptions being the linked record and attachment fields, which use a type called an object.)

The currency field accepts numbers.

Despite being able to format data into a readable currency, Airtable only understands and interprets data in that field as being a number.

We see $20.00, but from Airtable’s perspective, it’s just 20.

This is, of course, quite simple.

However, it gets frustrating when we see $20.90 because Airtable sees 20.9.

So, how can we change this?

Well, since Airtable is looking at the currency field as a number (and thus doesn’t care about any formatting), we can take the number value and force Airtable to treat it as text.


Here’s a formula that will format numbers into a USD text format.

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

Here’s what the returned behavior looks like:

image

With this, you can correctly format numbers into a USD format within a text string inside a formula.

Here’s what the fully formatted, final output would look like using the original formula I posted:

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"
)

image

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?

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.

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.

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!

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

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! Heart

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.