Updating Multiple Data Entry Dates Automatically

App Building
, ,
1
  • Goal: I would like to better understand how via a query, I can automatically update all entry_date values within a table without explicitly selecting today's current date (assuming I am entering a new data value into the table and want today's date of 07-06-2024 to be entered into all the entry_date values in the table).

I currently have a query setup called updateTable which uses the array of the in the Table with columns Date and Action, and I have created a blank column in the Table called entry_date. When a new line is added to the table in Retool a new line is able to be input into the table via the GUI when the following are selected/input:

  1. Action type: Bulk update via a primary key
  2. Primary key column: ID
  3. Array of records to update: {{Table.recordUpdates}}

My objective is when a new row is added to the table in retool all current records in the entry_date column in the databases Table will show when the most recent record was added, perhaps using {{Date()}} somewhere?

If anyone has any understanding on how to accomplish this, I would very much appreciate it.

Thanks in advance,
Sam

2

You could do this before inserting records into the database. I'm not sure which method you're using for data insertion, but if you have selected the GUI, then you can use {{Date()}}. Still, if you have a query processing your records, you can add the entry_data object into your array before sending it to the database.

Let's say you have the following items in youTablele.recordUpdates array

 [
  { id: 1, name: 'Alice', age: 28, entry_date:'6/7/2024' },
  { id: 2, name: 'Bob', age: 34, entry_date:'6/7/2024' },
  { id: 3, name: 'Charlie', age: 25 }
];

Now, you need to insert the entry_date in your last property because that was the last row the user added via UI, and there is no entry_date for it.

 return Table.recordUpdates.map(obj => {
    if (!obj.hasOwnProperty(entry_date)) {
      return { ...obj, [entry_date]: new Date() };
    }
    return obj;
  });

I'm assuming that the value the user is entering doesn't have an entry_date. This code will inject entry_date only into that row. After this, you can pass this record set to the database and refresh the table.

There are other ways, but I don't want to confuse you.

I hope that will help you.

3

Hi @TabraizAhmed - Thank you for the quick response on this.

If for example, I wanted to update all entry_date fields (6/7/2024) including the blank, could I still use the above code you recommended?

Thank you!

4

Hi @Sam_Dreyfuss,

You've got to modify the query a little bit. So now, there is no if statement, and it will inject entry_date for each row with the current date value.

return Table.recordUpdates.map(obj => ({
    ...obj,
    entry_date: new Date()
  }));

Happy Coding :slight_smile:

1 Like
5

Thanks @TabraizAhmed, and would that code be put into the "Transform results" within the GUI tab?

6

I'm not sure what your existing setup is. However, this code should be used before sending a request to the database. If using UI, you must introduce a JS query that first injects the entry_date property. Then, you need to trigger your GUI query, but instead of picking values from your table, you need to refer to your JS query. If you think it is complex, feel free to contact me via Tabraiz Ahmed- Retool Consultant Contact

1 Like
7

@TabraizAhmed - my current setup looks like the following (I'm utilizing a query for the update):

image
image550×628 22.4 KB

8

I can give it a shot via creating the JS query first.

9

Yes, you can create a JS query that will transform the results and then pass those results to this SQL query to execute.

1 Like