Screenshot 2023-07-02 at 3.47.10 PM.png
Cover image for joseph_appsmith

Joseph Petty Verified userVerified user

Sr. Developer Advocate

Appsmith

Building a Table with Row Grouping, Using Variable Mutations

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. 

raw dataset

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. 

 

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}))
    },
groups

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
		}
	]
}
final result

 

And here's the finished app:
https://app.appsmith.com/app/groupby/table-row-grouping-64a1c9ac39b60e452e6d53c5

Feel free to fork this and copy the code to use in your own app, then change out the sampleData for your own.