Pull in data from another table based on a linked field


#1

Hi Community,

I’m sure there is a way to do this with linked tables, I just can’t see how.
I have one table, with Yoga Poses. In that table the English Pose name is in one column, and the Sanskrit name in the 2nd column.
In a second table, I want to type in the English pose name and then pull in the Sansrkit pose name from Table 1 into the 2nd column in Table 2.

I see the Lookup command but can’t figure out Linking and Lookup relate. It looks like you need to link a table, but then make it a lookup too?

Thanks!


#2

Yes, first you will need to create the Link, then a second field for the Lookup which points to the Sanskrit name in your Yoga Poses table.

Then, every Link you make in Table 2 to an English name (under you Link field/column) will result in the Lookup (second field/column) pulling the Sanskrit name to Table 2.


#3

Is there a video or screenshot that shows this? I am trying to use https://guide.airtable.com/linking-tables/ to do this, but it’s not working.
Here’s a video of what I am trying to do:

My goal is to get the “Mautic Campaigns” cell to show, "LW Test 1"
Thanks!
Josh


#4

can you please clarify if there is a way of automatically linking two fields from two tables based on the Id field – as in similar to database table join with primary and foreign keys ?
what I saw is that I need to select the record manually to link. I would like to automatically pick the record based on the id matching on both tables ? is this possible ?


#5

Let me make sure I have this straight:

  1. You have two populated tables in the base.
  2. [TableA] contains {FieldB}, the value of which corresponds to the primary field in [TableB].
  3. You want to enter a value into {TableA::FieldB}, have Airtable locate the record in [TableB] with a matching primary value, and populate {TableA::FieldC} with a value taken from the aforementioned record from [TableB].

No, currently that is not possible.

However, is there any reason your heart is set on entering the same data multiple times? If [TableB] exists, simply make {TableA::FieldB} a linked record field pointing to [TableB]. Then, rather than having to enter redundant data, with the accompanying chance of error, the user simply clicks on the plus sign in the linked record field and selects the appropriate record from [TableB]. This may be done from a scrolling list, or the user may enter however many characters are needed to narrow the list of matching records before selecting the appropriate one. In either case, the process is still less labor-intensive than having to enter matching data in multiple locations. Once a link has been made, lookup or rollup fields are used to populate [TableA] with data from [TableB].

This also works with imported data. That is, if you have a linked record field defined within a table and then use the CSV Import Block to add records, Airtable converts values imported into that field into links to records in another table. (If you are creating the table containing the linked record field through CSV import, initially import the values as a basic data field — for instance, single-line text or numeric. Once the import is complete and the table created, right-click on the field header, select ‘Customize field type,’ and change the field to a linked record field directed at the appropriate table. Airtable will convert the imported values into links.)

Finally, if the field in [TableA] whose value is supposed to match the primary field of [TableB] is a calculated field, it is still possible to create an [almost] automated link to [TableB]:

  1. In addition to the formula field in [TableA], create a new linked record field connected to the appropriate table.
  2. After data has been entered or imported into [TableA], select the cells in [TableA] for which links are desired. This may be anywhere from a single record, to a block of records, to an entire column: Click the field in the uppermost of the records to link; scroll to the row containing the lowermost, and, while holding down the Shift key, click in the lowermost field. This selects the uppermost, lowermost, and all intervening fields.
  3. Press Ctrl-C to copy the selected fields.
  4. Click in the uppermost linked record field. (This should be located in the same record as the uppermost selected field.)
  5. Press Ctrl-V. This pastes the copied field values into a matching number of linked record fields, which Airtable subsequently converts into links.

Note that in all of these cases, if a value is entered into {TableA::FieldB} that does not match the primary field of a record in [TableB], a new [TableB] record is created, either by the user selecting ‘+ Create a new record’ at the bottom of the list of [TableB] records or automatically in the case of imported or copy-and-pasted data. Typically, if key-matching is used, the only indication no matching record was found is the fields to be populated with data from the matching record remain empty; it is up to the user to search for records with empty fields; determine this was caused by no matching record having been found (as opposed to the field having been empty in the matching record, as well); and create a new [TableB] record manually.

I realize that was an awfully verbose “No” — but I guess it was actually more of a “No, but…”


#6

let us assume the simple example of Order, and Order Details
Order table contains

Order Id, Customer Name, Order Amount

Order Details table contains:

Order ID, Line Item ID, Line Item Quantity, Line Item Amount

In simple terms, I want to have a single record in Order table, and multiple records in Order Details table which are linked based on Order ID.

I would like Order Amount to be the total amount of the order by summing up each of the order line items amounts. Hope this makes sense ?
I do not want to manually search , or allow the user to locate the order ID in order to link both. This is because I am populating those data via API.

I am sorry , I do not really sure whether I am missing anything, and appreciate your reply.
can I populate the Linked record using API ?

Thank YOU