Preserving the order of columns when getting a list of column names

Anvil Q&A
1

The idea:
I have a data table with columns where every time the user adds a new product, it auto creates a column. This is intended to allow the user to be able to track usage by a specific date, or date range. New rows are added to that table whenever a product’s quantity changes for any specific day that it’s used, since the very left column is a Date column. The idea is you pull all the rows from the table whose date matches the cell values in the Date column so that the user can see all the quantity changes on that day/date range.

Here’s what I’m struggling with:
Because the columns are dynamic depending on how many new products the user has added and when the application is used, I need to be able to access a list of them in their data table order at any given time. I know that I can do app_tables.table.list_columns, but the issue is that it loses its order and returns the list of dicts in alphabetical order. Is there a way to pull the column names without losing the order?

2

Would either of these work?

  1. Create a separate data table with a Simple Object column that stores the column names in order.
  2. Look at the earliest date at which each column contains a non-None value to infer the order of column creation.
1 Like
3

Hi @vquach.

Generally, this is an awkward way to do things that is going to cause some pain! In a database, the set of columns is supposed to stay fixed, and you represent data with additional rows. That is to say, it’s “long” data, rather than “wide” data.

So, instead of:

date qty_product_A qty_product_B qty_product_C
2021-01-01 1 2 3

you should make a table like this, which represents the change for each product individually:

date product qty
2021-01-01 A 1
2021-02-15 B 2
2021-03-01 C 3

That way, you don’t have to rely on column ordering (which, as you have discovered, isn’t predictable!)

Also, as a general performance tip, and making a guess about your application, I would recommend creating two tables - one with the current quantity of each product, and one for the historical data. That way, it’s always easy to check current stock levels (a common operation), but you can still query historical data.

4 Likes