Help

Add a number of days to a date script

Topic Labels: Scripting extentions
3186 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Ahmed_Elagami
7 - App Architect
7 - App Architect

Hello Everyone,

I am trying to convert a string value to a datetime ?
or how can I add a no of dates on a date format?
Example, I have a date 08/16/2020 I would like to add 5 days to be 08/21/2020 in Scripting block
I have this script, to create all number of eps and create the date assigned to that specific episode
I have the start date value, and I have No of eps value.

I tried to use a moment to add a duration of days, but it’s not working for me
moment.duration().add(1,date)

let importTable = base.getTable(“Titles”);
let importQuery = await importTable.selectRecordsAsync();
let episodesscheduletable = base.getTable(‘Episodes Schedule’);
for (let record of importQuery.records) {
let rowcount =0;
rowcount= record.getCellValue(‘EPS’)
while (rowcount>=0)
{
if (rowcount===0)
{
}else
{
//let now = new Date().toLocaleDateString("");
let date =record.getCellValue(‘Start Date’)
var rightNow = new Date();
//rightNow=date.
let Pattern=record.getCellValue(‘Release Pattern’)
let fullName = record.getCellValue(‘English Title’) + ’ ’ + record.getCellValue(‘Season’) + ’ EP’ + rowcount ;
//if (Pattern===1){ moment.duration().add(1,date);}
//if (Pattern===2){ date=date+6}
//if (Pattern===3){ date=date+5}
//if (Pattern===4){ date=date+4}
//if (Pattern===5){ date=date+3}
//if (Pattern===6){ date=date+2}
//if (Pattern===7){ date=date+1}
//if (Pattern===0){ date=date+rowcount}
await episodesscheduletable.createRecordAsync({
“Name”: fullName,“Release Date”:date
})
}
rowcount=rowcount-1
}
output.text(New record for created!);
}

2 Replies 2

There are couple things I noticed in your script:

  • Moment.js is a library/extension thing that is not part of standard Javascript. You can’t use Moment unless you import the library.
  • As designed, your script will create a new record for an episode regardless of whether a record for that episode has already been created.
  • Your script starts a loop at 0 and then is explicitly told to do nothing when the index is 0. You could just start at 1.

Try the code below:

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)
const patterns = [
    {pattern: 1, add: 1},
    {pattern: 2, add: 6},
    {pattern: 3, add: 5},
    {pattern: 4, add: 4},
    {pattern: 5, add: 3},
    {pattern: 6, add: 2},
    {pattern: 7, add: 1},
]

for (let record of importQuery.records) {
    var rowCount = record.getCellValue('EPS');
    let current = 1
    
    while (current <= rowCount) {
        var startDate = new Date(record.getCellValue('Start Date'))
        var year = startDate.getFullYear();
        var month = startDate.getMonth();
        var day = startDate.getDate();
        var pattern = record.getCellValueAsString('Release Pattern')
        var add = patterns.filter(x=> Number(x.pattern) == Number(pattern))[0].add
        var fullName = record.getCellValue('English Title') + ' ' + record.getCellValue('Season') + '.' + current
        
        if(!episodesScheduleNames.includes(fullName)) {
            var newDate = new Date(year, month, day + add*current);
            
            await episodesScheduleTable.createRecordAsync({
                "Name": fullName,
                "Release Date": newDate
            })
            output.text(`New record for ${fullName} created!`);
        }
        current++
    }
}

Let me know if that works.

Hi @Kamille_Parks , Thank you so much for your support.

I have developed the script using a batch function to query create 50 records per time, I have a slight problem here with date calculation increment

so with date calculations sometimes it doesn’t give the right value like
7/7/2021 + 7 = 14/7/2021
14/7/2021 + 7 = 20/7/2021 ( which is the wrong value ) the value here should be 21/7/2021
That’s the code I use
From my season level, Having some parameters like
1- Start Date
2- Pattern Release
3- No of Episodes
I tell the code what to follow in each scenario, The thing is with a simple increment of day calculation coding, It doesn’t do the job correctly
Like Date 7/7/2021 + 7 = 14/7/2021, 14/7/2021+7 = 21/7/2021 but the result I get is 20/7/2021, doesn’t make sense to me

// start delete table function
//--------------------------------------------------------------------------------------------------------
let table = base.getTable("Episodes Schedule");
let query = await table.selectRecordsAsync();
let records = query.records;
let recordCount = records.length;
let recordsToDelete = records.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', table, recordsToDelete);
if (recordsDeleted !== null) {
output.markdown(`## ${recordsDeleted} records deleted from ${table.name}.`);
output.markdown(`These records can be restored from the trash bin within the next 7 days if you didn't mean to delete them.`);
};
async function batchAnd(action, table, records) {
let recordsActedOn = records.length;
switch (action) {
case 'Delete':
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
//--------------------------------------------------------------------------------------------------------
// End delete table function
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)
{
output.text(myDate.getDate() + 7)
myDate.setDate(myDate.getDate() + 7); 
await episodesscheduletable.createRecordAsync({
"Release Date": myDate 
});
RowsToCreate = RowsToCreate -1 
}
}

Is there a way you can help me with that, this little tiny thing will make the whole process faulty
Please anyone with Scripting knowledge can help
Thanks alot guys
Ahmed Elagami