At the end of a long day, I was on zoom with my friend and coworker Austin. We were debating approaches to a feature where users could input scheduling for a forecasting app.  

Austin: But each time they start to input a row, that’s going to cost them credits… 

Me: Oh, I have an idea! 

Austin: You’re going to say we should use an array, aren’t you… 

It has been a while since I was an intern dedicated full-time to writing blogs about Sigma. For the past nine months, I’ve been developing real apps (not just silly poker games) for real companies using Sigma. Production-grade apps come with their own challenges and lessons. Today, I want to tell you about one of my favorite tools in Sigma’s toolbox, or really, two suites of tools: Arrays and Lists. 

Both are versatile tools that app developers have used for years but are likely less known to your local bar chart dealer. Here are some of my favorite, newly discovered tricks for using Arrays and Lists, and when to use each. 

Arrays vs. Lists 

Before we get into the tips and tricks, you should understand the difference between an array and a list in Sigma, and when to use each. Both approaches can achieve similar results, and arrays and lists each have advantages in certain use cases. Let’s start with lists. 

Lists are single strings used to store multiple points of information separated by a delimiter. The default delimiter is a comma, but you can choose anything you want. You can have lists of text (Value 1,Value 2,Value 3) or lists of numbers (1,2,3). But all lists, and everything in them, are stored as text. This makes lists poor at storing non-text data. 

Lists are also somewhat fragile. For example, if you’re looking for a last name of “John” in a list that also contains “Johnson”, the Contains() function will return true even if “John” is missing from your list. When using lists, you can call on any number of text functions, but the three I use most are ListAgg() (technically an aggregate function), Contains() and SplitPart(). 

An array is a variant form of data, kind of like a simple JSON object. They look like this: [“Value 1″,”Value 2”]. Arrays come with a suite of functions to help manipulate them in different ways. They also store information in a stable, predictable manner thanks to the quoted, bracketed format. On top of that, you can package any data type into an array: text, numbers, even other arrays. So why would you ever need a list? 

Lists, and the functions used on them, can be faster to evaluate when the information stored is simple. For example, if you’re storing system-generated IDs that are all text, all the same length and never contain commas, a list is very good at storing and manipulating that information quickly. Basically, if you need to cast something as a type other than text, or the information you’re capturing is messy or unstructured, like names, an array is probably the way to go. Arrays can also be used with Sigma’s Unnest feature, more on that later. 

A table to help you decide when to use a List versus an Array in your Sigma app. 

Bulk Selection Method 

The first use case for arrays and lists I want to highlight is a selection method: a surface where users select rows in a table by clicking a checkbox. These selections then typically power input table update actions. Sigma has out-of-the-box functionality for this with input tables, but we’ve found that exposing your core input table directly to users can be risky, and linked input tables come with their own limitations, the biggest being that performing source swaps on linked input tables is rather dubious. 

This method lets users select any number of rows in a table, with the added benefits of selecting groups of rows rather than one at a time, and customizing the appearance of the checkbox, all without exposing your core input table or losing the ability to swap sources. Here’s how to set it up with a list. It’s also possible with arrays, and some of the logic can be simpler, but I’ve found lists to be more performant here. 

1. Create a selection control 
   • Text-based control, named appropriately
     
     
2. Create a base input table and a presentation child table
   
   
3. Create a calculated column in the base or presentation table: if(Contains([selection-control], [row id]), “✅“, “❌“). 
4. Create an action sequence triggered by clicking your new column in the presentation table. The sequence needs to cover two conditions. Bonus tip: you can rename action sequences and actions. 
   1. Remove IDs, condition: Contains([selection-control], [Selection/row id]) 
      • Set selection-control = Replace([selection-control], [Selection/row id] & “, “, “”) 
   2. Add IDs to control, condition: Not Contains([selection-control], [Selection/row id]) or IsNull([selection-control]) 
      • Set selection-control = [selection-control] & [Selection/row id] & “, “
        

As a bonus, you can create a button that uses ListAgg() to select all. If you’ve made it this far, I bet you can figure that one out on your own. 

This technique can be expanded to more advanced use cases, such as removing rows from a group, or having a default group that users can both add to and remove rows from. It can also be used to select groups of rows at once. For example, a user clicks a line and the whole invoice is selected, something a simple input table setup can’t do.  

List Updaters 

This one’s straightforward. Say a number of users need to approve the same row, or you want to track unique commenters, store comments, etc., but you don’t want to build a whole table and relationship to track it. In this example, I’ll show off an upvote tracker. 

1. In your core input table, create a text column called [Upvoted_By]. 
2. Create a calculated column [Upvoted_Array] as Json(Coalesce([Upvoted_By], “[]”)).
   
   
3. Create a column [Upvote] in your user-facing table (a child of the backend input table) with this logic: If(ArrayContains([Upvoted_Array], CurrentUserFullName()), “Upvoted”, “Upvote”).
   
4. Create an action to update the row in the core input table, targeting the base table by column [row id]: Text(ArrayDistinct(ArrayConcat([Selection/Upvoted_Array], Array(CurrentUserFullName()))))
   

We’re storing the array as text because, as of this writing, Sigma doesn’t have a viable native variant column for input tables. 

Staging an Insert 

This technique combines controls and Unnest to let users stage an insert without actually inserting any data. You can either insert as arrays or convert lists to arrays in a calculated input table column, depending on what makes the most sense. In this example, we’ll save as a list and convert it to an array, to limit how often we need to change data types, though that decision depends on your use case. 

1. Create a text control and give it an intuitive name 
2. Create an input table with a single row and a column that references your text control, [stage-control] 
   • Repeat steps 1 and 2, adding a column to the input table for each field you want to capture in the stage 
3. Build UI controls and a button for users to stage their records 
4. Add a button that sets control values, creating lists similar to the selection stage but simpler
   
   
5. Create and join Unnest tables for each staged column (or use custom SQL) 
   • Data > Table > Unnest > select an array column > add the values and indices
     
6. Join the Unnest tables together on their indices to support visualization
   
7. Build a presentation visualization or table so users can see what they’re staging 
8. Add a button that inserts the staged rows from the Unnest table
   

The main limitation is that staged rows are session-based until inserted, like controls, so you can’t replace input tables wholesale, and if users don’t commit the stage before logging out, they’ll lose their work. On the other hand, this technique is a nice credit-saver: it eliminates the need for a staging input table, and therefore any insert or update actions that go with it, potentially halving your credit usage in certain cases. 

 The Bulk Insert Method 

I wrote about a single-row bulk insert method in an earlier blog: pack an entire table into one row with ArrayAgg() or ListAgg(), insert that single row, then explode it back out for analysis. At the time, that last step required custom SQL to parse the aggregated columns back into individual rows. The unnest-and-join approach from the staging technique above solves that same problem natively: you can explode the receiver table’s aggregated columns with Unnest tables joined on their indices, the same way we joined the staged columns, without writing any SQL. 

Sigma charges input credits based on rows created, updated or deleted in a published input table: one credit per row, up to a cap of 100 credits per single action. The cap is a great feature which help keep costs from exploding. However, with smart design we can keep our bulk actions well below the cap. For example, insert a 3,000-row forecast with the native action and you’ll hit that 100-credit cap. Capture the same forecast in a single row, and the cost drops to a single credit. 

A few other places the same pattern earns its keep: 

• Bulk-loading a forecast, budget or any tall dataset 
• Snapshotting a table’s full state into a single row for point-in-time comparisons 
• Shuttling data between two Sigma workbooks without tripping per-row credit limits on either end 

That said, custom SQL still has its place. Unnest works well when you’re exploding a handful of columns, but every additional column means another Unnest table to create and join in. If your table is wide, dozens of columns you need exploded, that join chain gets unwieldy fast, and a single custom SQL query that splits every column in one pass will be easier to maintain than a stack of joined Unnest tables. Between this post and the last, you’ve got both paths: unnest natively for a lean, SQL-free setup, or fall back to custom SQL when the table gets too wide to join your way through. 

Conclusion 

Arrays and lists aren’t flashy, but they solve two different problems. They let you track state, selections, approvals, upvotes, without spinning up a new table and relationship for every use case. They also let you move data in and out of Sigma without paying for it row by row. 

I’ve still got a lot to learn about variant data in Sigma, and I’m excited to keep finding new places to put arrays to work in production apps. What use case, optimization or cost savings could your team unlock with them? And no, I’m still not writing out that ListAgg() select-all formula for you. 

References 

• Array functions | Sigma Documentation 
• ListAgg | Sigma Documentation 
• ListAggDistinct | Sigma Documentation 
• SplitPart | Sigma Documentation 
• Json | Sigma Documentation 
• Variant | Sigma Documentation 
• Contains | Sigma Documentation 
• Text functions | Sigma Documentation 
• How to Insert Multiple Rows in Sigma: Bulk Insert vs. Snapshots 
• About billable usage events | Sigma Documentation