Rollup + IF(SEARCH())

[Lance_Devore]

Hello,

Here’s my base:

I’m using a Roll Up to see if the URL in “Leads” table includes the UTM in “Inputs” table. If it does, I want it to add the corresponding “Source” from the “Inputs” table as the “Referral Source” in the “Leads table”

I thought I might be able to achieve this as adding this formula with the Roll Up:

IF(SEARCH({UTM},{Input}){Source})

But it tells me that’s an invalid formula.

Thanks for any help!

[kuovonne]

A rollup needs to have an aggregation function for its values. You can nest the aggregation function inside another function, but you cannot completely omit the aggregation function in a rollup.

Take a look a conditional rollups to see if that will work for you.

[Lance_Devore]

Thank you for your reply. I’m apologize, I’m not very adept in this field, but doesn’t the SEARCH() act as an aggregate?

Where as “=” looks for an exact match, SEARCH() looks to see if a specified text contains another specified text within it. But maybe my definition of aggregate is wrong.

I may need someone to point me to the right formula for this one. I’m having trouble finding the right solution from the conditional rollups documentation.

I just need a way to check if Web URLs contain a UTM string from a list of 23 UTM strings, and if they do I need them to interpret that string as a different text.

[Lance_Devore]

Also, perhaps I’m reference my fields in the linked table incorrectly.

In my linked table, I have a field called “UTM” and field called “Source.” But when I write {UTM} and {Source} in my formula, it says those fields don’t exist. I’ve made sure my URL field is linked to that table.

[kuovonne]

An aggregation formula (in the context of Airtable formulas) takes a list of values and converts it to a single value, such as a sum, minimum value, a single string of text, etc. The SEARCH function looks for a value in a single string.

You could perform a SEARCH after aggregating the values in the rollup field.

IF(
  SEARCH({UTM}, ARRAYJOIN(values)),
  "UTM found in rollup values",
  "UTM NOT found in rollup values",
) 

Can you post a screen shot? It would make it easier to understand the exact field types, field names, and values.

[Lance_Devore]

The empty “Source” field in “Leads” is where I want to display the correct source from “Inputs”

[kuovonne]

Thank you for posting the screen shot.

I don’t see any linked records or any rollup fields in your screen shot, but it looks like you used to have a lookup field. So maybe you changed the design of the fields? Rollups only work if you already have linked records.

It looks like you need to link the tables in a many-to-many relationship, so that each record in the [Leads] table is linked to the the corresponding records in the [inputs] table. After all the records are linked, a rollup can then show a list of {Source} values for each record in the [inputs] table.

A rollup or formula cannot create the many-to-many relationships for you. If you know JavaScript (or can hire someone who does), you might want to look into a Scripting Block solution.

[Lance_Devore]

Yes, my “Input” field in Leads is linked to the “UTM” field in Inputs in a many-to-many relationship. Its an old screenshot so maybe I hadn’t done that before I took the screenshot.

Maybe I should skip the Roll Up and just do a formula only?

In Excel, this all can be achieved by a formula. I’ve tested the formula and it works. Maybe you can help me translate it to an Airtable formula?

=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH(Inputs!$A$1:$A$46,B2))), Inputs!$B$1:$B$46),"")

In this formula, “Inputs!$A$1:$A$46” contain all the different possible UTM strings

Column B (in the Leads sheet, where the formula is written in column C) contains the URLs we are checking to see if they include one of our UTMs. For each row, this changes to B2, B3, B4, etc.

If it finds one, it displays the corresponding Source found in Inputs!$B$1:$B$46

If it doesn’t find one, it leaves the cell blank.

[Lance_Devore]

Alternatively, but not ideally, I could skip referencing another table and just write everything into a formula like this:

IF(SEARCH("?utm_source=youtube&utm_medium=video”,{Input}),”YouTube”,
IF(SEARCH(“?utm_source=google&utm_medium=cpc&utm_term=tampabrand",{Input}),“Google AdWords Tampa Brand”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantstampa",{Input}),“Google AdWords Implants Tampa”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantsorlando",{Input}),“Google AdWords Implants Orlando”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=orlandobrand",{Input}),“Google AdWords Orlando Brand”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=srtampa",{Input}),“Search Remarketing Tampa”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=4_tampa",{Input}),“GA AllOn4 Tampa”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=4_orlando",{Input}),“GA AllOn4 Orlando”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=conqtampa",{Input}),“Google AdWords Conquest Tampa”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=conqorlando",{Input}),“Google AdWords Conquest Orlando”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=brand-us",{Input}),“Brand US”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_campaign=jacksonville",{Input}),“D5 Conquest”,
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=zygomatic",{Input}),“Zygomatic Dental Implants”,
IF(SEARCH(”?utm_source=google&utm_medium=cpc&utm_campaign=periodontal",{Input}),“Periodontal”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_campaign=ntl&utm_term=allon4",{Input}),“Bing AllOn4 US”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=orlandobrand",{Input}),“Bing Brand Orlando”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=tampabrand",{Input}),“Bing Brand Tampa”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_campaign=ntl",{Input}),“Bing National”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=conqorlando",{Input}),“Bing Conquest Orlando”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=conqtampa",{Input}),“Bing Conquest Tampa”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=implantsorlando",{Input}),“Bing Dental Implants Orlando”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=implantstampa",{Input}),“Bing Dental Implants Tampa”,
IF(SEARCH(”?utm_source=bing&utm_medium=cpc&utm_term=zygomatic",{Input}),"Bing Zygomatic“
)))))))))))))))))))))))

But this tells me it’s an invalid formula. Anyone see a problem with how I’m structuring it? I wrote it using the solution found in this thread: Nested IF(SEARCH()) Formulas

I also tried it without the brackets around Input, as in {Input}. But it still said it was invalid.

[kuovonne · Accepted solution]

Airtable cannot replicate your Excel formula, but your idea of directly using a formula without referencing the other table will work.

Airtable formulas are very picky about syntax. They require straight quotes instead of curly quotes. Also, a single misplaced, missing, or extra character (parenthesis, comma, quote) can throw off the entire formula.

I suggest that you try a simpler version that uses concatenation instead of nesting, and make sure you do not have any curly quotes. “ ” versus ".

IF(SEARCH("?utm_source=youtube&utm_medium=video",{Input}),"YouTube")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantstampa",{Input}),"Google AdWords Implants Tampa")
&
IF(SEARCH("?utm_source=google&utm_medium=cpc&utm_term=implantsorlando",{Input}),"Google AdWords Implants Orlando")

You could also split up the searches to search for source, medium, and term in different parts of the function:

IF(SEARCH("utm_source=youtube",{Input}),"YouTube")
&
IF(SEARCH("utm_source=google",{Input}),"Google")
& " " &
IF(SEARCH("utm_medium=cpc",{Input}),"AdWords")
& " " &
IF(SEARCH("utm_term=implantstampa",{Input}),"Implants Tampa")

Or you could use more complex formulas for spliting up the string.