If you're struggling to understand SmartSuite formulas, it could be because dot notation seems a little bit confusing. In this post, we're going to unpack what dot notation is and why you should actually embrace it in your SmartSuite formulas. Mastering this will unlock new levels of functionality you cannot achieve without this fantastic feature.
So if learning more about this interests you, let’s dive right into it.
In this blog post, we're breaking down dot notation. We'll explore what it is and how to implement it for your formulas in SmartSuite.
Getting Started with Dot Notation
Let's hop into this topic by first looking at a typical data schema. I'm working with two tables in SmartSuite:
• Consultations
• Users.
Data Schema Overview
In our example:
- The Consultations table links to two types of users: Client and Consultant.
- We restrict the types of users that can be linked in each case. For consultations, clients must have a client role, and consultants must have a consultant role in the Users table.
This structure is essential to understand before we delve into dot notation.
The Goal
We aim to evaluate our consultants based on client reviews and ratings. Specifically, we want to show:
- The number of reviews a consultant has received.
- The average rating of these reviews.
Here's an example output:
"In 2 reviews, Jared has an average rating of 3.5 stars."
Delving into Dot Notation
Dot notation allows us to reference fields within linked records directly in our formulas. This approach is unique to SmartSuite, differentiating it from other no-code platforms like Airtable.
Here’s a breakdown of how dot notation works in SmartSuite:
Formulas: Step-by-Step
Count If Formula
First, we use a COUNTIF formula to count the number of reviews:
COUNTIF(Consultations.Rating > 0, Consultations)
Explanation:
- Consultations is our linked record field.
- .Rating specifies the field within the linked record.
- The formula counts only those records where the rating is greater than zero.
Average If Formula
Next, we calculate the average rating using AVERAGEIF:
AVERAGEIF(Consultations.Rating > 0, Consultations.Rating)
Explanation:
Similar to COUNTIF, it performs an average calculation on ratings that meet the criteria (Rating > 0).
averaged_rating = lambda reviews: sum(reviews) / len(reviews) if reviews else 0
Putting It All Together
Here's how to concatenate multiple parts into one cohesive formula:
CONCAT("In ",COUNTIF(Consultations.Rating > 0, Consultations)," reviews, ",{Name}," has an average rating of ",AVERAGEIF(Consultations.Rating > 0, Consultations.Rating)," stars.")
Ultimately, this formula will output something like:
"In 2 reviews, Jared has an average rating of 3.5 stars."
Use Case Visualization
Let's look at Jared's details:
- Jared has three linked consultations, but only two have ratings.
- The average rating formula will exclude the consultation without a rating, providing a fair average from available data.
Advanced Use-Cases
Dot notation can be used beyond simple averages and counts. You can string together more complex conditions and calculations, elevating your no-code solutions.
One advanced use is concatenating different text elements through linked records relationships. This feature grants unparalleled granularity and flexibility in data manipulation.
Conclusion: Embrace Dot Notation
By now, you should have a clearer understanding of how dot notation can transform how you work with SmartSuite formulas. Whether you’re counting linked records or calculating averages, dot notation equips you with the tools to make more meaningful data connections.
To experience the future of work management firsthand, start your free SmartSuite trial today!
