Building a Table with Row Grouping, Using Variable Mutations

July 2, 2023 · 4 minutes

· 0

Intro

Building on the theme from the accordion widget post, today we'll be building a table widget with row grouping, or a tree table, nested rows, or whatever you want to call it. This post will get into some advanced techniques involving variable mutations, array methods, and Lodash. Let's get started!

Goal

Build a table widget that groups the rows by a certain column, to 'roll-up' the data. The user should be able to click any group to expand the matching rows, as well as change the column that the data is being grouped by.

To achieve this, we'll need a function that returns just the group labels, for when the groups are collapsed. Then we'll need another function that finds the matching rows for the selected group. Lastly, we'll merge these datasets together and sort the output, before connecting it to the table widget.

Sample Data

I used ChatGPT to generate some sample data that had repeats in some columns, so the data can be grouped. Then I saved this as JSObject1.sampleData, and connected it to a table widget. This table is just to display the raw source data.

You can replace the sampleData with data from your own API or query and apply the same grouping technique.

Building the UI

First, add a table widget to show the grouped data, and a select widget to choose the column to group by. To generate the select options, just use the Object.keys() method on a row of the sample data, then map over it to build the select options.

{{Object.keys(JSObject1.sampleData[0]).map(k=>({label:k,value:k}))}}

Variable Mutations

Appsmith now supports variable mutations, which means you can update values of the variables declared in your JS Objects. Previously the storeValue() method was used for more complex UI tasks like this, but now variable mutations can be used to simplify the code and improve performance!

Start by declaring a few variables that will be used in several of the functions.

	data: this.sampleData,
	column: sel_groupByCol.selectedOptionValue,
	value: '',

This gives you a single place to update the values that will be passed to the other functions. It also keeps you from having to repeat the longer names throughout the other functions, so you can just reference this.data, this.column, and this.value.

Next, add a function that returned just the groups, with the data deduplicated and sorted.

    groups: ( column=this.column, data=this.data) => {
        const colValues = data.map(r=>(r[column]));
        return [...new Set(colValues)].sort().map(g=>({[column]:g}))
    },

And now to find the matching rows that have the same value as the selected group:

	rowMatches: (column=this.column, data=this.data) => {
		const value = this.value;
		return data.filter(row=>row[column]===value)		
	},

Then, the two datasets have to be merged and sorted, by the same column that we're grouping by.

	groupWithMatches: (column=this.column) => {
		const groups = this.groups();
		const rowMatches = this.rowMatches();
		const returnData = groups.concat(rowMatches);
		return _.orderBy(returnData, [column],['asc'])
	},

And lastly, the rowMatches() and groupWithMatches() functions have to be re-run with each selection of the table widget. So a wrapper function is used to call the first two functions every time a row is selected.

    rowSelected: () => {
        this.column = sel_groupByCol.selectedOptionValue;
        this.value = tbl_groupedData.selectedRow[sel_groupByCol.selectedOptionValue];
        this.rowMatches();
        this.groupWithMatches()
    },

Putting it all together

Here's the full JSObject:

export default {

	data: this.sampleData,
	column: sel_groupByCol.selectedOptionValue,
	value: '',
	
	onOpen: async () => {
		this.rowSelected()
	},

	rowSelected: () => {
		this.column = sel_groupByCol.selectedOptionValue;
		this.value = tbl_groupedData.selectedRow[sel_groupByCol.selectedOptionValue];
		this.rowMatches();
		this.groupWithMatches();
		resetWidget('tbl_groupedData')
		
	},

	groups: ( column=this.column, data=this.data) => {
		const colValues = data.map(r=>(r[column]));
		return [...new Set(colValues)].sort().map(g=>({[column]:g}))
	},

	rowMatches: (column=this.column, data=this.data) => {
		return data.filter(row=>row[column]===this.value)		
	},

	groupWithMatches: (column=this.column) => {
		const groups = this.groups();
		const rowMatches = this.rowMatches();
		const returnData = groups.concat(rowMatches);
		return _.orderBy(returnData, [column],['asc'])
	},

	sampleData: [
		{
			"category": "C",
			"text_1": "foo",
			"text_2": "bar",
			"num_1": 1,
			"num_2": 45
		},
		{
			"category": "B",
			"text_1": "baz",
			"text_2": "qux",
			"num_1": 2,
			"num_2": 78
		},
		{
			"category": "C",
			"text_1": "abc",
			"text_2": "def",
			"num_1": 3,
			"num_2": 12
		},
		{
			"category": "D",
			"text_1": "ghi",
			"text_2": "jkl",
			"num_1": 4,
			"num_2": 56
		},
		{
			"category": "A",
			"text_1": "abc",
			"text_2": "555",
			"num_1": 2,
			"num_2": 4
		},
		{
			"category": "A",
			"text_1": "xyz",
			"text_2": "123",
			"num_1": 1,
			"num_2": 90
		},
		{
			"category": "C",
			"text_1": "mno",
			"text_2": "pqr",
			"num_1": 4,
			"num_2": 34
		},
		{
			"category": "B",
			"text_1": "rst",
			"text_2": "uvw",
			"num_1": 3,
			"num_2": 67
		},
		{
			"category": "D",
			"text_1": "ejhi",
			"text_2": "fgb",
			"num_1": 3,
			"num_2": 11
		}
	]
}