Duplicating child records when creating parent record

Hi Guys

I wanted to share some amazing help from @Chem. We’ve built a sales CRM for a client - this is the basic flow:


Also see my explainer to the support desk: https://www.loom.com/share/062c6f48abf044efa8880cb28224b9fe

@Chem - gave us a brilliant solution using Google Scripts:
“Hello Adrian, thank you for your patience! Here’s a video explaining 2 options for this in-depth: https://www.screencast.com/t/ovSQDZNw
The second option would be similar to https://docs.tadabase.io/categories/solution-guides/article/google-scripts---get-record-and-create-new-records-from-connected-table

Hi Chem - thank you. Yes it’s the 2nd option. We want to populate a product component orders table. Although the customer buys a bed (master product) - the warehouse delivers 3 boxes (product components). So yes we need help building this option.

Okay, here we go!

Please give this a watch when you have 13 minutes to do so :slight_smile:

I’ve attached the Google Script and Copied the app in the demo to your account. Please note that for the copied app to work, you’ll need to add new API Keys and a Webhook since they aren’t copied over.

/*************
* Variables
**************/
var Tadabase_Api_Id = 'PzQ464BrJG';
var Tadabase_Api_Key = 'rXG6q0xTuTtt';
var Tadabase_Api_Secret = 'V75y0yIkpvaghpHfZ7V0NlGBO7pqKqhM';

// Table IDs
var Child_Table_1_Id = 'K2ejlOQo9B'; // Product Components
var Child_Table_2_Id = '698rd2QZwd'; // Product Orders Components

// Field Slugs
var Child_Table_1_Join = 'field_38'; // Product Components - Master Product (Join)
var Child_Table_1_Text = 'field_37'; // Product Components - Name
var Parent_Table_2_Join = 'field_35'; // Product Orders - Master Product (Join)
var Child_Table_2_Text = 'field_40'; // POC - Name
var Child_Table_2_Join = 'field_41'; // POC - Product Order(Join)

var Child_Table_1_Currency = 'field_44';
var Child_Table_2_Currency = 'field_45';

/*************
* API Settings
**************/
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 getChildOneRecords(filterValue) {
  var Table_Id = Child_Table_1_Id;
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records?filters[items][0][field_id]="+Child_Table_1_Join+"&filters[items][0][operator]=is&filters[items][0][val]="+filterValue, Request_Var);
  return JSON.parse(response);
}

function saveChildTwoRecords(data) {
  var Table_Id = Child_Table_2_Id;
  var request = Request_Var;
  request.method = 'POST';
  request.payload = data;
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records", request);
  return JSON.parse(response);
}

function doPost(e) {
  //Get the ID of the Parent 2 record that was just created. 
  var id = e.parameter['id'];
  if (typeof(e.parameter[Parent_Table_2_Join+'[0]']) !== "undefined") {
    
    //Save the Parent_Table_2_Join Connection to a variable
    var filterValue = e.parameter[Parent_Table_2_Join+'[0]'];
    
    //Get list of Child 1 Records
    var items = getChildOneRecords(filterValue).items;
    
    //Create a new Child 2 Record for each Child 1 record retrieved. 
    for (var i in items) {
      var item = items[i];
      var data = {
        [Child_Table_2_Text] : item[Child_Table_1_Text], 
        [Child_Table_2_Join] : id,
        [Child_Table_2_Currency] : item[Child_Table_1_Currency]
      }
      saveChildTwoRecords(data);
    } // End for loop
  }
}

Hope this helps someone else…

PS: Tadabse is awesome!!!

6 Likes

@Chem is awesome…#Tada4Life

2 Likes

Thank you so much for sharing this! :smile: @adrian.peck

1 Like