Skuid allows you to do a lot of things without code. But sometimes, adding just that little bit of code can take your application from good to great. It can be that magical “missing piece” that fills any design or data gaps and makes the user experience just a little more seamless.
For those without technical backgrounds, adding code to your app can initially seem somewhat intimidating. However, Skuid makes it easy by providing Skuid Labs, where the Skuid builder community can share pre-built pages and code samples that anyone can easily download and implement. One of my personal go-to code resources for Skuid is custom aggregation formulas, which can be found here. In this post, I’ll go through custom aggregation formulas, how to use them, and a real-world use case you can apply to your own applications today.
What’s a custom aggregation formula?
As the name implies, a custom aggregation formula allows you to aggregate data from your models. If you’re familiar with Excel, you’ve likely used functions like SUM, COUNTIF, AVERAGE, etc. These formulas also allow you to manipulate your data in cool ways. For example, you might use the COUNTIF or SUMIF functions to add useful conditions, like “return a count of leads that are rated as ‘warm.’”
These custom aggregate formulas basically do the same thing, with the main difference being that in Skuid, you must provide the functions with the name of a Model whose data you would like to aggregate (vs. a range of cells like you would in Excel).
When and how to use custom aggregation formulas
Typically, you can use Skuid Aggregate models to return things like sum, counts, averages, etc. However, not all data sources allow for aggregation models. And even when using a robust data source like Salesforce, custom aggregation formulas can let you move beyond the out-of-the-box limitations of the tool.
Generally, we recommend using custom aggregation formulas when:
- Your data source doesn’t support aggregate models
- You want to summarize UI-only data
- You want to summarize data by fields that don’t support aggregations (e.g., multi-select picklists)
To use these formula functions, add the aggregationFunctions.js file as a static file in your Skuid Platform site or as a static resource in your Salesforce org. Then you can reference this static file/resource as a JavaScript resource in your Skuid Page. This is my preferred way to enable these aggregation functions, as it's more performant than including the JS inline on every Skuid page.
.png)
However, you can also add it as an inline JavaScript resource in any Skuid page where you want to use these functions. For simplicity, import the example page, AggregationFunctionsExample.xml, into your Skuid site and experiment with the functions there!
All functions are contained in a single JS file because (a) they rely on a common utility function, and (b) this enables the functions to be easily dropped into any Skuid Page via a single inline JavaScript resource. The full list of included functions can be found here.
Use case: Certification reporting
Recently, we decided to use these aggregation formulas to answer some of our key questions surrounding the new Skuid Certification exam. For example, when we were testing questions for the exam, we needed to see how many questions we had for each topic.
That meant we needed to summarize our data by the values of a multi-picklist field. The catch? Each question could be connected to multiple topics. As a result, we couldn’t use the typical Salesforce reporting methods (you can group by single-select picklists, but not multi-select). Using our aggregation formulas, however, we were able to get the data we needed and display how many questions were related to each topic.
Once we launched our Certification Exam, we also wanted to know how many customers had completed their certifications. Specifically, we had a report of builders who had passed the exam but needed a way to aggregate the data and group it by customer account.
We started by creating a conditional count based on values in another object. For reporting on our certification program, we need to view and report on data from multiple systems: our learning management system (REST), our testing provider (OData), our badging provider (REST), and our CRM (Salesforce).
Skuid to the rescue! We could set up data source connections to all these systems and show them side by side. The goal was to see how many accounts had certified users. So, from the testing system, we got a list of users who passed the exam and their emails. Then we queried contacts from the CRM with those emails.
Finally, we queried accounts related to those contacts to get a list of accounts with certified builders. But the tricky part was returning the total number of certified users related to each account. Again, this was where custom aggregation formulas came in clutch. On the account model, we created a UI-Only formula field to return a count of users from the contact model that were related to that account.
The formula was:
AGG__COUNTIF("Contacts", "Email", "AccountId", {{Id}})
That is, “return a count of contact emails if the AccountId field from the contact model matches the {{Id}} field from this (Account) model.”
In the end, we were able to discover how many users from each customer account had passed their certification exam.
Implementing your own custom aggregation formulas
Of course, your own mileage may vary. However, I think you’ll find a number of potential applications for custom aggregation formulas in your own Skuid applications and pages. For instance, maybe you have your own internal learning modules/educational programs and want to know how many employees from each department have completed them. Or perhaps you need to know how many customers fit into a specific market segment based on multiple criteria. Really, the possibilities are endless!
Need help getting started with Skuid Aggregate formulas? Check out the Skuid Community.
