Bulk write to table using id's from multiple selected rows in table

Queries and Resources
1

I have a table (selection mode set to multi). I would like to select a few rows and then use the id's from the selected rows to write data to another table using the ids as a key.

This seems really easy when the selection mode is single {{table.selectedRow.data.id}} but how would I use a bulk insert using the ids from all selected rows?

Thanks

2

@aran Just to clarify, it looks like you're wanting to read multiple records from a Retool component and then take those ID's and perform a bulk insert into a database table. Can you confirm what type of database you are using? In the meantime, this docs page shows you how to perform bulk inserts in Retool (which, yes, we do support!).

3

Hi Thanks for the reply. Thanks for the link to the docs, I have been over them a lot of times over the past weeks and can not seem to find the answer there, I have tried several different ways.

So I am using the table component that takes information from a MySQL query. I would like to be able to have a user select several rows from the table, and using the ID from the selected rows as a foreign key writing data in a different SQL table.

So as a basic example:

SELECTED customer_id irelevent_data
:white_check_mark: 1 a
2 b
3 c
:white_check_mark: 4 d
:white_check_mark: 5 e

I want to be able to select a bunch of customer_id's, press a button and write to a new table using the id's I have selected

payment_id customer_id paid
101 1 TRUE
102 4 TRUE
103 5 TRUE

If I was doing it single line by single line, I realise its super simple. just using the key:Value pairs of
id_key = {{table.selectedRow.data.id}}. but when the table component selection type is set to multiple, it outputs an array... I am not very familiar with arrays.

I realise I can call each selected id with {{table.selectedRow.data[0].id}}, {{table.selectedRow.data[1].id}}... etc.. but i would like to do this dynamically as I don't know how many records will be selected.

I have tried using the Bulk insert method, but I am not sure how write {{table.selectedRow.data[n].id}} that will insert a record for every selected row.

I hope this makes sense? What is the best way to accomplish this?

4

Hi there @Aran

I think this is a great opportunity for you to check out

{{ formatDataAsObject(table1.selectedRow.data).id }}

This data structure will contain all the IDs you're wanting to utilize :+1:

5

Ah man, I'm so glad to have seen the above. All the tutorials I was working through worked either only for document IDs or didn't do more than one. This line did work to get an array of UID (using firebase auth to delete more than one user at a time).

however, the query doesn't run. It appears not to know what to do with this new array. It does, however, run when I only select a single row to delete.

Do you know how I can get this to function within the delete a user query on firebase auth?

7

Hey @arkonis!

Are you still having trouble here? If so, you might want to check out these docs on triggering a query for each item in an array. In a JS query, you can write something like:

var ids = formatDataAsObject(table1.selectedRow.data).id;

function runQuery(i) {
  if (i >= rows.length) {
    console.log("Finished running all queries");
    return;
  }
  console.log("Running query for row", data);

  your_delete_query.trigger({
    additionalScope: {
      id: ids[i],
    },
    onSuccess: function (data) {
      runQuery(i + 1);
    },
  });
}

runQuery(0);

Then, you can pass {{ id }} instead of {{ formatDataAsObject(table1.selectedRow.data).id }} , in your delete query which will get populated when you trigger it using additionalScope in the script above.

Let me know if that works?

8

Morning mate,
This actually did mostly resolve the initial problem with a few critical tweaks but I'm now facing another one that this might solve.

trying to get a custom column of my user list table to show total document number they have and because of the way firebase/firestore are linked i'm running into a block.

However, this might help with that and loop through each UID and return their subcollection's doc count? Will give it a go.