Inside this article:
- Create a bulleted list of tasks
- Count the amount of completed tasks
- Create a bug ID
- Count unassigned tasks
- How can I streamline a long set of IF statements?
- What formulas does Coda offer?
How is the Coda formula language similar?
We've kept many of your old favorites in Coda's formula lanuage. You'll notice a few familiar things in Coda, for example:
1) Math - You can perform math functions just like you're used to in Coda.
- = Sum (14,23,5) → 42
- = 12+3+5 →20
- = 5*5 → 25
2) IF Statements - If you have different results you want to see based on a value, you can use your tried and true IF set up.
- = IF (4>2, "Woo hoo!", "Nope, no chance")→ Woo hoo!
3) Time - You can aslo call up time just liket you're used to.
- Today ()→ Today's date
- Today()-7 → Today's date - 7 days
- Now() → Today's date and time
How is the Coda formula language different?
But, what's different? We've made a few tweaks and adjustments to make working in Coda more streamlined, efficient, and flexible. The two major differences are:
Named objects not cells
In your traditional spreadsheet program, you perform formulas based on cell coordinates. For example, (A1:A31)/(B1:B31). While accurate, formulas like this don't grow with you. What happens when you have an A32 and B32? The margin of error gets a lot bigger, and you already have enough on your plate. You shouldn't have to wrestle with formula adjustments over time.
To combat this problem, Coda's formula language abandons cell coordinates and uses the actual objects' names instead. For example, in our example (A1:A32)/(B1:B31), if column A held all of our Produce, and column B held all of our Prices, the Coda formula would simply be Produce/Price. That way, as our columns expand, so will our formulas. So, when you're working in Coda simply use the table, column, and row names when making calculations, and you're formulas will grow along with you.
Chaining not wrapping
A familiar sight in a traditional spreadsheet is a line of parenthesis at the end of a formula )))))))). This makes it hard to parse what's actually happening in the formula. To make it easier to track (and write!) your formulas, Coda uses chaining.
For example, if we wanted to count how many of our products have less than 5 in our inventory, we would build our formula like this:
= Products.FILTER(Quantity <5). Count()
With the "." Coda allows you to create a chain of commands so you can easily retrace your steps, and make your formulas easier to understand by your wider project group or new team members. In the formula above, what we're asking is:
- First, look at the Products table
- Then Filter that table looking at only cells where the Quantity is less than 5
- Then Count that number
Create a bulleted list of tasks
- Tasks.Filter(Team=[Industrial Design] and Status="Done").BulletedList()
- Take the Tasks table
- Find items owned by the Industrial Design team that's been completed
- Make a bulleted list of the completed items
Count the amount of completed tasks
- Tasks.Filter(Team = thisRow && Status = "Done").Count()
- Take the Tasks table
- Find items owned by the team that matches this row where the task has been completed
- Count the amount of results the meet your query
Create a bug ID
- concatenate("bug-", ROWID(thisRow).TO_TEXT())
- Create a bug id of bug- [this row's number]
- Convert that ID to text
Count unassigned tasks
- Contacts.filter([Assigned Team Member]="Unassigned").Count()
- Look at the Contacts table
- Find the items where the Assigned team member is Unassigned
- Count how many unassigned items there are
Formula syntax FAQs
How can I streamline a long set of IF statements?
You'll want to check out the Switch and SwitchIf formulas for this one as a substitute for long if/then statements. With Switch, if the first condition is true then it performs the first action and exits the Switch. If it doesn't meet the first condition it jumps to the second condition. If it meets it then it will do a different action. And so on... it's kind of like a big flow chart but in your table. For example:
SwitchIf(Today() > Date(2019, 01, 20), "Hello future!", Year(Today()) = 2017, "Hello present!", Today() < Date(2017, 03, 04), "Hello past!")
What formulas does Coda offer?
We've got plenty of formulas for you to sink your teeth into. You can find the full list right here - https://coda.io/formulas