Skip to main content

I am trying to have AirTable take an ID number (from column a) and pull first and last names into columns b and c. I would like to use a second table as the reference sheet that would only hold this information (ID, First Name, Last Name).


I have done something similar to this in Google Sheets by using a second sheet in the file to reference this information and then I have the following script running:

function onOpen(event) {


var FirstNameInsert = SpreadsheetApp.getActiveSheet().getRange(‘B2:B2000’);

var LastNameInsert = SpreadsheetApp.getActiveSheet().getRange(‘C2:C2000’);


var FirstNameFormula = “=vlookup(A2:A20000,‘Active Students’!A:C,2,false)”;

var LastNameFormula = “=vlookup(A2:A20000,‘Active Students’!A:C,3,false)”;


var FirstNameCheckCell = SpreadsheetApp.getActiveSheet().getRange(‘firstname’).getValue;

var LastNameCheckCell = SpreadsheetApp.getActiveSheet().getRange(‘lastname’).getValue;


if ( FirstNameCheckCell != FirstNameFormula ) {


FirstNameInsert.setValue(FirstNameFormula) // This will enter the formula in column B row 2 - 1000


}


if ( LastNameCheckCell != LastNameFormula ) {


LastNameInsert.setValue(LastNameFormula) // This will enter the formula in column C 2 - 1000


}

var sheet = SpreadsheetApp.getActiveSheet()

var timezone = “GMT-7”;

var timestamp_format = “MM-dd-yyyy hh:mm a”;

var date = Utilities.formatDate(new Date, timezone, timestamp_format)

var Timestamp = SpreadsheetApp.getActiveSheet().getRange(‘timestamp’).getValue


if ( date <= Timestamp ) {


sheet.sort(4, false);

}

}


However, I can not figure out how to do it in Airtable. Any suggestions or help is appreciated.

For your reference, I’ll be using Airtable’s terminology which differs from Google Sheets (i.e. what you call a sheet is referred to as a table in AT, columns are fields, etc.)


Are you just trying to get a filtered view of Students who are actively enrolled? If that’s the case, you wouldn’t make another Table, you’d make new a View for the existing table.


If that’s not what you’re trying to do:

Whatever table you have which references the cStudents] table should have a Link to Another Record field, pointing at the Students] table. You’d then add two Lookup fields: 1 for the first name, and 1 for the last name.

I have created an app where students use their ID number to log out for the restroom. The data from the app is being stored in AT. I want to be able to have the ID pull their names. I don’t think I can link the ID fields because they are in field A. Is that correct? I am brand new to Airtable.

Yes, technically. The first column cannot be a Link field, but the first column is always the column that is linked to.


You reference using two sheets in Google Sheets, in Airtable you would use two tables. The ID would be the first column in the first table, and the second table would include a column in any position other than 1 which links to the ID field in the first table. Then you’d be able to use the Lookup fields as I described.

Yes, technically. The first column cannot be a Link field, but the first column is always the column that is linked to.


You reference using two sheets in Google Sheets, in Airtable you would use two tables. The ID would be the first column in the first table, and the second table would include a column in any position other than 1 which links to the ID field in the first table. Then you’d be able to use the Lookup fields as I described.


So I duplicated my ID Field in my active students table so that I could link it to my log table and set up the look out fields. It worked for the first time the ID number showed on the table only. It also did not pull the name if a new entry is made (a new log in/out from the app). Is there away around this?

So I duplicated my ID Field in my active students table so that I could link it to my log table and set up the look out fields. It worked for the first time the ID number showed on the table only. It also did not pull the name if a new entry is made (a new log in/out from the app). Is there away around this?


Can you provide a link to your base, or a copy of your table with the data cleared out if it contains sensitive/confidential info?

Can you provide a link to your base, or a copy of your table with the data cleared out if it contains sensitive/confidential info?


I greatly appreciate all of your help. I get an error when I try to post links or pictures. I added a few spaces to get around this.

https:// airtable .com/invite/l?inviteId=invHisprmSiohI9Me&inviteToken=b9c11e3ba93e1c7e05058e6c77e24225e469a661e920d0294bf625cd27906979

Got it. The Lookup fields only pull in data for records which have the Link to Another Record type field {Active Students} filled in.


The {Student ID} field should be your primary field in the [Active Students] table, but should not be the primary field in the [Log] table. The {Student ID} field in the [Log] table isn’t telling Airtable to “find this record in the other table”, its telling Airtable “this is the name of this record”.


As much as possible, the primary field for each table should produce unique values. You already have a timestamp field, I would make that the primary field in the tLog] table. That way you only have 1 field to input the student ID number (the Link to Another Record field {Active Students}).

Reply


Terms & Conditions