Avoid creating new record for same user?

Spencer_Carrill · ‎Jun 12, 2020

Hi all! I have a Javascript integration question

I apologize if this is basic, I am quite new here. I have created a Discord bot that is storing game win rate stats using an Airtable base. Each user on Discord has a unique ID which I am storing in the first column of the table. I have a function on my bot that allows users to add themselves to the base by creating an entry with their Discord ID and with their number of games played and won. I don’t want to have two records for the same user, however, so I want to add a logical check that searches my base to see if there is already a record wherein the discordID column matches the ID of the user attempting to be added to the base.

Basically, if my discordID is 222, I want to search my base for entries where the discordID is 222, and if more than 0 appear then I want to NOT add myself, but if 0 results are returned then I DO want to add myself.

I have tried doing this below, but it doesn’t seem to be working as expected (i.e. my console will log “duplicate found” but it will still add the user to my base:

let userCount = 0;

client.base('data').select({
    filterByFormula: '{userID} = '+user.id
  }).eachPage(function page(records, fetchNextPage) {
    records.forEach(function(record) {
      userCount += 1;
      console.log("duplicate found");
    })
  }, function done(error) {
    console.log('error');
    return;
  });

if(userCount === 0) {
  client.base('data').create([
      {
        "fields": {
          "userID": user.id,
          "gamesPlayed": 0,
          "gamesWon": 0,
          "gamesPlayedBlue": 0,
          "gamesWonRed": 0,
          "gamesPlayedRed": 0,
          "gamesWonBlue": 0
        }
      }
  ], function(err, records) {
      if (err) {
        console.error(err);
        return;
      }
      records.forEach(function (record) {
        console.log(record.getId());
      });
    });
} else {
  message.channel.send("This user's stats are already being tracked in the database!");
}

If anyone is able to help, I would be very appreciative; thank you all!

Warmly,
Spence

Mike_Pennisi · ‎Jun 23, 2020

Hi Spence,

The most important detail which is missing from your code is that the eachPage function is asynchronous. I’ll share an updated version that addresses this, and then I’ll explain why it’s important.

solution (click to expand)

 let userCount = 0;
 
 client.base('data').select({
     filterByFormula: '{userID} = '+user.id
   }).eachPage(function page(records, fetchNextPage) {
-     records.forEach(function(record) {
-       userCount += 1;
-       console.log("duplicate found");
-     })
+     userCount += records.length;
+     fetchNextPage(); // Don't forget to call this function as per the documetation
   }, function done(error) {
+    if (error) {
       console.log('error');
       return;
+    }
+    if (userCount > 0) {
+      message.channel.send("This user's stats are already being tracked in the database!");
+    } else {
+      create();
+    }
   });
 
-if(userCount === 0) {
+function create() {
   client.base('data').create([
       {
         "fields": {
           "userID": user.id,
           "gamesPlayed": 0,
           "gamesWon": 0,
           "gamesPlayedBlue": 0,
           "gamesWonRed": 0,
           "gamesPlayedRed": 0,
           "gamesWonBlue": 0
         }
       }
   ], function(err, records) {
       if (err) {
         console.error(err);
         return;
       }
       records.forEach(function (record) {
         console.log(record.getId());
       });
     });
-} else {
-  message.channel.send("This user's stats are already being tracked in the database!");
 }

eachPage is asynchronous. You call it with a function, and the airtable.js library immediately makes a request to Airtable’s servers to retrieve the data.

Once airtable.js has made a request (and before the response returns), the rest of your script continues to execute. That means

if (userCount === 0) {
  // etc.

runs before the response has arrived. The value of userCount will always be zero at this moment because (as fast as it may seem) the response never returns instantly.

Later, when the response arrives, airtable.js invokes the function you provided (the function named page in your example). At this point, changes to the userCount variable no longer make a difference because the code which relies on it has already executed.

One solution is to wrap the code which creates a record in a function, and then to only call that function once the response from Airtable’s servers arrives. That’s what’s going on in the modified version I shared above.

Devika_Naik · ‎Dec 16, 2020

Hi Mike,

I have a similar problem and tried implementing the solution given by you, but I am still facing some issues. So on landing page, I am getting a user email which I am passing to this component. What I need is, when this component loads, it should figure out if the user already exists, then just give back the data for that user in the return. if user does not exist, then it should add that email to airtable and render a different component stating you are added. What my current code does is, for existing user, it gives the result on the page on the first go, but also adds it like a new user. so next time when I try to see info for this user, it shows me no records in return since it has picked up info for the new user which has nothing in it. Is there anyway I can check for no duplicate constraints or something in airtable directly? Secondly, when a new user is added, it’s added succesfully, but I want to render a different component based on the usercount value in the return, I tried doing that but it failed. Do you have any tips how I could do that?

Here is my code:

const addNewReader = async() =>{
base(‘SubscriberPoints’).create( [
{
“fields”: {
“email”: inputemail, }
}
] )
}

    const addorFetch = async () =>{
        let userCount = 0;
        base('SubscriberPoints').select({filterByFormula: `{email} = "${inputemail}"`,
        maxRecords:1
          }).eachPage(function page(records, fetchNextPage) {
                 records.forEach(function(record) {
                  console.log('Retrieved', record.get('email'));
                  console.log(records);  // show full record JS object
                  const readers = records
                  console.log(readers)
                  setReaders(readers);
      
              })
              userCount = userCount + records.length;
              fetchNextPage();
          }, function done(err) {
              if (err) { console.error(err); return; }
          })
          if (userCount > 0) {
            return;
              } else {
             addNewReader(inputemail);
        }
        
    
    }

useEffect(() => {
        addorFetch();
      
   
}, []);

    return (
        <AnimationRevealPage disabled>
        <Container>
          <TwoColumn>
            <ImageColumn>
              <Image css={imageCss} src={imageSrc} imageBorder={imageBorder} imageShadow={imageShadow} imageRounded={imageRounded}/>
              {imageDecoratorBlob && <DecoratorBlob css={imageDecoratorBlobCss} />}
            </ImageColumn>
            <TextColumn textOnLeft={textOnLeft}>
              <TextContent>
                <Heading>{heading}</Heading>
                <Description>{description}</Description>
                <Statistics>
    
                     {readers
                        .map((reader) => (
                            <Reward
                                reader={reader}
                                key={reader.id}
                            />
                        ))}
                 </Statistics>
                <PrimaryButton onClick={goBack} buttonRounded={buttonRounded} as="a" >
                  {primaryButtonText}
                </PrimaryButton>
              </TextContent>
            </TextColumn>
          </TwoColumn>
        </Container>
        </AnimationRevealPage>
    );

};

What I ideally want is, if user exists, show them the above return with their points. If it doesnt, then add it in the backend and render a different component which shows, Hey, you have been added. and next time when he logs in, based on the points he has, he will be able to see it.

Devika_Naik · ‎Dec 16, 2020

here is the return part

    return (
        <AnimationRevealPage disabled>
        <Container>
          <TwoColumn>
            <ImageColumn>
              <Image css={imageCss} src={imageSrc} imageBorder={imageBorder} imageShadow={imageShadow} imageRounded={imageRounded}/>
              {imageDecoratorBlob && <DecoratorBlob css={imageDecoratorBlobCss} />}
            </ImageColumn>
            <TextColumn textOnLeft={textOnLeft}>
              <TextContent>
                <Heading>{heading}</Heading>
                <Description>{description}</Description>
                <Statistics>
    
                     {readers
                        .map((reader) => (
                            <Reward
                                reader={reader}
                                key={reader.id}
                            />
                        ))}
                 </Statistics>
                <PrimaryButton onClick={goBack} buttonRounded={buttonRounded} as="a" >
                  {primaryButtonText}
                </PrimaryButton>
              </TextContent>
            </TextColumn>
          </TwoColumn>
        </Container>
        </AnimationRevealPage>
      );

};