Re: Scripting stopes after certain Number of creating records ,Why?

Ahmed_Elagami · ‎Oct 20, 2020

Hello Everybody,

I have created a scripted code that populates episodic shows from season level.
so I have Show Title, Season, Eps count, Start Date, Pattern Release per Week
So this script gives me each date for each episode depending on the pattern release /Eps count and start date.
The Scripting app works perfectly except that it keeps freezes at some point
Like I have 2000 Eps to create, sometimes, it stops at Episode 560, other times it stopped at 916
and it always stops at the same count of episodes, I tried to make sure that everything is in a text mode, no special characters and no odd thing in the lines
still, stop at the same point.

1- Can you please help me figure this out, Why does it stop at all?
2- Can anyone help me in modifying the code, If anything Changes in the fields that are included in the script like " Release Date", “EPS”, “Origin”, Windowing", it modifies only on these fields, keeping the same record, not to create a new record with the updated ones.

let importTable = base.getTable("Titles Release Table");
let importQuery = await importTable.selectRecordsAsync();
let episodesscheduletable = base.getTable('Episodes Schedule');

const episodesScheduleQuery = await episodesscheduletable.selectRecordsAsync()
const episodesScheduleNames = episodesScheduleQuery.records.map(x => x.name)

for (let record of importQuery.records)
{     let myDate = new Date();
      myDate = new Date(record.getCellValue('Release Date'));
      let rowcount =0;
      let EpsNum =0;
      let cnt = 0;
      let fullName='';
      let ShowOrigin=record.getCellValue('Origin');
      let ShowType=record.getCellValue('Type');
      let ShowPlatform=record.getCellValue('Platform');
      let ShowWindowing=record.getCellValue('Windowing');
      let ShowRating=record.getCellValue('Rating');
      rowcount= record.getCellValue('EPS')  ;
      let EpsCount =0;
      EpsCount = record.getCellValue('EPS')  ;
      let Pattern=record.getCellValue('Pattern Release');
      if (rowcount==0)
         {}
      else if  (rowcount==1)
         {
            fullName = record.getCellValue('English Title')    ;
            myDate.setDate(myDate.getDate());
            await episodesscheduletable.createRecordAsync(
            {
                  "Name": fullName,"Release Date": myDate
            })
             rowcount=rowcount-1   ;
         }
      else
      {
      while (rowcount>0)
      {
            if (Pattern==5)
            {
               EpsNum = cnt + 1;
                    if (cnt==0)
                     {
                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'   ;
                           myDate.setDate(myDate.getDate());
                           if (myDate.getDay()==5 || myDate.getDay()==6)
                           {
                              continue;
                           }
                           else
                           {}
                     }
                     else
                     {
                           if (EpsNum == EpsCount)
                           {
                              fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
                           }
                           else
                           {
                              fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
                           }
                           myDate.setDate(myDate.getDate() + 1);
                           if (myDate.getDay()==5 || myDate.getDay()==6)
                           {
                              continue;
                           }
                           else
                           {}
                     }
            }
            else if (Pattern == null || Pattern == 0 || Pattern > 7)
            {
               EpsNum = cnt + 1;
                output.text('NULL  EpsNum ' + EpsNum + 'EpsCount ' + EpsCount + 'rowCount' + rowcount)
               if (cnt==0)
                  {
                     fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'  ;
                  }
               else
                  {
                     if (EpsNum == EpsCount)
                     {
                        fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
                     }
                     else
                     {
                        fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
                     }
                  }
               myDate.setDate(myDate.getDate());
            }
            else
            {
               EpsNum = EpsNum + 1;
               fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
               if (cnt==Pattern)
                  {
                     cnt = 0;
                     output.text('EpsNum ' + EpsNum + 'EpsCount ' + EpsCount)
                     if (EpsNum == EpsCount)
                        {
                          fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
                        }
                        else
                        {
                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
                        }
                     myDate.setDate(myDate.getDate() + (8-Pattern));
                  }
               else
                  {
                     if (cnt==0)
                     {
                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'   ;
                           myDate.setDate(myDate.getDate());
                     }
                     else
                     {
                        output.text('EpsNum ' + EpsNum + 'EpsCount ' + EpsCount)
                        if (EpsNum == EpsCount)
                        {
                          fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
                        }
                        else
                        {
                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
                        }
                        myDate.setDate(myDate.getDate() + 1);
                     }
                  }
            }
            cnt=cnt+1;
            if(!episodesScheduleNames.includes(fullName)) 
            {
            await episodesscheduletable.createRecordAsync(
            {
                  "Name": fullName,"Release Date": myDate ,"Windowing": ShowWindowing ,"Origin": ShowOrigin ,"Type": ShowType ,"Platform": ShowPlatform ,"Rating": ShowRating
            })}
               rowcount=rowcount-1   ;
              // output.text(`New record created!`);
      }
      }
}

Jeremy_Oglesby · ‎Oct 20, 2020

Hi @Ahmed_Elagami,

This may not actually be the reason your script is stopping at a certain point, but it’s worth looking at…

You are creating each new record individually inside your loop with the

await episodesscheduletable.createRecordAsync()

call.

Instead, you should create an array of records to be created with your loop. At the end of your loop, you will have an array full of objects representing the records you want to create, in the same format you have for your createRecordAsync() call – but none of those records will have been created yet (ie, nix the .createRecordAsync() call in your loop – only output the array of records as a result of the loop).

After your loop has created your array of recordsToBeCreated, you can use this batching function to create them in batches of 50 at a time:

This function wraps the createRecordsAsync() function (when you pass "Create" as the first parameter), and may result in better luck with creating a large number of records in a single script.

What may be happening is that you may be running into API limits where your loop is calling .createRecordAsync() too frequently in a small period of time and overwhelming Airtable’s API limits.

I’d suggest giving that a shot first and see if it solves your problem.

Ahmed_Elagami · ‎Oct 21, 2020

Thank you, Jeremy, This is definitely a quit nice approach, and worth trying, I did apply the batching block and it seems to work like a charm. The only thing that’s not working is the Dates calculations, it only gives me the end date for all episodes which is not what I am trying to populate
so I have Pattern Release /Week, it tells me how many episodes are there to be aired inside a week, and it calculates the airing date for the whole episodes starting from the release date :

while (rowcount>0)
{
if (Pattern==5)
{
EpsNum = cnt + 1;
if (cnt==0)
{
fullName = record.getCellValue(‘English Title’) + ’ ’ + record.getCellValue(‘Season’) + ’ EP’ + EpsNum + ‘-’ + ‘First’ ;
myDate.setDate(myDate.getDate());
if (myDate.getDay()==5 || myDate.getDay()==6)
{
continue;
}
else
{}
}
else
{
if (EpsNum == EpsCount)
{
fullName = record.getCellValue(‘English Title’) + ’ ’ + record.getCellValue(‘Season’) + ’ EP’ + EpsNum + ‘-’ + ‘Last’ ;
}
else
{
fullName = record.getCellValue(‘English Title’) + ’ ’ + record.getCellValue(‘Season’) + ’ EP’ + EpsNum ;
}
myDate.setDate(myDate.getDate() + 1);
if (myDate.getDay()==5 || myDate.getDay()==6)
{
continue;
}
else
{}
}
}
else if (Pattern == null || Pattern == 0 || Pattern > 7)
{
EpsNum = cnt + 1;
// output.text('NULL EpsNum ’ + EpsNum + ‘EpsCount ’ + EpsCount + ‘rowCount’ + rowcount)
if (cnt==0)
{
fullName = record.getCellValue(‘English Title’) + ’ ’ + record.getCellValue(‘Season’) + ’ EP’ + EpsNum + ‘-’ + ‘First’ ;
}

These are the While / If I am using to calculate each release date of each episode.
The Naming of each Episode works perfectly as they are string results, But when it comes to date calculations inside a batching block, it gives me always the last date for all episodes.

Is That something you can help me with to figure out how can I make the loop works for the dates inside the batching block?
Also I need some help in how to start applying the Update and Delete function inside my Code if possible ?

Here’s my all code

> const importTable = base.getTable("Titles");
> const importQuery = await importTable.selectRecordsAsync();
> const episodesscheduletable = base.getTable('Episodes Schedule');
> 
> const episodesScheduleQuery = await episodesscheduletable.selectRecordsAsync()
> const episodesScheduleNames = episodesScheduleQuery.records.map(x => x.name)
>  
> let contactsToMake = [];
> let NoOfrecords= importQuery.records.length;
> let LastRecord=0;
> 
> for (let record of importQuery.records)
> {          
>        
>        LastRecord = LastRecord +1;
>       
>       let myDate = new Date();
>       myDate = new Date(record.getCellValue('Release Date'));
>       let rowcount =0;
>       let EpsNum =0;
>       let cnt = 0;
>       let TitleName ='';
>       TitleName = record.getCellValue('English Title');
>         
>       let fullName='';
>       let ShowOrigin=record.getCellValue('Origin');
>       let ShowType=record.getCellValue('Type');
>       let ShowPlatform=record.getCellValue('Platform');
>       let ShowWindowing=record.getCellValue('Windowing');
>       let ShowRating=record.getCellValue('Rating');
>       rowcount= record.getCellValue('EPS')  ;
>       let EpsCount =0;
>       EpsCount = record.getCellValue('EPS')  ;
>       let Pattern=record.getCellValue('Release Pattern');
>       if (rowcount==0)
>          {}
>       else if  (rowcount==1)
>          {
>             fullName = record.getCellValue('English Title')    ;
>             myDate.setDate(myDate.getDate());
> 
>                
>             if(!episodesScheduleNames.includes(fullName)) 
>             {
>              
>             contactsToMake.push({
>                 fields: {"Name": fullName,"Release Date": myDate,"Windowing": ShowWindowing ,"Origin": ShowOrigin ,"Type": ShowType ,"Platform": ShowPlatform }
>             });
>             }
>            
>             if (contactsToMake.length == 50  ) 
>             {
>           
> 
>                let contactsMade = await batchAnd('Create', episodesscheduletable, contactsToMake); 
>                contactsToMake= [];
>             }
>             else if (contactsToMake.length != 50 && LastRecord==NoOfrecords)
>             {
>                let contactsMade = await batchAnd('Create', episodesscheduletable, contactsToMake); 
>                 contactsToMake= [];
>             }
>              rowcount=rowcount-1   ;
>          }
>       else
>       {
>       while (rowcount>0)
>       {
>             if (Pattern==5)
>             {
>                EpsNum = cnt + 1;
>                     if (cnt==0)
>                      {
>                            fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'   ;
>                            myDate.setDate(myDate.getDate());
>                            if (myDate.getDay()==5 || myDate.getDay()==6)
>                            {
>                               continue;
>                            }
>                            else
>                            {}
>                      }
>                      else
>                      {
>                            if (EpsNum == EpsCount)
>                            {
>                               fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
>                            }
>                            else
>                            {
>                               fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
>                            }
>                            myDate.setDate(myDate.getDate() + 1);
>                            if (myDate.getDay()==5 || myDate.getDay()==6)
>                            {
>                               continue;
>                            }
>                            else
>                            {}
>                      }
>             }
>             else if (Pattern == null || Pattern == 0 || Pattern > 7)
>             {
>                EpsNum = cnt + 1;
>                // output.text('NULL  EpsNum ' + EpsNum + 'EpsCount ' + EpsCount + 'rowCount' + rowcount)
>                if (cnt==0)
>                   {
>                      fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'  ;
>                   }
>                else
>                   {
>                      if (EpsNum == EpsCount)
>                      {
>                         fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
>                      }
>                      else
>                      {
>                         fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
>                      }
>                   }
>                myDate.setDate(myDate.getDate());
>             }
>             else
>             {
>                EpsNum = EpsNum + 1;
>                fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
>                if (cnt==Pattern)
>                   {
>                      cnt = 0;
>                   
>                      if (EpsNum == EpsCount)
>                         {
>                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
>                         }
>                         else
>                         {
>                            fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
>                         }
>                      myDate.setDate(myDate.getDate() + (8-Pattern));
>                   }
>                else
>                   {
>                      if (cnt==0)
>                      {
>                            fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum + '-' + 'First'   ;
>                            myDate.setDate(myDate.getDate());
>                      }
>                      else
>                      {
>                        
>                         if (EpsNum == EpsCount)
>                         {
>                           fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum  + '-' + 'Last' ;
>                         }
>                         else
>                         {
>                            fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + ' EP' + EpsNum   ;
>                         }
>                         myDate.setDate(myDate.getDate() + 1);
>                      }
>                   }
>             }
>             cnt=cnt+1;
> 
>            
> 
> 
>             if(!episodesScheduleNames.includes(fullName)) 
>             {
>                
>                contactsToMake.push({
>                 fields: {"Name": fullName,"Release Date": myDate ,"Windowing": ShowWindowing ,"Origin": ShowOrigin ,"Type": ShowType ,"Platform": ShowPlatform}
>             });
>             }
> 
>           
>             if (contactsToMake.length == 50  ) 
>             {
>              
>              
>               
>                let contactsMade = await batchAnd('Create', episodesscheduletable, contactsToMake); 
>                 contactsToMake= [];
>             }
>             else if (contactsToMake.length != 50 && LastRecord==NoOfrecords)
>             {
>                let contactsMade = await batchAnd('Create', episodesscheduletable, contactsToMake); 
>                 contactsToMake= [];
>             }
> 
>               
>                rowcount=rowcount-1   ;
>             
>       }
>       }
>       
> }
> 
> 
> 
> 
> async function batchAnd(action, table, records) {
>     let recordsActedOn;
> 
>     switch (action) {
>         case 'Update':
>             recordsActedOn = records.length;
>             while (records.length > 0) {
>                 await table.updateRecordsAsync(records.slice(0, 50));
>                 records = records.slice(50);
>             };
>             break;
>         
>         case 'Create':
>             recordsActedOn = [];
>             while (records.length > 0) {
>                 let recordIds = await table.createRecordsAsync(records.slice(0, 50));
>                 recordsActedOn.push(...recordIds)
>                 records = records.slice(50);
>             };
>             break;
> 
>         case 'Delete':
>             recordsActedOn = records.length;
>             while (records.length > 0) {
>                 await table.deleteRecordsAsync(records.slice(0, 50));
>                 records = records.slice(50);
>             }
>             break;
> 
>         default:
>             output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
>             recordsActedOn = null;
>     }
>     return recordsActedOn;
> }

Thanks Again Jeremy, I hope I can get this sorted.
My Best
Ahmed

Ahmed_Elagami · ‎Oct 24, 2020

Hi @Jeremy_Oglesby, Is there a way you can help figuring out, How can I apply my dates calculations inside the batch loop. Thanks in advance.

Ahmed_Elagami · ‎Oct 27, 2020

Hi @Jeremy_Oglesby , everybody. Is that something you can look at and try to figure the reason why it gives me only the end date of each episode to all episodes, I can share a test base here if you want?

Waiting for your comments, suggestions.
My Best
Ahmed Elagami

Jeremy_Oglesby · ‎Oct 27, 2020

Hey @Ahmed_Elagami

I’m taking a look, but it’s a big script. You’ve got a lot going on in here, and I’m only able to give it half my attention at best during my work day. Hopefully we can get it sorted out.

Jeremy_Oglesby · ‎Oct 27, 2020

Hi @Ahmed_Elagami,

I’m afraid you’re going to need to rethink the structure of your script. As it is now, you are trying to do far too much work inside your for loop, and what exactly you are trying to achieve is not quite clear to me from your code.

I’m not able to move all the bits of your code around for you, but I can give you a suggestion of a pattern to follow.

The overarching suggestion is that you need to batch up ALL of the records you are trying to create into a single batch, no matter how large, and at the very end of your script, outside of and after your records loop, call the batchAnd('Create', ..., ...) function. You only need to call this once on your full batch – the whole point of the function is that it takes care of creating batches of 50 out of your records for you. As your script is now, you are calling batchAnd('Create', ..., ...) multiple times inside your for loop, which is very confusing and is probably the cause of your issues.

You have this in your code:

let contactsToMake = []
...
...
for (let record of importQuery.records)
{
   ...
   ...
   contactsToMake.push({ ...})

Instead of using a for loop and pushing records into your array, I’m going to suggest you use the Array.map() function. The purpose of the Array.map() function is to take each item in an array (like your raw records), transform the data in some way, and return new data into a new array as the result. So the basic setup for your case should look like this:

const contactsToMake = importQuery.records.map(record => {
   ... do all your data fetching and transformation 
   for this particular record here...
   ...
   return {
      fields: {
         ...
         ...
      }
   }
})

const contactsMade = await batchAnd('Create', episodeScheduleTable, contactsToMake)

You should try and do all of your transformation work inside that map loop (map will loop over each record in your Query the same as the for loop did). And then you should try to call the .bactchAnd() function only once at the very end of your script.

I’m not sure what it is that you are trying to do with your rowcount, Pattern, cnt, LastRecord, etc, so I can’t really advise on how to fit that in with this new schema… but my hunch is that you may not actually need some of those constructs.

Hopefully this is understandable enough to get you started. I’d suggest getting started with this, and then ask more specific questions in new posts on the forum as you run into issues.

Ahmed_Elagami · ‎Feb 08, 2021

@Jeremy_Oglesby, Thanks for your input, We already figured it out and the code worked perfectly, I just needed to define my Date to be a new date and takes the new Loop rules, but I am having a tiny issue I hope you can help me with. The batch doesn’t work well with pattern adds to the date

So if you run this batch script to add 7 days to each date the result is not accurate

const importTable = base.getTable("Titles");
const importQuery = await importTable.selectRecordsAsync();
const episodesscheduletable = base.getTable('Episodes Schedule');
 
const episodesScheduleQuery = await episodesscheduletable.selectRecordsAsync()
const episodesScheduleNames = episodesScheduleQuery.records.map(x => x.name)
 
let contactsToMake = [];
 
for (let record of importQuery.records)
{          
    let myDate = new Date();
    let RowsToCreate=0; 
    RowsToCreate= record.getCellValue('EPS')  ;
    myDate = new Date(record.getCellValue('Release Date'));
 
    while (RowsToCreate>0)
    {
        myDate.setDate(myDate.getDate() + 7);    
        await episodesscheduletable.createRecordAsync({
            "Release Date": myDate 
        });
        RowsToCreate = RowsToCreate -1   
    }
}

The result
14/3/2021
21/3/2021
27/3/2021
3/4/2021
10/4/2021

So 14+7 = 21
21+7= 28 not 27

Is there something wrong with my code, I just want to add 7 to each date add up, but it’s not working properly, Do you have a quick fix for that?

Thanks, Jeremy for your continuous help and support.
waiting for your reply.

Ahmed_Elagami · ‎Feb 11, 2021

I would like to have your input please, Sorry for any disturbance.

Ahmed