Building, Breaking, and Building A CRM with Retool

 I like no- or low-code solutions to things. I've often wanted to simply push a button or move some GUI around and have the code implement itself.

 I've thought about building something that's like a customer relationship management (CRM) system for keeping up with my network better than my little spreadsheet where I click links and then go like something.

The general idea in this CRM Development is:

•  To have a GUI to add people to a NRM (Network Relationship Management).  

•     Attach it to a database (MySQL is what I went with eventually using Amazon Relational Database service, but you can use PostGRES, and probably others).

•     Make sure components are connected to each other in the retool interface.
  

This video is a good start.

Watching the tutorial video, heard some SQL commands and went 'Oh no 😳" before going "Wait I know basic SQL", which is good, because you'll see.

 When you get set up, there's a plethora of resources you can use -- Including Google Sheets! And Slack ,Datadog, Jira, Amazon S3, and many, many more. I go with Google Sheets initially.

There is a very handy tutorial. It reminds me a little of Tableau.

Not sure who that gentleman is, but hello.

Even when attached to a spreadsheet, the SQL query pane is still in use down there, letting us SELECT users.

Part of the tutorial was unresponsive at this point, so I eschewed it and went to play around myself and build something from scratch, using an MySQL database I made on Amazon's Relational Database Service (RDS)

 

 

I wish I could drag the query bar down further for more space to build the app without having to zoom out; Better yet, I'd love to delete the box down there and bring it up when necessary (Update: You actually can via these little buttons at the top; You can minimize the bottom, right, and side drawers)

 

                                         And knowing is half the battle.

Way back circa 2007 or so, we had something called Desktop Widgets on Windows XP and Vista -- Microprogams that sat on the Desktop and updated automatically with information. Those have mostly migrated to the tiles function in the Start menu in Windows 10, but one lone widget still stands proudly; The Sticky Note.

All that to say, in 2023, possibly by using a home-made API, I'd love to have a network update ticker of recent posts connections make on LI (or, preferably, Twitter).

I added a calendar with an Event Handler that shows a popup notification for 4.5 seconds when something is added. In reality, it pops up the event when I click a number, which is more user error than tool error. So I changed it to make confetti appear.

I did find that one way to add to a form is an event handler called an Only run when.

Moving onto another sample app, I notice it doesn't show the connection between elements; If I click a name in the table, the corresponding info pops up in the 'About' pane on the right -- But the event handler doesn't seem to have a tie to it.

Eventually, I got down to work, and that involved replacing the dataset from Google Sheets to one from a MySQL server hosted on AWS. Surprisingly, that was the easy part.

I want to have a form take input and put it into a database; It's surprisingly complicated.

Well, it's not a SQL script, I can tell you that. 

The scripts that work look like {{DB_name.Table_name.column_name}}, and I'm not sure if that's an existing language in curly brackets. It reminds me a little of JavaScript, and I have not dealt with JS in a few years. This is the language you'll be using in GUI mode, which is "less prone to error".

 This video was great, and due to a lot of correspondence (Thank you, Lauren, Ben, Kabirdas, Victoria, and Jamie!), I found a bug that prevent MySQL from operating the same as PostGRES does in the resources settings immediately; Part of the record didn't update within the settings of the resource, and I had to do it manually.

 There is a form element I attached to my table that is attached to my DB; That was much easier than...whatever the script above is.

I spent the better part of a week talking to several people, trying to simply save a change to a table. I had already used the forms to insert new data into the table, but as my SQL isn't the best, most things threw an error, or only saved to local storage, not my MySQL table.

 I thought about reverse engineering the sample app, but when it never loaded, I could make a new app with the DB, though sometimes, it would not act as it did in the past when connecting a data source to a table without a few attempts.

While contacting support and the helpful community forum with several copies of the app, it still took about 3 weeks to figure out. I had to redo my MySQL database and table about 3 times.

Turns out it needs a permanent reference to make changes -- basically the id, which I didn't have until I remade the DB and reattached it. If I changed the name of a person, email, or business, it wouldn't count, because making anything changeable the 'primary' portion of the DB to Retool would cause problems.

That is more my fault and less of Retool's.

For someone with more web development (and SQL) experience, most of this probably isn't an issue. 

 I wouldn't write the tool off entirely, but I would encourage people who are not used to the syntax of what may be JavaScript to play around at first, ask the community for help, and make sure your databases are not weirdly formatted.

Now that I know more, I can do more, and that's the important thing.

If any of this helps you, give this post a share.