Duplicate Record - Change Start Date to Next Monday

Welcome to the community, @IT_Center! :grinning_face_with_big_eyes: This could be done with a script, but it can also be done without a script if you add a couple of extra fields to calculate the new dates.

If you can share your current script, it’ll be easier to show how to modify it to meet your needs.

Thanks Justin. Here is the current script that duplicates the record (but sadly actually doesn’t work with a button trigger).

Blockquote

let table = base.getTable("Assignments")
let query = await table.selectRecordsAsync()
let records = query.records

let fields = table.fields


let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

let original = await input.recordAsync("Original Record", table)

let obj = {}

filteredFields.map(x => {
    Object.assign(obj, {[x.name]: original.getCellValue(x.name)})
})

await table.createRecordAsync(obj)

Thanks,

Samuel

Thanks for the update. As currently written, there are a few issues. First, that script should throw an error on this line because nothing has been defined named exclude:

let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

If you want to exclude specific fields from being copied, you should create a string array containing the names of those fields; e.g.:

const exclude = ["Field 1", "Field 2", "Field 3"]

That line just needs to go somewhere before (above) the filtering line mentioned above.

You’re probably also seeing a strikethrough on selectRecordsAsync() in this line:

let query = await table.selectRecordsAsync()

If you mouse over it, you’ll see a message. Long story short, the blanket-capture of all fields for all records is deprecated (highly discouraged, eventually(?) to be enforced). The recommended process is to specify only those fields that you want to capture, thus allowing the script to run more efficiently. However, because you actually want to capture all fields before copying, you can avoid typing all of the field names manually by using this structure:

let query = await table.selectRecordsAsync({fields: table.fields})

I don’t recommend doing that in all use cases, but for this situation it works. (Also, if you rearrange some of your lines, you can do this slightly easier, but I’ll save that for the final tweaked script below.)

To reverse the date fields that you mentioned, you’ll need to modify this part:

filteredFields.map(x => {
    Object.assign(obj, {[x.name]: original.getCellValue(x.name)})
})

As written, that makes a literal copy of all fields without any modifications. To perform the date tweaks that you mentioned above requires a few changes. Those will be in the final version below.

Re: the button issue, this script is already designed to work with a button field because of this line:

let original = await input.recordAsync("Original Record", table)

If you run the script manually, it will prompt you to pick a record. However, if you have a button field that’s set to run this script, this line will retrieve the record where the button was clicked. If it’s still not working after applying the revised script below, let me know.

One other thing that I ran into while testing is a date offset issue. Long story short, dates retrieved from date fields are assumed to be based on GMT time, not your local time (regardless of the date field formatting options). To counter this, I added a utc_offset variable near the start of the script. Set this based on your local time zone compared to GMT. In my case, I’m in the Pacific timezone, which is GMT -8. Without applying this offset, the dates could be a day off from what they should be.

Here’s the revised script with all of the above changes applied (fill in the exclude array as desired).

let table = base.getTable("Dates")
let fields = table.fields
let query = await table.selectRecordsAsync({fields})
let records = query.records
const utc_offset = -8

const exclude = []
let filteredFields = fields.filter(fld => !exclude.includes(fld.name) && fld.isComputed == false)

let original = await input.recordAsync("Original Record", table)

const main = async (original) => {

    let obj = {}
    filteredFields.forEach(fld => {
        switch(fld.name) {
            case "Start Date":
                const startDate = new Date(original.getCellValue(fld.name))
                startDate.setHours(startDate.getHours() + utc_offset)
                const newStartDate = new Date(startDate.getTime())
                newStartDate.setDate(newStartDate.getDate() + 1)
                while (newStartDate.getDay() !== 1)
                    newStartDate.setDate(newStartDate.getDate() + 1)
                const newEndDate = new Date(newStartDate.getTime())
                newEndDate.setDate(newEndDate.getDate() + 5)
                obj["Start Date"] = newStartDate
                obj["End Date"] = newEndDate
                break
                
            case "End Date":
                break
                
            default:
                obj[fld.name] = original.getCellValue(fld.name)
        }
    })

    await table.createRecordAsync(obj)
}

if (original)
    await main(original)

Thanks for the update. As currently written, there are a few issues. First, that script should throw an error on this line because nothing has been defined named exclude:

let filteredFields = fields.filter(x => !exclude.includes(x.name) && x.isComputed == false)

If you want to exclude specific fields from being copied, you should create a string array containing the names of those fields; e.g.:

const exclude = ["Field 1", "Field 2", "Field 3"]

That line just needs to go somewhere before (above) the filtering line mentioned above.

You’re probably also seeing a strikethrough on selectRecordsAsync() in this line:

let query = await table.selectRecordsAsync()

If you mouse over it, you’ll see a message. Long story short, the blanket-capture of all fields for all records is deprecated (highly discouraged, eventually(?) to be enforced). The recommended process is to specify only those fields that you want to capture, thus allowing the script to run more efficiently. However, because you actually want to capture all fields before copying, you can avoid typing all of the field names manually by using this structure:

let query = await table.selectRecordsAsync({fields: table.fields})

I don’t recommend doing that in all use cases, but for this situation it works. (Also, if you rearrange some of your lines, you can do this slightly easier, but I’ll save that for the final tweaked script below.)

To reverse the date fields that you mentioned, you’ll need to modify this part:

filteredFields.map(x => {
    Object.assign(obj, {[x.name]: original.getCellValue(x.name)})
})

As written, that makes a literal copy of all fields without any modifications. To perform the date tweaks that you mentioned above requires a few changes. Those will be in the final version below.

Re: the button issue, this script is already designed to work with a button field because of this line:

let original = await input.recordAsync("Original Record", table)

If you run the script manually, it will prompt you to pick a record. However, if you have a button field that’s set to run this script, this line will retrieve the record where the button was clicked. If it’s still not working after applying the revised script below, let me know.

One other thing that I ran into while testing is a date offset issue. Long story short, dates retrieved from date fields are assumed to be based on GMT time, not your local time (regardless of the date field formatting options). To counter this, I added a utc_offset variable near the start of the script. Set this based on your local time zone compared to GMT. In my case, I’m in the Pacific timezone, which is GMT -8. Without applying this offset, the dates could be a day off from what they should be.

Here’s the revised script with all of the above changes applied (fill in the exclude array as desired).

let table = base.getTable("Dates")
let fields = table.fields
let query = await table.selectRecordsAsync({fields})
let records = query.records
const utc_offset = -8

const exclude = []
let filteredFields = fields.filter(fld => !exclude.includes(fld.name) && fld.isComputed == false)

let original = await input.recordAsync("Original Record", table)

const main = async (original) => {

    let obj = {}
    filteredFields.forEach(fld => {
        switch(fld.name) {
            case "Start Date":
                const startDate = new Date(original.getCellValue(fld.name))
                startDate.setHours(startDate.getHours() + utc_offset)
                const newStartDate = new Date(startDate.getTime())
                newStartDate.setDate(newStartDate.getDate() + 1)
                while (newStartDate.getDay() !== 1)
                    newStartDate.setDate(newStartDate.getDate() + 1)
                const newEndDate = new Date(newStartDate.getTime())
                newEndDate.setDate(newEndDate.getDate() + 5)
                obj["Start Date"] = newStartDate
                obj["End Date"] = newEndDate
                break
                
            case "End Date":
                break
                
            default:
                obj[fld.name] = original.getCellValue(fld.name)
        }
    })

    await table.createRecordAsync(obj)
}

if (original)
    await main(original)

What would I need to modify to make it add 1 day as opposed to 1 week?