I'm working on a script that will move records (and its child records) from one base to another. It's mostly working but one issue I'm running into is figuring out the table information of a linked record. For instance, I have two tables: Companies and Users. A User belongs to a Company. If I wanted to move User 1, all the information and Company 1 with all its information, I need to know table information for both so I can grab the fields for each table and create the new records.
So when I have a User.company, I'm given all the field information but nothing about the table. Is there an easy way to look the table info up? I was hoping tableId was attached to the field or some identifier that shows which table this field is to but I don't see it. Since I can rename a linked field name, I can't use the field name to search for the table. For instance, I can rename the Company field to Organization in the User table so searching for an "Organization" table wouldn't help.
Any suggestions? I feel like I'm missing something simple. Either field.tableId or base.searchTableByFieldId or something I can use.
Is your script an Airtable script or are you using a different scripting platform?
An individual field does not know what table it is in. However, a linked record field does know the id of the table that it links to. The information is stored in the field options, which is available via Airtable scripting or if you are using the REST API, through the metadata: field.options.linkedTableId.
If you know the original linked record field, you could use the field options to get the linked table ID and the id of the inverse field. Then you could get that inverse field and find its linked table ID to get the original table.
Or you could just do an exhaustive search of all the tables in the base to see which one has a field with that ID. I believe that field IDs are unique across a base, although I have never seen confirmation about this in any documentation.