Help

IF Formulas Help

Topic Labels: Formulas
Solved
Jump to Solution
439 2
cancel
Showing results for 
Search instead for 
Did you mean: 
thaid
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello - we use a formula field to create unique & automated "Activity Titles" for records.

Our current formula pulls from various fields:

  • Program (multi select)
  • City (linked field)
  • Type (single select)
  • Date (date)

Current formula is: 

Program & " - " & City & " " & {Type} & (" ") & " (" & (DATETIME_FORMAT(Date,'YYYY/MM/DD')) & ")"

This results in an Activity Title that looks like: Corporate Event - Philadelphia Webinar (2024-03-21)

The problem we've run into is that some of our Activities are with multiple cities - 30+.

So when the formula pulls into those Cities into the title, the title then becomes unusably long - Corporate Event - Philadelphia, New York, San Francisco, Pittsburgh, Buffalo, Winchester, Miami, San Diego, Portland, Seattle, Minneapolis, Richmond Webinar (2024-03-21)

Is there a way to utilize an IF formula that looks at the City (linked records) field and do something along the lines of "If there is more than one .city listed, use the term "multi-city" in the title formula. If there is 1 city listed for the activity, use the City name.

If so, can someone help me write that IF formula?

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

Hello @thaid,

How about changing the field to a Rollup field and using the COUNTA function?

{Program} & " - " 
& IF(COUNTA(values)<=1, values, "multi-city") & " " 
& {Type} & " "
& " (" 
& DATETIME_FORMAT(Date,'YYYY/MM/DD')
& ")"

 

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

Hello @thaid,

How about changing the field to a Rollup field and using the COUNTA function?

{Program} & " - " 
& IF(COUNTA(values)<=1, values, "multi-city") & " " 
& {Type} & " "
& " (" 
& DATETIME_FORMAT(Date,'YYYY/MM/DD')
& ")"

 

thaid
5 - Automation Enthusiast
5 - Automation Enthusiast

thanks, Sho!

Based on your suggestion, I found an even simpler solution - added a Count field (which we needed anyway) to count how many linked fields there were - ie how many Cities participated in each record. 

Then took your formula (thank you) and did a basic if function. 

Appreciate your help to get this solved today!