Control table column order with dynamic query

Hi all. I have a query which returns a dynamic number of columns (dates in this case). I'm basically building a pivot table.

Tables appear to remember the placement of columns. Say my first query returns this:
2021-01-01
2021-02-01
2021-03-01
2021-04-01

If I then change the query to include a date within those dates, the table adds it as a column at the end, even if in the JSON it's in the right sequence

2021-01-01
2021-02-01
2021-03-01
2021-04-01
2021-01-10

I have tried using 'dynamic column settings' and have been successful in things like renaming columns, but am unable to change the order.

Is there anything I can do? Happy to consider:

  • Something that completely resets the table as it's always correct the first time

  • hand-crafting a javascript snippet to re-order the columns, maybe setting column index or similar

  • javascript to move an individual column that wasn't in the table previously.

  • Delete and recreate a table? but don't think this is practical

  • Return a 'synthetic column header' as the first row of my query, but that's a bit rubbish.

Any ideas? Retool team help?

Thanks!

3 Likes

Hey domjammoo, happy to help here 😁 As you suggested, it looks like a hand-crafted JS transformer on your query could be a workaround for your use case. I am looking into whether there is a better way to do this in Retool and will keep you posted!

Thanks everett. In terms of what the transformer would do, though, is there a way to programatically control the order of the columns? As I mentioned, the query output is in the correct order, it's just that the table column order doesn't change when the query output changes

Hey domjammoo,

I'm sorry to say that it looks like my first response was too optimistic. Our table components "remember" the ordering of columns and it is not possible to dynamically change the column order as a result :cry:

It looks like the only potential workarounds here are quite impractical:

  • Manually create a new table component populated with sorted data each time you need to add a column.
  • Create your own custom component (if you have access that feature on your current plan) that allows you more control over the order of your columns.

I apologize for my initial misleading response—I wish I had better news for you :pensive:

Would you mind if I changed this post from a 'How Do I' to a 'Feature Request' so that other users who are interested can more easily chime in on this?

Yep feel free to change to a feature request.

Don't think i have the chops to do a custom component. The option, however of returning a first row which is a logical header row is not the end of the world - I can programatically format that row to a different colour etc, and it would work. Think I'll explore that route. And then the actual column names can just be a numeric sequence.

will let you know how i get on

thanks

1 Like

Hey domjamoo, Thanks I went ahead and changed it to a feature request. If you have success with your idea of using a logical header row and if you don't mind, could you share that solution here? I imagine this workaround might be helpful for other users.

1 Like

+1 for this feature.

We, too, have this exact use case, where we have table components with a variable number of columns, many of which are labelled by date, and changes to the query result in dated columns out of order. The ability to set column order in a transformer would be a huge value add.

+1 on this feature, very similar use case.

@everett_smith can you explain more how creating a custom component would solve this issue?

+1 On this feature.

Hi all just want to chime in here, it looks like the best workaround is currently to have a header row that is decoupled from the column headers and then assign those manually along with the data. It's far from ideal but I've included a screenshot and sample app for illustration.

Let me know if you have any questions!
Dynamically Ordered Columns.json

Thank you for this. I tried using the json file you listed and it seemed incomplete as far as the example goes. Thank you

Hi retool staff, is there any news for this feature request?

I have multiple queries based on user activity one of the queries are triggered and shown on the table component.
The problem is column order is drawn wrongly on the table component but the SQL query has the right ordering.

Hi @az1z Not yet, unfortunately! But I'll update this thread when our team is able to ship a fix

So this is what I've done, bit rough and ready. We have a need to track invoices across multiple legal entities in different countries on different accounting platforms which is what this is for.

The source data in this example is a view called v_invoice_payment_date. You should be able to swap this out for any data source as long as the data is structured in a way that is 'pivot-able', i.e. organised vertically (you cannot pivot an already cross-tabbed data structure).

First I create an array from the column names of the query:

Then I put that in a table

You then select a vertical value, which needs to be the same as the 'pivot category', the horizontal value, and then the pivot value itself. You do not 'save' this table in any way; a later step uses recordUpdates to access what's been checked.

In parallel I have this transformer which turns the SQL input into an array. It also applies a bunch of filters based on some UI components but these are optional, the only mandatory thing it does is apply formatDataAsArray to the original v_invoice_payment_date data source.

image

This is the main chunk of code. I use a timestamp as the prefix for the column headings so that they should never duplicate. I then create a 'synthetic' column heading for my first row.

// Add sequence number
// Generate the columns from a number of header columns
// FORCE A FIRST ROW THAT HAS ALL OF THE DATES IN THE RIGHT ORDER WHEN THE APP IS LOADED TO PREPARE THE TABLE, WHICH THEN GETS OVERWRITTEN BY THE REAL QUERY  


const x_uniq_runtime = "___________________" + Date.now().toString() + "_"
const x_random = x_uniq_runtime + "total" 

const x_data_input = {{v_date_range_filter.value}}

      
// The column names and whether they have been selected 
const x_table_object_keys = {{table_object_keys.recordUpdates}}
  	.filter(x => x['Pivot Category'] === true)
		.map(x => x['Object Key'])

const x_pivot_category_vertical = {{table_object_keys.recordUpdates}}
  	.filter(x => x['Pivot Category Vertical'] === true)
		.map(x => x['Object Key'])

const x_pivot_category_horizontal = {{table_object_keys.recordUpdates}}
  	.filter(x => x['Pivot Category Horizontal'] === true)
		.map(x => x['Object Key'])

const x_table_pivot_value = {{table_object_keys.recordUpdates}}
  	.filter(x => x['Pivot Value'] === true)
		.map(x => x['Object Key'])

// https://stackoverflow.com/questions/54907549/keep-only-selected-keys-in-every-object-from-array
const x_data_categories = x_data_input
		.map(x => 
         x_table_object_keys.reduce((acc, curr) => {
      	 acc[curr] = x[curr];
      	 return acc;
    		 }, {})
        );

const x_data_categories_uniq = _.uniqWith(x_data_categories, _.isEqual) // using lodash, works.

// GENERIC: create array of unique combinations of x, y and the value
const x_pivot_data = x_data_input
	.map(
	  ({[x_pivot_category_vertical]:pivot_category_vertical,
    [x_pivot_category_horizontal]:pivot_category_horizontal, 
    [x_pivot_category_horizontal]:pivot_category_horizontal_label, 
	  [x_table_pivot_value]:pivot_value})  =>
	  ({['pivot_category_vertical']:pivot_category_vertical,
    ['pivot_category_horizontal']:x_uniq_runtime + pivot_category_horizontal,
    ['pivot_category_horizontal_label']:pivot_category_horizontal_label,
    ['pivot_value']:pivot_value})
	)

// GENERIC: Create horizontal dimension
// n --> item in array, i --> index of item, a --> array reference, (in this case "list") (note x is different to my use)

const z_dim = x_pivot_data
		.map(x => x.pivot_category_horizontal)
    .filter((n,i,x) => x.indexOf(n) === i)
    .sort()

var pivot_value = 0;
var array_out = []
var array_out_formatted = []

var o_row_object = {};
var o_row_object_formatted = {};


// CREATE HEADER ROW, BIT CLUNKY

let first_column_name = Object.keys(x_data_categories_uniq[0])[0] // First key of first object in array

const z_dim_labels = x_pivot_data
		.map(  
      	({['pivot_category_horizontal']:pivot_category_horizontal, 
          ['pivot_category_horizontal_label']:pivot_category_horizontal_label}) => 
      	({['pivot_category_horizontal']:pivot_category_horizontal, 
          ['pivot_category_horizontal_label']:pivot_category_horizontal_label})
      )
    .sort()

const z_dim_labels_uniq =  _.uniqWith(z_dim_labels, _.isEqual) // using lodash, works.

	o_row_object = {}; // blank out 
  o_row_object_formatted = {}; // blank out 
	z_dim.forEach(function (y_item, y_index) {
    let pivot_value = z_dim_labels_uniq
			.filter (x => x.pivot_category_horizontal === y_item)
    	.map(x => x.pivot_category_horizontal_label)
    
    Object.assign(o_row_object, {[first_column_name]: first_column_name, [y_item]: pivot_value});
  	Object.assign(o_row_object_formatted, {[first_column_name]: first_column_name, [y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0})});
		})

  Object.assign(o_row_object, {[first_column_name]: first_column_name, [x_random]: 'row_total'});
	Object.assign(o_row_object_formatted, {[first_column_name]: first_column_name, [x_random]: 'row_total'});


	array_out.push(o_row_object);
	array_out_formatted.push(o_row_object_formatted);

const x_slice_start = x_table_object_keys.length
const x_slice_end = z_dim.length

// MAIN LOOP

x_data_categories_uniq.forEach(function (x_item, x_index) {
  o_row_object = {}; // blank out 
  o_row_object_formatted = {}; // blank out
  x_row_total = 0; // blank_out
  z_dim.forEach(function (y_item, y_index) {
    let pivot_value = x_pivot_data
    		.filter(x => x.pivot_category_vertical === x_item[x_pivot_category_vertical] && x.pivot_category_horizontal === y_item)
    		.reduce((prev, cur) => prev + cur.pivot_value, 0);

    Object.assign(o_row_object, x_item, {[y_item]: pivot_value});
    Object.assign(o_row_object_formatted, x_item, {[y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0}) });
    x_row_total = x_row_total + pivot_value;
  })
  
  // row total

  Object.assign(o_row_object, x_item, {[x_random]: x_row_total});  
  Object.assign(o_row_object_formatted, x_item, {[x_random]: x_row_total.toLocaleString(undefined, { maximumFractionDigits:0})});  
  array_out.push(o_row_object);
  array_out_formatted.push(o_row_object_formatted);
})


// END OF MAIN LOOP

// Column totals
	
	o_row_object = {}; // blank out 
  o_row_object_formatted = {}; // blank out 
  x_row_total = 0; // blank_out
	z_dim.forEach(function (y_item, y_index) {
    let pivot_value = x_pivot_data
			.filter (x => x.pivot_category_horizontal === y_item)
    	.reduce((prev, cur) => prev + cur.pivot_value, 0);
    
    Object.assign(o_row_object, {[first_column_name]: "col_total", [y_item]: pivot_value});
  	Object.assign(o_row_object_formatted, {[first_column_name]: "col_total", [y_item]: pivot_value.toLocaleString(undefined, { maximumFractionDigits:0})});
        x_row_total = x_row_total + pivot_value;

  })

// Total row total (duplicated code)


	  Object.assign(o_row_object, {[first_column_name]: "col_total", [x_random]: x_row_total});  
  	Object.assign(o_row_object_formatted, {[first_column_name]: "col_total", [x_random]: x_row_total.toLocaleString(undefined, { maximumFractionDigits:0})});  


	array_out.push(o_row_object);
	array_out_formatted.push(o_row_object_formatted);


return array_out_formatted

The next table uses this as the data source
image

Which gives this, in correct date order:

The actual column names look like this. I was originally prefixing them with a string of underscores to stop them being visible, but it's basically timestamp then date, which is intrinsically sorted in that order in the query, therefore they will always come out in the right order in here:

image

image

I then realized that I can make the header text and header background the same to hide the actual column names:

image

PLEASE HANDLE WITH CAUTION, it's unfinished / a bit fragile so make sure you don't rely on the output. Also I'm sure just the column header generation bit could be stripped out without needing the full pivot functionality. And there's definitely a more elegant way to do the central loop, that will have to wait for v0.2.

Hope that's useful

Dominic

1 Like

+1 for this feature.

+1 for this feature

Hi guys,

I'm new on retool.
I got the exact same problem of sql columns (also building pivot table) being automatically (strangely) reordered once displayed in a table.
I solved "simply" with a transformer. Not that natural but it makes the trick.

I had the feeling I should explore this way because of the tooltip.
image

In short, I rebuild my list of columns in JS. That way, I ensure the order and the type of columns.

let columns=[];
columns.push({"name":"Day","type":"string"});
let d = new Date();
let currentyear = d.getFullYear();
let max_month_index = d.getMonth(); //renvoie un entier en 0 et 11
if ({{year}}.value != currentyear) {
  max_month_index = 11; }

for (let i = 0; i <= max_month_index; i++) {
  let j=i+1;
  let columnJ = (j+'').padStart(2, '0') ;
  columns.push({"name":columnJ,"type":"number"});
}

return columns ;

Hope this might helps someone waiting retool team to improve the table component.

jul