Solved

Regex or Subsitute Formula? Or Both?

Forum|Forum|2 years ago
March 24, 2023
9 replies
56 views

LeighAnnMoltz
Known Participant

I have a field that has a ton of linked records and I'm trying to format that into a nice list for use in the Pages extension.

It's generally coming through with commas and quotation marks separating it.

I found this formula in another thread, which seems to remove the opening quote marks, but not subsequent.

IF(LEFT({Marketing Tactic - Detail}, 1) = '"',

SUBSTITUTE(

MID({Marketing Tactic - Detail}, 2, LEN({Marketing Tactic - Detail}) - 2),

'""',

'"'

{Marketing Tactic - Detail}

)

I'm trying to format something like this:

"Digital Signage (BOH) - TVs installed to reach branch salespeople in lunch rooms, warehouses and other strategic locations. ", Email to Salespeople - Email to increase brand or product awareness with salespeople usually offering a stocking incentive. , Flyer for Salespeople - Flyer used for vendor to increase awareness about products or stocking offers available to salespeople., Post on Intranet - Announcement posted on the intranet website to increase awareness about products or stocking offers.

to something like this:

Digital Signage (BOH) - TVs installed to reach branch salespeople in lunch rooms, warehouses and other strategic locations.

Email to Salespeople - Email to increase brand or product awareness with salespeople usually offering a stocking incentive.

Flyer for Salespeople - Flyer used for vendor to increase awareness about products or stocking offers available to salespeople.

Post on Intranet - Announcement posted on the intranet website to increase awareness about products or stocking offers.

I can handle hard returns, I prefer if I can use bullets or hyphens or something to designate the list elements.

Thank you in advance!!!

Best answer by Ben_Young1

Hey @LeighAnnMoltz!

Based on the information you provided, I put together the following example:

To do this, I implemented a rollup field with the following formula:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n• "
                )
            )
        )
    )
)

You can define a custom separator by replacing the dot symbols in the formula. If you want a hard break between each item, then you can insert an additional new line operator like this:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n\n• "
                )
            )
        )
    )
)

+22

Ben_Young1
Brainy
Answer
Forum|Forum|2 years ago
March 24, 2023

Hey @LeighAnnMoltz!

Based on the information you provided, I put together the following example:

To do this, I implemented a rollup field with the following formula:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n• "
                )
            )
        )
    )
)

You can define a custom separator by replacing the dot symbols in the formula. If you want a hard break between each item, then you can insert an additional new line operator like this:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n\n• "
                )
            )
        )
    )
)

LeighAnnMoltz
Author
Known Participant
Forum|Forum|2 years ago
March 24, 2023

Hey @LeighAnnMoltz!

Based on the information you provided, I put together the following example:

To do this, I implemented a rollup field with the following formula:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n• "
                )
            )
        )
    )
)

You can define a custom separator by replacing the dot symbols in the formula. If you want a hard break between each item, then you can insert an additional new line operator like this:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n\n• "
                )
            )
        )
    )
)

Your sample accomplishes EXACTLY what I'm trying to do!! Thank you.

My implementation is incorrect. This is what it looks like:

This is how I plugged in your formula:

IF(

{Marketing Tactic - Detail},

IF(

COUNTALL({Marketing Tactic - Detail}) = 1,

"• " & TRIM("" & {Marketing Tactic - Detail}),

IF(

COUNTALL({Marketing Tactic - Detail}) > 1,

"• " &

TRIM(

ARRAYJOIN(

{Marketing Tactic - Detail}, "\n• "

)

What did I mess up?

LeighAnnMoltz
Author
Known Participant
Forum|Forum|2 years ago
March 24, 2023

Wait .... I think it may the "rollup" .... I simply added a formula field for this. If I add a rollup field though, I can't input a formula. So perhaps this is a very silly question. How do I do that?

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
March 24, 2023

Your sample accomplishes EXACTLY what I'm trying to do!! Thank you.

My implementation is incorrect. This is what it looks like:

This is how I plugged in your formula:

IF(

{Marketing Tactic - Detail},

IF(

COUNTALL({Marketing Tactic - Detail}) = 1,

"• " & TRIM("" & {Marketing Tactic - Detail}),

IF(

COUNTALL({Marketing Tactic - Detail}) > 1,

"• " &

TRIM(

ARRAYJOIN(

{Marketing Tactic - Detail}, "\n• "

)

What did I mess up?

Instead of a formula field, you need to create a rollup field.
If you use the rollup field, you can just copy and paste the exact formula I posted; you won't have to change it at all.

Here are some screenshots for additional context:

After saving the field, we get:

LeighAnnMoltz
Author
Known Participant
Forum|Forum|2 years ago
March 24, 2023

I think I understand. You're able to use "rollup" because those multi-selects are records in another table that you're linking. Thus, rollup.

The way I currently have the data set up in this table is that it's just a multiple select field. This is a huge table .... I can change it if that's my only option, but I really prefer not. Is there a way to accomplish this without the rollup/linked record requirement or not really?

+22

Ben_Young1
Brainy
Forum|Forum|2 years ago
March 24, 2023

I think I understand. You're able to use "rollup" because those multi-selects are records in another table that you're linking. Thus, rollup.

Ouch. That's rough. If I had to nominate a field to indiscriminately eject from databases and data structures, it would be multiple select fields. In 99% of use cases that have a multiple select field implemented, the better choice would have actually been a linked record field.
Based on the data you were working with, I strongly advise that you change your multiple select field into a linked record field.
It's not nearly as hard as it might seem, and it will make your database so much more sustainable and resilient.

You can change the field type into a linked record field and Airtable will automatically convert your preexisting multiple select options into new records in the table that's created.
I've included a quick video snippet of how quickly and cleanly it can be done.

If you watch that video and still decide that it's not worth it for you, then let me know and I'll figure a formula for your multiple select-based use case.

LeighAnnMoltz
Author
Known Participant
Forum|Forum|2 years ago
March 24, 2023

I decided to bite the bullet and switch it over while I was waiting lol only took a few minutes, I just didn't want another sheet in the base. It's for the best! Thank you for your help. This is exactly, exactly, exactly what I needed. I wouldn't have figured that formula out!

+29

kuovonne
Brainy
Forum|Forum|2 years ago
March 24, 2023

Glad you got it figured out. I also agree that having a linked record in a different table instead of a multi-select is much nicer.

In case you are interested, I tend to use a slightly different rollup formula. If you know there will be no empty lines, you can omit the ARRAYCOMPACT().

IF(

COUNTALL(ARRAYCOMPACT(values)),

"- " & ARRAYJOIN(ARRAYCOMPACT(values), "\n\n- ")

)

Michelle_Koch
New Participant
Forum|Forum|1 year ago
July 15, 2024

Hey @LeighAnnMoltz!

Based on the information you provided, I put together the following example:

To do this, I implemented a rollup field with the following formula:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n• "
                )
            )
        )
    )
)

You can define a custom separator by replacing the dot symbols in the formula. If you want a hard break between each item, then you can insert an additional new line operator like this:

IF(
    values,
    IF(
        COUNTALL(values) = 1,
        "• " & TRIM("" & values),
        IF(
            COUNTALL(values) > 1,
            "• " & 
            TRIM(
                ARRAYJOIN(
                    values, "\n\n• "
                )
            )
        )
    )
)

It's a year+ since this thread was started, but you just solved an issue for me that I had been banging my head against for two days.

And I learned something really useful about Rollup fields. THANK YOU!!

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded