Build Complex Formulas in SmartSuite! 🧮

Formulas play a crucial role in creating robust databases within SmartSuite, enabling users to accomplish complex tasks efficiently. However, writing complicated formulas can often seem daunting and overwhelming. In this video, we will break down the process of creating elaborate formulas step-by-step, allowing you to achieve the desired output with ease. So, if you want to enhance your formula-writing skills in SmartSuite, stick around, and let's dive into it.

SmartSuite and No-Code Automation

Before delving into the intricacies of complicated formulas, let's take a moment to introduce SmartSuite. Developed by GAP Consulting, SmartSuite is one of the top no-code solutions we adore. Our mission at GAP Consulting is to assist individuals in getting organized and automated without the need for coding knowledge. SmartSuite is a particularly powerful tool, and in this video, we will focus on writing complex formulas to harness its full potential. But before we get started, we invite you to join our free automation training to familiarize yourself with the key elements of no-code tools like SmartSuite. You can sign up for the training on our website, gapconsulting.io/webinar-registration.

Understanding the Problem

In the context of our video, we will address a common scenario where we need to calculate the percentage of completion for a task based on the completion status of its subtasks. For instance, let's consider a simple solution within SmartSuite comprising tasks and their associated subtasks. Each subtask has a name, a status (complete, ready for review, or in process), and is linked to a task. To simplify this process, we want to create a formula in the tasks application that automatically calculates the task's overall completion percentage based on the completion status of its linked subtasks.

Breaking Down the Formula

To achieve this, it is crucial to understand the components required for the formula. First, we need to determine the total number of subtasks associated with a task. Second, we must identify the total number of completed subtasks. Finally, we will divide the number of completed subtasks by the total number of subtasks and multiply the result by 100 to obtain the percentage of completion.

Simplifying the Formula

While it is possible to create separate fields for calculating each component individually, it is more efficient to build a single formula that combines all the necessary calculations. In our video, we demonstrate the step-by-step approach to create this more complicated formula.

Step 1: Formula One

We start by creating a formula field called "Formula One" that calculates the total number of completed subtasks associated with a task. By utilizing the advanced editor in SmartSuite, we can specify that we want to count the linked subtasks that are marked as complete.

Step 2: Formula Two

Next, we create another formula field called "Formula Two," which simply counts the total number of linked subtasks associated with a task.

Step 3: Final Formula - Percent Complete

Finally, we utilize the results from Formula One and Formula Two to create the final formula that calculates the percentage of completion. In this formula, we divide the value from Formula One by Formula Two and multiply the result by 100 to obtain the percentage. The resulting field is named "Percent Complete."

Combining the Formulas

To simplify the final formula and eliminate the need for separate fields, we copy the formulas from Formula One and Formula Two and paste them directly into the Percent Complete formula. Once we have confirmed that the calculations are accurate, we can safely delete the Formula One and Formula Two fields, as they are no longer required.

Conclusion

In conclusion, writing complicated formulas in SmartSuite becomes much more manageable when broken down into smaller, manageable steps. By following the step-by-step approach demonstrated in the video, users can enhance their formula-writing skills, achieving better, faster, and stronger results in SmartSuite. If you have any questions or require further clarification on anything we covered, please feel free to drop them in the comments section below. In the meantime, keep on building and exploring the endless possibilities of SmartSuite.