Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 05, 2020 07:58 AM
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.
Solved! Go to Solution.
Nov 05, 2020 09:18 AM
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}
Nov 05, 2020 09:18 AM
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}
Nov 05, 2020 09:45 AM
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?
Nov 05, 2020 09:47 AM
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?
Nov 05, 2020 09:48 AM
Nov 05, 2020 09:50 AM
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.
Nov 05, 2020 09:53 AM
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
Nov 05, 2020 09:57 AM
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.
Nov 05, 2020 10:02 AM
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.
Nov 05, 2020 10:14 AM
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: