Hello Community,
I am looking to build a tool that when someone types in their email address it returns a value saying they exist in the database and displays their membership data. How would I go about this.
Hi @Mia_Mathews - welcome to the forums! The first thing would be to connect your DB as a resource in Retool. The docs have good explainers for most databases out there.
Once that is set, create a new app with a text input box where the users will enter their email and a button to click to check it. Then create a query for your database that is something along the lines of SELECT EXISTS (SELECT 1 FROM emails_table WHERE email_addr = {{ textInput1.value }}) as email_check which will return TRUE or FALSE.
On the button, create an event that triggers the query on click. How you present to the user whether the email exists would be a design choice, but one example would be creating a text box that displays the results as text by entering something like {{ query1.data[0] ? "The email " + textInput1.value + (query1.data[0]?.email_check ? " is found in the database" : " does not exist in the database") : "" }} as the value of the text box.
Good luck, and happy building!
3 Likes
I tried connecting a google sheet but it did not let me connect a sheet when I did this the list would be in a google sheet
Ah - well it will be a bit different for a Google Sheet since that isn't a database. I don't use the Google Sheets resource, so I can't direct you off the top of my head. I would guess instead of a SQL query you would use JavaScript to check whether the value exists in an array returned from the Google Sheet.
1 Like
So I uploaded the css of the google sheet to a table in the retool database, this is where I am with the form
Where do I go from here? I cannot find how to attach the button to the field and trigger the query
CSV*** sorry correcting the typo above
If you click on the button, the right hand side will have an "Event Handlers" section. Click the + to add an event, and select the query you want to trigger.
Also, you will need to change the placeholder names I am using (e.g., textInput1, query1, etc.) with the actual names of your components/resources/queries.
So the query works from retool database but I am still lost on how to make the validate button trigger the query from what is input din the field and then return the value of that persons name and their email address along with a status of their membership
When you select the button component, you will see "Event Handlers" in the Component Inspector on the right hand side. For example:
When you click the "+", a little pop-up like this shows up:
Select the query (or queries, as you can see in my original screenshot) you want to trigger and you should be good to go.
Regarding showing more information than a simple yes/no check, you need to update the query. Something more along the lines of SELECT * FROM emails_table WHERE email_addr = {{ textInput1.value }} (the inner part of the original query).
Then you can connect a table (or key/value pair component, or whatever) to the results of the query. For most components you can have default text for an empty result (i.e. the email doesn't exist) which you can modify in the right hand side. For a table, it is in the "Appearance" section:
And you can enter something like "email not found"
so if the email does exist in the database can I make it display the persons name, their email and then their membership status to the right of the form
Yes, the simplest way is to add a table or a key/value component to the right of the form, and set the data source for the component you choose to the query results.
1 Like
can you help me with how to set up the code for that I am trying with the table but I am fearing I am doing it to wrong. How would I display that with a key value when the email is true from the button
I would need it to be first name, last name, email, membership status if the email is found in the database.
Did you change the query to something along the lines of SELECT * FROM emails_table WHERE email_addr = {{ textInput1.value }} as I mentioned previously?
The original query only gives you a TRUE/FALSE as per your initial requirement, but now you want to get information about associated to the email address if it is found, so you need to return whatever information you want to show in the query. I'm using SELECT * here to return all possible fields, but if you want to return only specific fields, you would use
SELECT
first_name,
last_name,
membership_status,
favorite_drink,
pets_name,
-- etc.
FROM
emails_table
WHERE
email_addr = {{ textInput1.value }}
as per a typical SQL query.
Okay I have done that and the query works now the table is not displaying those parameters.
What do I select as the data source and can I make the columns each a row instead
So I also did that and tried doing the same command form the button but it is saying it is not allowed because of scope.
The data source is the query. I don't know what you mean making the columns a row; you might want to try the key/value component instead (Getting started with the Key Value component | Retool Docs).
Awesome, @jg80! Just wanted to add that we have office hours if you want to chat through your app live @Mia_Mathews