Can't find way to script a link update

Ok, I can update text or number values in tables, but I’m stuck when it comes to linked records.
The documentation is kind of cryptic on this part and did not find good examples anywhere.
So if some coder could help me figuring out, That would be appreciated.

My goal is to have a base that contains the capabilities of each of the people of our association, the list of tests they can do, accept incoming request for trainings and list all trainings performed. Only experts can perform trainings

I’ve got 4 tables in a base:

  1. People

  2. Tests

  3. Request

  4. Trainings

Basically, a person (trainee-name) would create a request via a form, that would add a line in the base ‘Request’.
When he selects a test in training-request, that shows all potential tutors that are Experts for this test.
We would then choose a tutor and click on the button to create a line in “Trainings”
The current table Trainings was filled by hands and I want it to be scripted-filled by pressing button in Request table
At the moment, it uses the copy function the button but that kills the link and just paste the text

After that, when the training is done, i want to have a button that would change status of the training and create a link between the “training-request” test and the “trainee-name” in People table (in “Trained” column)

In addition, I would like that the column Tutor potential in Request table, lists only the tutor for the specified training-request. At the moment, it’s only listing the experts whatever the test.

My current code is not working and I don’t get how to solve that…

I made a list of interacting links :
Request.Trainee name ↔ People.Request
Request.training-request ↔ Tests.Request
Request.Tutor chosen ↔ People.Tutor selected
Trainings.Trainee name ↔ People.Trainings asked
Trainings.Tutor chosen ↔ People.Trainings made
Trainings.training-request ↔ Tests.Trainings

and
Request.Tutor potential is a lookup formula from training-request with the Tests.Experts column

First code : goal is to create a “training” row in the Trainings table from one request. That would be done with a button that would paste all info from “Request” to Trainings.
For now, I know how to create a line with the request ID copy pasted.

const table_request = base.getActiveTable();
const table_training = base.getTableByName(‘Trainings’);
const row = base.context.currentRow;
let input_Request_ID = row[‘Request-ID’];
base.addRow(table_training, {‘Request-ID’: input_Request_ID}, ‘Default View’);
);

but I can’t apply the same logic to linked column.

so I have created all references :

const table_request = base.getActiveTable();
const table_training = base.getTableByName(‘Trainings’);
const table_people = base.getTableByName(‘People’);
const row = base.context.currentRow;
let Request_Trainee_name = base.getColumnLinkId(‘Request’, ‘Trainee name’);
output.text(Request_Trainee_name);
let Request_Training_request = base.getColumnLinkId(‘Request’, ‘training-request’);
output.text(Request_Training_request);
let Request_Tutor_chosen = base.getColumnLinkId(‘Request’, ‘Tutor chosen’);
output.text(Request_Tutor_chosen);
let Trainings_Trainee_name = base.getColumnLinkId(‘Trainings’, ‘Trainee name’);
output.text(Trainings_Trainee_name);
let Trainings_Training_request = base.getColumnLinkId(‘Trainings’, ‘training-request’);
output.text(Trainings_Training_request);
let Trainings_Tutor_chosen = base.getColumnLinkId(‘Trainings’, ‘Tutor chosen’);
output.text(Trainings_Tutor_chosen);
let People_Request = base.getColumnLinkId(‘People’, ‘Request’);
output.text(People_Request);
let People_Training_asked = base.getColumnLinkId(‘People’, ‘Trainings asked’);
output.text(People_Training_asked);
let People_Training_made = base.getColumnLinkId(‘People’, ‘Trainings made’);
output.text(People_Training_made);
let People_Tutor_selected = base.getColumnLinkId(‘People’, ‘Tutor selected’);
output.text(People_Tutor_selected);
let Tests_Request = base.getColumnLinkId(‘Tests’, ‘Request’);
output.text(Tests_Request);
let Tests_Trained = base.getColumnLinkId(‘Tests’, ‘Trained’);
output.text(Tests_Trained);
let Tests_Trainings = base.getColumnLinkId(‘Tests’, ‘Trainings’);
output.text(Tests_Trainings);

And I have all my references for linkID :
kT86
v0r4
bGnK
984E
yxi5
1y2U
kT86
984E
1y2U
bGnK
v0r4
8WuT
yxi5

but how to add a row in Trainings (Trainings_Trainee_name) with the value from (Request.Trainee name) ?

I’m lost in these interactions ^^

Hi @Ben.

You cannot add a row and add a reference to another table at the same time. You have to add the row, return the row id and update the column with the linkIds.

For example:

# first step: add a new row
newRowId = base.addRow(table, {'Name': 'Alex', 'Age': '18'});

# second step: update the link column
base.updateLinks('linkId', 'tableName', 'linkedTableName', newRowId, ['linkRowId1', 'linkRowId2', ...])

Hope that makes it a bit easier for you to figure out.

2 Likes

Thanks a lot.
I’ll give a try but i’m not really sure about the fields required in the updatelinks code…

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.