Can't find way to script a link update

Ok, so I managed to write a script that creates a row with a request ID in the training table, and to get the ID of the newly created row.

/* Defining tables */
const table_request = base.getActiveTable();
const table_training = base.getTableByName(‘Trainings’);
const table_people = base.getTableByName(‘People’);

/* Defining context */
const row_button = base.context.currentRow;
const input_Request_ID = row_button[‘Request-ID’];
const view_training = base.getViewByName(table_training, ‘Default View’);
let Request_Trainee_name = base.getColumnLinkId(‘Request’, ‘Trainee name’);
let Trainings_Trainee_name = base.getColumnLinkId(‘Trainings’, ‘Trainee name’);
let People_Training_asked = base.getColumnLinkId(‘People’, ‘Trainings asked’);
let People_Request = base.getColumnLinkId(‘People’, ‘Request’);

/* Adding a row in Training table with the request ID */
const rows_training = base.getRows(table_training, view_training);
base.addRow(table_training, {‘Request-ID’: input_Request_ID}, ‘Default View’);

/* Getting the ID of the created row by searching its Request ID */
const rows_training_new = base.getRows(table_training, view_training);
for (var i=0; i<rows_training_new.length; i++) {
const row = rows_training_new[i];
if (row[‘Request-ID’] == input_Request_ID) {
var row_created = row;
}
}

Now, I must understand the base.updateLink(link_id, table_id, other_table_id, row_id, other_rows_ids) fields…

I want to put the value from Request_Trainee_name (linked to let People_Request) into the Trainings_Trainee_name (linked to People_Training_asked).

There are three tables involved so I’m quite lost :slight_smile:

You have to call the function for each link you want to create. So basically link_id, other_table_id and other_rows_ids will change.
The two values for table_id and row_id should not change, since target table and row will both be the same.

Thanks, but what is the “link id” ? I don’t get the meaning of each parameter…

link_id : what is it and how to get it ?
other_table_id : table id of the destination table ?
other_rows_ids : row id of the target link ?

The example is everything but clear in the doc :confused:

The link_id is exactly what you get with that function call. It is the id of the column you want to add the reference to the other table.

base.getColumnLinkId(‘Request’, ‘Trainee name’);

other_table_id is the id of the table that holds the records you want to add to the column and other_rows_ids are the ids of the rows in that other table. The names are most likely self explaining.

1 Like

Here is a short example of a javascript which updates a linked column:

If you press the button “set” the script will compare the Name column and puts the correct link into the column.

const table = base.getActiveTable();
const targetTable1ColumnLinkId = base.getColumnLinkId('Table1', 'Table2');

curRow = base.context.currentRow;
curRowId = curRow['_id'];
name = curRow['Name'];

const table2 = base.getTableByName('Table2');
const view2 = base.getViewByName(table2, 'Default View');
const rows2 = base.getRows(table2, view2);

rows2.forEach((row) => {
  if (name === row['Name'])
  {
    base.updateLinks(targetTable1ColumnLinkId, 
                     'Table1', 
                     'Table2', 
                     curRowId, 
                     [row['_id']])
  }
});
1 Like

I managed to set-up your script and to understand it, sadly I can’t get how to reach my goal.
I made a small base to try to figure out the basic concept.

https://www.swisstransfer.com/d/8196dfa1-52b0-4a4f-9142-43f1078bb4e2

3 tables :
image
image
image

Goal :
Pushing the button “transfer” on line 1 of Table 2 will

  • create a line in Table 3
  • Paste assay1 in the Name column (that is working)
  • Copy the link “Test1” from Table2to1 in Table3to1

I made a script that outputs all info but it seems I’m issing one step.

My guess is that I should get some info from Table1 row id to provide to Table3to1, but I’ve spent hours without success.
Maybe it’s just not feasible this way…

Thanks again for your help, that may help future newcomers, as the nice showcase made by @AkDk7

PS : by the way, it seems I managed to create a bug as rows created in Table 3 seems persistent even after deletion… @rdb any idea ?

Hi @Ben.

I don’t know why but I got an error when I imported your POC base. And from Table3 I couldn’t delete any row. I had to recreate the Table3 from start. But here is a working version for you. It is a lot easier than you think.

1 Like

Wow !!
That’s so nice from you.
I will be able to move forward now.

Few questions about the script.
What is the purpose of the console.log ?
Is there a manual reference to understand the [curRowId] ?
I get the other parts but not this one…

1 Like

What is the purpose of the console.log ?

I used console.log over output.text to make it visible in the browser dev tool (F12) console. You can remove these lines safely.

Is there a manual reference to understand the [curRowId] ?

Sure, the reference is right here: Base - SeaTable Programming Manual
The docs say you have to put the rowIds into an array: [rows[0]._id, rows[1]._id, rows[2]._id, rows[3]._id]. So it has to be like this: ['iuzhi8o7g87g...', 'o8z7gz87i...', '8uzgi7ufgi7...'].

1 Like

I have worked on my final table and I am now able to perform every step needed, thanks to you.

All your explanations and examples should be added to the manual, so much clearer

A BIG THANK YOU !!

1 Like

Thank’s, was fun helping you. Good luck

2 Likes

Thanks for the great community effort!

3 Likes

@AkDk7 As you found that funny, I’m now stuck to a second layer of complexity.

I pursue here as it’s exactly in the same minding…

If you want to play with me, You’d be welcome.

I made a table here :
https://www.swisstransfer.com/d/2fb7a339-0b85-4b9b-a799-da3329705aee



Table 1 lists Competency, with 2 columns linked to Table 2 and 1 column linked to Table 3
Table 2 lists People, with 2 columns linked to Table 2 and 2 column linked to Table 3 (that I forgot to rename)
Table 3 list Training request and that where the action is !

Some people are Tutors for some competency, some are only autonomous (relationship between Table 1 and Table 2)

Some training would take place from tutors to learn the competency to a trainee.
At the end of the training, the tutor would click the button and the trainee would have this competency set in the autonomous column , or from the other side, the trainee name would be added to the Autonomous column in Competency table.

I’ve made a lot of trials and I don’t know how to catch this one.
The main difference here is that I’m not directly related to a new row that I would create and manipulate, but I have to catch the row from a linked data.

I’ll continue my research, but guidance is welcome!

The sample I gave you is pretty simple. Query the rows of the tables, find the column linkIds and execute the update_link method.

Indeed, the sample was great and i managed to reproduce that for my purpose.
The issue here for me is getting the row i guess.
Anyway, i’ll try to solve that.

Tbh I didn’t quite get what result the Finalize was supposed to present. :slight_smile:

I can easily get that :wink:

Basically, in my example, pressing the button in Table Trainings should add the Competency ‘assay2’ in the ‘Autonomous’ column in Table People at Lara’s line.

So basically what you have to do is…

  1. Get the changed row
  2. Get all rows of table People
  3. Iterate over 2.
  4. Compare the rowId of 1. with the content of the array of the column “Training 1”
  5. If that matches add (append a new item to the list) or update (overwrite the list with one or more items) the link for the column Autonomous

That should do the trick.

1 Like

Yes, that was mosty my plan.
I need to add a loop to search for the correct row to update.

For now, its holidays

Thanks for confirming my plan.
I’ll work on that after a whole week far away from internet

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.