Create a new column in table?

Discussion
1

Is there a way to add a new column in table as a result of a UI action? This would work like adding a new column in Excel.
My use-case is as follows:
I have a (SQL) table of products and a table of historical prices of the product. I am using the crosstab select to get a historical overview of prices that looks something like:

[product_id] [2022-12-05] [2022-12-19] [2022-12-31] [2023-01-03]
    112           4.23          4.5           4.5        4.86
    113           3.98          3.91         5.39        5.45

and I am displaying this in a Retool table. Most products change the price on the same day and I'm looking for an easy way to add a new price for multiple products in the UI. I am thinking about the following flow for this:

  1. create a new column in the table above with the header set to today's date
  2. update the values in the column as normally
  3. on save trigger a query that would iterate through {{recordUpdates}} as normally, getting the product_id and updated price from each row

Open to suggestions on how to better achieve this flow with what Retool currently supports.

Also, on the similar note: is there a way of setting default formatting for the cell in a table? In my example above, I don't know the dates (column headers) in advance, but I do know that all the values in it will be a currency number. I also know the property types of all the other columns. Is there a way to set "display all numbers as currency, unless explicitly overridden in the column settings"?

2

Are your sure your data structure with a new column per-entry is the most efficient way to manage your data?

A structure of product_id, effective_date, price would scale a lot better for ingress and egress of data and help your analysis in SQL. For data presentation, I totally understand your choice to pivot the data as above, but I'd really recommend you to save and manage your data the other way around.

Jonathan

3

It is saved in a way you describe, that's no issue. The point here is that:

  1. the product table contains a lot of other fields besides price, none of which change often (or ever)
  2. when the price changes, it changes for many products at a time

So I'm really looking for the fastest way to input many values at once

4

@Maja ,

Assuming I am duplicating you correctly: you want to enter a bunch of new prices and add those new records to your price history table.

You want to loop through record updates and create a new arrays of the changes and pass those to a bulk update query, something like this:

  1. Add a custom column and name it "Today" or something and give it a currency type and make it editable.

  2. Make this tranformer:

// trMakeBulkUpdateArray transformer
let updates = []
myTable.recordUpdates.forEach(row => {
  updates.push({product_id: row.product_id, price: row['Custom Column 1'], price_date: moment().format('YYYY-MM-DD')})
}
  1. Then use {{trMakeBulkUpdateArray.value}} in the Array of records to insert property of your bulk update query.

On your second question about default column types, can't help you there other than to suggest that maybe you should look at dynamic columns? Haven't used them myself, but there are a bunch of tips on the forum.

5

You are right, this is exactly how I imagined it to work. My question is around step nr. 1. Adding a new column needs to be a result of UI action.

6

I played with Dynamic Columns Settings hoping to come up with some brilliant solution. But it is not what I thought it was so I don't think that will be helpful.

It sounds like you just want to let the user add prices for a single day? How about returning an empty column from the database, call it "new_day" or something. Since that is the same name every time Retool should remember it and the setting you give it.

Then have a date component where you use selects the date the new prices apply to. My code samples should work exactly the same, just put the date component value into the price_date field.

When they are ready to do another day, refresh the query which will reset everything.

That is a little sloppy I admit, but hopefully at least gives you some inspiration.

1 Like
7

Thanks for sharing these ideas!

I was also thinking dynamic column settings seems to be the closest feature we support for this. Unfortunately, we run into a couple of bugs :disappointed: Currently, dynamic column settings specifically don't work with column type currency & it doesn't allow you to create an editable column. I can update this thread if we're able to fix these bugs.

One idea I had was to use a form or some other component besides the table to trigger the queries that add the new column & prices to the db. Use string or numeric values for the prices. Use dynamic column settings to map over all of the columns, set the columns to type text, add a mapper that adds a '$' in front of the price. Not sure if that's helpful, but thought I'd share just in case :crossed_fingers: (You can see in the screen recording--it still needs a bit of work to be more automated)
Screen Recording 2023-01-18 at 12.45.27 PM.mov