Retrieving Linked Records via API

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.

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?

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))

Bill is awesome!

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?

That was my assumption as well.

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?

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.

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.

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

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

Indeed. More deep wisdom. :winking_face:

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.

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.

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

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

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.

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.

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.

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

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…