a parent table with some fields used as a template
two fields in the parent table, 1 numeric fieldA, other is an variable array of connected id (other table) fieldB
a child table that receives some of the parent table fields
The logic is every time I create a record in the parent table, I should create as many records in the child table as the logic (the number in fieldA * the number of id in fieldB)
I checked the post by Chem in Create X amount of connected records based on a Number field
and I’m trying to tweak this by adding a second loop for the second field.
I’m using the Custom Javascript Pipe, set the Parameters as the photo, and I include the code to review. I added a rule in the parent table (create) to execute this pipe and set a text field to receive the pipe value (type). The text field shows “success” after creating the record in the parent table, but no child records are created. Any help?
![Captura de Pantalla 2022-09-25 a las 17.32.18|690x425]
(upload://fN1P2DAk9aarT8TTNgnLSUfFWJ6.png)
var appId = ‘5nQxnE2rxY’;
var apiKey = ‘Iuxh8uKqG1q’;
var apiSecret = ‘XXXXXX-just for caution’;
var APP_ID = appId;
var APP_KEY = apiKey;
var APP_SECRET = apiSecret;
//get parent table record data
var PARENT_TABLE = ‘{stabilitytableParent}’;
var RECORD_ID = ‘{recordId}’;
var NUM_BATCHES_PARENT = ‘{numbatchesParent}’;
var ALIAS_CODE_PARENT = ‘{aliascodeParent}’;
var CONDITIONS_PARENT = ‘{conditionsParent}’;
var TYPE_PARENT = ‘{typeParent}’;
var TIME_PARENT = ‘{timeParent}’;
//define variables from child table
var ALIAS_CODE_CHILD = ‘{aliascodeChild}’;
var TYPE_CHILD = ‘{typeChild}’;
var CONDITIONS_CHILD = ‘{conditionsChild}’;
var TIME_CHILD = ‘{timeChild}’;
var STABILITY_TABLE_CHILD = ‘{stabilitytableChild}’;
var NUM_BATCH_CHILD = ‘{lotChild}’;
var STABILITY_PROGRAM_CHILD = ‘{stabilityprogramChild}’;
for (let i = 0; i < {NUM_BATCHES_PARENT}; i++) {
for (let j = 0; j < ({TIME_PARENT}).length; j++) {
var settings = {
“method”: “POST”,
“headers”: {
“X-Tadabase-App-id”: appId,
“X-Tadabase-App-Key”: apiKey,
“X-Tadabase-App-Secret”: apiSecret,
}
};
var data = {
[STABILITY_PROGRAM_CHILD]: RECORD_ID,
[TYPE_CHILD]: TYPE_PARENT,
[ALIAS_CODE_CHILD]: ALIAS_CODE_PARENT,
[CONDITIONS_CHILD]: CONDITIONS_PARENT,
[TIME_CHILD]: [TIME_PARENT,(j)],
[NUM_BATCH_CHILD]: (i)+1
}
var request = settings;
request.method = ‘POST’;
request.payload = data;
UrlFetchApp.fetch(“https://api.tadabase.io/api/v1/data-tables/7oOjDdjB9A/records”, request);
};
}
@Chem
Well, thanks to my son (CS @UoT) and some personal trial & error, I could fix the problem. The point is that when using the Custom Javascript, and you use the fetch function, the fields in the form of an array are mixed with a normal string; that’s why you need to “force” that making a split to count the number of records to iterate in the loop. Other minor fixes like the “ortography” to set the payload properly for the iterated item as xxxx[j], etc. I’m copying the complete code below for the curious members.
As a summary, you can get a parent table that is used as a template also because it contains a one-to-many connection field to a third table (elements of the template) and you can create a variable number of childs connected to the parent but also to the third table. You may imagine that anyone could replicate this including other connected fields in the parent to other template elements by adding new loops.
var appId = ‘5nQxnE2rxY’;
var apiKey = ‘shhhhhhh’;
var apiSecret = ‘XXXXXX’;
//get parent table record data
var PARENT_TABLE = ‘{stabilitytableParent}’;
var RECORD_ID = ‘{recordId}’;
var NUM_BATCHES_PARENT = ‘{numbatchesParent}’;
var ALIAS_CODE_PARENT = ‘{aliascodeParent}’;
var CONDITIONS_PARENT = ‘{conditionsParent}’;
var TYPE_PARENT = ‘{typeParent}’;
var TIME_PARENT = []
var TIME_PARENT = ‘{timeParent}’;
var TIME_PARENT_PARSED = TIME_PARENT.split(‘,’);
//define variables from child table
var ALIAS_CODE_CHILD = ‘{aliascodeChild}’;
var TYPE_CHILD = ‘{typeChild}’;
var CONDITIONS_CHILD = ‘{conditionsChild}’;
var TIME_CHILD = ‘field_183’;
var STABILITY_TABLE_CHILD = ‘{stabilitytableChild}’;
var NUM_BATCH_CHILD = ‘{lotChild}’;
var STABILITY_PROGRAM_CHILD = ‘{stabilityprogramChild}’;
for (let i = 0; i < NUM_BATCHES_PARENT; i++) {
for (let j = 0; j < TIME_PARENT_PARSED.length; j++) {
var settings = {
“method”: “POST”,
“headers”: {
“X-Tadabase-App-id”: appId,
“X-Tadabase-App-Key”: apiKey,
“X-Tadabase-App-Secret”: apiSecret,
}
};
var data = {
[STABILITY_PROGRAM_CHILD]: RECORD_ID,
[TYPE_CHILD]: TYPE_PARENT,
[ALIAS_CODE_CHILD]: ALIAS_CODE_PARENT,
[CONDITIONS_CHILD]: CONDITIONS_PARENT,
[TIME_CHILD]: TIME_PARENT_PARSED[j],
[NUM_BATCH_CHILD]: (i)+1
}
var request = settings;
request.method = ‘POST’;
request.payload = data;
UrlFetchApp.fetch(“https://api.tadabase.io/api/v1/data-tables/7oOjDdjB9A/records”, request);
};
}
@Chem Thanks for your interest. The answer is yes, I checked this info but I couldn’t use it because the parent table has 1 connected field to a third table; for each new parent record, this connected field may have a variable number of ID; example: it’s a time table with records 1 month, 3 months, 6 months, 12 months up to 48 months, each study created in the parent table can select different times depending on the type of study, we cannot preset that.
The looper is set to repeat the same info from 1:1 fields to the Childs. This is not my case.
@Chem here you see the parent table (stability program) and the child (stability tests). The stability time connected field can be different and not in the same order (1, 3, 6, 9, 12 months, or 1, 6, 12 months i.e.) from record to record depending on the study, and so the number of child records.
The thread above can be used in various situations and I am pretty sure that when properly understood and applied by the Tadabase users, it can lead to great results.
Wouldn’t it be a great idea to do this in a video of Built with Tim or from @Chem ? Since implementing requires a little more knowhow of JS.
My experience using this kind of JS pipes is somehow disappointing. The TB API is not capable of processing a significative number of records (to create or update), so when Google App Script runs the script and sends the response to TB, doing the same every day for many days, I’ve found that the average processing is about 10 records/min (consider also the created/updated records are triggering different table rules). So it’s quite frequent that arrays of >100 records time out and you will have to terminate manually the process. So if your expected array is <30 records, it will work fine. @Chem@moe Any chance to improve this API or connection speed to Google?
