Hello everyone here on the freeCodeCamp channel
wanting to learn how to make your own CRM with Google Sheets or Postgres SQL, I say
oh as I will be showing you how to build a CRM with both. So if you are more comfortable
with Google Sheets, we will start off with that. Or if you want to use a database I will
be showing you how to make in Postgres SQL two. My name is Andrew kooba, and I'm a software
developer and course creator on YouTube. I am also your host for this action packed video
for all skill levels. It is my personal mission to show you that anyone can make a CRM or
customer relationship management system and make it customizable to them and their business.
A CRM is essentially a way to manage your customer data. For example, imagine having
1000s and 1000s of customers, it will become virtually impossible to remember which one
of your customers but what, when, or when the last time that you email them what CRMs
allow you to add this data to the system, filter through it to find customers based
on a variety of variables, and even automate your processes such as sending thank you emails
or payment reminders. The main purpose of CRM has traditionally been improving customer
service and driving sales. CRM software's actually have been around since the late 1990s,
but have really flourished in the recent years. Some examples of famous CRMs are Salesforce,
Oracle, NetSuite for large enterprises, and HubSpot, and fresh work for smaller businesses.
Now, there are many reasons why you might want to build your own CRM. For me the most
obvious is that CRMs can be expensive, and often you are paying for features that you
don't really care about that much. Using local solutions, such as the one we will be using
in this course, allows you to create your own personalized CRM experience totally for
free, or at least at a fraction of the traditional price. This CRM will allow you to sell your
data in a visual way, meaning we will be creating dynamic graphs and charts, as well as allows
you to add or edit data from the platform itself. It will also allow you to perform
refunds directly from the platform using the stripe API, as well as send out automated
emails based on templates of your choice. API stands for Application Programming Interface.
They allow for technologies to essentially talk with each other and are essential to
so many services that we rely on today. They are in fact behind most apps we use on a day
to day basis. And they can shape the information paths between one technology to another. API's
are everywhere. We are in fact going to be using the stripe API and a mailing API in
this course, but there are so many you can choose from to improve your CRM offering.
This course is going to adopt what is considered a low code approach to creating platforms.
This means there are no hard coding prerequisites for doing this course. Although unlike no
code, and understanding the fundamentals of HTML, CSS and JavaScript are advised if you
want to take your project to the next level in terms of styling and functionality. To
break it down for you. Here's what we are going to learn step by step. In part one,
we will build out our CRM from Google sheets in which we will fetch all of our data into
the platform, but then also learn how to add new data to the existing Google Sheet directly
from the platform we have built. We will also learn how to edit data to so that it is saved
back in the Google Sheet afterwards. Next, in part two, we will integrate stripe for
processing refunds thanks to the pre configured stripe API. After that, in part three, we
will learn how to send out automated email notifications with the bailing API. And finally,
in part four, we will upgrade and transition the backend away from Google Sheets and to
Postgres. SQL has scalability and to handle more complex features. Okay, so what are we
waiting for? Let's get to building out our CRM. Okay, so welcome to the Regional platform.
This is the platform that we will be using for this tutorial in order to create a dashboard
that looks like this. Okay, so in order to do this, I'm going to start off by creating
a new project on the main dashboard. Here are some previous projects that I have done,
we're going to go ahead and just click here to create a blank app. And let's go ahead
and give this a name. So I'm going to call this FCC CRM and just click Create up which
will take me to this blank dashboard. Okay, so here we go. Here's our blank dashboard
with a table that has been created for us just as a demonstration. I'm going to simply
go ahead and delete that table to truly make this a blank slate. Now, we are going to be
working with some data now in any sort of situation. So you're a small business owner,
and you have a lot of data that you then want to use to create an app. That's the angle
that we're going to go with. Okay, so we are going to take this sample data from Northwind.
Right here. This sample data will provide us with a table of categories, regions for
our customers, territories, customer demographics, customer demographics, again, a table of employees
to show which employee deals with which customer and order, employee by territory, supplier,
product, shipper, the sales order itself, the order details, and so on. And now we're
going to insert all that data. So this is the data that we are going to work with for
the first part of this tutorial, I have gone ahead and put all of this data in a Google
spreadsheet for you. As you will see here, here, all our customer, there is a lot of
data, we also have the sales orders, and so on and so on. If you would like to make your
own table, all you have to do is go to your Google Drive or sign up if you don't have
an account, create a spreadsheet here by clicking on Google Sheets, and start creating columns
and rows. You can even label the tabs down here as previously seen and create new tabs
with new tables. So that is just some background for you on Google Sheets and how to create
your own. But for now, and for this tutorial, if you want to follow along, please go ahead
and just use the table with the Northwind data I pre met. This data mimics a generic
company that receives orders. These orders are represented by rows here in this table,
which we have put in a tab called sales orders. Data in the sales order table can also link
to specific products that can be cross linked to here, employees that deal with the orders,
the customers that make the order, and so on and so on can also be linked, I would advise
taking a moment here to really understand what is going on in this database. So I will
be sharing this link with you in the description below. Please go ahead and have a look at
that. Once again, here are all our orders, there are up to 831 orders that we are going
to be working with. So great a lot of data let's carry on. So the first thing I'm going
to do is create a new resource. So let's go ahead and go to our resource page. Here, you
can see all the resources available to us. I'm going to go ahead and select the Google
Sheet one on this occasion, as we want to be able to connect the Google sheet we are
looking at to the platform. So these are the steps you will need to do in order to make
that happen. Great. So now let's go back to our app. And don't forget to refresh the page.
Now I'm going to make a new query. And as the resource, I'm going to choose the new
resource we just met, which is called Northwind CRM data. So there we go, we have officially
linked up our Google Sheet to this platform. So now let's go back to the app we are building,
we are just going to use a component, the table component to be precise from the UI
panel on the right, in order to map all our data onto it. Okay, so that is what we are
going to do. If that helps, I like to think of this platform as having a bottom panel.
That is to make queries a panel on the right, that is for all our UI components. And a panel
on the left, which is foreseeing the shape of our data that comes back to us after we
make a query. I will be showing you this later on. Okay, so now we can actually pick a spreadsheet
ID. And I'm just going to select Northwind data as that is what we call a Google sheet
here. And for the sheet name, well, the first thing that I want to get is the order sales
orders. So let's go ahead and select that here, sales orders, just typing that in making
sure that it's spelt exactly the same way that we did, right here, sales order, sales
order. Great. And just click Save and Run. So now we should see all our data coming up
down here. Wonderful. I'm just going to rename this to get sales or der data so that it's
more readable for us later on. Wonderful. So we are now getting all our sales order
data. And to create a table with it. Well, all I'm going to do is just drag a table in
here. And there we go. We will see our table populated or the sales order data. This is
because we are on this query. Our query here is selected so it knows to use this query
in order to populate our table. While we're here, I'm just going to get rid of this query
as we are not using it at the moment. Wonderful. So So that is our table, it is looking good.
Let's carry on. Before we move on, I'm actually just going to edit this table a little bit.
So that looks a little bit neater for us. So let's do that now. So I can simply click
on the columns here and rename the column if I want. So for example, maybe let's call
this order ID like so. And that will update in here, let's also get the customer ID. So
I'm just gonna go Cust, ID, just like so, and so on. So while we are here, I'm also
going to show you that you can format each of the columns. So for example, the required
date, what if I click on this I can even choose Date Time, local timezone, or original timezone,
it is completely up to you, whichever one you'd wish. Let's go with original timezone,
like so. And that will format the date to be a little bit more readable for us, as opposed
to this format right here. Let's do the same on the ship's date. So once again, let's go
ship dates, and just select Column Type, original timezone, and you will see that changing.
And finally on the order date. I'm just going to click here, Column Type, date, time, original
time, so wonderful. So this is already looking a bit better. While I am here, let's have
a look at what else we can do. We can of course, also hide columns. So for example, perhaps
I don't want the ship region, all I would do is simply go here and click on the little
i to hide it. And I'm also going to hide the shipper ID and the fruit number. Okay, so
that is looking better with the information that I want. One thing to note here is they
actually added this charge ID, this charge ID is from stripe, I have added it in the
sales order right here. So this column has been added by me. And I have done this so
that we can demonstrate how to issue straight refunds on particular orders. So once again,
this was not with the Northwind data here, this is a column that I have populated myself
and created myself. But don't worry, if you're using the link in the description below. You
should see this too. Okay, so while we're here, I'm just going to rename all of these
as well. So let's maybe change this to amp ID for employee ID. Let's also do order, date.
Shipped, dates. Quiet, dates, ship name, I'm actually going to hide I don't really think
we need this ship address, I'm just going to put ship address like so. Ship city, I'm
just going to give us city, postcode and their book postal code. And it's just leave this
US country and the charge ID. Wonderful. So this is already looking much better for me.
Great. So we have our table. This is looking good. Let's go ahead and add a few more tables
while we're here. So I can go ahead and perhaps let's add the table of customers as I think
that is some good data that I would want as a start up. So once again, I would just go
into here, click new resource query, making sure that I am on the Northwind CRM data.
As that is the Google sheet that we have linked here. I'm going to read data from a spreadsheet.
I am also going to select which ID so Northwind data is what we call the spreadsheet. And
then the sheet name. We know that is customer so I'm simply just going to type customer
here and click Save and Run. And while I'm here, I'm going to rename this query so that
we can keep track of them better get customer data, just like so. Great. So now we can put
another table in however, I'm going to show you a really cool way to organize your data,
we can use the tabs component. So if I just go ahead and whack in the tabs here, I can
now put one table and have one and another table on tab two. So I'm going to put all
my sales ORA is going to drag that table, drag this table up in here. And there we go.
So these are now our sales orders, we can even put some text here in order to make this
more obvious to everyone. So it's going to grab some texts. And I'm simply going to put
sales orders. Like so we can also make this bold, if you wish. We are using markdown to
make this bold. Markdown is a lightweight markup language for creating formatted text.
Using a plain text editor is the same thing you use when writing GitHub with Miss, you
can find all the syntax you need here, if you wish. So there we go. Now that's more
clear that up sales orders. And then I can also do so on tab two. So on tab two, this
is going to be customers. So I'm just going to put customers making sure that it's spelled
correctly, like so. And making sure I'm on the query, I'm just going to grab a table
and just put it here, in order to populate this with the data that we see from my query
displaying right here, after we have run the query. So there we go. Great. Once again,
if you wish, you can choose to hide certain things, perhaps let's go ahead and hide a
few now because this is quite low information. So perhaps I don't want the facts number,
I can go ahead and hide that. I can also hide the region, if I wish, it is totally up to
me. So perhaps I will hide the region, I think this is enough data for me. And of course,
we can just pull that out, like, so. Great. So we've got our customer data. And then we
also have our sales orders. We can also of course, rename the tab so I can do so right
here, sales orders. And then we have customers as well. So there we go. I'm going to actually
do one more table before we continue adding other things. Another table that I would like
to add is the table of orders themselves. Because at the moment, we can see here the
order number and it gives us the information about the customer and the shipping date and
the status. However, I just want to know what is exactly associated with this order. So
for this, I'm actually going to use the order detail. So I'm going to get this table in
here. So once again, new resource query, making sure that I am on Northwind CRM data, I'm
going to read data. And I'm going to choose the sheet Northwind data and the sheet name
is order detail. So I'm just going to go order detail, like so making sure that it's for
correct and save and run. And there we go. So we have that here. Let's also rename this
to get order details. And I'm just gonna drag that once again up here, so that we can see
the order details, just like that. Okay, wonderful. So this is looking good. Once again, perhaps
let's put some text here, just to let everyone know that these are order details. And I'm
going to get rid of that emoji. We can actually do some editing on here too. So for example,
here is my tab, we can style the tab with all these pre configured things, or we can
you know, go fully custom. At the moment, I'm simply just going to choose to change
the header like sir. So I'm going to choose to make this white. And that's already looking
a little bit more stylish for me, please feel free to go wild. That part is totally up to
you. So there we go, we have now completed the part where we put in all our tables. Now
I'm going to show you how to create pie charts and visually show this data thanks to these
components right here. So I'm just going to go and use a component such as chart in order
to do this, or we can make a progress circle. That part is completely up to us. For now,
though, I'm just going to create a new tabs item here. So tabs component, I'm just going
to drag that in so that we can display different types of data on different tabs. So I'm just
going to make this a little bit bigger. Like so. And great. And once again, I'm just going
to change the header background to be white. So I can simply just drag this up here, or
I can put in FFF, fff to symbolize weight by myself. So tab one, I think the top one
is going to be an overview. So let's make this an overview. And it's going to just be
an overview of the amount of orders that we have shipped. So I'm going to use the table
right here, I'm going to use the ship date in order to show us how many orders have been
shipped. And I'm also going to use the table data here in order to show us how many items
have been sold at full price. Okay, because you can see discounts on implied to some,
I will know exactly how many orders we have sold at full price. So to do this, I'm simply
going to get the progress circle and drag it in here, just like so. So here we have
a progress circle. And now let's link it up. At the moment, the value is hard coded. However,
we don't have to hard code it, we can use the table data in order to make this dynamic.
So as we said, we want to show how many orders have been fulfilled, or another word shipped.
So we are going to get our sales order data which is an array, and we are going to use
the JavaScript method of filter to filter out each order that has a ship date have not
an empty string, as if it is not an empty string, it means a ship date exists. And we
can say this order is fulfilled. Next, we are going to find out how many of those exists.
And we will do so with the length property on the array, we will then get that value
and divide it by the total amount of orders in the get sales order data array and multiply
by 100. As that is how percentages work. Okay, so fulfilled orders over a total of orders,
multiplied by 100 will give us the percentage of fulfilled orders. So to do this, this is
the syntax for doing so in here, I need to get these two curly braces, well, we know
that this table is made up of get two sales order data. So let's get says order data get
sales order data. And we need to get its data. So we can use this in order to see how the
data is formed. So he is get sales order data and you will see that comes back as an object.
That object has data in it, which is why we are getting data. And data is an array. It's
an array of different objects. So let's filter our array, I'm going to use the JavaScript
method of filter to filter out each item in the array, let's call it an order. So like
so order. And for each order in our array, I'm going to get the orders, shipped date.
And if it does not equal an empty string, then I want to get this arrays length, and
then just divide it over the get sales order. Data Data length, so the length of the entire
array there was a spelling mistake here, let's go
ahead and correct get to sales order data, we go get sales order data. Now let's multiply
by this 100 Because that is how you would get a percentage so that we go a percentage.
So just talking this through again, what we are doing is getting the sales order data
that we can see here. So as you can see here, this is the object that returns back, we're
then going into data, which is an array essentially of all this information. And for each item
in this array, which we have called order, we're going to filter out by the Shipped Day
date. So this right here, and if ship date is null, so it doesn't contain anything, well
then we know that it's not been shipped. And then we're going to divide that by the length
of this entire array. So by the amount of items in the sales orders are multiplied by
100 Because that is how you'd get the percentage. So as you will see here, 97% of our orders
have been shipped. So in other words, 97% of our orders have a shipped date. Wonderful.
So hopefully that was easy. Let's carry on. So now once again, I'm just gonna drag over
some text austraila over here, and let's do Shift orders. Just so people can know what
we are talking about ship or, Doris. Again, just get rid of the emoji, we don't need that.
And then we can also have some texts tell us exactly the percentage. So once again,
I'm just going to drag that up here. And we can do something like this, I'm just going
to get rid of that currently. And then we can put the percentage in there if we want.
Currently 97% of all orders have been shipped, just so it's super clear to whoever's reading.
And once again, this should be easy, we can actually grab the value of this if we want.
So this is progress circle one. So I can, instead of doing the calculation again, just
get progress, circle one, and I can get its value. So there we go. And we can use some
JavaScript on this, to make it to two decimal places, if we wish to fix and then just pass
through a two, just like, so. Great. So this is looking good. Let's just add a percentage
here. and wonderful. So now that we've done that, how do you think we would make a percentage
for all the orders that have been sold at full price? Well, once again, I would simply
grab a progress circle. So this is progress circle number two, this time, just going to
put that in here. And this time, let's get the get order details. So get order details
data, that's correct and don't want the value six to be hard coded, I want to get the order
details data, get order details, data, which is the array, and this time, I'm actually
going to filter by the discount. So very similar to what we did
last time, you can use the filter JavaScript method to filter out each order. So I'm just
going to put order, I'm going to call every item in the array an order. And for each order,
I'm going to get the order discount this count, because that is what it's called here, the
order, it's going to an item discount. And if the order discount equals the string of
zero, because that is what we have here we have strings of zero, not numbers, then I
want to get the length of the array. So I want to get exactly how many have the string
of zero and the discount, and divide this by the array itself. So get all the details
data dot length, and of course, multiply it by 100 to get a percentage. So there we go,
making sure that this is in two curly braces, because that is the syntax for doing so. Okay,
great. So we can see 61% of my orders have been sold at full price. So I'm just going
to copy that. And then once again, let's just get the progress circle to value this time.
Currently 61.11% of all orders have been sold at full price. And then let's just make sure
we have no spelling mistakes here either. And great. And once again, it's perhaps just
give this a title. This time saying full price orders, full price orders. Just like so. Just
making sure that they are all aligned correctly to make them look good next to each other.
So there we go. Okay, great. So we have now learned how to use the progress circles. The
next thing I'm going to show you is how to show a statistic. So let's go ahead and perhaps
this time show something like the total of all the orders. So for this I'm just gonna
Get the unit price and add it all up. Okay, so that's what I want to do and multiply it
by the quantity sold. So unit price by quantity. And for this, I'm actually going to use a
statistics component. So we already have this here, it's already sort of nicely styled for
us to look like a statistic. So there we go. Let's also pop some of these down. Now, I'm
just going to move these down. Of course, the styling is completely up to you, please
feel free to go wide on the styling however you wish, I'm also going to put in a divider.
So just whack that in here. Again, this is just assigning preference, please feel free
to start this up, however you wish once you have got to grips with the retool tool. Okay.
So just putting these circles back like that, that's making them smaller. Again, you could
just fiddle around this forever. So please just bear with me. And great. So here, I want
to show the total of all the oldest salts, I'm just going to put total. And, like so
total. And then here, well, we don't want to hard code this, once again, let's get up
our curly braces, because we want to get, we can also do it by table. So this is table
three, let's get table three, let's get the data in it. And we just want to go by selected
row. So I can use selected row selected row. And then I can get the data. And what I can
do is get its quantity. And then I just have to multiply it by the table three selected
row. Data Unit Price. Okay, so now whatever row I click on, it will show me the total.
So that is looking pretty good. Perhaps us maybe make this clear what this is total of
select tid. Row, and now we can simply get the total just by clicking the row. So that
was quite handy for us as small business owners, I also just want to perhaps get rid of this
as it doesn't really mean anything. So let's get rid of the caption here. And I don't really
need this increase amount. So we don't want a positive trend. I'm just gonna delete that,
like so. Great. So there we go. Now we've just made a useful tool to essentially show
us the total of the selected row. The next thing I want to do is just show us the total
amount of customers that we have in this dashboard and the total amount of orders. Okay, so this
is really an overview, this tab has everything to do with an overview of the total dashboard.
So let's go ahead and do that. So this time, I'm simply just going to use text to do this.
And in here, I want to be able to show all the customers that we have. So I'm just going
to once again, perhaps I'm just going to hard code, customers here like so. And then in
here, well, I do want to display the amount of customers that we have. So to do this,
I'm going to use get customer data, and then just get the length of all of this data right
here because this data is the customer data. So if I just simply go by length of the amount
of essentially rows that we have, that should give me the customer amount that we have in
this dashboard. So there we go. Once again, if we look at this table, all I have done
is simply get the length of the array, and the array symbolizes the rows. Okay, so each
array item is essentially a row. So if I just count the rows, it will show me the amount
of customers that we have. Great. So that's our customer data. I can of course, spread
this out a little bit if I wish. And here we can have the order data. So this is going
to be the amount orders. And once again, to get the order amount, well I'm simply going
to get the sales order data, so get sales, order data, data, and then just get the length.
Great. So now we can see the amount of orders that we have. We can also see the amount of
customers. And if I add orders to this table, this should change dynamically. So this is
looking good. There's our overview. Let's carry on. Let's have a look at tab two for
tab two I want to show exactly what the work distribution is by employee. So if we have
a look at the sales orders, you will see that each order, we can actually have a look here
as well has an employer ID by it. So right here, employer ID, so we know what order is
assigned to which employee. But we want to see that visually. So for this, I am just
going to use another component. This time, I am want to create a pie chart. So to do
this, I'm going to go to charts and I'm just going to drag this over like so. And I'm also
going to select pie chart. And this time we are getting the sales order data. That is
correct. However, I just want to show the data by its employer ID. So undervalue labels,
let's select employer ID, just like so. And there we go. So now we can see all the work
done by employer ID. So essentially, what is happening here is that we can see that
employer number four, has the most work as the most orders assigned to them. So this
is looking good. Let's carry on. The next thing I want to do is actually show me the
employers name by this number, right, because we have the data here. If you look here, we
have the employees along with the employer ID. So once again, I'm going to go in here
and click a new resource query, making sure that Northwind CRM data is selected. But this
time making sure that Northwind data is selected to as the spreadsheet name, I want to get
the implore you making sure that it's spelled exactly the same as it spoke here. So I implore
you, and just click Save and Run. And once again, I'm just going to get a table making
sure that on this query, let's call this get employee data. And I'm just going to drag
in a table right here. And that will show me all the employee data, just like so. So
this is looking good, I can now get the employee data. I can also hide things of course, like
for example, let's say I just want their name, I can do so very easily like this by hiding
all the other tabs. Great. So now we have the employee ID, we have their last name,
their first name, as well as their title. Wonderful. And let's just rename this tab
to distribution. So work distribution district you shouldn't like so I'm just going to make
another tab. And this time, the tab that I want to create is a tab called team and a
title another one called Demo grow, to show us where most of our orders come from. Great.
Now, for team well, I'm actually just going to get the same table right here. And I'm
just going to show us all the data that we sort of had before. Because I think it's useful
to have you know all the employee data, not just the first name and the last name, so
that we can filter through it. So this is going to hold all our team data, just like
so. And demographic. Well, as we said the demographic, I want us to show exactly where
most of our orders are coming from. So I can do so easily. Once again, I'm going to use
a pie chart for this. So let's go ahead and find a pie chart. And this time, I actually
want to get the customer data, let's get the customer data, let's do it by where the customer
is based customer data. Just like so making sure this is a pie chart. And this time, I
just want to get the country because that's what I want to display. I want to show what
the breakdown is by country for all our customers to just like that. We can even position this
however we want. So perhaps this is a good way. and wonderful. You will see that most
of our customers come from the US just by simply dragging a pie chart like so. Okay,
great. So this is looking good. Let's move on. I'm just going to drag this a bit lower.
To make sure that's the same level. We will be doing all the styling a bit later on. This
is looking good for now. So great. We are displaying all the data that we have in the
Google spreadsheet we are picking out which sheets we want to use. And then we are also
getting an overview of the data that we have imported in here in various tabs that we have
have made, the next thing that I want to do is actually add data from a spreadsheet. So
I'm going to show you how to do this now. So as we mentioned, at the moment, we're just
reading data, I can also add data to this spreadsheet from this platform. To do this,
I'm going to show you how to do this. First off, let's actually create a modal. So I'm
going to grab the modal, like, so I'm just going to drag it in here under the sales orders
title. And I want this modal to pop up and essentially be able to create a new order
that I add in here. So let's go ahead and do that. A modal is a pop up window that we
are going to see, to add the data to our spreadsheet, as we just mentioned, we will do this by populating
the modal with form field inputs and a submit button. So first off, what do we want a normos
all but we're gonna have lots of inputs, essentially input all of this data. But first off, let's
just start perhaps with a title, I want my title to say create an order as that is what
is happening here. So create an order. Just like so, we can also change the background.
So if I perhaps one it red. So like a red like this, that is also possible to Okay,
so there we go. And there is an option to create a form to make our lives easier. So
let's go ahead and find that form first. So I'm just going to search for form. And drag
that in here. So now this is good as it comes with all the button and the styling. And it
just makes it a little bit nicer for us to look at. So here's our order form. It's dragging
this all the way up here. And let's just drag out the title as well. Great. And now let's
go ahead and get inputs for all of this data. So all of the data you see in the row we're
gonna have to collect. So the first thing that we're going to collect is the order ID.
So let's go ahead and do that here, perhaps the value we're going to leave empty, and
to value or we could have an order number as the placeholder, it is totally up to us.
The label, we're going to put order ID as that's what we want to put in here, just like
so. And we can hide the label, we can show the label, we could also make the label appear
at the top or bottom, it is completely up to you. So that's my input for the label,
let's carry on. The next input that I need is to get the employer ID. So let's go ahead
and do that. Because the order date we're going to have to select. So I'm just going
to make that up here. So here's a label. And then for this, let's call this employee ID.
Just like Sir, and once again, let's also make the same for the customer ID. So let's
put the customer ID. So customer ID employee ID, the next thing we do at some dates. So
let's go ahead and get a date picker up. So I can do so quite simply just move this down
a little bit perhaps. And this time, let's get the date picker. And just drag that in
here. So here we have the date picker. I'm going to call this ordered by ordered date
and then we can select a date copy paste that okay, so got the order date. The next thing
we need is the required by so required by date. And then finally, we need the address
fields. So perhaps has put a divider in there. So I'm just going to drag a divider in just
so you can separate the two visually and now let's get an input. So a text input
just drag it all the way over here. This is gonna be for the address let's put that below
the divider since contract the divider up here once again gonna happen for the city,
the country and the region. So here we have four inputs or text inputs. If wherever reason
you need a number input that is one specifically for numbers, so just bear that in mind. So
these are all text inputs. So we've got the address the next one, I want to be city. This
one I want to be country. So let's just change the level of that. And this one I want to
be region. And we have one more thing. So we're not actually done in this address section,
because we also need the postcode, so let's also have the post code. Okay, so that is
it for the region, or address parts. Once again, we'll split this out because we do
need some more things. Even though we can't see the ship ID and the shipper ID and the
fright ID. And this shipped on date, because we hit some of them, we are going to have
to put this into a form as this data is required. I'm simply just going by the fields here.
Okay, so I'm just going through all of them and just making sure we are collecting data
for all of those to be populated. So once again, let's just get the divider out. Let's
drag that across here. And then let's go ahead and just put in some more inputs. So once
again, I'm just gonna drag that below here. This time, let's perhaps make the label at
the top because we're gonna have three here. So one, oops, two, three. And then just position
them a little bit. So label one, two, and three. And let's have this as ship name. Let's
have this as shipper ID. And let's have this as the fright field freight shipper Id not
shipped. And finally, we're just going to have a date picker to pick the date that this
was shipped on. So once again, it's got the date picker, and just driver in here. So just
like so and give us the label of shipped on. Great. So this is looking good. And of course
we have the submit button as well. So nice. I'm really happy with how this is looking
so far. Okay, great. So we're collecting all that data. But now we need to essentially
get all this data and get this data to make a query with it to add data to the sales order
table. So what I'm going to do is create a new query this time, this query, let's call
this ad sales order, date. Okay, so that's what our query is called. This will be to
Northwood CRM. And then we're going to append data to a spreadsheet, choose the spreadsheet?
Well, that's Northwind data on the sheet we want to add to his sales order. So let's just
go ahead and put sales order like so. And in here, what do we need to do what we need
to add data that looks like this. So let's look in here. Let's look at the data. Okay,
and we need to make an object that essentially looks exactly like this. I'm going to copy
that. And I'm going to make an array. So open up the square braces, and then we could just
make an object and paste all the data like so. But I think this is quite messy. So I'm
going to take you through the step by step. Okay, so open up the array, make an object,
I'm going to copy this object right here. So the first thing that we need to put in
is the, we could start with the charge ID. Let's go ahead and do that to just make sure
that everything's in order. So the first one is charge Id always making sure to spell everything
exactly the same as you see it. So charge ID and for now I'm just going to put the string
of test, put a comma. And the next thing is the Cust ID. So once again, let's put the
string of Cust ID and put the string of test. The next is an ID for employee ID. And once
again, I'm putting test and you get it. So fright is next test by a comma, it's gonna
copy this actually, because there's a lot of repetition fright order date then we have
the required date. Just paste. Next we have the ship, address, and paste. Next we have
the ship, set do paste ship country is next ship, country. ship name we have this shipped
date. Ship for it. Shit, still code and the ship region. Okay, so that is it, let's quickly
make sure that we didn't make any spelling mistakes, even though we will get an error.
So that's all the data that we have. Okay, and let's check it out. So I'm just going
to click save and run this. So I'm just going to get this up a little bit so we can see
what returns back to us. Okay, so that ran successfully, we saw the little pop up. Now
let's have a look here and go all the way to the bottom of sales orders as we did append
the row to this table. So it should appear at the very end of this table. And there we
go. I did press it twice. So two rows have been offended. And everything is working.
Great. Wonderful. So now that the test is working, let's replace these test strings
with actual values from our form field right here. So all I'm going to do is match the
input value to the key. Okay, so there we go charge ID, well, we know that the charge
ID at the moment, I'm going to hard code it because all the charge IDs are the same. This
is just a test charge ID that I'm getting from the stripe API that we will go into later.
So this one I can hard code. So let's go ahead and do that and put that as the string. The
next thing is the customer ID. So we know that that's going to be text input two's value.
So I'm just going to replace this let's get our curly braces up. And I'm going to grab
this element which we called test input to test in foot two, and I'm going to get its
value just like so the should say text input value, apologies text input value. Okay, great.
So this is looking good. We can of course, if we really want to change these. So if I
really wanted to rename them, I could. So just know that there is an option to do that.
I'm not going to do this for this tutorial as that will take a lot of time. But know
that if I wanted to change this, I could. Great. Now let's get the employer ID. So for
this, we're going to use text input three, get the two curly braces and go text input
three dot value. Great. Let's do the same for the freight. So here we go. That's text
input nine. Once again, let's just get rid of these quote marks. Let's get the test word
here got our curly braces and this time we need to get text input nine so text in porch
nine dot value making sure to spell text input correctly just like so. The order date while
the order date is Date Time to so let Let's go ahead and replace the test value here with
date, time to dot value, the required date, once again, that is date time one. So I'm
just going to get the test and use the curly braces to get date time one dot value. So
it really is this simple. That's all I'm doing the shipping address or the ship address.
In other words, that's going to be text input seven. So I'm just gonna get the curly braces
and get text input seven, we can use the drop down to make our lives a little bit easier.
This ship city is going to be six. Country is five, and region is eight. So let's try
remember that just going to copy this one. Ship city we said was six. ship country think
we said that was five but we'll double check. And the ship
region is eight. Do we have ship region here? Here we go ship region. So let's make that
eight. And just a few more left, we need the postcode which is four. So let's find the
postcode in here. There we go. And replace it with text input four. And then we just
have the ship name which is 10. Ship ID 11. And I think that shouldn't be it. So ship
name is text input 10. Ship ID is 11. And I think there's just two dates left. So let's
have a look. Again back here. One date left date one is this shipped on date. So let's
just go in here and this is date, one, date one dot value. So this is looking good. Let's
test it out. So let's gonna save that. And this time to test it out. I'm just going to
input some data. So let's go ahead and do this. Employee ID, I'm going to say to customer
ID three, order ID again, this is just for tests. Let's pick a date, I'm just going to
pick the date and the time for this one. The address again, I'm just going to put test
for now, city test, country test, region, test, postcode, these three, ship name a,
a, and then fry a and then shipped on let's just have a date for this one. Let's go with
the 10th of love. And then click Run. So now okay. We did get an error. Why is this so
we can actually read right here what has happened. And you will see the line has not been added
there was an error. And I think it is something to do with this. We have left a little apostrophe
there. So that's gone. And I think we also don't have the order ID. So that is one we
didn't pick out if I'm not mistaken. So I'm just going to put that in the front or after
the order date. So just in here, just to make sure it's the same as here for readability.
So order ID and the Order ID is going to be the text input one. So text input one dot
value. Once again, just make sure to format that correctly. So put a comma there. And
great. So now if we save this and run it and have a look in here. Well there you go. The
row has been updated with the correct values in each column. Great. So that is now working.
We have now figured out a way to add data From this platform to our Google Sheet, I
just need to do one last thing that is connect the button right here so that it sends when
we click this button. So I'm just going to click that right here. So I've just selected
the button if you can see that. So here we have it. And we need on submit, we could make
the button forward if we wish. On submit, so that's the submit button. We also want
some events. And I just want to trigger the query, add sales orders, I want to run this
query rather than us running it manually, I could do it by attaching it to this button.
Okay, so that's what I want to happen, just like so. And we could also add other things.
So for example, I can also add, instead of trigger query just to send confetti so that
we know that is done. And then as a third thing, so after we get the sales order data,
I want to display it in here sit on the table, which means I essentially need to make a request
and get all of this data again. So I need to run the query to get sales order data.
So I'm just going to do that in here. So in the query rather than hitting the button,
and I'm going to trigger the query, get sales order data, and save that. So now, when we
fill out this form, and click the submit button, I'm just going to zoom out a little bit, so
you can see the submit button. And when we click the submit button, so let's do another
one this time, this time, I'm going to put order ID www at the end. And I'm just going
to click submit. So we are making a query to add data to this Google Sheet, we are then
sending the confetti, and we are making a GET request to get all the data in here. Which
means that now if I look here, and just go to the end, so let's go to the very end, I'm
going to put in 76, that ah, that is the new line that we have just added. So I'm just
going to zoom back in again. So you can see a little bit better. So great, this is all
working fine. I'm really happy with how this is working. Of course on here, there are inbuilt
tools to download all the data, you can also filter. And you can also reload manually,
if you wish. Okay, so these are all options that you can do on the table as well. Correct.
While we're here, I'm just going to change the color of this as I think blue is a bit
passive, let's go ahead and make it red. So just like that. Wonderful. And I'm just going
to go back to the first page. So page one. Great. So we can now add data directly to
our database here using the platform. But there are other things we could do as well.
So I can actually also delete data from this table so that it shows up here as well. So
that is the next part, we've just added data. Next, I'm going to show you how to delete
data to Okay, so for this part, I'm actually going to have to write a new query to delete
data. However, first off, let's go ahead and add a button to this table. So I can do so
easily by selecting the table. And this time, I'm not actually going to add another column,
I'm going to add an action. So here are my actions. And I'm just going to call this the
Delete action. So here's my action I just created. And let's call this Delete. Okay,
so just like so. So there we go, that has now been appended to the left of my table.
And on here, I want to run a query. Well, that query is going to be the delete row query.
So let's go ahead and write it, I'm just going to create a new resource. And make sure that
it is using the Northwind CRM data. And next I want to not append data or not update, I
want to delete a single row from the spreadsheet. So that is looking good. Let's choose a sheet.
Well, that is the Northwind data sheet. And the sheet name that I want to work with is
the sales order sheet. So making sure that I'm on this sheet right here. And now I'm
just going to have to filter too much row and let's go by order ID. So I'm just going
to select the order ID table making sure to spell it exactly the same as we did in here.
So I'm going to filter by order ID and make sure that equals the table. So table. One,
however it's not here, and I'm just going to put selected row data order ID. So just
like that, so we're literally going to search for a match By order ID and we're going to
find that order ID and delete the entire row if it equals the Selected Order ID of this
row. Okay, so I'm just going to save this. And let's rename this to delete. Selected,
row. Great, and hit preview. So at the moment, I'm on this row, that's the selected room
on, and this is the row that would get deleted from our spreadsheet if I hit run. Okay, so
there we go. And, of course, this is great. But we don't want to run this manually, we
want to attach it to the actions. So I'm just going to get that and run a query, the query
I want to run is the Delete selected row. And there we go. So now let's check it out.
It's going to move that down here. And let's delete this row. So I'm just going to click
here. And great. So now if we look in here, that rows now deleted, and of course, we need
to refresh this data. So on success of this, I want to add one more thing, and I want to
trigger a query. And that query is to get to sales, order data again, and just click
sick. So let's go ahead and delete another row. So this time, let's delete this row.
And I'm just gonna click delete. So now that is deleted, you'll just see that disappear.
And tada, this table refreshes with the fresh data from this spreadsheet. Great. So we are
now adding data, we are deleting data. This is looking awesome. Now, we have one last
thing to do, and that is process a refund. So for this, I'm going to show you how to
do this using the stripe API. So to do this, I'm actually just going to create a container
so that we can store our refund in here, so that it looks a little bit nicer visually.
So I've just gone ahead and created that. And now, let's also create another form that
we're going to put in here. So I'm just going to put in a form into here. So let's again,
just find a form that we can use. Let's go ahead and put that in here like so, let's
perhaps also change the button color. So I don't really like this button. Let's change
the color. Let's make it some stripe colors, perhaps. So I'm just going to use the hex
code that I have. Here, just like so. And then I'm also going to make this button the
full width. And not only that, let's change what it says I'm going to say refund, and
then also notify as we want to send an email notifying the person that the refund has been
made. So great, let's go ahead and just put in some text so you know what's happening
in here. I'm just going to drag that in here. And this should say, create, we found just
like so don't need the exclamation point. Now let's put in some text. And then we'll
just make a little note so people know to select a row from the sales order orders to
refund an amount okay, because we want to pick out all the information from a row is
select just like we did with the Delete in order to make our refund. Okay, so there we
go. The next thing I want to populate, so I want to now populate fields by me just selecting
a row so we don't have to do much manually this is going to be the charge ID that we
want to refund. So I'm just going to use that we also want the refund amount. So for this
I'm actually going to use a number input as this has to be a number so I'm just going
to drag that in here like so and put the label as refund amount that we decide and then let's
also have a refund reason for this I'm going to actually have a select because I only want
there to be three or so reasons that you can make a refund on let's name this we find is
in and let's populate the reasons so some reasons could be you know, let's go ahead
with did not we these are the values actually said these the values we're gonna pick them
up by so didn't like I'm just gonna make them strings This one is going to be too late.
And this one is going to be can. So those are the values we are going to use in order
to pick out our reasons. But now let's have also a way to read them a little bit better.
So we have the values, let's also perhaps have some display values. So I can actually
just clear this. And let's just test this out. So test test. So now there we go. So
it's either using these or we can write our own and my own are going to be does not like
it, the second one is going to be arrived too late. And the third one is going to be
I would like to cancel. So just like so. So great. Now we have our select, let's carry
on. The next thing I'm going to do is just put in a text of the refund summary. So I'm
just going to drag in some text here. This is just going to be a refund. Summary. Okay,
so I'm just going to display a little summary of what we are refunding and why. So I'm just
going to dry that in here, some more text, oops, making sure that this actually is a
bit bigger. Okay, and in here, I'm just going to put the refund amount. So a little summary
of what we wrote above. And I'm going to use number input one dot value to display that.
Okay, so just like so that's my little summary that I'm building, we found a map helps us
put some of these in here. So we've got the refund amount. And then let's also have a
reason. So again, I'm just going to put a reason. And of course, it's blank for now,
because we haven't picked one, this is select one, so I'm just going to replace this with
select one. But if I now choose one, like doesn't like it, that will show up here, okay
as the value. So there we go. That is sort of a recap. And of course, if I just put in
a number here that will show up there too. And lastly, I just want to be able to send
a message, along with you know the refunds. So what we're what we're going to email to
the customer. So once again, I'm just going to perhaps make this a little bit bigger.
Just like so and just stretch this out a little bit. And this time, I'm just going to use
a text input. So text input, we could use the rich text editor, however, I'm just going
to use the text area as I don't want anything crazy. I just want a message like so that'll
be sent over and an email. But of course, if you'd like to use the rich text editor
to that as an option. Okay, great. Now for the message itself. Well, I want to use values
from here. And what I can do is make an email text. So I can go ahead and just write my
own Java script here. So I'm going to use a JavaScript transformer to do this. And I'm
just going to use this to write some javascript. So for example, if I get the order ID, so
what is the order ID? Well, we know that the order ID is whatever row we've selected from
the table. So I'm going to use selected row data order ID, and save that as the order
ID. The other thing that I want to save is the order amount. So let reef fund amount
equal and this time, I'm just going to use the number input one. So number input, one
value, just like so. And now we can construct our message which is simply going to say do
order and then the order number and then I went ahead and made the refund of X amount
your card. If there's anything else you can do, please let me know. Okay, so that's all
I'm going to write. So let's go ahead and do it. Now, let's also write different things
for different reasons that we chose for the refund. So in fact, if I go let opening blurb
equal And then I'm just going to use a switch case. So I'm just going to make that a little
bit bigger for you, I'm going to switch out the reason. So I'm gonna go select one dot
value and switch it out. So if the reason is too late, well, then the opening blurb,
Nurb. Making sure to spell exactly as it's written up, that is going to be I'm making
sure it's back six, actually, I'm so sorry about your order. In being late, I went ahead
and refunded the value to your cart. And of course, replacing this with the refund amount.
So I'm simply going to do refunded and then back tick plus refunded amount, plus and then
the string again. Okay? So that's for the case too late, make sure this is select one,
okay, because we are using the value from here, select one. So that is one case, essentially.
Okay, so there we go. And then let's put a break. The next case that we're going to cover
is the digit like, so making sure to spell it exactly the same as we did here. So didn't
like did like, just like that didn't like, and what happens with this case? Well, let's
again, change the opening blurbs or opening blurb to be backtick. I'm so sorry, you did
not like your order. So that's all I'm going to do for this one, making sure to put a full
stop at the end of this. And then finally, the last case we're going to do is cancel.
And what happens when we cancel? Well, the opening Blub this time it's going to be changed
to making sure that is a capital B. I fully cancelled your order as you requested to full
stop. Great. So that will change the opening blob. Now what do we want to return? Well,
let's just return order and then pass through the order ID. So essentially, this right here,
the order ID. So let's get some curly braces. And in here, I'm just going to put order ID
and then let's also add a nother string to this. This time I'm going to put in the Oh
opening learn as it is after the changes with this switch case, I went ahead and refunded
and then we'll put in the refund amount for you have also defined at the top refund amount
to your card. And then let's finish it with best wishes. really tall. But and save that.
And let's rename this as email, text just like so. Did email so there we go email text.
And in here Well, I'm just going to replace the default value with email, text dot value.
Don't forget to put in the breaks between each of the cases as well. So break and brake.
Also case should not have the double dots here. So just get rid of those. And there
we go. Let's try change the reason And great, that is updating as it should. Now I do want
this to appear on separate lines. So I can simply do that like so. So in the string,
just gonna do Ford slash n, Ford slash n, and preview that. Save it. Okay, so that no
spaces out. Let's do the same after here. So I'm just going to copy this for after string,
and do so after here, too. So there we go, that is looking a lot neater, I don't see
of course change the reason again, would like to cancel, that text should now update to.
And it's also updating with the refund amount, and the Order ID that we have selected. So
this is looking great, we've got the text showing up, this is looking good, I'm just
going to make this a little bit bigger still, so that we can read the whole text when it's
longer. So just like that, wonderful. So this is looking good. The next thing I need to
do is just connect this to the stripe API to process the refund amount on to the charge
ID. So if I just select this, well, the charge ID, we need to actually show that in here
too. So I'm just gonna get a nother text tag like so. And this is just gonna display the
selected row. So once again, I'm going to get the table one selected row data. And I'm
just going to get the charge ID. So that should now show up for us on whatever when we click
the charge ID is the same as we are just using stripes tests API, but just know that that
will change. Okay, if it was different. Okay, so to recap, we have just used a transformer
here, I have used a JavaScript transformer as because we have three different messages,
we want to send one for each drop down value, we to create a dynamic text field value that
will populate with the correct message. This is a perfect use case for a transformer. So
great, we are collecting all the data now to actually make a stripe refund. So for this,
I'm actually going to create a new resource query. And this time, well, I want to create
a new resource. And this time, I'm going to select stripe. So let's call this CRM stripe
resource, you can call it whatever you wish. And we need to put an API key. So to do this,
all you have to do is sign up to stripe.com. Okay, and here, we are going to all the documentation
that we need in order to create a refund. So sign up to stripe gave your API key. And
then we are essentially going to use this to create a refund. And we're also going to
use some test charges. Okay. So here is my stripe dashboard, I'm just going to sign in.
Just like so with my email address that used to sign up before. And on your dashboard,
you will see some test data that you can use in order to test your API. So here, let's
go to the dashboard. Here is the home page. And I can use the test data. Okay, in order
to process a refund, just to see it's working here is my key as well. So I'm just going
to copy that and put it in here. Please do go ahead and use your own as this will not
work, I will be deleting this, and just create resource, just like so. So there we go, I've
now connected my stripe API to my stripe dashboard using my secret key. Now let's go ahead and
create a refund. So what I want to do here is I essentially want to run a query when
manually triggered, and I want to post this to refunds, okay, so it's a post request.
There we go. And in here, well, the Stripe account, I'm gonna leave that blank and leave
this blank to the amount where we know that this is going to be the number input one.
So number input one dot value, because that's the amount I want to refund and the charge
we also know what the charge is. I can actually just take it from here. So text 17 value,
text 17 value, you could have also taken it from table data selected row charge ID, it's
up to you. I just think this is perhaps a little bit more foolproof as we're actually
seeing the charge ID here. So great, this is looking good. Let's save that. And then
let's rename this to post refund. So just like so, and just hit preview, and there we
go, you will see the response of my refund, you will see that refund has been done, we
have refunded the amount one, okay. And if you go to your dashboard, and just go to payments,
so this is my test data, you will see all your refunds done here at the moment, it was
just a preview. Okay, so that is how you would see it. And to get your charge ID, all you'd
have to do is create a payment. And then let's say we have a payment for 20 pounds. And then
we'd have to just input the card information. So if I google stripe, test cards, here we
go, this is exactly what I need. And I'm going to make a fake payment. So let's just go ahead
and use these test cards, I'm going to copy this. And I'm going to essentially make a
fake payment using this fake card data, it says to use any three digits, so let's just
go ahead and do that as well as put any future date. So let's put 30th or December oh three,
and then 123. This is saying it's in the past. So 1221 There we go. So there we go and submit
payment. Okay, so we've just created a fake payment on our dashboard. There we go. Okay,
so here is our payment. And if I want to get the charge ID as we need to get the charge
ID in order to make a refund. Here is the charge ID. So instead of just taking the charge
ID that I have hard coded here, please go ahead and essentially replace this with your
own charge IDs here. So just like so. And just literally just replaced that with all
the charge IDs I have here all the way to the bottom, so that you can put your own charge
ID in order to make refunds. So there we go, I've just replaced all the charge IDs with
my own charge ID from the stripe API Dashboard, click save, refresh this page, so we get the
new charge IDs showing up. Okay, so there we go, that is now updated with the charge
ID that we just put in here. And now if I make a refund, so instead of testing this
out, I'm actually going to run this. So at the moment I'm refunding the month zero, let's
go ahead and make the refund amount one and click Run. There we go, we have now refunded
one to the 20 pound charge that we have here. And we look at this. So let's go ahead and
look at all the payments again, you will see a partial refund of one was made on to this
payment. So we have just partially refunded this charge right here. So hopefully that
makes sense. Please watch this a few times. If it does not make sure to replace all this
data here with your own charge that you created on the stripe dashboard. Okay, great. So we
are processing refunds from this dashboard. Now, let's carry on. The next thing that I
want to do is actually notify us when we create the refund. So the refunds just been made
by also want to send an email. So I'm going to do that next. For this, we are going to
integrate with Google Mail. So hopefully you do have Google Mail. If you don't, you can
use the SendGrid API to however, this is just my personal preference, but it is up to you
whichever one you choose. So to do this, I'm actually going to create a new query again.
So just here, and this time, we're going to create a new resource and choose the ES TP
API. Okay, so that is the one I want to use. Let's go ahead and call this G Gmail like
so. The host, the host is going to be SMTP dot g gmail.com. Just like so as we are using
Gmail, the port is going to be 465 the username you just have to literally use your own email
address for this. So I'm going to put Ania at Free Code camp.org And the password is
actually the password for your email address. So I'm going to go ahead and do that. put
in my actual password in here, and just test the connection. Okay, so my connection was
a success. So I'm going to create this resource. However, if you are having issues, you might
need to do some extra configuration on your Gmail. So we might have to go into actual
Gmail accounts, I'm going to show you how to do this, this is something that I had to
do in order to get this working and in your settings with fording and pop, I am a P, just
make sure that you are enabled, I am a P, and make sure you've enabled pop as well.
And one last thing you need to do is enable all apps like so. Okay, so you're sort of
lowering, I guess, your security settings and allowing third parties to use your email
on your behalf. Okay, great. So that is something that you need to do in order to get this done.
Hopefully, that works after you've enabled those three things. And now we can get to
hooking this up so that it sends an email when we press the button. So let's continue
with this our query, well, let's use the Gmail query just made the from email, we're going
to do Ania at free, code camp.org. The to email, I'm just going to hard code this for
now to Kubo ania@gmail.com. And then the subject is going to be your refund has gone through.
And then I'm just going to get the whole text area one. So text area, one dot value. Okay,
and let's save this. And now let's run it. And if I look in my email, you will see I
got an email saying My Refund has gone through, okay, from Ania at Freako camp.com. So this
is looking good, I'm really happy with how this is looking so far. Let's carry on. Of
course, we can also have an input for the email that is completely up to you. In fact,
I would recommend it. So just go ahead and just drag an input tag. So for example, once
again, I would do so like this, let's just go ahead and put you know, email address that
we want to send this to just drag it out like so. And let's put the label as email address.
And this just means that I'm going to get text input 12 This time, text input 12 dot
value. Okay, so whatever email we put in here, that's the email that it will be sent to.
Great. So we're sending an email, we're making a refund, let's just hook up this button because
we want this to trigger the query for yes, indeed, Gmail, but also posting a refund fast.
And let's just make sure that this is renamed to send email just for readability. So we
are triggering that. But we're also going to trigger the Send Email query to so two
things are being triggered. So this is looking great. I'm really happy with this, I'm going
to do one last thing before moving on to use the postgres approach. And that is just show
you how to use mapping in order to use the two databases. So for example, if I want to
use the employee ID to show me the employee name. Okay, so I'm going to get the three
I'm going to look in here and I'm going to get the name I'm going to show you how to
do this now. So let's go ahead and just create an overview of an order. So I'm going to drag
this in here like so. And let's call this of view of order number. And I'm just going
to put in the order number. So it's going to be the selected row this time. So I'm just
going to go into table one selected row data, order d. Making sure number is here, just
get rid of that. So we're going to show the order number. And once again, I'm just going
to drive through some explain a text explain to the user what is happening here. Select
row from sales or Does for over view. Okay, so that's sort of explaining what's happening.
And now once again, I'm actually going to get some text here. And this is going to have
the customer name. Let's also do it for the ship address. Let's also have the employee
name. And then one last thing I'm going to do is just use this little cool thing. So
if I just go down here, there's so many things you can use, of course, I'm going to use a
timeline. So I'm going to use a timeline to display essentially the order details and
how far it's gone, and so on. So I'm just going to drag that in here, like so. And great.
So these can be a little bit smaller. In fact, I'm going to shrink them down a little bit.
There we go. And now I'm going to use the text component again, and just put it in here.
Because what I want to do is essentially look in the table. And instead of having the customer
as an ID, I want to show the name by looking in here, finding the customer ID and returning
back the name. Okay, so I could use it easily, all I'm going to do is just delete all of
this. And this time, I am going to get table, the customer table. So this is table. Two.
So let's get table to, let's get the data from it. And I'm going to pause through the
table once selected. Row data, Cust ID. Okay, so I'm just passing that through, I need to
make sure that this is an integer, so I'm gonna wrap it and pass int to make sure is,
and I'm actually just going to minus one from it. So that's what I'm passing through and
passing through the index. And I'm getting back the contact name. Okay, because I'm going
in here I'm looking in here, I'm passing through the index, and I'm returning the contacts
name. And the index is whatever this number is minus one. Okay. So that is how you would
do that. That is how you would essentially join data from two different tables. Let's
do the same for the employee name this time. So just down here, it's essentially all the
same thing. However, this time, I'm going to look in Table three, I believe it's table
three, let's have a look, is this table. This is table five, I can use Table five, or I
can use table four, it's completely up to me, let's go ahead and use table four, why
not? So let's look in Table four. And this time, let's get back the first name because
contact name doesn't exist. So first name. And I'm going to also get the making sure
there's not a custom ID, but this time, we're going to pass through the employer ID. So
we're getting the first name. And I'm gonna copy all this because I also want to get the
second name. So there we go. First Name and Last Name. So this is looking good, we can
show that that's a space and wonderful. So that again, is how you would get data from
two different tables. And the last thing we'll do is just show the shipping address. This
is actually really easy because all I'm going to do is go look in table one, which is the
main table at selected row. Data ship address, okay, because I'm looking table one, which
is this table and just getting the ship address. So here's a nice little overview for us. And
of course, the orders. Well, this is again, easy because I am simply going to get rid
of these. I'm going to use the curly braces to go into table one. So the main table selected
row, data order date. So we've got the order date. The next thing we need to do is I'm
going to change this to be order created as well. Order created. And then let's have order
required by. And then the final one will be order shipped or shipped date, it's up to
you. So, shipped date just so people can see the status of an order. So this required why
so that should be required dates. And this should be shipped date. Great. So now whatever
order we select, this will all update. Okay, so as you will see here, that's been updated.
Let's click this one. As you will see here, this is customer ID 34. And this is employee
four. If we click it, we will see that customer ID 34 is shy Colin and the employee is yell
polet. Let's go ahead and click another one. Let's do this one. Yeah, tech gelita. And
Soufan buck is the employee. Okay, and of course, these are updating to. So wonderful,
this is looking so so good, just a few final pieces of styling, and then we are ready to
move on. So first off, perhaps let's make this a tiny bit bigger. So I'm just going
to use some markup to do this and put three hashes here, like so. And I will immediately
turn a lot bigger like that. And then perhaps us also have another one here. And I'm not
sure what should we should call this perhaps is call this, let's just call it overview.
That is essentially what this is over there. Sure to spell it correctly. And then here,
let's also have, let's perhaps take out sales orders in here, because I think it's a little
bit obvious, I'm going to move this up a little bit. So move up the button. Let's move this
down a few smudges. Let's also put this in line with the other thing. And this let's
call this sales order. My man meant, okay, so I think that's looking a lot more obvious
as to what that is. And then let's also make a little header. So here we go, I'm just gonna
select the header and make the background color. Let's make this blue just to keep everything
sort of the same color. And now let's also put in some text. So I'm just going to drag
this over into here. And once again, let's just use this markup, Sergeant for US store,
just like so. And then perhaps this will support e commerce. CRM. Okay, so just making it super
clear what this is, you can of course, ecommerce CRM, you can do so much styling in here, it's
honestly completely up to you, I am just going to do the bare minimum. But there's literally
hours and hours of styling that you can do to this, perhaps this actually made this one
line, I don't think it should be too. So there we go. Just like so, we can also add a profile.
So depending on who's logged in, they will have a different profile here. So I'm going
to go ahead and choose an avatar and just drag that in. So there I am. Okay, so and
that will be for whoever the user is, we can also have various other things. So if you
do choose to build this out even farther, we can also have a burger menu, meaning that
if you want to add different pages, or if you know, you're going to start moving things
out, for example, if you want the refund page to be on its own page, you can also have a
navigation bar here, which will show you different menu items. And that will take you to a nother
page. So again, like you could have the refund page on its separate page if you want that
is an option for you, too. So there we go. I hope you've enjoyed this. Everything is
now connected, everything is working. We can issue refunds. We can send emails, we can
add new data to this table. If it doesn't exist already on our spreadsheet. For example,
perhaps you want to put any manual order that wasn't taken online. You can sift through
customers using the filter tools. You can look through all your orders, you can look
at team members You can look at demographics, you can have an overview of how many orders
are shipped, how many orders, you've sold it for price, literally pretty much anything
you could want. Okay, so this is done for part one. Hopefully you've enjoyed this section,
if you want to learn how to use Postgres with this database that is coming up next. Okay,
now it's part two, in which we switch our database to be a Postgres database. Okay,
so to do this, I'm actually just going to imitate a Postgres database by putting in
some data in here using the Manage Database approach. So all I'm going to do is simply
get all of this data that we see right here. So as you can see, I've already copied it,
it is under database samples, Postgres SQL Northwind, dot SQL, make sure you are in the
postgres SQL section, and just copy all of this. Or if you want, you can literally just
get the file as I have here. And then in this file, before we get to switching this out,
so as you can see, here, we are creating a database, we are then creating tables, so
category, region, territory, and of course, sales order. So these are all the tables that
we see here. We are essentially creating the tables here and then importing the data. But
as you know, we also need to create another column. And that is with the charge ID, this
is your charge ID, this is the charge ID that you should get from your stripe dashboard,
as you should remember, from before, so if you go back to your stripe dashboard, under
payments, this is the 20 pound payment that we made by creating payment here in the test
data section. And all you need to do is get the charge ID. So you will see the charge
ID here. It starts with ch. So get that and we are going to create another column and
a table and input the charge ID. So we're going to do so in here, let's search for the
sales order table. So here we are creating the table and just after ship country, I'm
just going to add charge ID. Okay, and then in here, well, sure, let's just make it the
same as the customer ID. So just put in that, like, so. Great. So we've done that. And now
let's actually go to inputting the data. So here, you will see we are inserting the data,
we of course need to add a another field in here. So what I'm actually going to do is
just find anything that looks like this, and then replace it with ship country, but then
also add charge ID. Okay, so let's go ahead and replace all of those. So that is now added,
I don't think we use ship country anywhere else apart from in this sales order, which
is why I thought it was safe to do this. We indeed don't, it seems to be only in this
section. And of course, we also need to input the charge ID. So this is going to take a
little bit longer, because we can't use the same approach. There's different data being
added into here all times. So I essentially need to make a comma, and then just paste
the charge ID. So let's get this charge ID into here, like so. So there really like I
said, isn't an easy way to do this. Let's copy this, because I'm just gonna, maybe if
we select everything in here, that is a parenthesis with a semicolon, then we can essentially
input the charge ID before this. So I'm just going to copy all of these, this will take
a while. So just bear with me. This is not an easy process. But we have to do it in order
to get our data to look exactly the same as it does on your Google spreadsheet. So this
is great. Of course, all this data that was given to us, it's important to learn how to
start working with huge datasets like this, especially when you go on to becoming a software
developer, because this is likely the amount of data you will work with in a small startup
for example, or a bigger startup, whatever you are thinking to join. Okay, so we are
nearly that at the bottom of the sales orders that we are inserting. And now all I'm going
to do is just delete like a comma. And then I am going to paste it needs to do that. I'm
just going to paste the charge as a string like so. And then I'm also gonna just shut
that off like this. So I think this is looking okay, hold on. We seem to have messed something
up here. So we've got the postal code, we then have the ship country. And then we have
the charge ID. Great. So I am happy with this. Let's say this. And now I'm literally going
to get all of this data again. So copy all of it, everything that we have just altered.
So once again, we have added another column to the sales order table. And we have just
inserted the charge IDs into the table, too. So now Yes, I am just going to copy all of
this, and then go here. And let's create a new query. So resource query, and this time,
I'm going to choose to use the managed database, so we can essentially just insert it all into
here. So I'm going to paste everything that we copied, this might slow down your computer,
so just let it do its thing, because we are going to insert data into the Manage Database.
Okay, so just go ahead and click Save, and just wait for that to do its thing. Okay.
And once that is done, let's go ahead and rename this, I'm going to rename this to add
data. That seems to be an error. So let's just go ahead and delete the first lines.
Okay, so just delete this line. And just click save. We made the character type too long.
So where we create the sales order, let's just find that again, we're going to increase
this VAR char, we can make it 14. So make sure that we are giving it enough space. And
click Save. Great. So that is how we add data to our managed database. Now let's use it
because at the moment, we essentially have all this data, but that includes all the tables.
So these right here, I just want to get one table. And that's the sales orders in order
so that we can replace it. So we can use the postgres database on the main table right
here. So let's go ahead and create a new resource here. So resource query, and make sure that
is on the Manage Database. And I'm just going to select all, because that's how you'd get
all from sales order. Okay, so just like we wrote it in here, Sales Order has to be exactly
the same as sales order in here. And then let's click Preview. And there we go, we are
getting the table along with a charge ID that we have just added. Let's go ahead and rename
this to something. So I'm going to save this. And let's save this get sales order. Data,
Postgres SQL, so that we know that it's different to the other get data query. So there we go.
That's what I've saved as. And now I can simply get this table and replace this with this
query. Okay, so now we are using the postgres data instead of the spreadsheet data. So that
was super easy. We go, let's carry on. So now let's get the customer data. So again,
I'm just going to make a new query. And this time, let's call this
get customer data, Postgres SQL. And again, we're just going to do select all from this
time, we're going to go from the customer table. So just like so save and run. And this
time, I'm just going to replace the customers. So I'm going to go in here, select this table,
get customer data, PG SQL making sure that it's felt exactly the same. So let's just
go ahead and do this for the other tables. Some of this will be broken because, of course,
we are not using this anymore, but we will fix that in a bit. Let's just make sure we
do all of these first. Next we need to get the order details. So again, new resource
query. Let's call this get order. Details, PG SQL. And this time, I'm going to do select
all from Order Detail. Okay, and save and run. So this is all looking good. Let's go
ahead and replace this with get Order Detail peih Gee e g SQL same for in here get sales
order data, P G SQL. However, this time, we need to go into the data. And we actually
need to get into the Shipped date, okay, and then filter by order, or you can filter by
ship date, you can of course, rename this to whatever you wish. So perhaps it should
do ship date. So for each item that exists, let's call it a shipping date. Okay, so there
we go. And then I'm just going to get the sales order data data. This time, I'm going
to go by order ID length. This is just because our tables are a bit different. Now, if we
go into here, I will show you this. So all I would do is simply go into here and find
the query that I am making. So for example, that sales order data, once again, here's
our data, but it's now an object not an array, I will then have to go into the Shipped address,
which is an array and then filter out the Shipped date. Okay, so that is the Shipped
address where the ship date ship date. And I will just filter out by these. So it was
just a little bit of a different structure that you have to be aware of. But you can
also have a look in here, if you ever get stuck. So great. And this time again, we are
not filtering out by if it doesn't have an empty string, we're just going to go by No.
Okay, wonderful. And same for this one here. So this progress circle, we're going to get
the order details. Postgres SQL data will want to go into the discount this time. And
then we're going to filter by the discount itself. So discount. Discount. And if it does
not equals 0.00, actually, we should do, then we're going to get the data order ID length.
So there we go. Order it. And there we go. So that is now back to normal. This is all
looking good. These should be okay, because we are working by table on some of these,
so that's fine. However, this one again, this should be updated. So get customer order data,
Postgres SQL data. And this time, we need to get by the customer ID, because that is
what we want. We all know exactly how many customers and once again for this, so get
sales order data, Postgres SQL data, and let's again, do it by order it. Okay, wonderful.
So I think this should be nearly it's just a few more to go. Let's go ahead and update
these right here. So again, get sales order data, Postgres SQL. And this time, I can actually
just select the customer ID here and hide the order. Making sure this is also by employee
ID. So that is now back to normal. And this one, well, we need to get the employee data.
So let's go ahead and do that. Let's make a new query. And let's call this get employee
data, Postgres SQL again. And this time, we're going to do select all from employ. He just
like so and save and run. And then once again, it's just a case of replacing this. So get
employee data, P G SQL. Great and the same for here. So I'm just going to add PG SQL.
And same for the demographic. So customer data, PG SQL. This one however, we also need
to do some additional things. So let's go ahead and just make sure that this is by country.
So then we will go to country and customer ID. So wonderful. And like I said, these are
the last things we need to do. So in this one, well, essentially, we just need to get
the, we don't need to get the contact name anymore. So we just need to do that, because
we are filtering by contact name instead. So there we go. And just do the same for here.
So again, we are going to the data. And then we are going into the first name array and
just getting the value. So we don't need to go into any objects and the same for here.
Last name. And then we can get rid of this like so. Okay, we have one last thing to do,
in fact, two more things, because we also need to delete data. So I'm going to show
you how to do that, again, we're going to make a new query. And this time, I'm going
to delete a sales row. Again, I'm just going to add PG SQL. So it's clear how we're going
to do this. So this is not get this is delete, I'm going to do a delete a sales row. And
this time, I'm going to write delete, from Sales Order tables. So let's get the sales
order table where order ID equals, and then I'm going to get the main table. So table
one selected row. Data order ID. So this is how you would essentially delete a row a complete
row. Okay, so I'm gonna save that. And now I'm going to get this query because we're
gonna have to replace it in the actions. So where we have actions here, don't forget that
we do need to change this. So let's trigger a different query now. And it's going to be
delete sales row. So that is updated. And one last thing we need to do is the Add Row.
What do we seem to have done something wrong in here? No, that's the other one. So delete
sales row is now added to here to the action, let's just double check it is the correct
one. So delete, and yes, delete cells row is the one that's going to run. And then also,
let's make sure to write a query to add data. So once again, a resource query. And let's
call this ad, sales, row, Postgres SQL. And now, to add a sales row, well, essentially,
we are going to have to So I already have pre written this for us, I'm just going to
show you this. This is how we would do this. So here we go. We are essentially just copying
how we did it when we inserted sales rose into here. So sales order, like so we have
simply copied this. And instead of inputting the hard coded data, we are using data from
our macro. So let's just double check this is all right, as something seems to not be
liking this the order ID, well that is text input one value, so let's change that the
customer ID that is going to be text input two. So let's change that text input to just
like so. The employee id is going to be text inputs three, text, input, three. The order
date is date time two. So let's change that, which makes this date time one for the required
date. The ship date, well that is just date from what I remember date one and then the
shipper ID perhaps let's make this a little bit smaller shipper ID is text input 11. So
let's change that. The fright is text input nine. The ship name is text input 10 And then
the last ones is ship address. So once again, I'm just gonna make the smallest we can all
see it ship address is text input to ship city text input six ship region is text
input eight postal code is a four and then we just have ship country which is a five.
And of course, the charge ID, which we're gonna hard code as this. That's right, so
let's just go ahead and delete all this, I'm just going to put an empty string, like so.
And save that. Okay, so there's our ad sales row, which means now, that's makes sure that
on this submit, we make the right query, so submits, I want to run the query ad sales
order, PG SQL instead. And great. So let's run that. and wonderful. So cool. This is
looking good. I believe that's it, I believe we have now replaced everything that we need.
I hope you've enjoyed this tutorial, please do let me know if there's anything that you
would want me to add to this, I will be more than happy to do that. Hopefully, you've learned
a lot in this. And you can really take this and make things your own. One last thing that
we can do is of course filter as well. So I'm actually going to show you how to do that
super quick. So this time, I'm actually just going to make a text input. And this is how
we're going to query data from the sales order. So for example, if we have this here, and
we can such, I'm just gonna write a super simple query for this. So let's go ahead and
do that now. So I'm just gonna click here, resource query. Let's call this search, sales
orders, PG SQL. And in here, I'm going to select all
from sales order, where now we're going to get the text input 13. So text input 13 dot
value, or order ID equals and then we're going to get just put parse int and pass through
the text input 13. So text, input that in dot value, so this should be where it doesn't.
So just put a bang there. Okay, so now I should be able to filter let's just save this. And
that should just run every time we change the value in here. So if I just want to search
by order, I can do so like that. And then I'll just show me the search for data in here.
Now to update this in here. Well, we're gonna have to change this up. So let's just switch
this out to searched search sales orders instead. So get sales orders, such sales orders. Just
like that. Okay, so now it's hooked up, and I can make the table listen out to search
for any odor that I want. Wonderful. So hopefully that makes sense. I hope you've enjoyed this
tutorial. Again, if there's anything I've missed, please do let me know this has been
a long one but I hope you've learned a lot and I can't wait to see you in some future
tutorials to thanks so much again, and I'll see you soon.
