Help

Linked Tables Lookup

Topic Labels: Base design
Solved
Jump to Solution
354 2
cancel
Showing results for 
Search instead for 
Did you mean: 
stevenjo57
4 - Data Explorer
4 - Data Explorer

Been a long time since I've used Air Table. Having trouble recalling how Linked Tables work.

I have two tables, one Table is a list of all checks with primary field being a check number. This is a list of EVERY check related to a bank account.

I have another table, that has a smaller subgroup of the first table. These are checks that were entered into our accounting system.  The primary field for this table is also check number.

What I need to do, is take the larger group and check to see if the record is in the smaller subgroup, and flag it.  Thus I will develop a list of all checks in Table 1 that are not in Table 2.

So essentially, Take each check in Table one, look in table 2 and if that check number is there, flag it.

This way I can create a list of checks I need to add to my accounting program.

I am a bit embarassed as about 5 years ago I had a grip on all of this, but at my age I essentially have to relearn everything after that period of time.

I appreciate all help.  Thanks.

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

If I were you I would try the following:

1. In Table 2, create a linked field to Table 1
2. In Table 2, in a view where all records are shown (i.e. not filtered), click the header of the primary field, thus selecting the entire column
3. Hit CMD / CTRL + C to copy all the values
4. In the same view, click the header of the linked field we created in Step 1
5. Hit CMD / CTRL + V.  This will paste the values and automatically link to the record in Table 1 that has the same check number
  - Note that if there are any check numbers in Table 2 that don't exist in Table 1, this method will create new records in Table 1 with those check numbers.  As you said that this is a subgroup we shouldn't need to worry about this
6. In Table 1, create a new view and filter by all the records that are not linked to any records in Table 2.  This is a list of all checks in Table 1 that are not in Table 2

It sounds like this is a one off, but if the need arises you can also automate this process too; lemme know if you want to know more about that

See Solution in Thread

2 Replies 2
TheTimeSavingCo
18 - Pluto
18 - Pluto

If I were you I would try the following:

1. In Table 2, create a linked field to Table 1
2. In Table 2, in a view where all records are shown (i.e. not filtered), click the header of the primary field, thus selecting the entire column
3. Hit CMD / CTRL + C to copy all the values
4. In the same view, click the header of the linked field we created in Step 1
5. Hit CMD / CTRL + V.  This will paste the values and automatically link to the record in Table 1 that has the same check number
  - Note that if there are any check numbers in Table 2 that don't exist in Table 1, this method will create new records in Table 1 with those check numbers.  As you said that this is a subgroup we shouldn't need to worry about this
6. In Table 1, create a new view and filter by all the records that are not linked to any records in Table 2.  This is a list of all checks in Table 1 that are not in Table 2

It sounds like this is a one off, but if the need arises you can also automate this process too; lemme know if you want to know more about that

This worked.  I couldn't figure how to actively link them after they were imported. The cut and paste seems to have been the method.

Thanks!