Skip to main content

Update Current and Create New Record via Script (Modifying date fields)

P

Good afternoon!

I’ve seen something like this get asked a couple of times, but usually there’s no answer or just a recommendation to check documentation or something to that effect.

I am an absolute JS neophyte, but I’m reasonably computer-literate. My goal is this:

A button-activated script that:

  1. Duplicates the selected record
  2. Copies the contents of the “End Date Minus 7” field of the ORIGINAL record into the “Start Date field” of the NEW record
  3. Copies the contents of the “End Date Minus 7” field of the ORIGINAL record into the “End Date” field of the ORIGINAL record.

Essentially it takes a 15-day shift and splits it into two 8-day shifts that overlap by a day.

It’s a little loopy, but the maneuvers seem like they should be simple enough. How would I approach this?

3 replies

Alexey_Gusev
Forum|alt.badge.img+23

Hi,
it may be simple or quite hard depending on the type of fields in your table, that are different from usual text.
for example,

for first 3 fields it’s simple - script can just take the value from field ‘Name’ and puts it to the same field of new record.
but then it meets single-select - an object

which you can’t just write to a new record. you should write only an object
{name:'Todo'} (or {id:"sel8......"} , but usually name used)

there are also some field types require value transforming.

and the fifth field, formula. it’s non-writable, you should just omit such fields.

I did some procedure when I need to clone a part of my records, at first I thought it should be easy, but then I realized that I could just copy-paste then group by data created, sort and bulk edit some columns like in your case.

anyway, I completed my script (months ago). if I find it, i’ll put it here.

Alexey_Gusev
Forum|alt.badge.img+23

yes, it works. modified it to add your field names and run from button.
just set your table name and run

const table=base.getTable('Table1')
const [START,E7,END]=['Start Date','End Date Minus 7','End Date']
const flds=table.fields.filter(f=>(!f.isComputed)&&(f.name!=START))
const convert=(fieldType,value)=>fieldType=='singleSelect'? {'name':value.name}:
fieldType=='multipleSelects'? value.map(v=>({'name':v.name})):
fieldType=='multipleAttachments'? value.url:
fieldType=='multipleRecordLinks'? value.map(v=>({'id':v.id})): value;

const rec=await input.recordAsync('',table);
const newrow=r=>Object.fromEntries([[START,r.getCellValue(E7)],...flds.map(f=>
[f.name,convert(f.type,r.getCellValue(f))])]);
if(!rec) throw new Error('Record not defined')
await table.createRecordAsync(newrow(rec));
await table.updateRecordAsync(rec.id,{[END]:rec.getCellValue(E7)})
P
  • Patrick_Bliss
  • Author
  • New Participant
  • 3 replies
  • October 25, 2022

Thank you for the help Alexey!

Right now I’m getting an error when trying to run the script:

ERROR
TypeError: Cannot read properties of null (reading ‘map’)
at convert on line 7
at on line 11
at newrow on line 10
at main on line 13

If it helps, “Start Date” and “End Date” are date fields. “End Date Minus 7” is a formula field that uses the following formula:

DATEADD({End Date Minus 7}, -7, ‘days’)

Reply


Most helpful members this week

Terms & Conditions