Search and filter all records and add records to the logged in users:

App Development
1

Hi.

I am currently struggling with a query which I want to run, without stressing the system too much. I’ve outlined the goal below.
User 1 and User 2 run a query. User 1 query color is green, User 2 is color white. The result is a datatable with results which are tied to the user.

The aim: let the user run a query on a TB-datatable

  • Seach all companies with a pre-selected colour.
  • Return all the ‘rows’ which are valid. (search all records)
  • Add the rows to the logged in user.

My goal is to run this query without an external “loop” from e.g. Make.com. Since the add rows number is flexible, I cannot make use of the "Insert an X amount of Records’…

Does anyone have any suggestions how to set this is up and not stressing the system?

Have a look at the Setup in MS Excel

Question TB
Question TB1084×616 10.9 KB

2

I seem to recall a post from @Moe I think that said they were looking into allowing SQL queries on Tadabase, although it sounds like you may need more than a report here? I may be way off the mark on this and I haven’t searched for the post, but you may want to check with Tadabase if there is anything in the pipeline.

3

@GREDDIE,

Yeah I tried searching for this post, but I couldn’t find any. @moe @tim.young, is this specific query possible in TB? Or should I move to some external (e.g. Make.com) integration ?

4

Hi, this request reminds me of using the Custom JavaScript pipe, but you need to use code for that.In simple terms, you need:

  • a filtering request from 1 table depending on 1 or more conditions, saving this request in a variable
  • for the former array, start a post request to TB to create records with the fields involved

I have used this kind of scripts for several apps, they are based on the Google servers and TB contracted “bandwith”, usually ideal for <200 records result from a filetered search. Here is one example you can change “activo” by the term you need… all the {…} variables need to be setup in a table/record rule linked to custom or record values

//Headers
var appId = '{appId}';
var apiKey = '{apiKey}';
var apiSecret = '{apiSecret}';

//Parameters
var mpsAreaTable = '{mpsAreaTable}';
var rollingMpsTable = '{rollingMpsTable}';
var mpsVersionTable = '{mpsVersionTable}';
var companyTable = '{companyTable}';
var companyId = '{companyId}';
var companyStatusField = '{companyStatusField}';
var mpsVersionStatusField = '{mpsVersionStatusField}';
var statusValue = '{statusValue}';
var operator = '{operator}';
var mpsAreaLinkField = '{mpsAreaLinkField}';
var mpsVersionLinkField = '{mpsVersionLinkField}';
var mpsAreaId = '{mpsAreaId}';
var mpsVersionId = '{mpsVersionId}';
var versionMonth0Field = '{versionMonth0Field}';
var rollingDatesVersionField = '{rollingDatesVersionField}';
var mpsAreaStatus = '{mpsAreaStatus}';
var companyAreasLink = '{companyAreasLink}';
var companyVersionLink = '{companyVersionLink}';
var companyAreasText = '{companyAreasText}';
var companyVersionText = '{companyVersionText}';

//Finding all MPS Areas when status = activo
var settings = {
    "method": "GET",
    "headers": {
      'X-Tadabase-App-id': appId,
      'X-Tadabase-App-Key': apiKey,
      'X-Tadabase-App-Secret': apiSecret,
    }
};

const url2 = "https://api.tadabase.io/api/v1/data-tables/{mpsAreaTable}/records?filters[items][0][field_id]={mpsAreaStatus}&filters[items][0][operator]={operator}&filters[items][0][val]={statusValue}";
var res = UrlFetchApp.fetch(url2, settings);
var items2 = JSON.parse(res.getContentText())["items"];
var numItems2 = items2.length;


//Finding all MPS Versions when status = activo
var settings = {
    "method": "GET",
    "headers": {
      'X-Tadabase-App-id': appId,
      'X-Tadabase-App-Key': apiKey,
      'X-Tadabase-App-Secret': apiSecret,
    }
};

const url3 = "https://api.tadabase.io/api/v1/data-tables/{mpsVersionTable}/records?filters[items][0][field_id]={mpsVersionStatusField}&filters[items][0][operator]={operator}&filters[items][0][val]={statusValue}";
var res = UrlFetchApp.fetch(url3, settings);
var items3 = JSON.parse(res.getContentText())["items"];
var numItems3 = items3.length;
for (let x = 0; x < numItems3; x++) {
    var rolling = [];
    var rolling = items3[x][rollingDatesVersionField];
    var rolling_parsed = rolling.split(',');
    var rolling_parsed_num = rolling_parsed.length
};

//Creating the array of dates from the filtered MPS versions


for (let i = 0; i < numItems2; i++) {
    for (let k = 0; k < numItems3; k++) {
        for (let j = 0; j < rolling_parsed_num; j++) {
            if ((items2[i][companyAreasText] === items3[k][companyVersionText]) && ((items3[k][companyVersionText] === "XXXXX"))) {
                var settings = {
                    "method": "POST",
                    "headers": {
                        "X-Tadabase-App-id": appId,
                        "X-Tadabase-App-Key": apiKey,
                        "X-Tadabase-App-Secret": apiSecret,
                    }
                };
    
                var data = {
                    [mpsAreaLinkField]: items2[i][mpsAreaId],
                    [mpsVersionLinkField]: items3[k][mpsVersionId],
                    ['field_143']: rolling_parsed[j]
                };
                var request = settings;
                request.method = 'POST';
                request.payload = data;
                UrlFetchApp.fetch("https://api.tadabase.io/api/v1/data-tables/{rollingMpsTable}/records", request);
            };
        };
    };
}
1 Like
5

@Aprada

Thanks for sharing the code. I am trying to build the request with my own variables, but i am having some issues with it. Do you have some other documentation where I can read more about it or share the request which the code which build makes? In the code you provide, the request is creating an array with dates where the status is ‘activo’ ? And do you also run specific table rules in this request to ensure that all data elements are recorded ?

Hope you can help me with fixing this one :grinning: