Validate Text Input From Postgres DB

Queries and Resources
1

Hello All,

I'm new to Retool but learning so much quickly thanks to guides and this community. I am trying to validate a users text input against their record in a postgres db. I have a form and one of the questions asks a user to enter the last 4 of their ssn. I want to validate that number against their record in the db table before allowing them to hit submit.

How would I go about doing that?

Thanks,

Screen Shot 2022-01-08 at 1.08.50 AM
Screen Shot 2022-01-08 at 1.08.50 AM3328×1542 505 KB

2

Hello,

I was able to figure it out and used regex validation against the query. Worked out amazing!

Thanks

3

Hey @Miotx, welcome to the community :hugs:

This would work if you just want to validate the last 4 digits of a users SSN.

If you want to validate the correctness of those 4 digits you would need a JS query for your form that would:

  • Hit the DB and verify that the last 4 digits are the same
  • Execute the rest of the query accordingly.

Makes sense? Not sure if that's what you need but judging from your og post you would need to hit the DB for confirmation first.

4

Hey @minijohn , thank very much!

That seems like a better and more secure solution! How would you structure the JS query?

Thank you!

5

Very roughly like this:

(async () => {

  // get roster record (not sure how you identify a single record)
  // params passed via additionalScope can be accessed
  // in the query via {{ssn}}
  var roster = await new Promise((resolve) => {
    get_roster.trigger({
      additionalScope: {
        id: id, // replace with primary key you're using to fetch a record
      },
      onSuccess: (data) => {
        resolve(data);
      }
    });
  });

  // check if last 4 digits of SSN are the same
  if (textinput1.value == roster.last_4_ss) {
    
    // submit form
    await new Promise((resolve) => {
      post_data.trigger({
        additionalScope: {
          ssn: textinput1.value,
        },
        onSuccess: (data) => {
          resolve(data);
        }
      });
    });
  } else {
    utils.showNotification(
      title: "SSN validation error",
      description: "Couldn't verify the last 4 digits of your SSN",
      notificationType: 'warning'
    )
  }
})();

Hope that helps :mechanical_arm: