Help

Update main table with addresses from another table based on main table clinic names

Topic Labels: Automations Sync
468 3
cancel
Showing results for 
Search instead for 
Did you mean: 
StephanieLynn
4 - Data Explorer
4 - Data Explorer

Hi, I'm trying to make either a script or automation to update Table A from a list of values in Table B.

Table A is a list of shifts in multiple clinic locations. I'd like to be able to select a clinic location in Table A (from a single select column), and have the address for that clinic populate in the Address column in the same table. In Table B, I have the list of clinic names with their addresses. I'm trying this script, and it will run, but it doesn't do anything to the Address column in Table A. 

Any help would be greatly appreciated- new at writing scripts... Thank you in advance!

Screenshot 2024-04-22 at 4.10.51 PM.png

3 Replies 3

You don’t need a script to do that. You just need a lookup field.

In your shifts table, turn your single select field into a linked record field that points to the clinics table. Then, create a lookup field that looks up the address.

For a more detailed tutorial on how to do this, you can check out my free Airtable training course, which you can take for free by signing up for a free 30-day trial with LinkedIn Learning.

— ScottWorld, Expert Airtable Consultants

Assuming it's a one off, try:
1. Changing the primary field of "Clinic Addresses & Hours" to be the "Clinic" field
2. Create a linked field between your two tables
3. Click the field header of the "Clinic" field in "Relief Doctor Calendars"
  - This selects the values of the entire column
4. Hit CTRL / CMD + C
5. Click the field header of the linked field to "Clinic Addresses & Hours"
6. Hit CTRL / CMD + V
  - This should link all your records up
7. Create a lookup field to display the "Address" value

---
If this needs to happen for newly created records, try creating an automation that'll use a "Find Record" step to look for the appropriate record in "Clinc Addresses & Hours" and use an "Update Record" step after to either link them together and use a lookup field, or to update the address manually assuming you want a static value

https://support.airtable.com/docs/use-automations-to-timestamp-status-updates

How you trigger it will depend a lot on how your data's getting created
---
If you really want to do it via a script, in line 8, try changing `rangeRecord.getCellValue("Address")` to `rangeRecord.getCellValue("Clinic")`

For debugging stuff, try adding 'console.log' to your code to see what's going on as well.  For example, for debugging your current issue you can try adding one between your current line 8 and 9 to show that it did manage to find a match, e.g. `console.log("Match found")`.  If the script never outputs "Match found", that means either your data has no matching clinic names or there's something that needs fixing with your code

I also highly recommend checking out this post by @Bill_French as he walks through how to filter for data in an efficient manner: https://community.airtable.com/t5/show-tell/filtering-with-loops-in-javascript-how-to-avoid-insanity...

Hi,
Your script doesn't work as expected because of Table A field "Clinic" type. 
Single-select cell value is object like { name: 'SomeName', color:'lightRed2', id:'12345' }
You can try to change line 6:

 

let lookupValue=record.getCellValueAsString('Clinic')

 

or

 

let lookupValue=record.getCellValue('Clinic').name

 


you can insert some output inside inner loop, like 
console.log(returnValue) between 9 and 10
to be sure if the statement in 9 became true and how much time the update runs

The second problem you will encounter - you are trying to loop single-write operation. It it hits the Airtable limit, smth like 15 edits per second, it will prevent further edits. Like the script starts to work but then stopped to update.
Instead, you need to construct array of updates and then run it with multiple-update operation.

To be honest, @ScottWorld is right - you are trying to script core Airtable functionality, that can be done by linking tables and by add lookup field. This can be done without scripting,

But if you still want to write scripts, and continue to improve with JS learning, you can read about ES6 syntax . Core things to learn : arrow-functions, 'array-helpers': map, filter, find 
Usually, you have some input data, received as array of records:
{id:recxyz123abc890, name:'PrimaryFieldValue'} with .getCellValue('Fieldname') method
which you should turn to array of updates like 
{{id:recxyz123abc890, fields: {'FieldToUpdate' : 'New Value to set'} })

here is a possible way how your script can be written (starting from line 4):

 

let lookup=await lookupTable.selectRecordsAsync({fields:['Clinic','Address']})
let getaddr=x=>lookup.records.find(r=>r.getCellValue('Clinic')===x)?.getCellValue('Address')
let update=rec=>({id:rec.id,fields:{'Address':getaddr(rec.getCellValueAsString('Clinic'))}})
let upd=mainTableRecords.records.map(update).filter(n=>n.fields.address)
console.log(upd) // just for info and debug purposes
while (upd.length) await mainTable.updateRecordsAsync(upd.splice(0,50))