Logo
For example "scheduled tasks"
Search
Info
Content
Category Navigation
  • 🎯  Solution Guides

  • Low Code Solutions

    Add "Tabs" to your app

    ID Target Link

    Use an address field to display a static street view thumbnail

    Turn your menu into a fixed menu

    Auto close mobile menu

    Display record information in a card

    How to add a back button to your app

    Add a collapsible accordion element to your app

    Code Snippets

    PHP Curl Create New Record

    PHP Get Records With Filters

    Google Scripts - Get record and create new records from connected table

    Save Tables and Fields into Spreadsheet

    Google Scripts - Export all records

    Webhook to save deleted records

    Send Messages to Slack

    Restrict Space and Force Uppercase in a Form

    Building Advanced Features

    Use Validation Rules to Ensure Unique Votes Per User

    Use Record Rules to Create an Audit Log

    Track current inventory with transactions

    Schedule Bookings and Prevent Double Bookings

    Create orders with multiple items and varying quantities

    2 Step Forms

    Unique Record Validation

    Restrict Editing Records to Record Owners

    Sum date/time field

    Duplicate Records

    Advanced App Customization

    How to embed a Tadabase page on another website

    Adding external fonts

    Custom Favicon

    Making apps mobile friendly

    Show date/time as a countdown

    Importing many images at once

    Adding QR Codes

    Adding charts to PDFs

    Customizing your app shortcut for mobile phones

    Animate parts of a page

    Design Customization

    Changing background colors

    Set background colors to the edge of a page

    Change the background of a column

    Add padding around a component

    Using an image as background for a page

    Adding a background image to a row, column, or component

  • Duplicate Connected Records

Categories
🎯 Solution Guides
Code Snippets
Google Scripts - Get r...

Google Scripts - Get record and create new records from connected table

Objective 

There are many situations when you'd like to trigger several records to be created when a new record is updated. 

For example: 

  • In classroom attendance, when a new day is started, create a new attendance record for each student. 
  • When adding a specific job automatically add all the connected tasks that should be done for this job. 
  • When adding a new survey automatically insert all the questions that must be answered for that survey.
  • etc... 

In this example, we'll look at how to accomplish one of the scenarios above. We can easily do this with any programming language, but we'll use Google Scripts since its: 

  • Free
  • Doesn't require up to spin up any servers or hosting

Requirements

Let's talk about our structure and requirements. 

We're a web development agency and each job has a bunch of tasks that must be done before a job is completed. 

For example: 

If the job is: "New Website" we want the following tasks all marked with the appropriate status for each: 

  1. Purchase domain
  2. Set up hosting
  3. Set up FTP accounts
  4. Setup Google Apps email accounts
  5. Create website wireframes
  6. Create a website using Tadabase
  7. Upload site to hosting
  8. Send client site instructions. 

Each of these questions must be answered with the correct status and any notes relevant. 

 

Structure: 

We'll start by structuring these 2 tables. 

  1. Job Types
  2. Job Tasks List

Job Types

In the Job Types, we'll put our list of all possible jobs we can assign. For example: 

 

Job Task List

In the Job tasks table, we'll add all the possible tasks for each job and connect each task to the job it can belong to. 

As you can see from above, each task is connected to a job type. When this job type is added, these tasks will need to be added to that job. 

Now we'll add 2 more tables:

  1. Jobs
  2. Tasks

In the jobs table at the minimum we need one field that connects to Job Types: 

and in the Tasks table we need a minimum of 3 fields: 

  1. Task - this will be the field that will be set to whatever the job's task is. 
  2. Job - this will be a connection to the Job
  3. Status - so after the job is added we can set the status for each task connected to this job
  4. Notes - optional to we can add notes about each task. 

 

So what will the outcome look like: 

When we add a new job in the Jobs table, we'll choose the job type then get all the tasks connected to this job and insert it into the Job Tasks table and connect it to this newly created job. 

 

Here's what our code will look like. 

We'll start by defining some variables: 

/*************
* Variables
**************/
var Tadabase_Api_Id = 'APPID';
var Tadabase_Api_Key = 'APIKEY';
var Tadabase_Api_Secret = 'SECRET';
/*************
* 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
        }
    };
/*

Next, we'll create a function that will get all the tasks from the Job Task List table that share the same Job Type as the new job created. 

/*
Function : getTasks
Descriptions : get List of Tasks that belong to this job
Be sure to change the TABLEID to the table ID that contains the list of quesitons. In our case that's the Job Task List table. 
*/
function getQuestions(catId) {
  var Table_Id = "TABLEID";
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records?filters[items][0][field_id]=field_40&filters[items][0][operator]=is&filters[items][0][val]="+catId, Request_Var);
  return JSON.parse(response);
}

Now, we also need to create a new record in the Job Tasks table for each record we retrieved in the getTasks function: 

/*
Function : saveJobTasks
Descriptions : save Tasks in Job Tasks table. 
Make sure to change the SECONDTABLEID with the table id for the "Job Tasks Table" 
*/
function saveQuestionsList(data) {
  var Table_Id = "SECONDTABLEID";
  var request = Request_Var;
  request.method = 'POST';
  request.payload = data;
  var response = UrlFetchApp.fetch(Api_Url+"/"+Table_Id+"/records", request);
  return JSON.parse(response);
}

Finally, let's put it all together. 

When a web-hook is sent to this script


function doPost(e) {
  //Get the ID of the job job that was just created. 
  var id = e.parameter['id'];
  
  //var servery = e.parameter['field_36'];
  var job_type = "";
  if (typeof(e.parameter['field_39[0]']) !== "undefined") {
    
    //Save the JOb Type to a variable
    var job_type = e.parameter['field_39[0]'];
    
    //Get list of tasks for each job_type
    var items = getQuestions(servey_category).items;
    
    //Create a new Job Task for each Job Task List retrieved. 
    for ( var i in items) {
      var item = items[i];
      var data = {
        'field_41' : item.field_37, //Question List Name
        'field_42' : item.id, //Record ID
        'field_43' : id // New Survey ID
      }
      saveQuestionsList(data);
    } // End foreach
  }
}

How did we do ?

Previous Article
PHP Get Records With Filters
Next Article
Save Tables and Fields into Spreadsheet
Article Navigation
  • Objective
  • Requirements
  • Structure:
  • Job Types
  • Job Task List
  • Back to top
    API
    100% Operational
    Apps
    100% Operational
    Builder
    100% Operational
    Overall Status
    100% Operational