Copy and Paste from Date to Link fields

[Max_Brungardt]

I’m trying to make a script that will read from a Date field and copy and paste that data into a Link to field in the same table.


If there is a better way of doing this I am completely open to it. The end goal is to setup an Automation that will check for a change in date from the source and reflect that in a weekly report table.

[JonathanBowen · Accepted solution]

Hi @Max_Brungardt - for this to work, your date source field must be the primary field in the table (as this is what linked fields link to), so your set up would need to be something like this:

If it is like this, then this simplified script will do the job:

let dateT = base.getTable('Dates');
let dateQ = await dateT.selectRecordsAsync();

for (let rec of dateQ.records) {
    let dateFieldID = rec.id
    await dateT.updateRecordAsync(rec, {
        'Date Link': [{id: dateFieldID}]
    })
}

The trick is that the linked field accepts an array of objects and each object is of the form:

{id: SOME_RECORD_ID}

[Max_Brungardt]

This is great! Thank you. My only issue is that the table you show has only these two fields. My table has up to 30 fields and when I run it I get an error. Any ideas on how to work around this?

[JonathanBowen]

I don’t think the error will be related to the other fields (unless you are trying to update these other fields at the same time). What is the error you are getting?

[Max_Brungardt]

[JonathanBowen]

Not sure, to be honest. It looks like an error unrelated to the script - connection issue perhaps? Can you post your full script - there might be an issue there.

[Max_Brungardt]

The entire script (and only script as of now) is the one you put up just with my changed field and table names

And this is an example of how the Table could look

[JonathanBowen]

Ah, OK. The issue is the point that I noted in the first post. The “date source” field needs to be the primary field on the table for this to work. Your primary field is the property. Actually, this won’t make it error but you will get the property name in the linked field not the date source.

Taking a step back - what is it you are trying to achieve by putting the source date in the sale date? Is this just a snapshot of the initial date? It doesn’t need to be a linked field to do this, but you may have a different purpose.

[Max_Brungardt]

The idea is that we have a Base with a Table of properties with sale dates and other information. We want another Base’s table to represent a grouping of sale dates of those properties. The important part is we don’t want to have to manually update those links. We just want to be able to change the date in the source Base Table and have that reflected in the second Base Table. I know this could easily be done by keeping the information in the same base, but this makes keeping track of reporting more difficult.

Thanks for all your insight.

[JonathanBowen]

OK, so like this?

Here, the date link field links to the second table.

This can be done with a script but you can’t create and link records in one go - you have to create it if it doesn’t exist, then link it.

I haven’t tried this out, but I think the way to do this is to use an automation that run a script. Have the automation based on “when a record is updated” and watch the source date field. Then, run the script. The script will be something like:

• Get the new date source value
• Query the dates table to see if the date already exists
• If it does, get the ID. If it doesn’t, create it, then get the ID
• Back on the properties table update the linked date field with the ID from the previous step.