How to add sorting functionality to datagrid with repeating panels?

Anvil Q&A
1

What I’m trying to do:

I have a datagrid that consist of repeating panels, populated by data returned from a SQL call. I would like to be able to sort on one column containing numeric values. Is there a way to enable this functionality?

What I’ve tried and what’s not working:

I have not been able to comprehend any of the solutions offered by other responses within the forum, as the most common recommendation is to replace the header row with a different element. However, I have not been able to figure this out.

Code Sample:

# this is a formatted code snippet.
# paste your code between ```

Clone link:
share a copy of your app

2

Is it always sorted on that same column? Or do you need to allow the user to choose the column to sort on?

How much data is involved? A few hundred rows, or tens of thousands?

3

Hello,

It will always be the same column. And at most it will be 100 rows.

4

If it’s always the same column, why not do the sorting when you are retrieving the data from the database? Databases are good at sorting efficiently.

2 Likes
5

If the sorting is always on the same column, then you can get the list sorted by the database as @jshaffstall suggests.

If the list has few items (100 is a few), then you can sort it when you assign it. Something like this (untested, I’m on my cell now):

repeating_panel.items = sorted(items, key=lambda row: row['key'])

If you want the user to decide which column to order by and you are showing plain numbers or text (nothing fancy in the template form), then you can use the DataGridJson. I use it on all my production apps, it’s a time saver and makes my apps look consistent to each other.

1 Like
6

I’m using similiar solution. Up to 2000 rows the perfomance is not a big issue:
column_name = your key from databse

I’m using custom header row with links to control the sorting. So I can sort after each column.

image
image1838×89 62.5 KB

Function responsible for sorting:

  • watch out if you want to sort the linked columns. For e.g. [‘user’][‘email’]
  • for more rows 2000+, call on server side is more suitable. In my case I use self.Search_click() function that I use to filter my database with other textboxes.
 def sorting_function(self, column_name, sorting_way):
    """function used for sorting in combination with headers"""
    self.lb_sort_col.text = column_name
    if sorting_way == True:
      self.lb_asc_dsc.text = 'dsc'
    else:
      self.lb_asc_dsc.text = 'asc'
    if int(self.total.text) < 2000: #large data need to be sorted on server side by a new call
      key_func = lambda x: x[column_name] or ''
      if column_name == 'DUMMY':  # replace 'user_column' with the actual name of the column, replace DUMMY to make an exception for user columns
        key_func = lambda x: x[column_name]['email']  # replace 'email' with the actual key for the user email
      self.repeating_panel_assay.items = sorted([r for r in self.repeating_panel_assay.items],
                                                  key=key_func,
                                                  reverse=sorting_way)
    else:#big data server side sorting
      self.Search_click()

Function to control the icons of header:

  def set_sorting(self, object_name, column_name, **event_args):
    """This method is called when the link is clicked, sort after ACN column"""
    sorting_way = None#variable for sorting order
    list_all_links = [self.h_matrix, self.h_long, self.h_short,
                      self.h_application, self.h_indication, self.h_active_inactive]
    #check if some other column were already sorted and set their icon back to the normal unsorted state
    for links in list_all_links:
      if object_name != links:
        links.icon = 'fa:sort'
    #change icon of sorted column
    if object_name.icon == 'fa:sort':#never sorted
      object_name.icon = 'fa:sort-asc'#set ascending
      sorting_way = False # ascending
    elif object_name.icon == 'fa:sort-asc':#already sorted as ascending
      object_name.icon = 'fa:sort-desc'#set descending
      sorting_way = True # descending
    elif object_name.icon == 'fa:sort-desc':#once again get back to ascending
      object_name.icon = 'fa:sort-asc'
      sorting_way = False # ascending
    self.sorting_function(column_name,sorting_way)
    pass

Link calls of header columns:

  def h_matrix_click(self, **event_args):
    """This method is called when the link is clicked"""
    object_name = self.h_matrix#Link name
    column_name = 'matrix'#real name in database
    self.set_sorting(object_name, column_name)

  def h_long_click(self, **event_args):
    """This method is called when the link is clicked"""
    object_name = self.h_long#Link name
    column_name = 'name_long'#real name in database
    self.set_sorting(object_name, column_name)

  def h_short_click(self, **event_args):
    """This method is called when the link is clicked"""
    object_name = self.h_short#Link name
    column_name = 'name_short'#real name in database
    self.set_sorting(object_name, column_name)