Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 16, 2019 02:02 PM
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.
Jul 16, 2019 04:06 PM
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 [Students]
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.
Jul 17, 2019 09:43 AM
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.
Jul 17, 2019 10:21 AM
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.
Jul 17, 2019 10:30 AM
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?
Jul 17, 2019 10:44 AM
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?
Jul 17, 2019 10:51 AM
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
Jul 17, 2019 04:14 PM
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 [Log]
table. That way you only have 1 field to input the student ID number (the Link to Another Record field {Active Students}
).