Retrieve linked record without coding secondary table name?

[Andrew_Hildebra]

I’m using the curl API to pull json data from a table linking to records in a secondary table.

I have a working function that pulls down and parses both tables. It then uses the record ID listed in the main table and plugs in the corresponding data in the secondary table. It works fine.

But it would be very helpful to perform this without coding the name of the secondary table. Is there any way to retrieve the name of the table source of the linked record without knowing it ahead of time? Or retrieve the record with just the ID and not the table name?

[openside]

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.

[Andrew_Hildebra]

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.

[Bill_French]

Andrew_Hildebrand:

Is there any way to retrieve the name of the table source of the linked record without knowing it ahead of time.

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.

openside:

… add a formula field in your primary table that just displays the secondary table’s name as text.

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.

Andrew_Hildebrand:

… putting the name of the secondary table in the name of the linked record field.

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.

[Andrew_Hildebra]

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.

[Bill_French]

Andrew_Hildebrand:

… is a good one. If my table were only ever referencing records from one other table at a time

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.

Andrew_Hildebrand:

my tables (at least now) are considerably shorter than the 100 record pull limit.

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

[pmsoltani]

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?

[Bill_French]

pmsoltani:

IMO, however, the name of the table should be included in the API result.

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.

[kuovonne]

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.

[pmsoltani]

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.