I have a complex set up, and I have tried to simplify an issue I cannot yet solve. I would love some guidance or help.
Lets imagine 4 tables. Call them Parent, Child, 3rd and 4th.
The connection from Parent to 4th is too remote to create a ‘related’ table view in Tadabase - which is what I want to do. So I am trying to concatenate or merge (into Parent) the selected connections from Child to 3rd and use these to connect Parent to 3rd. When this is done I will be able to create related views from Parent to 4th … I really hope this makes sense!
I have crossed this bridge before in Tadabase by using Rollup. But this time it different. Because what I am trying to Rollup are multi-select connections - not just numbers or text.
The Parent is connect to the Child. And the Child table has multi-select joins to a 3rd connected table. The 3rd table is connected to the 4th.
Lets imagine separate records in the Child table are :
A B C
B D J
A G
In the Parent table record I would like to have the merged or concatenated set of these connections to the 3rd table.
In this case it would be A B C D G J
I have tried using a record rule, but that just replaces the existing Parent record with the latest Child record. I have tried using ‘Rollup’ (which seems perfect for the task) but it doesn’t seem to work for multiselect - I guess its not made to handle arrays.
… and I guess I am asking how to join arrays ?
I have seen a method to do this for multiselects within the same record, but I am looking for a way to merge the multiselects to build connections (to the 3rd table) from the Parent record.
I hope that makes sense and that there is a solution.
First of all I want to say thanks for your amazing support, I have never witnessed support so good from any other software company - it really gives me confidence in Tadabase .
Secondly, thanks for your wonderful solution. It may take me a day or two to implement and follow all that you showed, but what you showed is exactly what I am looking for
Sorry I was very delayed with this and finally had time to try it live. Its so good and almost perfect.
But what I really want to create isn’t a text field with a list of the unique values, but a field thats is complete with the unique connections. So that the parent is connected to all the same ABC records.
Is that possible? I hope it is and is a simple tweak of the code.
Hey @mtif, Sure! I’ll show you the code tweaks necessary to save to a one-to-many Connection field instead of a text field below.
Line 43 changes from… listOfJoinValues.push(item.val) …to the following listOfJoinValues.push(item.id)
Line 46 and 47 from…
var uniques = listOfJoinValues.unique();
uniques.toString().replace(/,,/g, ",").replace(/,/g, ", ");
…to the following listOfJoinValues.unique();
Finally, update the Table Rule and set the (Join) field in the Parent table (create one if you haven’t) to the Pipe Value of Response (string).
Click here to view all of the updated code
var Tadabase_Api_Id = 'APP_ID';
var Tadabase_Api_Key = 'API_KEY';
var Tadabase_Api_Secret = 'API_SECRET';
var Parent_Record_Id = '{parentRecordId}';
var Child_Table_Id = '{childTableId}';
var Connection_From_Child_To_Parent = '{fieldSlug}';
var One_To_Many_Field = 'field_ID_val';
Array.prototype.contains = function(v) {
for (var i = 0; i < this.length; i++) {
if (this[i] === v) return true;
}
return false;
};
Array.prototype.unique = function() {
var arr = [];
for (var i = 0; i < this.length; i++) {
if (!arr.contains(this[i])) {
arr.push(this[i]);
}
}
return arr;
}
var Api_Url = 'https://api.tadabase.io/api/v1/data-tables';
var Request_Var = {
'method' : 'GET',
'headers': {
"X-Tadabase-App-id" : Tadabase_Api_Id,
"X-Tadabase-App-Key" : Tadabase_Api_Key,
"X-Tadabase-App-Secret" : Tadabase_Api_Secret
}
};
function getChildRecords() {
var response = UrlFetchApp.fetch(Api_Url+"/"+Child_Table_Id+"/records/?filters[items][0][field_id]="+Connection_From_Child_To_Parent+"&filters[items][0][operator]=is&filters[items][0][val]="+Parent_Record_Id, Request_Var);
return JSON.parse(response);
}
var childItems = getChildRecords();
var listOfJoinValues = [];
for (var i = 0; i < childItems.items.length; i++) {
var element = childItems.items[i];
for (var j in element[One_To_Many_Field]) {
var item = element[One_To_Many_Field][j];
listOfJoinValues.push(item.id)
}
}
listOfJoinValues.unique();
In fact it works so well I have already found a new use for it. Which throws up a last final question.
Is there a way to turn the 4th and final Variable into a pipe Parameter too, so that I can re-use it without having to duplicate the whole pipe?
I realise that within the variable ‘field_ID_val’ the “_val” part has particular significance, I just wonder if there is a way to pass in ‘field_ID’ as a parameter and generate the necessary _val part ?
Thank you so much for the feedback, and please let me know if you need any further help setting this up.
