Hi, I’m new to scripting in Airtable and scripting in general :slightly_smiling_face: I’m having trouble getting started and was hoping someone can point me in the right direction.
I’m trying to build a script where for each new record in a table (“User_Answers”), I would like to score it against all records in a different table (“Company_Answers”).
Company_Answers looks like this:
Company ID | Answer 1 | Answer 2 | Answer 3
1 | True. | True. | False
2. | False. | False. | False
What I’m struggling with at the moment, is the basics and I can’t seem to find the answer in the documentation.
How do I get the value of a cell into a variable? (For example, the value of answer 2 of user 2.)
How can I set a variable with an array with all the field names in a table?
For example, an array with for company_1_answers = [answer 1, answer 2, answer 3]
Thank you for this background info. You don’t say if you want to learn to code or just want working code. It sounds like you want to learn to code, but please correct me if I am mistaken.
You also imply that you want the script to run as an automation (for each new record in the table). I strongly recommend that you first start writing the script in Scripting App, and not as an automation script. Scripting app has far better tools for managing input and output and debugging. After you learn scripting app, you can layer on the info required to use a scripting automation.
This is actually a fairly complex task for someone completely new to scripting. You are on the right track in breaking it down into smaller steps.
I recommend looking at the documentation for input.recordAsync to find out how to input a record in in scripting app. (It will be different in an automation, but it is easier to start in Scripting app.) This will get you the a record for a user.
The look at the divination for the getCellValue for the record model to get the value of a specific answer for that user.
You will need to get the company record and then use getCellValue for each answer. Then push each value into the array.
If you get stuck, you can post your code and ask for specific advice.
Note that if your main goal is to learn scripting, you may want to take a step back from this project and take a basic JavaScript class to get JavaScript fundamentals down first. Then add in the Airtable specific knowledge.
First of all: thanks for taking the time to write this elaborate answer! That’s incredibly nice of you.
I learned the basics of coding but haven’t done it in two years so I’m rusty to say the least. Ofcourse I want working code, but I’m looking forward to the process of relearning to code while producing working code :slightly_smiling_face:
I didn’t know about the Scripting App! I will use this to start from and I’ll be back when I’m a bit further along in the process, and post the code when I’m stuck. Thanks once again!
@kuovonne I’m a bit further and have some working code.
// Load tables to work with
let table_users = base.getTable('Users');
let table_company = base.getTable('Companies');
let table_key = base.getTable('Key');
let table_score = base.getTable('Score');
let view_users = table_users.getView('Grid view');
let view_company = table_company.getView('Grid view');
let view_key = table_key.getView('Grid view');
let view_score = table_score.getView('Grid view');
let result_users = await view_users.selectRecordsAsync();
let result_company = await view_company.selectRecordsAsync();
let result_key = await view_key.selectRecordsAsync();
let result_score = await view_score.selectRecordsAsync();
// Get values from Key table to calculate score with
var queryResult = await table_key.selectRecordsAsync();
let key_record = queryResult.records[0];
let active_match = key_record.getCellValue('active_match');
let active_mismatch = key_record.getCellValue('active_mismatch');
let non_active_match = key_record.getCellValue('non_active_match');
let non_active_mismatch = key_record.getCellValue('non_active_mismatch');
let score = 0;
var queryResult = await table_users.selectRecordsAsync();
// Get latest record from user table
let user_record = queryResult.records[0];
var user_id = user_record.getCellValue('User_ID')
var user_answer = user_record.getCellValue('Dieren');
for (let record of result_company.records) {
// Need to add a for loop for each field name
// getCellValue should be te fieldname, not hard coded.
let company_answer = record.getCellValue('Dieren');
if (user_answer == 1 && company_answer == 1) {
score += active_match;
} else if (user_answer == 1 && company_answer == 0) {
score += active_mismatch;
} else if (user_answer == 0 && company_answer == 0) {
score += non_active_match;
} else {
score += non_active_mismatch;
}
// write score to score table in new record
table_score.createRecordsAsync([
{fields: {'User_ID': user_id, '5': score}},
]);
// set score back to zero
score = 0;
}
I’m stuck at the moment trying to achieve to set up a for loop circling through all the fieldnames in user_record. I would like to have the variable user_answer be something like user_record.getCellValue(fieldname1), and the next iteration be user_record.getCellValue(fieldname2),
Any tips on how I can improve my code would be very welcome as well! I’m pretty sure I’m making loads of rookie mistakes :slightly_smiling_face:
One approach is to have an array of filed names and then loop through the array.
const fieldNames = ["fieldname1", "fieldname2"]
for (let fieldName of fieldNames) {
// do stuff with the field name.
}
You’re off to a good start. Your use of variable names and white space is clear. One thing that will help you along in your coding journey is writing your own functions. I also encourage you to practice refactoring your code as you learn more. For example, eventually you will want to take that createRecordsAsync outside of the loop.
Is there a way to get the fieldnames without hardcoding them in the array? There are about 100 fieldnames and they should be flexible, if the name changes.
You can get all of the fields in a table. However, that will give you all the fields, not just the answer fields. You will need a method of determining which fields are the ones you actually want.
Ok, I will start out with a hardcoded list of field names :slightly_smiling_face:
Final question before I have to call it a night here… hopefully you can help me out again.
// Load tables to work with
let table_users = base.getTable('Users');
let table_company = base.getTable('Companies');
let table_key = base.getTable('Key');
let table_score = base.getTable('Score');
let view_users = table_users.getView('Grid view');
let view_company = table_company.getView('Grid view');
let view_key = table_key.getView('Grid view');
let view_score = table_score.getView('Grid view');
let result_users = await view_users.selectRecordsAsync();
let result_company = await view_company.selectRecordsAsync();
let result_key = await view_key.selectRecordsAsync();
let result_score = await view_score.selectRecordsAsync();
// Get values from Key table to calculate score with
var queryResult = await table_key.selectRecordsAsync();
let key_record = queryResult.records[0];
let active_match = key_record.getCellValue('active_match');
let active_mismatch = key_record.getCellValue('active_mismatch');
let non_active_match = key_record.getCellValue('non_active_match');
let non_active_mismatch = key_record.getCellValue('non_active_mismatch');
let score = 0;
var queryResult = await table_users.selectRecordsAsync();
// Get latest record from user table
let user_record = queryResult.records[0];
var user_id = user_record.getCellValue('User_ID')
const parameters = ["Dieren","Gezondheid","Internationale hulp en mensenrechten","Kunst en cultuur"];
table_score.createRecordsAsync([
{fields: {'User_ID': user_id}},
]);
for (let i = 0; i < parameters.length; i++){
var user_answer = user_record.getCellValue(parameters[i]);
for (let record of result_company.records) {
// Need to add a for loop for each field name
// getCellValue should be te fieldname, not hard coded.
let company_answer = record.getCellValue(parameters[i]);
if (user_answer == 1 && company_answer == 1) {
score += active_match;
} else if (user_answer == 1 && company_answer == 0) {
score += active_mismatch;
} else if (user_answer == 0 && company_answer == 0) {
score += non_active_match;
} else {
score += non_active_mismatch;
}
// write score to score table in new record
await table_score.updateRecordsAsync([{
id: user_id,
fields:{parameters[i]: score}},
]);
// set score back to zero
score = 0;
}
i + 1;
}
I’m trying to update a record in my scoring table by looping through the field names. I’m getting an error with this code. Any idea what I’m missing?
Learning to debug is an important part of learning to code. I recommend liberal use of console.log to inspect your variable values and see where the error occurs.
