Retrieve linked record without coding secondary table name?

Hi Andrew, you could add a formula field in your primary table that just displays the secondary table’s name as text. The formula would just be: “SecondaryTableName” . Then that field would show up in every record and you can use in your code once you retrieve the primary table records.

Thanks openside. That could work. I was also just thinking about putting the name of the secondary table in the name of the linked record field.

I guess they’re both open to user error. But it’s better than nothing.

This is certainly an ugly issue because APIs should be naturally efficient about retrieving data given any single record ID. Lacking an API that supports helper methods to interrogate the schema and relationships, we’re basically on our own.

In a 25,000 record table, that’s 24,999 times the amount of data space actually required. :winking_face: Sure, it would work, but oh my - what a waste of resources.

Before doing that, consider using the table ID concatenated with the record ID (delimited with a dot or something). That would be at least a little more space-efficient.

Possible Better Hack?

What if you added a new field (such as “Linked Table”) and on one record – and one record only - you populate this field with the secondary table name?

• As we know, the API never returns empty values, ergo - this Linked Table field will never be visible in the JSON results (except for one record).
• We also know that a query to find the Linked Table requires only that we filter based on Linked Table != "" (i.e., not empty).
• In a dynamic environment, a single query into the primary table returning a single result record would provide the secondary table name without forcing any bloat in the primary table, and without redundant values nor any redundant filters that need to process.

This proposed approach is simply a less pukey hack that requires an interrogation of the primary table. However, it would be relatively performant and would meet the need for a single API process to perform dynamically driven linked table identification.

I think that single record method is a good one. If my table were only ever referencing records from one other table at a time. But I want to build this system that might have one column referencing one table, and another referencing another. Your method could still work there, but gets a bit harder to keep track of.

And, my tables (at least now) are considerably shorter than the 100 record pull limit. So resource efficiency is a lower priority for me than simplicity of use.

Not true - simply create multiple Linked Table cell values that each map into their respective indexed fields. This would support (n…) linked fields for the table and discovery would be dynamically sound.

Ergo, if you query for a single record result where [‘Linked Field’] != blank and more than one exists, you can now enumerate the results and know that each such field is indeed a link field.

Why is the 100 record pull limit relevant to this conversation? I don’t follow.

Thanks @Bill.French and @openside. Both suggestions are excellent. IMO, however, the name of the table should be included in the API result. Are you aware of any request made to the Airtable staff on this?

Agree; this is not a bad idea and I don’t think there is a suggestion like this in the forum.

In my Google Apps Script Airtable integration library, I actually embellish result sets with the table name, the table ID, and the view ID (if used). I also add additional attributes such as the base ID, date/time of the request, item count, and even the version of my library. If there was a filter, I include that as well.

Maybe this will be possible when the Standard API gets some access to metadata. This information is already available in the Scripting API.

What if you created a new table for metadata, then used Scripting block to populate that table? Then the Standard API could pull the information from the table of metadata. The only table name that you would have to hardcode would be the name of the table with the metadata. You would have to remember to run the Scripting block whenever you changed the base design, but it is still less error prone than the other workarounds where a human has to manually type in values in a record or formula field.

Haven’t had the chance to work with the Scripting block yet. Although it’s free for a limited time, the Blocks are premium features of Airtable, while the API is free.

@Bill.French How can we involve an Airtable staff in this conversation?

To be clear - there are two completely unique feature issues in this thread. I’m referring to the latter (i.e., …the name of the table should be included in the API result.)

Airtable engineers and support people are watching this community very closely. I can’t speak for the team, but I imagine that sometimes they don’t actively respond into requests for new features because no one ever likes to say “no”. :winking_face: And there are plenty of feature requests that should never be developed (if ya’ know what I mean).

This one happens to be pretty good idea and would serve many needs. However, it is also an extended functionality that no one is truly denied from having. As I mentioned, I enhance my API result sets using the simple power of JSON serialization. It’s just so damned easy to create extensible features to the API. And for this reason alone, Airtable’s time is perhaps better spent improving aspects of the platform that cannot be extended so easily.

And you can also file a support request to voice the need, eh?

This one happens to be pretty good idea and would serve many needs. However, it is also an extended functionality that no one is truly denied from having. As I mentioned, I enhance my API result sets using the simple power of JSON serialization. It’s just so damned easy to create extensible features to the API. And for this reason alone, Airtable’s time is perhaps better spent improving aspects of the platform that cannot be extended so easily.

Is this via the Google Apps Script that you mentioned earlier? If so, would you please provide an example on how to add the table name to the API result? Unfortunately, I’m not familiar with Google Apps Script and would appreciate the help.

And you can also file a support request to voice the need, eh?

Perhaps I will. Right now, I’m deciding on the architecture of my app, so I could be settling on a workaround for the time being.