Skip to main content

Hi all.

So I seem to be stuck. I have a table called People. 100s of records with them all associated with a Company Name (from Company Table). Some people are designated (checkbox) as being a billing contact or primary contact for a company. All of this is great. I made a view called People By Billing view that shows me just those marked

Now each year we sent out invoices. We have a table called Payments, with one record for each Company in the Company Table.  This is all good. Here is where I am frozen. What I want to do is pull those that are marked as primary or billing from People to link in the Payments. We want this because we send out emails via the SendGrid tool to those people and attach the Payment ID to it so we can process. 

I wrote a script that loops through a People By Billing and writes them to a linked record in Payments but it keeps overwriting the record already in the linked record, and honestly, I don’t feel like this needs to be a formula. I don’t want me client to have to run it.  I want the People_Link to just pull those from the People By Billing View in People.  The records you see in there now were pushed via my inefficient script! And Thanks as always! - Jon

 

I might as well add the script here in case someone can see where I am missing the addition to People_Link and not overriding.

 

let table = base.getTable("Payments");
let mainTable = table.getView("Current Year View Need to Pay");
let mainTableRecords = await mainTable.selectRecordsAsync();
 
//Substitute "Product" for table which contains range to search in
let table_look = base.getTable("People");
let lookupTable = table_look.getView("People By Billing View");
let lookupRangeRecords = await lookupTable.selectRecordsAsync();
 
//Replace "Item.barcode" with column name which has the values you want to look up 
for (let record of mainTableRecords.records) {  
   
     let lookupValue = record.getCellValue("Company Name")[0]["name"];
     let existingLinks = record.getCellValue("People_Link"); // Get existing links from the 'Projects' field
     console.log(lookupValue+ "Is the lookup company");
     console.log(record.getCellValue('People_Link'));
    for (let rangeRecord of lookupRangeRecords.records) { 
        if (rangeRecord.getCellValue("Company Name")[0]["name"] === lookupValue) {
            let linkID = rangeRecord.id;
             let newLinks = [];
             if (existingLinks) {
                existingLinks.forEach(link => {
                   newLinks.push({ id: link.id });
                   output.text(link.id)
                 });
            }
            newLinks.push({ id: linkID }); 
          // This just keeps showing me one record even though I know there are two available. It keeps overriding the first. 
            await table.updateRecordAsync(record, { "People_Link": newLinks }) 
 
       } 
    }
}

 

 

  

Hi,

TBH, i don’t like using ‘for’, so I changed it in other way.
I couldn’t test it as it needs much time to create test table 
 

let table = base.getTable("Payments");
let mainTable = table.getView("Current Year View Need to Pay");
let mainQuery = await mainTable.selectRecordsAsync();

//Substitute "Product" for table which contains range to search in
let table_look = base.getTable("People");
let lookupTable = table_look.getView("People By Billing View");
let lookupRange = await lookupTable.selectRecordsAsync();

const company=rec=>rec.getCellValue("Company Name")[0]["name"]
const lookup=value=>lookupRange.records.filter(r=>company(r)===value).
map(({id})=>({id}))
const append=r=>[...(r.getCellValue("People_Link")||[]), ...lookup(company(r))]
const update=r=>({id:r.id,fields:{"People_Link":append(r)}})

const upd=mainQuery.records.map(update).slice(0,5)
output.table(upd.map(u=>u.fields)) //show planned updates
await input.buttonsAsync('',['Continue']) //and wait to press Continue (or Stop)
while(upd.length) await table.updateRecordsAsync(upd.splice(0,50))

Added .slice(0,5) to update first 5 records, not the whole table. and confirmation with ‘Continue’
So you can check and evaluate before doing full update.
To update whole table - just remove slice part. 

Hm I feel like I’m missing something, sorry.  People are linked to Companies, and Companies is linked to Payments, right?  

If so, what if we tried the following?

  1. Create a conditional lookup field in Companies that displays the record IDs of people who have either the Billing Contact or Primary Contact checkbox ticked
  2. Create a lookup field in Payments to display the record IDs of those People
  3. Create a formula that will check whether the value of the lookup field is the same as the value in People_Link
  4. Create an automation that will trigger off of the formula field, and if it’s not a match, it’ll paste the value from the lookup field into the linked field to update the link

 

Hi,

TBH, i don’t like using ‘for’, so I changed it in other way.
I couldn’t test it as it needs much time to create test table 
 

let table = base.getTable("Payments");
let mainTable = table.getView("Current Year View Need to Pay");
let mainQuery = await mainTable.selectRecordsAsync();

//Substitute "Product" for table which contains range to search in
let table_look = base.getTable("People");
let lookupTable = table_look.getView("People By Billing View");
let lookupRange = await lookupTable.selectRecordsAsync();

const company=rec=>rec.getCellValue("Company Name")[0]["name"]
const lookup=value=>lookupRange.records.filter(r=>company(r)===value).
map(({id})=>({id}))
const append=r=>[...(r.getCellValue("People_Link")||[]), ...lookup(company(r))]
const update=r=>({id:r.id,fields:{"People_Link":append(r)}})

const upd=mainQuery.records.map(update).slice(0,5)
output.table(upd.map(u=>u.fields)) //show planned updates
await input.buttonsAsync('',['Continue']) //and wait to press Continue (or Stop)
while(upd.length) await table.updateRecordsAsync(upd.splice(0,50))

Added .slice(0,5) to update first 5 records, not the whole table. and confirmation with ‘Continue’
So you can check and evaluate before doing full update.
To update whole table - just remove slice part. 

Love this script and worked like a charm. Thanks!

Terms & ConditionsAccessibility statement