Three New Formulas - ToTime(), ContainsAll() & ContainsOnly()

News from Coda
1

Hey everyone

Wanted to share a quick update that we’ve added a few new formulas to the Coda formula language that you might find useful in your docs.

Part 1: The New ToTime() Formula

Convert a DateTime value into a Time value

Examples:

=ToTime(Now()) returns 9:35:32 AM. In the past you had to write =Time(Now().Hour(), Now().Minute(), Now.Second()) to get the same result.

=thisRow.created().ToTime() returns 11:07:48 PM.

Convert a compatible string into a Time value

Examples:

=ToTime("9:00pm") returns 9:00 PM

=ToTime("11:10:00am") returns 11:10:00 AM

Part 2: New Contains Formulas

Coda already has a Contains formula which works as follows:

=List("Apple", "Orange").Contains("Apple") returns true

=List("Apple", "Orange").Contains("Banana") returns false

Most people don’t know that the Contains() formula is actually “Contains Any Of”. You can enter more than one parameters or a list and the formula returns true if any of the values you specified is in the list you are searching.

For example, let’s say you have the following tasks table:

You can write the following filter formula:

=Assignee.Contains([Angad Singh],[Matthew Tebbs])

This will return true for Task 1, 3 and 4. It will only return false on Task 2 since that is the only task that does not contain either Angad or Matthew.

The New ContainsAll() Formula

In the table above, let’s say you only want to see tasks that are assigned to both Angad and Matthew instead of either one of them. Currently you need to write the following formula:

=Assignee.Contains([Angad Singh]) AND Assignee.Contains([Matthew Tebbs])

We have now added a new formula called ContainsAll(), which returns true only if the value you are comparing to has all of the values from the search list. So you can now write the above formula as:

=Assignee.ContainsAll([Angad Singh],[Matthew Tebbs])

This formula will return true on Task 3 and 4, since they contain both Angad and Matthew. Unlike Contains(), it will not return true on Task 1, since it contains Angad but not Matthew.

The New ContainsOnly() Formula

In the table above, let’s say you only want to see tasks that are assigned to both Angad & Matthew but not anyone else. You could write the following formula:

=Assignee=List([Angad Singh],[Matthew Tebbs])

However, the formula above would not return true if the Assignee list was in the opposite order or had duplicates. So the correct formula would have to be:

=Assignee.Unique().Sort() = List([Angad Singh],[Matthew Tebbs]).Unique().Sort()

Instead, you can now just write the following formula:

=Assignee.ContainsOnly([Angad Singh],[Matthew Tebbs])

This formula will return true only on Task 3, since it is the only one that contains both Angad and Matthew and no other values. Unlike ContainsAll(), it will not return true on Task 4, since that contains Angad and Matthew but also Moriah.

Here’s a doc that shows all three Contains Formulas in action:

Hope these formulas are useful in your doc!

Angad

22 Likes
2

This is great. Glad to see updates like this.

1 Like
3

Great job, thanks adding practical samples too :+1:

1 Like
4

Those new constain formulas are great!!
Thanks for it!

1 Like
5

Is there any way to get to a number that matches the filter formula?

For example, how can I generate a list of users that shows that Angad has 3 tasks, and Moriah has 2, and so on?

ERIK R. HARTSTROM, ESQ.

ehartstrom@huberlawgroup.com

1 Like
6

Hi Erik, welcome to coda.

One way to do this can be seen here:

What this is doing is saying:
For every unique assignee find how many tasks have them listed as an assignee and count the number of tasks. Then format some text to show their name next to their task count.

Worthy of note: this is not how I would solve the problem, I would make a table specifically for People and then use it as the Lookup Table for the Assignee column. If that doesn’t make perfect sense let me know and I can make an example.

3 Likes
7

@Erik_Hartstrom @cnr
Totally agree! Here’s an example of what I was thinking in terms of building out the count of tasks in a table…

  • Create a row for each person whose tasks you want to see
  • You can get the count of tasks directly with a column formula like: [Task List].CountIf(Assignee.Contains(thisRow.Teammate))
  • Or, if you want to break it down and see more info about everybody’s tasks you could format your column as a “Lookup from Table” to the Task List Table, and first grab each person’s tasks with this column formula: [Task List].Filter(Assignee.Contains(thisRow.Teammate))
    • fun fact - this lets you do some fun things such as like see the tasks in a bulleted list: [Tasks].BulletedList()
  • Then you can crab a count of tasks by counting up each row’s value from your Tasks column: [Tasks].Count()

I added in some examples of this in another copy of the original example doc! Check out the formulas/formats on each column to see them in action and understand where they’re referencing from.

4 Likes