Combining Two Formulas To Test New Functionality While Not Breaking The Old

[dhigbee]

We have an intake form to generate URLs with UTMs parameters. I have created a new form with additional functionality. During testing, I need both the old and new forms to work.

My current Formula field concatenates the URL (Root Link) with the UTM parameters and values. There is also a check to determine if the URL provided in the Root Link field contains a "?". If it does, then the concatenation output is handled differently:

IF(FIND("?", {Root Link})=0,

{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})

My new form has the option to create some additional link types based on content. If you choose that option, the Root Link field is ignored, and the Media Viewer Type is considered instead.

I have a working formula for the new form which does not consider whether there is a "?" in the Root Link since it will not show that field if a "Media Viewer" type is picked on the form.

IF(
{Root Link},
{Root Link}&"?utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

IF({Media Viewer Type}="Landing Page",
"https://www.company.com/"&{Locale}&"/media-viewer?type=m&d="&{LP Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

IF({Media Viewer Type}="PDF",
"https://www.company.com/"&{Locale}&"/media-viewer?type=mp&d="&{PDF Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

IF({Media Viewer Type}="Video",
"https://www.company.com/"&{Locale}&"/media-viewer?type=v&d="&{Video ID}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

IF({Media Viewer Type}="Flipbook",
"https://www.company.com/"&{Locale}&"/media-viewer?type=i&d="&{Flipbook Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

"error")

)
)
)
)

If I use that new formula while the old form is still in use and someone provides a URL with a "?" it will output a URL with two questions marks in it, which won't work. 

My efforts to combine those two formulas results in the Formula field showing only the UTM parameters portion of the URL, and not the whole URL when a Media Viewer type is chosen. 

How can I combine the old and new formulas so that:

• If the old form is used, it is still checking the Root Link for a "?" and the Formula field is populating with the full URL with UTM parameters and values.
• If the new form is used, regardless of whether a Root Link or a Media Viewer Link is being used, the Formula field is populating with the full URL with UTM parameters and values.

Thank you.

[Hamlin_Krewson2]

I'd probably do this by first testing to see if {Media Viewer Type} has been set, then using a SWITCH() on {Media Viewer Type}, followed your first formula as the default in the IF. So, something like below. 

IF({Media Viewer Type},
SWITCH({Media Viewer Type},
"Landing Page",
"https://www.company.com/"&{Locale}&"/media-viewer?type=m&d="&{LP Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"PDF",
"https://www.company.com/"&{Locale}&"/media-viewer?type=mp&d="&{PDF Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"Video",
"https://www.company.com/"&{Locale}&"/media-viewer?type=v&d="&{Video ID}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"Flipbook",
"https://www.company.com/"&{Locale}&"/media-viewer?type=i&d="&{Flipbook Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input}),IF(FIND("?", {Root Link}),

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},ERROR()))

[dhigbee]

Thank you, @Hamlin_Krewson2 . So that did populate the full URL when a Media Viewer Link was selected (which was not happening previously), but resulted in blank fields where just a Root Link was used.  Any suggestions? 

[Hamlin_Krewson2]

@dhigbee You might have to replace my IF(FIND("?", {Root Link})...) with your original IF formula. I modified to that thinking you'd only be looking for a root link with a '?' in it. 

IF(FIND("?", {Root Link})=0,

{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})

[dhigbee]

@Hamlin_Krewson2 , I was thinking along the same lines and was playing around with that as well, but it's still not populating the formula field when there is a value in the Root Link field. It is only populating when a Media Viewer Link is used.

Here is the complete formula as it stands now:

IF({Media Viewer Type},

SWITCH({Media Viewer Type},

"Landing Page", 

"https://www.company.com/"&{Locale}&"/media-viewer?type=m&d="&{LP Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

"PDF", 

"https://www.company.com/"&{Locale}&"/media-viewer?type=mp&d="&{PDF Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

"Video", 

"https://www.company.com/"&{Locale}&"/media-viewer?type=v&d="&{Video ID}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

"Flipbook", 

"https://www.company.com/"&{Locale}&"/media-viewer?type=i&d="&{Flipbook Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},

IF(FIND("?", {Root Link})=0,

{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input},

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})

)

)

[Hamlin_Krewson2]

@dhigbee Can you post an example {Root Link} value that should work? Or screenshots? 

[dhigbee]

@Hamlin_Krewson2 , thanks for taking the time to help me with this one. I'm not a developer and I am trying to reverse engineer something created by someone else, as well as add additional functionality.

So, the Root Link can be any URL. E.g., https://www.airtable.com

If there is no "?" in the URL, we want to use this string:

{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input}

Note: The {Source Parameter} value is "?utm_source" (already includes the "?")

If the Root Link URL has some existing parameters on it which include a "?", e.g., https://www.airtable.com?id=123456, then we don't want the "?" to be repeated, so we want to use this string instead, which doesn't add the Source Parameter. It adds "&utm_source" followed by the value of the Source Input field:

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input})

The logic I am trying to get to is:

• IF there is a value provided in the Root Link field, the formula field should display the Root Link URL with the appropriately structured UTM parameters and values appended,
  ELSE
• If a Media Viewer Type has been selected, then the formula field should display that URL and UTM parameters and values instead, regardless of what is in the Root Link field.

The formula for my New Form (as provided in my original post) is working this way since we make the user choose which type of link they want (Root Link or Media Viewer). While the old form is still in use and we are testing the new form, I need the base table to be able to display values created in either form. 

I hope that makes sense. Thank you again for your help. 

[dhigbee]

@Hamlin_Krewson2 , I found a rudimentary way to make it work. I had created another formula field called  "Media Viewer URL" to generate that value, so I could just do this, which does appear to work the way I need it to:

IF({Root Link} = BLANK(), {Media Viewer URL},

IF(FIND("?", {Root Link})=0, 

{Root Link}&{Source Parameter}&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input}, 

{Root Link}&"&utm_source="&{Source Input}&{Medium Parameter}&{Medium Input}&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&{Content Parameter}&{Content Input}))

If possible, I would still prefer that the one formula field generate both the Root Link or the Media Viewer URL rather than have an additional formula field. If you can think of a solution for that, please let me know. Thanks. 

[Hamlin_Krewson2 · Accepted solution]

@dhigbee I'm curious, would {source parameter} ever NOT be "utm_source"? Pardon the question, I'm not completely familiar with URL tracking.

For me, the formula does seem to work correctly. 

That's using the following:  

IF({Media Viewer Type},
SWITCH({Media Viewer Type},
"Landing Page",
"https://www.company.com/"&{Locale}&"/media-viewer?type=m&d="&{LP Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"PDF",
"https://www.company.com/"&{Locale}&"/media-viewer?type=mp&d="&{PDF Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"Video",
"https://www.company.com/"&{Locale}&"/media-viewer?type=v&d="&{Video ID}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input},"Flipbook",
"https://www.company.com/"&{Locale}&"/media-viewer?type=i&d="&{Flipbook Name}&"&utm_source="&{Source Input}&"&utm_medium="&{Medium Input}&"&utm_campaign="&{Campaign Input (Child Campaign Name_Linked)}&"&utm_content="&{Content Input}),IF(FIND("?", {Root Link})=0,

{Root Link}&{Source Parameter}&{Source Input}&'&'&{Medium Parameter}&{Medium Input}&'&'&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&'&'&{Content Parameter}&{Content Input},

{Root Link}&"&utm_source="&{Source Input}&'&'&{Medium Parameter}&{Medium Input}&'&'&{Campaign Parameter}&{Campaign Input (Child Campaign Name_Linked)}&'&'&{Content Parameter}&{Content Input}))

Here's a read-only link to the base. 

[dhigbee]

Hi @Hamlin_Krewson2 . Your most recent reply made me see why mine was not working--I was missing a close parenthesis at the end of the 4th "Media Viewer'" choice.  I now have a working formula that will work for both old and new forms, which will also consider whether there is a "?" in the provided Root Link URL. Thank you!

To answer your question, the way we have our UTM generator setup includes the ampersands in the name of the parameters (e.g., "&utm_campaign") instead of adding them in the concatenation string, which your example is doing (so I removed them in my final formula code).

"utm_source" works a little differently in our case. We need it to be "?utm_source" if the Root Link URL does not already have a "?" in it, meaning it will be the first tracking parameter after the URL; or "&utm_source", if there is already a "?" present in the provided URL. That is why we need the FIND expression. 

Thanks so much for taking the time to work through this one with me. Much appreciated!