Hoping for help with getting script for repeating events working

This is the first time I have written a script myself as I have been self-learning over the past couple months. I am trying to build a script that will run when a record is created that creates repeated events. I have users who submit events through a form on the interface and added 3 fields focused on the workflow.

The flow I am hoping for is if someone checks off Repeating Event it will look at the Frequency field (single select) and the End On field (date) the workflow script will then create events with the same field values at the same times through the date specified in End On field. I know i could probably remove some of the script and use the conditions in the workflow but I have confused myself to the point of not knowing how to move forward without breaking anything more than it already is. Any hints or assistance would be greatly appreciated.

// Declare variables


let table = base.getTable("Table 1");


let currentRecord = //cannot figure out how to make it pull the current record that was created


let eventName = currentRecord.getCellValue("Event Name");


var calendar = currentRecord.getCellValue("Calendar to Publish");


var purpose = currentRecord.getCellValue("Purpose");


var category = currentRecord.getCellValue("Category");


var audience = currentRecord.getCellValue("Audience");


var startDate = currentRecord.getCellValue("Start Date");


var endTime = currentRecord.getCellValue("End Date");


var endDate = currentRecord.getCellValue("End On");


var frequency = currentRecord.getCellValue("Frequency");





// Check if the current record has a repeating event


if (currentRecord.getCellValue("Repeating Event")) {


  // Calculate the number of days between the start and end dates


  var numDays = (endDate - startDate) / (1000 * 3600 * 24);





  // Calculate the number of times the event should be repeated


  var numRepeats = 0;


  switch (frequency) {


    case "Annual":


      numRepeats = Math.floor(numDays / 365);


      break;


    case "Semi-Annual":


      numRepeats = Math.floor(numDays / 183);


      break;


    case "Quarterly":


      numRepeats = Math.floor(numDays / 91);


      break;


      case "Monthly":


      numRepeats = startDate.getMonth() + 1;


      // numRepeats = Math.floor(numDays / 31); I tried the above because I thought that it would be more accurate given months with 30 days and also February


      break;


    case "Bi-Weekly":


      numRepeats = Math.floor(numDays / 14);


      break;


    case "Weekly":


      numRepeats = Math.floor(numDays / 7);


      break;


    default:


      // If the frequency is not recognized, do not create any additional records


      break;


  }





  // Create the duplicate records


  for (var i = 0; i < numRepeats; i++) {


    var newStartDate = new Date(startDate);


    newStartDate.setDate(newStartDate.getDate() + (i + 1) * frequency);


    var newEndDate = new Date(endTime);


    newEndDate.setDate(newEndDate.getDate() + (i + 1) * frequency);





    var newRecord = {


      "Event Name": eventName,


      "Calendar to Publish": calendar,


      "Purpose": purpose,


      "Category": category,


      "Audience": audience,


      "Start On": newStartDate,


      "End On": newEndDate


    };


    table.createRecord(newRecord);


  }


}

Page 1 / 1

Hi Jeff, what error message are you receiving?

Could you provide a link to a base where everything is set up so that we can attempt running your code? Some example input and output data would also be very helpful

Thank you for the reply and apologies for the delay. I duplicated the base and made an invite link to the base as a creator and sent it to you in a message. In the base I have an Interface that shows the calendar and the create event form they will fill out, that is the only way end-users will be able to create events. I then created an automation that runs the script when a record is created in the table which gives me an error that Record is not defined. I attached images of the interface as well

Hi Jeff, I've made some changes to the code and I think it does what you're looking for; I've pasted it below for others who might stumble upon this thread

The biggest change I made was I modified your "Monthly" calculation to use 31 days instead as it made my life a lot easier heh. In order for us to properly calculate each event's "End Time" we need a static number, and the decision of how many days a "month" is pretty arbitrary

The other note I recall would be that you're doing a `getCellValue` for the `Frequency` field. This returns an object and not a string, so your switch case was having a hard time matching anything. I updated it to `getCellValueAsString` and updated the option fields to match the values you're matching against in your switch

let {record_id} = input.config()


// Declare variables


let table = base.getTable("Table 1");


let currentRecord = await table.selectRecordAsync(record_id)//cannot figure out how to make it pull the current record that was created


let eventName = currentRecord.getCellValue("Event Name");


var calendar = currentRecord.getCellValue("Calendar to Publish");


var purpose = currentRecord.getCellValue("Purpose");


var category = currentRecord.getCellValue("Category");


var audience = currentRecord.getCellValue("Audience");


var startDate = currentRecord.getCellValue("Start Date");


var endDate = currentRecord.getCellValue("End Date");


var frequency = currentRecord.getCellValueAsString("Frequency");


var numDays = currentRecord.getCellValue("Number of Days")





let frequencyNum


// Check if the current record has a repeating event


if (currentRecord.getCellValue("Repeating Event")) {


  // Calculate the number of days between the start and end dates


  console.log('numDays', numDays)


  // Calculate the number of times the event should be repeated


  var numRepeats = 0;


  switch (frequency) {


    case "Annual":


      numRepeats = Math.floor(numDays / 365);


      frequencyNum = 365


      break;


    case "Semi-Annual":


      numRepeats = Math.floor(numDays / 183);


      frequencyNum = 183


      break;


    case "Quarterly":


      numRepeats = Math.floor(numDays / 91);


      frequencyNum = 91


      break;


      case "Monthly":


      // numRepeats = startDate.getMonth() + 1;


      frequencyNum = 31


      numRepeats = Math.floor(numDays / 31); 


      // I tried the above because I thought that it would be more accurate given months with 30 days and also February


      break;


    case "Bi-Weekly":


      numRepeats = Math.floor(numDays / 14);


      frequencyNum = 14


      break;


    case "Weekly":


      numRepeats = Math.floor(numDays / 7);


      frequencyNum = 7


      break;


    default:


      // If the frequency is not recognized, do not create any additional records


      break;


  }


  let updates = new Array





  for (let i = 0; i < numRepeats; i++) {


    var newStartDate = new Date(startDate);


    newStartDate.setDate(newStartDate.getDate() + (i + 1) * frequencyNum);


    var newEndDate = new Date(endDate);


    newEndDate.setDate(newEndDate.getDate() + (i + 1) * frequencyNum);





    updates.push({


      fields: {


        "Event Name": eventName,


        "Calendar to Publish": calendar,


        "Purpose": purpose,


        "Category": category,


        "Audience": audience,


        "Start Date": newStartDate,


        "End Date": newEndDate


      }


    })


  }





  while (updates.length > 0) {


      await table.createRecordsAsync(updates.slice(0, 50));


      updates = updates.slice(50);


  }


}

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded