Importing tables and having them linked based on their contents? Why is this hard for me in AT?

[Joedavis44]

Hi,  I am very new to AirTable but have a lot of relational DB experience years ago, using first dBase, then MS Access.  I am working on a simple project for my golf club that calls out for a RDB solution.  I have started learning AT, but am stuck at a very basic level. Can anyone please advise?  I have read countless posts and watched videos and I somehow feel I am missing something very basic.

Here is the problem simplified down to the most basic level.  I have two tables, Members and Rounds Played.  The fields in Members are their unique Member ID first, then lots of Member details, like name, address, phones, etc.  Rounds played has three fields.  The first is the Member ID, then the month, and finally the # of rounds played that month.  From my background this is a pretty easy 1 to many relationship, the tables linked by the Member ID.   In my old world, Member ID would be the primary key for the Members Table, but I have learned enough to know that AT uses a hidden primary key that is based off my first column.  I don't think that is my problem though.  I started with the data in two Excel spreadsheets.  I created a new AT base, defined two tables using the structure I described above.  I then imported the excel files one at a time into the two AT tables.  Data all there and looks great.  Now I want to create a view that shows the Member ID and his name, then how many rounds he played each month.  I have added the "Link to Table" field to the Rounds Played table to connect the two tables, and then added a Lookup field to pull the Member name into the Rounds played view, but the Link and Lookup fields were blank.  That stumped me for a while.  I finally realized that I had to type in the Member ID into the Link field in order for the Lookup field to populate the member name.  That works, but I can't possibly do this manually for every record.  There are thousands of records.  The Member ID is already in both tables.  Why can't I link the tables using the data that is already there?

As I said, I may just be missing some very basic AT information that makes this simple, but I have spent way too much time trying to figure this out.  Any help?

Joe

[Pascal_Gallais- · Accepted solution]

@Joedavis44 

Hello Joe,

I think your problem comes from the fact that you "Member ID" is your first column in table "Rounds Played" and hence the primary key.

You should create a round table with its own key and "Member ID" as a text field to start with:

round_id in this example is an auto number.

Once you have populated this table, you can modifiy "Member_ID" field type to link it to your Member table:

You will then have the possibility to add lookup fields as you wish:

Regards,

Pascal

[TheTimeSavingCo]

You mentioned that the "Member ID" field is the first field in "Members", i.e. it's the primary field?  If so, try:
1. In 'Rounds', in a view where all records are visible, click the header of the 'Member ID' field
- This should select the entire column
2. Hit CMD/CTRL+C
3. In 'Rounds', in a view where all records are visible, click the header of the linked field to 'Members'
4. Hit CMD/CTRL+V

This should automatically link everything up for you and your lookup field will populate

[Joedavis44]

Thank you.  That took care of my problem.