Linked Tables Lookup

stevenjo57 · ‎Mar 08, 2024

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.

TheTimeSavingCo · ‎Mar 09, 2024

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

Adam, Consultant @ The Time Saving Co

See Solution in Thread

TheTimeSavingCo · ‎Mar 09, 2024

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

Adam, Consultant @ The Time Saving Co

stevenjo57 · ‎Mar 11, 2024

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!