Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 19, 2021 11:07 PM
For example, say I have a directory with all the employees an multiple levels of managers. Employees report to a first level manager. A first-level manager reports to a second-level manager, who reports to a director. I create a column for who each person reports to as a “parent” column. I want to create a column to shows all the people that report to a person that is linked to the original parent relationship. Is that possible?
The purpose I have is different that my example but the concept is the same. Would appreciate the help! -Liz
Solved! Go to Solution.
Oct 25, 2021 06:31 PM
Hey Liz,
Yeah, looks like that just needs to be updated.
Check out the one further down by @kuovonne. I tried it out and it’s working as it should.
Here’s a video of it working in my base
Oct 21, 2021 08:32 AM
You’ll probably need the ‘Same Table Backlinks’ script.
Oct 22, 2021 03:33 PM
Hey Hanna Thanks for taking the time to look into this! I copied the code exactly and added two tables to match the Family Tree and Family Tree 2 names, but I didn’t have much luck. I got the following error using my example:
Oct 25, 2021 06:31 PM
Hey Liz,
Yeah, looks like that just needs to be updated.
Check out the one further down by @kuovonne. I tried it out and it’s working as it should.
Here’s a video of it working in my base
Nov 08, 2021 05:43 PM
AMAZING! Thank you so much! That worked! I will leave the code below for anyone that finds this in the future. Thank you so much Hannah!
/*****************************************************************************************************
Name: Same Table Back Links
Date: July 9, 2021
Author: Kuovonne Vorderbruggen
Website: https://atmosphere.kuovonne.com
Create backlinks for a table with same-table linked records
This script is provided “as is”, without warranty of any kind. Liability of the
author will be limited to a maximum of the original purchase price of the script.
The author will not be liable for any general, special, incidental, or consequential
damages.
*/
/*******************************************************************************
Script Configuration Settings
*/
const inputConfig = input.config({
title: ‘Create back links for same-table links’,
items: [
input.config.table(‘table’, {
label: ‘Table with same-table links’,
}),
input.config.field(‘fldLinkAuthority’, {
label: ‘Original Link’,
description: ‘This linked record field is the “source of truth” for links. Its values will not be changed.’,
parentTable: ‘table’,
}),
input.config.field(‘fldBackLink’, {
label: ‘Back Link field’,
description: ‘This linked record field will be updated to match information in the original link field.’,
parentTable: ‘table’,
}),
]
});
/*******************************************************************************
Main function for the script
*/
await main()
output.markdown("## Done")
/*******************************************************************************/
async function main() {
if (!checkSettings()) { return }
const userContinue = await input.buttonsAsync(Update backlinks in ${inputConfig.table.name}?
, [“Continue”, “Cancel”])
if (userContinue == “Cancel”) {
return
}
const queryResult = await inputConfig.table.selectRecordsAsync({fields: [
inputConfig.fldLinkAuthority,
inputConfig.fldBackLink,
]})
output.markdown(Records to examine: ${queryResult.records.length}
)
// setup map of backlinks by recordId
let linkMap = {}
for (const record of queryResult.records) {
const currentBacklinks = record.getCellValue(inputConfig.fldBackLink)
linkMap[record.id] = {
currentBacklinkIds: (currentBacklinks ? currentBacklinks.map(obj => obj.id) : ),
newBacklinkIds: , // to be filled in later in script
}
}
// fill in newBacklinkIds
for (const record of queryResult.records) {
const linkedValue = record.getCellValue(inputConfig.fldLinkAuthority)
if (!linkedValue) {continue}
for (const obj of linkedValue) {
linkMap[obj.id].newBacklinkIds.push(record.id)
}
}
// figure out which records need updating
let recordsToUpdate =
for (const recordId of Object.keys(linkMap)) {
const currentBacklinkIds = linkMap[recordId].currentBacklinkIds
const newBacklinkIds = linkMap[recordId].newBacklinkIds
if ( (currentBacklinkIds.length == newBacklinkIds.length) && currentBacklinkIds.every(value => newBacklinkIds.includes(value))) {
continue // this recordId doesn’t need updating
}
recordsToUpdate.push({
id: recordId,
fields: {
[inputConfig.fldBackLink.name]: newBacklinkIds.map(id => ({id: id}))
}
})
}
output.markdown(Records to update: ${recordsToUpdate.length}
)
while (recordsToUpdate.length > 0) {
await inputConfig.table.updateRecordsAsync(recordsToUpdate.slice(0, 50));
recordsToUpdate = recordsToUpdate.slice(50);
}
}
/*******************************************************************************/
function checkSettings() {
if (
(inputConfig.fldLinkAuthority.type != “multipleRecordLinks”)
|| (inputConfig.fldBackLink.type != “multipleRecordLinks”)
|| (inputConfig.fldLinkAuthority.options.linkedTableId != inputConfig.table.id)
|| (inputConfig.fldBackLink.options.linkedTableId != inputConfig.table.id)
|| (inputConfig.fldBackLink.id == inputConfig.fldLinkAuthority.id)
) {
output.markdown(“Check fields in settings.”)
return false
}
return true
}
/*******************************************************************************
END OF SCRIPT
*/
Nov 09, 2021 06:47 PM
Awesome! So glad it worked for you! Thank you for following up.
Thank goodness for scripting ninjas like Kuovonne. :grinning_face_with_smiling_eyes: