Checking for Duplicates and Displaying Pop-Up Messages in Retool

[mondob]

Hi, I have a form where you can enter new leads.

When you click on the "Add Lead" button, the data from the form is imported into a BigQuery table.

I need a functionality where, before adding the data to BigQuery, it checks for duplicates based on the VAT number. If there are no duplicates, a pop-up message should appear saying, "Your lead has been successfully added to the database." However, if a duplicate lead is found, the pop-up message should display, "The lead you entered already exists in the database. Do you want to replace the existing lead?" The pop-up should also show the existing lead information and provide the option to choose between the new lead or the existing one to be kept in the database.

How can this be achieved?

[bonnyag]

You can do this one of two ways:

Option 1: Load the full table

If your app is already using a GET query that gets your entire leads table you could set a JS Query to be the form submit handler that checks if the lead already exists.

I'd recommend using _.find() which means your code would end up looking something like this:

// Import Leads Table
let leads = getLeads.data;

// Import Form value (it will get wiped when you submit and you need it for next steps)
let form = newLeadForm.data;
let newVAT = form.vat_id;

// Check if lead is a duplicate
let findLead = _.find(leads, {vat_id: newVAT);

// If findLead isn't undefined, that means it's already in your database
if(typeof findLead != 'undefined') {
  // TODO: OPEN MODAL OR SHOW NOTIFICATION
} else {
  // TODO: NEXT STEPS TO SAVE LEAD TO DB
}

The nice thing about doing it this way is that you load the leads database once and then you can add as many leads as you'd like without having to query the leads table again. The main disadvantage though is that it doesn't scale super well. If your leads table ends up with 10k records, you need to lead THE ENTIRE table in your client which can slow it down.

Option 2: Query the individual row

This option is also pretty straightforward but in practice a bit more difficult to implement. You need to do the following things:

1. On submit, query your tables database to see if there's any records with the VAT number entered by the user.
2. Check if the query returns anything
3. If so, show the duplicate modal otherwise save the lead to the db.

You could chain those using success handlers but it gets messy quickly and gets pretty difficult to troubleshoot quickly so I would recommend wrapping everything in a JS Query, meaning you'll need to use async/await. Your JS Query would end up looking something like this:

// Import Form value (it will get wiped when you submit and you need it for next steps)
let form = newLeadForm.data;
let newVAT = form.vat_id;

// Use this syntax to be able to reference the results of an async/await query
return (async () => {
  // Query leads table (await means the rest of your code won't execute until this query is resolved)
  let findLead = await findLeadQuery.trigger({
    additionalScope: {
      vat_id: newVAT
    }
  })

  // Check result (find what a negative looks like: null, undefined, [], "", etc. I'll assume it's null)
  if(findLead == null) {
    // TODO: NEXT STEPS TO SAVE LEAD TO DB 
  } else {
    // TODO: SHOW MODAL OR NOTIFICATION FOR DUPLICATE
  }
})();

By default, your JS Query will timeout after 10s, if you know your total queries will exceed that time, make sure to increase the timeout limit in the advanced settings for your query.

Although this approach is a bit more complicated, the nice thing is that the performance will remain the same regardless of the size of your leads database.

Hope this helps!

[mondob]

Thanks @bonnyag.
I'm trying to use 2nd option, but I struggle to make JS code to work.

I have SQL query findLeadQuery that returns "true" if vat_number exists in database, and returns "false" if it doesn't. The query looks like this:

select case when count<>0 then true else false end as exist_in_db from (
  select count(*) count from marts.leads_for_retool
  where vat_number = {{vat_number.value}}
)

Then I have addNewLead query that inserts new lead information to BigQuery table "leads_for_retool". This query works fine by its own.

Then I created finalCode that suppose to run findLeadQuery and based on the output of it it should either run addNewLead (when output is "false") or don't do anything ( when output is "true"). My finalCode script:

return (async () => {
  let findLead = await findLeadQuery.trigger()

  if(findLead == false) {
   addNewLead.trigger() 
  } else {
    "duplicate"
  }
})();

When I run findLeadQuery by its own, the output I get is "false":

But finalCode still do not trigger addNewLead.

What might be the problem?

[bonnyag]

@mondob I think the problem might be in the order of operations. The main issue with these async queries is that when you submit a form on retool, it will wipe out the form values and break queries' behaviors if they're not executed in a certain order. So what I would recommend is this:

1. Update your finalCode query to look like this:

let form = addLeadform.data;
let vatNum = vat_number.value; // If this number is part of your form just replace vatNum with whatever its reference is related to your form like form.vat_number

return (async () => {
  let findLead = await findLeadQuery.trigger({ additionalScope: {vat_num: vatNum} });

  if(findLead == false) {
   await addNewLead.trigger({ additionalScope: {new_lead: form} }); 
  } else {
    "duplicate"
  }
})();

2. Update the changeset in the addNewLead query to be {{new_lead}} (This will let you update its value programmatically using additionalScope).

3. Replace {{vat_number.value}} with {{vat_num}} in findLeadQuery

4. Make sure that the submit event handler triggers finalCode and not findLeadQuery

Hopefully this should help make sure your query has the proper behavior.

[mondob]

thanks @bonnyag!
I did steps you mentioned, but query still don't work.
I wonder might it be the case that the source of database is BigQuery and it returns data as an object of arrays? I wonder maybe this might do any impact for findLeadQuery query output?

[bonnyag]

@mondob I think you're right. Try to add console.log(findLead) right after you query it to see what the query actually returns so we can figure out how to properly reference this. The issue might be how you're referencing the results of findLead.

The solution might be as simple as changing findLead == false to findLead.data.exist_in_db == false.

[mondob]

@bonnyag, I've tried to replace findLead == false to findLead.data.exist_in_db == false but sadly it did not work.

I addedconsole.log(findLead) to the end of finalCode and after running it, there is now output:

[bonnyag]

@mondob Try to add console.log(findLead) right after let findLead=.... Then could you open up the debug console, run the query and post a screenshot of the debug screen after it runs, then we should be able to see what the object it returns looks like and figure it out from there.

[mondob]

@bonnyag Please see screenshot of the debug console attached:

[bonnyag]

@mondob Looks like the correct way of referencing the result from the async query is either findLead.exist_in_db or findLead.exist_in_db[0]. I would try to compare that to false and see if that fixes your issues.

[mondob]

@bonnyag I used findLead.exist_in_db[0] . and this worked! Thank you!
I also created modal DuplicateLeadModal1 which shows:

1. message that lead already exists
2. Table with info about existing lead
3. Options: "Keep existing lead" and "Replace Existing Lead"

For point 2 I created query existingDuplicateLead:

But it returns error:

Parameter type must be provided for null values. For example, "set first_name = {{ {RT$BQ_TYPE: 'STRING', value: null} }}".

Maybe you have any suggestions how this might be solved? I see that this is common error for BigQuery, but I can't understand how can I fix this

Also, I created GUI mode query for "Replace Existing Lead" choice. This also returns error:

Cannot convert undefined or null to object

My finalCode looks as follow now:

let form = addLeadform.data;
let vatNum = vat_number.value; // If this number is part of your form just replace vatNum with whatever its reference is related to your form like form.vat_number

return (async () => {
  let findLead = await findLeadQuery.trigger({ additionalScope: {vat_num: vatNum} })
    console.log(findLead);

  if(findLead.exist_in_db[0] == false) {
   await addNewLead.trigger({ additionalScope: {new_lead: form} }); 
  } else {
   await DuplicateLeadModal1.open({ additionalScope: {new_lead: form} })
  }
})()
  console.log(findLead)
  ;

I highly appreciate your support with this case.

[bonnyag]

@mondob Hmmmm. I haven't used BigQuery before so I'm not really sure about that error but for your GUI query the problem is that you can't pass additionalScope to a modal component, it only works for queries which is what's causing errors. Here's a few ideas:

1. Create two new temporary states called leadFormState and existingLeadState and set both to null by default.
2. Update finalCode to look like this:

let form = addLeadform.data;
let vatNum = vat_number.value; // If this number is part of your form just replace vatNum with whatever its reference is related to your form like form.vat_number

return (async () => {
  let findLead = await findLeadQuery.trigger({ additionalScope: {vat_num: vatNum} })
    console.log(findLead);

  if(findLead.exist_in_db[0] == false) {
   await addNewLead.trigger({ additionalScope: {new_lead: form} }); 
  } else {
    await leadFormState.setValue(form);
    let existingLead = await existingDuplicateLead.trigger( {additionalScope: {vat_num: vatNum} });
    await existingLeadState.setValue(existingLead);
    await DuplicateLeadModal1.open();
  }
})();

3. Now you can use existingLeadState.value to reference what is already in your database.
4. Now you need to set event handlers for both Keep Existing Lead and Replace Existing Lead
   4.a For Keep existing Lead, set the event handler to be Control Component and close DuplicateLeadModal1;
   4.b For Replace Existing Lead, update the filter by value in replaceExistingLead to be {{leadFormState.value.vat_number}} and the changeset value to be {{leadFormState.value}} and set the event handler to trigger replaceExistingLead

[mondob]

Thanks @bonnyag, I created two temporary states and that really solved my problem. Thanks for your help!