Anvil Datagrid vs. jQuery DataTables

Hi All, I’m looking for a little perspective here. One of my non-Anvil web applications heavily uses the jQuery DataTables plug-in (sample shown below):

I’ve taken advantage of some of the customizations you can do with DataTables, but nothing really crazy like multi-column sorting.

Anyway, I am considering moving this over into an Anvil (loving it a lot :slight_smile: ) app and this one data presentation component is giving me a pause. I’ve looked at both the datagrid component and the repeating panel component and neither have sorting or filtering built-in. I’m not opposed to adding that functionality in via code and some sort of UI elements, but am just a little wary of it being a little more complicated than I think it is, or maybe performance not being quite snappy. In my other application, I’m populating the DataTable via an AJAX call to a cloud-hosted SQL Server database and it is quite responsive even on a dataset of maybe 600 to 700 records. Each record is comprised of maybe 20 columns of text, none of which are more than 20 or so characters. On a large dataset, the initial call takes a few seconds, and then once loaded, sorting and filtering is really quick.

Initially, I’m thinking I run a call to SQL Server and bring back all data rows and storing that in a dictionary and populating a datagrid with that. For sorting, I’m thinking of probably just a lambda function. For filtering, not quite sure, but maybe I have to go back to that original dictionary and repopulate the datagrid with only records that match a filter word. I expect a little performance hit, and that is okay.

Anyone tackle a similar migration? Should I just dig in and start working through the Anvil equivalent? Or perhaps there is some way to implement a jQuery DataTable in Anvil?

Thanks in advance,
David

1 Like

I don’t know jquery very well so I can’t speak to any particular feature of jquery, but jquery is installed in Anvil so I would be surprised if copying your jquery wouldn’t work.

As to Anvil/python solutions:

For filtering you could achieve that by filtering the data before populating the repeating panel/data grid with queries when searching your data and/or iterating through the data with a for-loop or list/dictionary comprehension and filtering with an if-condition.

As for sorting, you could use the in built python sorted function/sort method to sort the data with a function/lambda as the key argument of both of those functions. (N.B. sorted returns the sorted list so requires reassigning the iterable it works on, while sort sorts in place and changes the iterable it works on and returns nothing). If the sort happened after loading the repeating panel/data grid, you would then want to reassign the new sorted data to that component.

Link to querying the database in Anvil:

You might look at the Tabulator component: Tabulator - with anvil components

2 Likes

Thanks for the suggestion and your pointers.

1 Like

Wow, that looks amazing. Will have to explore it and thanks for noting that. I did look through the Anvil library but only saw a handful, none of which related to tables or grids.

1 Like

That Tabulator component is a part of a third-party library called Anvil-Extras. If you don’t see something you want in the normal Anvil Library, I would suggest checking Anvil-Extras first. The maintainers are very active Anvil users themselves.

There’s also a wiki post that collects useful third-party components: [Wiki] Awesome custom components, dependencies (and the like)

2 Likes

Quick correction: Tabulator is a standalone dependency at present - so not part of anvil-extras. But it can also be added as a third party dependency: TGQCF3WT6FVL2EM2

It should work well for this use case. It has the filtering and sorting built in and works with a list of dicts.

2 Likes

I simply cannot recommend @stucork 's Tabulator highly enough.

I use it in every single project I have (that requires a table, which is all of them).

4 Likes

Very very cool, been noodling around with the clone. When reading through the Tabulator page, I see a wealth of information. One thing in particular that seemed to stand out (for my use case) is that you can freeze columns so they stay in place when left/right scrolling. When I tried that with the clone, it moved the name column all the way to the right and I couldn’t seem to get it to the left. Is that due to some limitations we have when going through our Anvil implementation of Tabulator?

That’s just a bug. The checkbox column is inserted by the anvil version for convenience, which would also need to be frozen for the first ‘real’ column to be frozen.

Try now it should be fixed.
Also use True instead of 'true'

Not sure where to designate that first column to be frozen as it is not in the self.tabulator.columns definition. However, when removing that checkbox entirely (as shown below), it works out ok and the name column is frozen. For my purposes, I would not use the checkbox column, so no loss in functionality there.

Thanks again,
David

1 Like