Retrieving Linked Records via API

[Karlstens]

Hey all,

Question for those with API experience. As linked records return the RecordID, do you typically create a Rollup Column to display and retreive the Linked Record name data, or, do you code the solution in your source to retreive the name of each record?

Simple two table base layout - an active score board;

And registered users;

My example code below, heavily influenced by the current Airtable API code samples - when retrieving the ScoreBoard, how best to show the Linked User names?

Although I’m yet to refine my find() code block, already I can see that returned names aren’t in any particular order, as opposed to the Player Name Rollup at least being the correct order.

So in summary, do you tend to code solutions in the base, or solutions in the API source when working with Linked Records? I can see advantages of knowing and understanding both.

Sample code below;

const Airtable = require('airtable');
const base = new Airtable({apiKey: apiKey}).base(myBase);


base('Score').select({
    // Selecting the first 3 records in Grid view:
    maxRecords: 5,
    view: "Weekly"
}).eachPage(function page(records) {
    // This function (`page`) will get called for each page of records.

    records.forEach(function(record) {
        console.log('Entry:', record.get('Entry'));
        console.log('Player:', record.get('Player'));
        console.log('Player:', record.get('Player Name Rollup'));
        console.log('Score:', record.get('Score'));

        console.log('Entry:', record.getId('Entry'));
        console.log('Player:', record.getId('Player'));
        console.log('Score:', record.getId('Score'))

        base('Registered').find(record.get('Player'), function(err, record) {
            if (err) { console.error(err); return; }
            console.log(record.get('Name'));
            //myNames.push(record.get('Name')) 
        });
    });
}, function done(err) {
    if (err) { console.error(err); return; }
});

[Bill_French]

Karlstens:

do you typically create a Rollup Column to display and retreive the Linked Record name data, or, do you code the solution in your source to retreive the name of each record?

I tend to work the latter pattern because the former requires the tables to be modified. In the world of Airtable, chances are good the table is already overburdened with all manner of kludginess to achieve operational requirements. I prefer not to add to the likely insanity that already exists.

And with that answer, we need to at least consider GraphQL and other ways to describe relational databases for API access.

[Karlstens]

Appreciate your help @Bill.French

Your help inspires me to keep on trying and learning. I’ve had a good crack at my High Score API project, and I think I might have it solved! :nerd_face:

const Airtable = require('airtable');
const base = new Airtable({apiKey: apiKey}).base(myBase);

let registered = [];
try {
    const records = await base('Registered').select({ 
        maxRecords: 100,
        view: "Grid view"
    }).all()
    records.map((record) => {
        registered.push({
            "Name": record.get('Name'),
            "id": record.id
        });
    })
} catch (error) {
    console.error(error)
} //console.table(registered);

let hiScore = [];
try {
    const records = await base('Score').select({ 
        maxRecords: 5,
        view: "Weekly"
    }).all()
    records.map((record) => {
        hiScore.push( record.get('Player'));
    })
} catch (error) {
    console.error(error)
} //console.table(hiScore);

const topFivePlayers  = new Set(
    hiScore.flat()
        .map(   element       => registered
        .find(  objectElement => objectElement.id === element)?.Name)
        .filter(Boolean));
    
console.log(Array.from(topFivePlayers))

I’m keen to hear your feedback, as I’m interested to know if I’ve coded anything back-to-front, or in a legacy manner.

One thing I’m wondering about is the use of .push() - as I was unable to get the correct syntax working without it. Is it acceptable, or is there a more preferable method?

[kuovonne]

Karlstens:

Appreciate your help @Bill.French

Bill is awesome!

Karlstens:

I’ve had a good crack at my High Score API project, and I think I might have it solved! :nerd_face:

Congrats! This looks like it has been a great learning project. Your output should tell you if you’ve solved it or not. If you aren’t sure, having a few different test cases should help you be sure.

But keep in mind that there are multiple possible solutions, so if your goal is learning (versus simply having working code), you can always code both options you were considering at first and compare the results and the code required. Or keep refactoring to see what else you can learn.

Here are some random thoughts that came to my mind when reading your code.

• why the try catch that just logs the error? If the first try/catch fails, why not quit since you won’t be able to do the later tasks.

• I’m a little surprised by the .push and wonder why you decided on it. What were you doing before? Were you having trouble with variable scope? I would expect to have the variable declaration before the try/catch and simple variable assignment inside the try/catch.

• why create a set from your hiScore array? Are you getting rid of repeats?

• your arrangement of white space in the array functions inside the creation of the set make the code hard to read

• Bill will probably tell you to create a hash of record ID to player name to avoid nested looping

• why are you filtering on the keyword Boolean?

[Bill_French]

kuovonne:

why create a set from your hiScore array? Are you getting rid of repeats?

That was my assumption as well.

kuovonne:

Bill will probably tell you to create a hash of record ID to player name to avoid nested looping

Yes, Bill would :winking_face: but only if we’re dealing with a significant scale issue. I’m unfamiliar with the data.

I’m a huge fan of SON arrays and such, but in this case, why go to all this trouble when a single array sort statement will do?

[kuovonne]

Bill.French:

Yes, Bill would :winking_face: but only if we’re dealing with a significant scale issue. I’m unfamiliar with the data.

We do know he only wants names in the top five slots. So he needs at most to convert five record IDs to names.

We also know that he is retrieving at most 100 names, although that might not be his intention.

So at this scale it probably doesn’t really matter.

But I get the impression that this is also a learning project, so it might make sense to learn techniques that do scale, even if not strictly necessary.

As for removing duplicates, it would be more efficient to remove duplicates before converting record ids to names. Again it doesn’t matter at this scale, but it is something to think about from a learning point of view. Then again, maybe thinking about scaling is too much for this stage of learning.

Bill.French:

why go to all this trouble when a single array sort statement will do?

I believe all the sorting is done in the view settings. So there should not need to be any sorting done at all in this code.

That bit of code is him matching record ids from a linked record field with names from the linked table. He is trying to take your advice about not using the rollup field.

If he did take advantage of the names in the rollup field the code would be much simpler. Query for the first five records in the sorted view, specifying that you only want data from the rolled up name field. Then map the records to the field value and make unique.

I would caution that the process of making the values unique does not necessarily preserve the desired order of the top five. It probably will, but technically elements in a set are not ordered and so there is no guarantee that the order of the array that is generated from the set is in the same order as the array that was made to create the set.

[Bill_French]

kuovonne:

But I get the impression that this is also a learning project, so it might make sense to learn techniques that do scale, even if not strictly necessary.

Wow! Such deep words of wisdom from a person who still has about 75 years to get smarter.

kuovonne:

That bit of code is him matching record ids from a linked record field with names from the linked table.

Ah, ok. I glanced at it and assumed that was a bubble sort.

kuovonne:

I would caution that the process of making the values unique does not necessarily preserve the desired order of the top five.

Indeed. More deep wisdom. :winking_face:

[kuovonne]

Bill.French:

Ah, ok. I glanced at it and assumed that was a bubble sort.

That is probably because the use of white space makes it hard to read the code, which I also pointed out.

This particular bit of code could be revisited for various reasons. It reminds me of when I was teaching paragraph and essay writing when my kids were in upper elementary and middle school. There are various things that can be cleaned up but it is hard to say what to do because some changes negate the need for others.

And really getting working code at all is sometimes such a triumph that it needs to be celebrated without nitpicking on other things.

On the other hand, if this is truly mostly a learning project. I would put understanding how to use white space for readability as far higher in importance than learning scaling tricks. Having readable code is a fundamental skill that makes everything else easier. Imagine trying to edit an essay in messy handwriting versus one written in MLA format. Fix the handwriting first so you don’t have to waste time figuring out what you wrote versus being able to focus on the actual content. There also isn’t only one readable style—there are many.

[Karlstens]

It’s become clear that I should comment my code before posting :joy:

I’ll see how I go with answering your initial questions - and firstly, to summarize what I’m attempting to do here;

Write a Javascript Node app that takes the top five players from a scoreboard, be it daily, weekly, monthly. If there are duplicates, remove them. As the ordered player names are linked to a Names table, I then have to match the returned RecordIds with their corresponding name to display correctly. The Names list could be thousands of names. It’s important for the order of the original array to remain, 1st, 2nd, 3rd, 4th and 5th.

Also to clarify, that this is a self-set learning project. My aim is to have a simple web-based game, that then posts to my Node server, and my server then uses Airtable as a database to save and retrieve player details and scores.

Why the try catch that just logs the error? If the first try/catch fails, why not quit since you won’t be able to do the later tasks.

At this stage in my project I haven’t needed much focus on error handling. What you’ve mentioned here will be ideal to implement soon-ish.

I’m a little surprised by the .push and wonder why you decided on it.
I decided on this because I couldn’t get a simpler method to work. :cry: This is where I lost most my time, trying to understand the various methods available to pull data from to tables, and then intertwine them (in my case, matching an object of Names and Ids to an Array of Ids) to get the desired result.

why create a set from your hiScore array? Are you getting rid of repeats?
Yes. The aim here is to remove duplicates as the same player may get into the top 5 players more than once. I now realise too that to guarantee the return of the top 5 players, I need to pull the top 21 players down - then de-dupe, then return an array of 5.

your arrangement of white space in the array functions inside the creation of the set make the code hard to read
Yes, I found this too. Admitingly my code is a mish-mash of what I found through reading online, and what I then worked into the code.

why are you filtering on the keyword Boolean?
This cleans out any nullish data from the final array, it’s probably not needed, but will keep for now.

He is trying to take your advice about not using the rollup field.
Yup, I have decided that the base will exist without supporting fields, and that I’m to learn to solve this problem with code outside of my base. (I too manage a base professionally, and the idea of loading in Rollup fields for every lookup/linked field in that base struck fear into my heart :rofl: ).

It’s important to note that although my code works in VS Code (yay!), as I started to transfer it into my Node JS server, it returned this error.

 SyntaxError: await is only valid in async functions and the top level bodies of modules

I won’t troubleshoot this yet, not until I’ve revised/rewritten aspects and have an understanding of them.

EDIT:
It’s worth noting this mornings direction, learning Axios. Already I’m making a lot more sense of my code - but am getting stuck with getting my response data where it needs to be.

[kuovonne]

Karlstens:

Also to clarify, that this is a self-set learning project.

Thanks for the clarification. It does make a difference when giving advice.

Karlstens:

At this stage in my project I haven’t needed much focus on error handling. What you’ve mentioned here will be ideal to implement soon-ish.

Given that you are trying to juggle and learn so many things at once, I suggest that you remove the try/catch for now. Get everything working without the try/catch and let things crash if there is an error. Chances are that there won’t be an error from the Airtable server if you form your requests properly. After you get the rest working, add the try/catch back in and make any necessary adjustments.

Karlstens:

I decided on this because I couldn’t get a simpler method to work.

Have you now been able to get a simpler method to work? I suspect that the issue was related to variable scope and the try/catch.

Karlstens:

Admitingly my code is a mish-mash of what I found through reading online, and what I then worked into the code.

When you use a few lines of code that you find online, I recommend taking the time to reformat them to make them readable when included in your code. Also take the time to understand how the code works. You won’t always be able to understand the code, and in the case of some complex algorithms or established libraries, there isn’t really a need. But code snippets like what you included here are worth parsing until you understand them.

Karlstens:

await is only valid in async functions

Ah the wonders of asynchronous code.That’s a whole can of worms in and of itself.

Karlstens:

It’s worth noting this mornings direction, learning Axios. Already I’m making a lot more sense of my code - but am getting stuck with getting my response data where it needs to be.

This is also related to the idea of asynchronous code. Your final console.log(highScore) is executing before the code in the .then. That is why it has the old value. Try adding another console.log immediately after you set the value of hiScore to see what I mean. Also add a console.log that shows the timestamp and some static text so you know which line was executed…