Guide
Heads up
This guide is part of the series where we build an amusement park inside Fibery. You can follow along and recreate these steps, or take these ideas and build out your own setup.
https://www.youtube.com/watch?v=gjeERWqE3iQ
Every Field in Fibery stores what you type into it. A Formula field doesn't - it transforms data you already have into something new. Formulas are also read only and they can't change your data, only recalculate it. So they can't break anything.
And a Formula can only reach into stuff it's related to. An Attraction can count its Tasks, not every Task in the Workspace. No Relations, no reach.
Below we'll look at Formulas, what they can do and how to build them.
Lookups
Let's go belt by belt - karate style, starting with the white belt. Lookups is your first move - they show something here that actually lives elsewhere (e.g. different Database).
For example, you're on a Task and want to know if the ride it belongs to is Open or Closed.
Because Lookup is so popular it has its own Field type. No need to reach for a Formula just yet. Let's pull State from Attractions Database to Tasks Database.
Next to your Fields inside Tasks Database click on + > + New Field > Lookup from…
Pick Attractions as RELATION, pick State as FIELD and name it something like State for ride.
You should now see State for ride inside Tasks Database.
Creating your first Formula
The Lookup stops at just one level. If you want to reach deeper, you'll have to go to a full-fledged Formula.
Let's try a simple one. Say your park is split into zones, and you want the zone on the task. The task knows its ride, the ride knows its zone - two levels.
In Tasks Database, create a new Formula Field, click on + > + New Field > Formula.
Set NAME to Zone manager, FORMULA to Attraction.Zone.Manager. Then click Add Formula to save.
The only syntax here is a dot (.), think of it as a door. It looks inside the thing on the left.
Rollups
Your next move is the Rollup. It takes a stack of the same thing (e.g. Date Field) and rolls it into one value.
For example, each Attraction gets safety inspections, and each inspection has a date. Point a Formula at the Inspections Database and roll all those dates into one answer: when was this ride last checked?
Create a new Formula and set NAME to Last inspection and FORMULA to Inspections.Max(Date). You should see the last inspection date inside the Formula.
What if you want to know if that last inspection was a success? Let's set up another Formula. Set NAME to Inspection result and FORMULA to Inspections.Sort(Date).Last().Result
First and Last reach to different ends of the stack, beginning or end. For example, the soonest deadline first: Tasks.Sort(Due).First().Name.
Next, let's count how many inspections happened in total. Set NAME to Total inspections and FORMULA to Inspections.Count(). You should see how many times each ride has been checked.
You can also roll up only a part of the stack. Say you only care about which Attractions have failed recently. Set NAME to Total failures and FORMULA to Inspections.Filter(Result = "Failed").Count().
Filter keeps only what matches it, Count works with what's left after the filter. This works the same with any Rollup like Sum or Avg.
Finally, let's get a nice % on our Database. Which Attractions pass the test most often? Set NAME to Success rate and FORMULA to Inspections.Filter(Result.Name = "Passed").Count() / Inspections.Count().
In fact, instead of a flat percentage let's make ours a progress bar. Click on Field > Display as > Progress bar.
When to use formulas
How do you know you need a Formula in the first place? Three signs:
You're flipping between records and assembling the answer in your head
You are checking a few Fields and doing math in your head (bad!)
You have a niche problem and need a snowflake formula (we actually encourage this, welcome home)
With that, the white belt is yours. You can now defend yourself from bullies. Or be one. But you're not winning any tournaments just yet.
Logic Formulas
Next up, logic Formulas. They check something, then answer depending on what they find. If this then that. This is your green belt.
If that sounds a lot like Automations , you are correct. But, an Automation acts - it changes Fields, moves states, creates things. A logic Formula only answers a question. It looks at your data and tells you what it sees. Read only, remember?
Let's start with a single comparison to see which Tasks are overdue. In the Tasks Database, make a new Formula, set NAME to Overdue and FORMULA to Today() > [Due Date].
About those square brackets []. All they do is wrap a Field name that is longer than one word, like [Due date] or [Client list].
Next move is the If. Let's take that same comparison and wrap it with conditions. Make a new Formula and set NAME to On track or late and FORMULA to If(Today() > [Due date], "Late", "On track").
If always takes three things: a condition, yes result, no result. Our condition is the checkbox from before, the results are whatever you want the field to say.
Another example. Rides don't live forever, so let's set up a Formula to check if the ride is Operating or Retired.
Switch to Attractions Database. Create a new Formula and set NAME to Running and FORMULA to If(IsEmpty([Retired on]), "Operating", "Retired").
How about checking more than one thing at once? Sure. Our park runs seasonal events, each has a start date, end date. Make a new Formula and set NAME to Seasonal event and FORMULA to If((Start <= Today()) and (Today() <= End), true, false).
Notice that the Formula checks if today is past the start and today is before the end. The and operator wants both to pass. Or is another operator you can use, and unlike and it only needs one condition to pass not both.
You can also nest several Ifs. Every ride has a wait time in minutes, but visitors want to know it straight. How long is the wait, Long, Medium or Short?
Create a new Formula and set NAME to Waiting and FORMULA to If([Wait time] > 60, "Long", If([Wait time] > 20, "Medium", "Short")).
Last green belt move. Say you have Views that rely on dates, or a math Formula somewhere. An empty Field there is trouble - because your stuff disappears from the timeline if there is no date. Let's set a Formula so that doesn't happen.
Switch to Tasks Database. Create a new Formula and set NAME to Timeline save and FORMULA to IfEmpty: IfEmpty([Finished date], [Due date]).
Think of it as a fuse, a backup. If the date is there - good, if not then this Formula will back up your timeline. Same goes for a math Formula that relies on actual inputs.
Writing Formulas
If you wanna know about operators and functions, check Formulas list. It has all Formulas Fibery has to offer with examples. Or you can use AI to write Formulas for you - it's one of the best AI things in Fibery. It's up to you how deep you wanna go.
Advanced Formulas
Finally, let's see some power moves.
Say people in operations want to see which rides are up for a safety check. Inside Attractions Database create a new Formula. Set NAME to Inspection and FORMULA to If((ToDays(Today() - Inspections.Max(Date)) > 14) and (State.Name != "Closed"), "🔧", "").
A ride that needs a check will show a wrench icon. Inspections.Max(Date) is your straight punch from the white belt. Today minus that date is a gap that ToDays turns into how many days passed. And operator checks the rides: affect only the rides still open.
Here is another example - two Formulas. First one counts tasks Tasks.Count(). Second one uses the first and shows the workload with If([Task count] > 5, "Heavy", "Light").
And one more example. Let's rate new ride ideas on three dropdowns: Impact, Confidence, Ease. Every option also carries a hidden number: High (3), Medium (2), Low (1). With a Formula you can calculate the actual Score.
Make a new Database called Ride Idea. Set up Single Select Fields for all three: Impact, Confidence, Ease. Add High, Medium and Low as OPTIONS. And turn on the Specify numeric value for each option toggle.
To calculate a score, create a new Formula. Set NAME to Score and FORMULA to Ease.Value * Confidence.Value * Impact.Value. Your backlog should now rank itself.
Formulas inside Automations
Formulas also live inside Automations, and the rules there change a bit. You can find Formulas inside Automations under the f icon next to actions.
First difference is Today(). In Automations you use Now(), because they care about minutes.
Another addition is Step 1. Use it to call a previous action done in Automation.
And finally - you can reach into any Database, no Relation needed. Unlike a regular Formula that only sees related stuff, Formulas in Automations reach anywhere.
Try formulas yourself
That's all the belts. Take a Formulas list and get cooking, or experiment with AI formulas. Try this in your Space and reach out to us if you get stuck.
Bonus: formulas from the video
If you followed our video along, here is every formula used there, in the order it showed up. Copy, paste, tweak.
Lookups & rollups (white belt)
Formula (from the video) | What it does | IRL example |
|---|
Attraction.Zone.Manager
| two-level lookup: task → ride → zone → manager | a workspace's billing country, two hops through its subscription and that subscription's customer Subscription.Customer.Country
|
Inspections.Max(Date)
| latest inspection date for this ride | an investor's last-contacted date, rolled up from every interaction logged with them Interactions.Max(Date)
|
Inspections.Sort(Date).Last().Result
| pull any field off the newest inspection | the stated reason behind a workspace's most recent cancellation Subscriptions.Sort([Cancelled Date]).Last().[Cancel Reason]
|
Inspections.Count()
| how many inspections this ride has had | total candidates who've applied to a job position Candidates.Count()
|
Inspections.Filter(Result = "Failed").Count()
| count just the failed inspections | candidates still sitting unprocessed in a hiring pipeline Candidates.Filter(State.Name = "New").Count()
|
Inspections.Filter(Result.Name = "Passed").Count() / Inspections.Count()
| pass rate as a percent | the Sean Ellis PMF survey score, straight out of Superhuman's product-market-fit template Responses.Filter([...].Name = "Very disappointed").Count() / Responses.Count()
|
Logic (green belt)
Formula (from the video) | What it does | IRL example |
|---|
Today() > [Due date]
| is this task overdue? | literally the same formula, used to flag an overdue asset-maintenance request Today() > [Due date]
|
If(Today() > [Due date], "Late", "On track")
| turn that check into a status label | a salary record's status: Future, Current, or Past If([Approval Date] > Today(), "Future", If([End Date] < Today(), "Past", "Current"))
|
If(IsEmpty([Retired on]), "Operating", "Retired")
| flag rides with no retirement date | a payroll record is "Current" until it gets an end date If(IsEmpty([Valid To]), "Current", "Past")
|
If((Start <= Today()) and (Today() <= End), true, false)
| is this event live right now? | exact same formula, used to flag whether a Shape Up six-week cycle is currently active If((Start <= Today()) and (Today() <= End), true, false)
|
If([Wait time] > 60, "Long", If([Wait time] > 20, "Medium", "Short"))
| nested If — three-way wait time label | a real billing-dunning ladder, escalating by days unpaid If([Unpaid For] <= 14, "Banner for admins", If([Unpaid For] <= 21, "Banner for everyone", If([Unpaid For] <= 28, "Workspace locked", "🤨")))
|
IfEmpty([Finished date], [Due date])
| fall back to the planned date when the actual one's empty | a conversation shows its logged date if set, else falls back to when the record was created IfEmpty(Date, [Creation Date])
|
Combos (black belt)
Formula (from the video) | What it does | IRL example |
|---|
If((ToDays(Today() - Inspections.Max(Date)) > 14) and (State.Name != "Closed"), "🔧", "")
| flag open rides overdue for a safety check | flags an open CRM account that's gone quiet If((ToDays(Today() - Touches.Max(Date)) > 7) and (State.Final != true), "📞", "")
|
Ease.Value * Confidence.Value * Impact.Value
| ICE score — rank ideas by three select fields | same three fields, same idea: this is Fibery's own GIST Planning template scoring its backlog Ease.Value * (Confidence.Value * Impact.Value)
|