Go back to 8base Academy
August 19, 2020

Develop a Custom CRM using Retool and 8base

Sebastian Scholl

*This is an automated transcript. Please excuse inaccuracies.

Hey there, my name is Sebastian. I'm the product manager here at 8base. If you like this video please like it as well as subscribe to our channel, it really helps us grow. So, in this video what we're going to be building is a custom CRM solution using a 8base back end and a re-tool front-end.  


If you look at the proliferation of low-code and no code tools that have popped up over the last years, it's really incredible what people that don't have traditional coding skills or computer science backgrounds can actually develop using little or no code.  


Now this is a great way to introduce to these two tools that is 8base and Retool. So, let's just dive in and build this thing. And so here we go as always what I've done is gone ahead and write a full tutorial that is hosted on GitHub. So, I will make sure that this link is in the video description, so you can follow along and complete each step with me if you want to or just go ahead and watch me do it. We're going to be starting from scratch and do everything just in the console of each tool right.


So, first off what you're going to need to have is 8base and a Retool account. Both platforms provide you know free tiers or free trials that will allow you to complete this entire tutorial having no problem at all. So, don't worry about having to create any new subscriptions at the moment.  


Now just to kick off the tutorial what we're going to start by doing is building the data model for our custom CRM and then making sure that the Graphql API that we're going to set up is working.  


Now it's a pretty simple data model just because what we're going to be doing is using a template that Retool actually provides you and then retrofitting it to work with a Graphql API. But this will give you a really great understanding of how to you know construct a simple data model with an 8base and as well as just give you all the tools you need to then expand upon that in the future.  


So, there's two tables we're going to need; deals and touch points. So, deals is going to be the main record on our CRM that we're going to use and then touch points is simply going to be a related record that shows when the deal was last interacted with whether it's a phone call or an email or an in-person meeting; whatever that might be.  


So, let's shoot on over to 8base and go into our data builder and start adding our tables. So, we're just going to go ahead and add two tables first one is going to be deals which we have added there and then I'm going to add another table called “Touch Points”. And then I'm also going to make my screen a little bit better size for that, cool touch points- awesome.  


All right now on Touch Points we're going to only add one field which is going to be a field called contact medium and so this is kind of like a select list they're going to be building or an Enum. So, let's say contact medium is the name of the field we're going to select switch type and then custom and this is going to allow us to add multiple options in here. So, one option is going to be email, another option is going to be phone and the third option is going to be in person, cool. And then all we're going to do is say that this is a mandatory field and by default it's going to be a phone. The phone is going to be the default value if nothing is specified. So, let's create that field cool so we just had our first field great job. And next what we're going to do is we're actually going to build a relationship between the deals table and the touchpoints table.  


Now this is easy if I just drag the deals table over onto this new field input and drop it into place it's going to wire up most of what we need done. So, as you can see that now we have a relationship to where it says there's allowed multiple touch points per deal; it's a mini to one relationship that's what we want. We're also going to make the deal field mandatory because we want to make sure that every touch point belongs to a deal. We don't just want a random touch point in our database that's not belonging to anything and then what we're also going to do is rename it to where the relationship from the touchpoints table is called deal not deals since it's a singular relationship, cool.  


So, once we do that we go ahead and create the field and that's then they're done. So, then what we're going to do is that's actually all we need for touch points we're going to then jump into the deals table and this is where we're going to have to add a little bit more information. So, you can see that it already did the reverse wiring on the relationship between deals and touch points so it knows that a deal has many touch points. However, if we go over to our tutorial here and we scroll down it's going to show us the structure that we need here for the deals table this is just kind of talking about the steps that we just went through. So, we're going to see that we want a field called email which is a text field that's mandatory and has 30 characters.  


So, email let's make that a text field, all ready character size and mandatory, cool. Next up we have stage which is going to be another switch like we just did lead, opportunity and customer mandatory default is lead, So, let's see if I remember all that when I go in here. Stage we're going to have a switch type field let's get that to be custom. Let's have a lead. Let's have an opportunity and let's have a customer cool and then it's going to be mandatory and it's going to be a lead by default. So, then that's then there and then we're going to have an amount which is going to be a number with two decimal places. Amount to how much the deal is going to be worth. Number two, decibel places. Create that field. Then we're also going to add a deal name, so we need that deal name text. I think that we can leave it. Let's have it mandatory, but we'll leave it for 100 characters and yep that's good. Same thing for department but not mandatory. The department, cool. Text- create a phone number. All right we're almost there I'm sure you got the hang of this at this point phone number, text 12. Cool create that- awesome. And then finally touch points which is already done so, cool.  


Now something that's really cool that was happening right there is as we're going through and defining this table it's actually reverse engineering our Graphql API for us. So, already do we have a Graphql API that we can use start querying records from, sending records to, listening to subscriptions on; all that cool stuff. And so just to show that we did this right I'm going to jump over to the API explorer which is essentially a you know an API client that we can use to execute queries against our API. You could use the one that's embedded here in 8base, graphical postman, they're all out there and really easy to use. However, what we can do is I have this this mutation right here that's going to create a deal as well as you can see create two touch points for the deal in one mutation. So, I'm just going to drop that in here and run it to make sure that we got all our names right and everything like that.  


So, I just ran it and our response is success. So, it created the deal with that name and was created at this time. And then was also cool is while running that mutation we also created those two touch points which automatically are getting associated with our deal since they're being done through the relationship or they're being created through the relationship. And we can verify that as well if we go into our deals table now and we look at the data it's right there. So, that's our first record that we created as well as we go into our touch points table exact same thing our touch points are there. So, nice work there.  


Now the last thing that we're going to want to do is essentially when we're using this API client here the API explorer, 8base is handling the authentication for us. However, when we're going to plug it into Retool, we're going to use this our API from a Retool resource. We're going to need a way of authenticating those requests going to the API. And what we're going to do in this tutorial, is simply use an API token so if I go to my settings and API token, essentially, I can click the plus button and I'm going to say this is the re tool API token. And I'm going to give it the administrator role.  


Now you can create custom roles and permissions that will only allow certain tables or actions to be taken. There's a lot of great tutorials that we have for that at 8base. However, just giving you the administrator token will allow it access to all our tables and so we'll use that for now just for convenience. Create that token and I'm going to hide this value before I publish this tutorial. And so, I'm going to copy it. And make sure that when you copy this you store it somewhere safe because you can only see it one time. So, if you don't do that, you're going to have to regenerate it. So, one second, I'm just adding it to a little to a text field or text file saving that there cool and now I'm going to close it great.  


So, at this point it's actually already time to jump over to Retool and start setting up our CRM. Our entire back-end is actually done. And so, I would say at this point if you wanted to like customize the back end in some really cool way, you actually could go in and start doing that. I'd wait till after the tutorial though because the way we're going to wire up to Retool, the naming conventions are important. So, it would just you'll run into less issues if you just follow along with the tutorial and then fix it or change it to what you're trying to accomplish afterwards.  


So, let's go over to Retool and the first thing that we're going to want to create is a new resource so we can see I have one here that I created while working on this, but we're going to create a new one. So, if I clicked on create new and then I'm going to scroll down to this API section and click on Graphql because we're going to be interacting with a Graphql API. And so, let's call this the 8base CRM backend resource. And what I'm going to first do is go into 8base, go into settings and then get this endpoint URL right here; this is the endpoint for our API. Go back here and drop that as the base URL. And so, the authentication method that we're going to be using for API calls or authorization method excuse me is using a bearer token. So, we're going to say that the key is authorization and then bearer space. And now I'm going to recopy from my text file that API key just drop it in there right after that. And so that's it we can test the connection to make sure that it's working. Connection success; so, it was able to um get a get a valid response back. Create the resource. And now we have that resource that we can now use in the apps that we build with Retool.  


So, I'm going to go back to my app section now and click on “create new” and “create from template”. Now in here if I type in CRM, they have this flexible CRM template that we're going to be able to use which is super cool. So, just click on that. I'm going to say pick this template. Let's give it a name which is “my new custom CRM”, cool, and then create the app. So, it's going to go ahead and jump into the Retool app editor interface. And once that is loaded, you can see that we kind of have this this view right here and a lot of error messages. But now what we're going to do is go and kind of retrofit it to make sure it's working with the Graphql API. The reason being is because this template was developed to work with a MySQL database. And so, we're going to have to go in and say, “Hey, you know this is how we're going to redesign essentially the queries that are getting run to access the data it needs.”  


And so, the best place to start for us is going to be the leads query which is right here. So, as you can see you know it has the SQL statement that would run against MySQL database. However, what we're going to want to do is um is update that to be a Graphql call. And so, if we go down here to or actually not down there- oh if we look here, we can see that there's some inputs that it's using and um and we're going to also update those as well.  


So, first off instead of using the onboarding dB which we don't have, we're going to go and select the 8base CRM back-end resource that we just created. So, add that there and then it's going to ask us to input the query itself.  


Now I go into the tutorial and I scroll down, I have the leads query written right here and I'm going to walk you through it. So, essentially, we're doing a query that has two arguments it takes a stage which is a string and a name which is also a string and then it's going to query for a list of deals that have both that stage and the name contains that string. So, this will allow us to kind of filter or search for certain results. And then in response I'm asking for a list of all those items and I want the id, the stage, the email amount, deal name and the create ad and updated at times on that record right, So, I'm going to copy this query and pop right back over to Retool drop it in there.  


Now Retool's really cool and it's super responsive, so it actually saw on my query that I'm looking for a stage and a name. And if I scroll down now you can see that it pre-populated those two variables. So, within Retool there's a global namespace or that we can access right so all our components are making available these objects with different data on them. So, for example if we go to button group one which is if we look up here at the top of our table, we can see that, “Hey, you know there's these different buttons and they have the different values on them.” So, we can see that like you know currently in button group one the values lead and we're going to use that as the argument to our query. So, I'm just going to close that name space there and I'm going to go in and say for the stage, double brackets. that then allows us to access that. And so, for a button group one, I want the value which is lead, cool. And then for name essentially what we're going to want is the name input which is this guy right here; so essentially what name are we searching for on a deal. And so, I'm going to say name input I want the value of the name input, awesome. And then I'm going to scroll down make sure that there's nothing else that we need, update, cool. Nothing there and so I'm going to save this query it's going to run it and if I go back here- let's see where would that- if I run the query right here, you can see it ran successfully, I can drill down into the data and I can see that one item came back which is our big opportunity at Big CO right.  


So, that was the deal that we created in our base back-end so we're already getting data into Retool. However, we want it to now populate the table component that is where it should go. So, if we click on the table here, we can see that it's still looking for the data from the old query which is leads.data. Now that's actually the nesting of the object that we're now returning from our API, so we actually have to drill deeper into it. So, leads data, then there's a property called deals list and then on that there's an items array and once we get that items array that's each record that we want on our table. And so by simply updating that we're already now seeing the deals record inside our table right here right; so that's our first opportunity.  


So, pretty straightforward and I'm super powerful.  


So, next up what we're going to want to move to is updating our touchpoints query, so exact same thing you know SQL query let's change it. So, 8base CRM backend and now what I’m going to do is I'm going to go into the query and once again I have it written out over here and so our touchpoints query is a very similar one. So, I'm going to copy that, paste it in and essentially what's happening here is we're saying, “Hey, give us a give us a list of all our touch points but filter them by the deal ID that we're passing in.” So, only show me touch points belonging to a specific deal.  


Now how are we going to pass in the appropriate deal ID for this query? So, if we go down to our variables, we can see it's looking for a deal ID. And what's really cool is the table component makes available an attribute called selected row; which is then you know essentially any row that's highlighted or by default the first row in the table. So, if you were to click on a different row it would update that property and that's going to allow us to actually drill in and find the deal id for the touch points that we want to see. So, if we do double brackets cool and we do the leads. Let me spell that correctly- leads table, cool- selected row data. So, it's already guessing where we're going to go here and then if I click that I click on ID. Now we have the current the current table value that we want, awesome. And so, if I look down nothing else that we want to change here, cool. So, I'm going to save that query, I'm going to run that query and we can see once again if we drill into that we have two items and each one is the created at medium you know phone call whatever it should be there. And now when we look at where this goes, we have this kind of touch points view. So, let's click into that and see what it's looking for.  


So, essentially you know this is the old way it was formatting it, but what we're going to want to do is we're going to want to make just a simple array of strings that represent each contactor or let's say that represent each touch point that's happened to date, right. And so, I have this little JavaScript function here that will work for this, right. Now if I take it and I drop it into our touchpoint's items input here, so it's saying that we're “getting some unexpected token”. Let's just see what that might be. So, we have touch points data list items we're going to map over each one of those and for each touch point we look at what it was created at, change that to a date string and then we're going to look at the contact medium. So, touch point p-o-i-n-t and let's see what's available on it if anything. Touchpoint.ID- see if that allows us to do something. Interesting. Okay. So, if we go here you can see that we have ID let's just run this query again make sure that we have the updated data and we're going to go back in and say when it was the contact medium… okay… ah it's missing a parenthesis. There we go, cool.  


So, now we update that and once again super responsive now we're showing the list of touch points that we we should be.


Now I think you guys get the flow of what we're going through here. So, we're just going to kind of move really quickly through the next four queries or the next four mutations, so that we can move on to just updating the rest of the components. So, for example for create lead this is actually going to be one where we add a new record or add a new lead to our database. So, we're going to go in say it's the CRM backend resource. From the tutorial we can see that we have this create lead mutation. Now when we create a lead, we're going to want its phone number, the department, deal name, email, amount and all that stuff. And so, what we're going to do is take this query this mutation, drop it in there and now we're going to go ahead and map all those inputs from our component which is a add new or create new lead modal, so these fields right here. So, we can see that the component is new deal name new deal phone new deal email. So, we're going to have to access all those. Let's go down to our variables and we're going to say here, new-deal, there we go, cool- new- deal- phone, got that. And so, then this is going to be a new- deal- department. New- deal- department, have that one. This one is going to be new- deal- name. Well got that one. Then we're going to go for the new- deal- email. Cool. And lastly one second- what did I do there? Okay close that back- new- deal- email. Got it. And then finally the new- deal- amount- value, right there. Awesome.  


All right, so we mapped all those up and then what we're going to do is we then we see that, “Hey, whenever you create a new lead rerun the leads trigger.” So, then we query it back and add it to the appropriate the appropriate response as well as close the modal that we had just opened to create it. So, I'm going to save that one, cool. And once it's saved. Awesome. So, let's do an upgrade deal so the upgrade deal the idea of this is that, “Hey, if someone's on offer a lead and we're going to upgrade them to an opportunity and then their opportunity we're going to upgrade them to the customer as well as downgrade them back down kind of like the sales funnel, right.” So, let's make sure that we're using the right resource.” Let's go ahead over to the upgrade deal mutation. Grab this mutation and drop it into our query input. And then it's going to show that we need the current stage which we know how to get from the leads table. Selected row data. Stage attribute- stage key, cool. And then same thing for the leads table, we want the ID because we have to run this mutation on the right record. So, we're going to go for the select or leads table- there we go. Data.ID. Cool. Got that one done as well.  


And then down here what we're just going to do is we're going to turn off or take off this um opportunities on success trigger thing that was with the old template. We're not going to need that anymore. So, just leave that there and run save and cool got that one saved. The downgrade deal is- oh you know what we messed up a little bit here so the stage that we're going to want to give it is actually the stage we want it to move to right because that's the update that we're going to be running. So, if we go back into the tutorial, we're going to see here that hey we have a leads table with the selected row. If the stage equals lead change to an opportunity. However, if it's not that it is an opportunity so let's change it to customer so that little ternary operation there, we'll take that and drop that in there. That's we're going to want it exactly, so we're seeing that this one is a lead. So, it would move to opportunity if we were to run the query on the current selected table. Awesome!  


Now very similar when it comes to downgrading the opportunity. Essentially what we're going to be doing is using a similar if not exact same query right here or mutation and drop that in to here. 8base CRM backend, drop that in. And then for the ID we're going to want the leads table selected rowdata.ID. And then we're going to do a little bit backwards to where if it's opportunity make it a lead or if it's not that it's a customer, so then make it an opportunity. So, let's pull that there and drop that right in to this input awesome. Now once again we're going to go down and remove opportunities, so we don't need that to run anymore and save our query.  


Now lastly, we're going to have the set details one which is going to allow us to update the value for our CRM you know so that's this little form here. So, it will be the name, the email and the amount. And so when we go into the 8base CRM back end let's run down to set details query take this mutation and drop it in. Cool. So, it's just going to be taking all those inputs that are optional and then running the mutation with the ones past passed in. And so, when we go ahead and update these values, we can just hover the component to see what the name of the component is. We can see this is the name is text input eight. So, let's go in here and say, double brackets text, input number eight value cool and it's nice that it shows the little confirmation what the value is. And so then for the email, we're going to go in and say this is number seven, text input number seven value. Cool. Finally, for the amount text input number six. So, text input number six value awesome and then we're just going to remove that empty field that we accidentally or I accidentally added. And then for the ID once again this is the leads table selectedrowdata.ID. All right save that query or that mutation and we're going to also remove opportunities from it as well I'm going to resave it because we don't want that to happen.


So, cool, those are actually all of our queries and mutations that we have to have for our CRM to work. Now what we really want to do and I'm even just going to close this bottom view here, is we want to make sure that we're wiring all these up to the components that need to display them right. So, for example we can see that we know why is this information blank right here or why is this here. It's because we're not reading the right properties. So, for example if I were to touch on or tap on the text 13 input or component and I would go into here, I'd see that it's still looking for you know the dataID.length.  


Now what I can do is I can go to touch points data, go into my data list and look at the items and then see how long that array is. So, length and suddenly boom that component is updated. For the image this is just generating a random URL. Yeah, it's just like getting a random picture, but it needs some type of seed value. So, just for fun what we can do is we can take the deal amount and do it that way so if we go to our leads table selectedrowdata.amount, that the deal is worth it's going to then do a modulo operation on it with 500 and then from this API that it's using get an image that will work.


Next up, what we'll want to do is this last contacted which if we go bring this down. Essentially what last contact is going to be is if I come down here, we're going to see that we're going to use moment.js which is a JavaScript date library. And then we're going to go through our touch points. Find the first one in the array. Look when it was created at. And then format it so it's the month-month/day-day/year-year format. So, let's just take this right here go back into Retool and for this value. highlight it all drop that in and we can see when they are last contacted now/  


And then for next step and next contact these are just kind of kind of funny. So, essentially what we're going to do here is say that. “Hey, if the last touch point was a phone call shoot them an email or if it was an email book an in-person meeting.” And for the next contact we're just going to say, “Hey, this was when they were last contacted reach out in a week right.” And so, to make those happen what we have is first next step which is just an if-then statement. So, we're essentially saying if it is an email make it a phone call, if it's a phone call book an in-person meeting or send an email. So, copy this, go back over, click on the text 15 component and drop into the value right here so we can see that hey based on the last touch point send an email on the next one. And then for this one what we're going to do is we're just going to use the moment.js date library to add one week to when the last touch point was created and once again format it as we need to. So, go in next contact, basics, open that up, drop that in and that is there, right.  


So, now here's what's really cool right so let's go in or actually let's just run the app. So, close this. I think everything's saved. Yep everything's saved. Cool. So, let's run our app. And here's our custom CRM right so let's create a lead let's make sure it works. So, my new deal from today. And I'm going to go in and say that this is the number. I have an email at email.co. This is a 50,000 deal and it's with the product department of the company I'm dealing with. I'm going to create this lead and we got an invalid error; expected a float. So, let's see if I'm able to add some decimals there. It should be able to handle that. Ah, I think I know what's happening. So, it's expecting a float, but this input is passing a string. So, what we can do is if we go back to our editor and we go to our create lead query and then in here for the amount what we're going to do is we're going to say, “parse float” as the and pass in the value into there. And so, now if we know that's updated let's save it and let's go back to our application. And so, let's create our new lead. And so once again, we're going to go through new deal some phone number eem@mail.co. Deal is 50,000 dot-dot. And we're going to use the product department create the lead and our query ran successfully and there it is now in our CRM. And let's say that we updated the deal size, so it was much bigger. Let's add some numbers in there, submit edit. So, now that deal is way bigger and then now it's moving forward. So, let's convert; so, we're going to click on that. Let's now convert it. Well so now it is an opportunity. So, now when we click on opportunities it's bringing it back to us there and so if I go back to opportunities. And now as you can see if I click on it it's going to update the things that. We need we don't have any touch points yet so it's not adding any of those and so let's see if we copy email- just copies it right.  


However, I think that this is probably where we're going to wrap up here because during the course of this tutorial we essentially built an entire front end for this CRM, we set up a back-end really quickly and we really didn't write any code to do any of it which is super cool and powerful. And I hope you can see that using these basic tools that we covered how far you can actually take this into building custom business apps. So, I hope you found this tutorial helpful if you like it please let us know by liking it as well as subscribing to the channel and leaving comments in below. I'm going to be adding the GitHub link for the tutorial below as well as some documentation links to both Retool and 8base that will help answer some questions that you may have. However, thank you for following along in this tutorial and I look forward to seeing you in future videos, take care.

Share this post on social media!

Ready to try 8base?

We're excited about helping you achieve amazing results.