[Tip of the Week] Array of Unique Values from Aggregation

1

hello all!!

for this week’s Tip of the Week, we wanted to share how you can create an Array of Unique values from a list in a column in your Table. this features enables many interesting use cases - you may want to display all the Users working on a specific product, the dates in which you have upcoming shipments, or display the results of an audit procedure you did in your operations. with any Column in your Table, you can now easily find the unique values within an organized (filtered, sorted, etc.) set of Table Records.

here’s a guide on how to do this:

  1. create a Table query with the Filters, Sorting and Limits you’d like to set:
    1
    13104×2024 307 KB
  2. add a Table Aggregation, select the Unique as the Calculation and the Column you’d like to find Unique values for:
    2
    23104×2024 323 KB
  3. once you display the Aggregation in your App, you’ll see an Array of Values available for use:
    Screen Shot 3
    Screen Shot 32196×1514 126 KB

hope this helps, let us know if you have any questions!!

2

thanks for share. I have not accessed community for a few weeks.

1 Like
3

no problem @mcc6025, let us know if you have any questions on using this Table Aggregation!!

4

Hello @gio is it possible to sort the aggregation? I have made a sorted query sorting in alphabetical order but my aggregation is not sorted as well.

5

You could try sorting your query by the Column of Interest from which you are aggregating unique values.

However, if you have more than records in your table than your query limit, this could affect which values appear in your aggregation - i.e. if you sort by your Column of Interest from A to Z, and all of the first 1000 records in your table have the same value in the Column of Interest, then you are only going to get an array of that one value as your aggregation result.

I am thinking of two options here:

One is to store any unique values for that column in a “helper table” - many ways to achieve this, it could be that you want to create this table prospectively to constrain selections to a list (in which case you could just get your unique values aggregation from that table instead, using a query that sorts the way you want), or you could have trigger logic when the value for this column field in your original table is populated that there is a check of your helper table and if the entry is unique, it is added to the helper table.

The other option is to piggyback your query/aggregations.

  • Your first query would give you the record ID unique values according to your filter/sort/limit conditions (i.e. the 1000 most recent created records with some conditions being true).
  • The piggyback query can filter your table by all the records whose ID “IS IN” the array above, and should be sorted accordign to your Column of Interest.
  • The aggregation on the piggyback query would be for unique values in your Column of Interest.
6

I would prefer the second option or using a custom widget for sorting.

Another way would be using the Tables API :slight_smile:

7

I haven’t used the Tables API much, could you elaborate on how that would work?

8

you create a http-connector that has your tulip instance as server address…

then you create a connector function that points to the /runAggregationendpoint.

There you configure the query parameters according to your need.
The Endpoint is described here:

Run an aggregate function (e.g. average, mode) on a table’s field. - Tulip Tables

Find more genral information about the tulip API’s here: Introduction

If you just start with this topic, this might feel overwhelming, but it is actually not very hard.
And it is worth it. It is very powerful!

A sample call could look like:

{instance}/api/v3/tables/tableID/runAggregation

And Parameters could look like so:

image
image3580×801 90.4 KB