Formula Question for a Newbie

1

Hello!

I’m new to Coda and have a basic task I’m trying to do with formulas. Essentially, I have multiple columns that contain a basic select list (either ✓ or X). I’d like to have a column that summarizes (with a bulleted list) the column names if there is a ✓ for that particular row. A sample is attached.

Screen Shot 2020-12-21 at 2.17.56 PM
Screen Shot 2020-12-21 at 2.17.56 PM714×378 14.9 KB

Any ideas how to do this? I’ve tried all kinds of variations using IF(), Switch(), and SwitchIF().

Thanks so much!

2

Hi @Alexander_Gaeta, welcome to the community.

Unfortunately, there is no way to access the column name of the row in an automated way. However, if you’re not going to change the name of the column then you should be able to make a Summary column like this:

List(
  If(
    thisRow.Apple="✓", "Apple", ""
  ), 
  If(
    thisRow.Banana="✓", "Banana", ""
  ), 
  If(
    thisRow.Pear="✓", "Pear" ,""
  )
).Filter(CurrentValue).BulletedList()

Giving you this result:

image
image1270×596 11 KB

Here's an explanation of what the formula is doing:

This creates a list of whatever the If functions return:

List(
  If(...),
  If(...),
  If(...)
)

This returns “Apple” if thisRow has a checkmark in the column Apple, otherwise it returns Blank

If(
  thisRow.Apple="✓", "Apple", ""
)

Filtering for CurrentValue returns all the parts of a table that aren’t blank. It’s equivalent to Filter(CurrentValue.isNotBlank()). Then the resulting list (now without blanks) is converted to a bulleted list.

List(
  ...
).Filter(CurrentValue).BulletedList()

That said, I don’t recommend this approach. Instead, create a column called Selected Fruit (or the like) and make it look up from the Fruit table. Make sure that “Allow Multiple Selections” is turned On.

image
image998×800 32.4 KB

Now you can select multiple different fruit types without having to create new special columns for each one, and your summary column will also always be updated without having to change the formula for each new fruit you add:

image
image652×584 13.8 KB

The formula for the Summary 2 column is:

thisRow.[Selected Fruit].FormulaMap(CurrentValue.ToText()).BulletedList()

This formula just converts each fruit into a text value with FormulaMap(CurrentValue.ToText()) (otherwise it looks like image ) and then turns it into a bulleted list.

7 Likes
3

Very interesting @cnr.
Can you explain why .filter(CurrentValue) is a valid expression? Shouldn’t the result of the expression inside filter() be only true or false?

4

I’m guessing it’s because Blank is falsey (it evaluates implicitly to false) You could use Filter(CurrentValue.isNotBlank() instead though.

1 Like
5

@cnr Thank you SO MUCH for the detailed breakdown. It’s super helpful and informative, and just what I needed. Really appreciate you taking the time. Gonna get to work instituting this into my current docs.

Happy Holidays!

1 Like