Formulas don’t have to be scary

Formulas_quick_tips_blog

There is never a dull day on the customer support team. Our customer’s problems are always unique, but many issues have similar themes. My name is Derrick Kotlus and I am a Customer Support Advocate here at TrackVia.

If the formula function in TrackVia is intimidating, you aren’t alone. A large portion of the calls and questions we receive from customers are formula related. Luckily, these questions can be taken care of quickly. Once you have a grasp of how formulas work, taking off from there is easy and you shouldn’t have any more problems. I’ve come up with my top five tips when it comes to dealing with formulas.

1. Not sure which syntax to use

The formula box is an interactive field that allows you to create formulas without knowing the exact syntax of each function. Simply clicking into a field will display a pop up functions, lookups, and fields that available for you to use. Typing an open curly bracket, { , will display all fields from the current table as well as an associated table for you to use. Below are two examples of possible formulas that would appear.

formula_blog_screenshotformula_blog_screenshot2

2. Choosing the correct calculated field type

Choosing the correct calculated field type is very important. The correct type of field depends on the value that you will be outputting. Additionally, the same formula in two different field types will appear differently. Below is an example of how a date looks in a calculated date field and a calculated text field.

formula_blog_screenshot3

3. Formula/filter relationships

Formulas can be used in conjunction with filters to create very specific and powerful views.  For example, if you wanted to see a list of projects that are due today, you can use a formula to output “1” when the date in a due date field is equal to today. We’ll call this field “Due Today.” You can then build a filter to say, “show me records where ‘Due Today’ is exactly 1.”  The formula could look like this: if({Due Date} = today(),1,0).

4. Specifying a condition

“If” statements are arguably the most powerful and most frequently used function. They allow you to specify a condition, then control what the output is when the condition is true and when the condition is false. The syntax (order) is as follows: if(Condition,True,False). For example, let’s say that you have two sales reps. Rep 1 controls the western region and Rep 2 controls the eastern region. If you want to output their name based on what region is selected in the Region field, you would use the following formula: if({Region} = “West”,”Rep 1”,”Rep 2”). This states that if the region selected is the west region, output Rep 1. In all other scenarios, output  Rep 2.  Advanced tip: If you have more than one condition, you can use “and()” and “or()” functions to add extra functionality.

5. Incorrect output or no output

Troubleshooting functions can be difficult. Sometimes functions are accepted by TrackVia but do not display the correct output, or any output at all! On top of this, when you have complex formulas, it can be very difficult to determine which part is failing. A great way to get to the bottom of this issue would be to break your function down into separate parts and place them in separate fields. This way you can identify which parts of the formula are working and which are not.

Thanks so much for reading. I hope this was helpful in making formulas less intimidating. If you have an other questions, please feel free to comment below or refer to our knowledge base. You can also reach out to us on Twitter.