The Power of Resource Queries: Creating a Comprehensive Sales Order System with Postgres SQL

As we delve into the world of data management, one fundamental concept stands out: resource queries. These powerful tools enable us to extract and manipulate data from our databases with ease. In this tutorial, we will explore the intricacies of creating a comprehensive sales order system using Postgres SQL, specifically focusing on the creation of an "ad" or "sales" row.

To begin, let's establish a common ground: we are working within a database that stores sales orders. This is our starting point, and from here, we will navigate through the process of creating a new row to add data to our existing structure. We'll follow this up with the creation of an additional query, which will enable us to extract specific data from our sales order table.

As we develop our system, it's essential to consider the importance of consistency and standardization. To ensure seamless integration with other tables in our database, we must adhere to established naming conventions and schema. In this case, we're focusing on the "ad" or "sales" row, which will serve as a vital component of our sales order system.

Now, let's dive into the nitty-gritty details of creating this new row. We'll be utilizing a pre-existing query as a starting point, and then making necessary adjustments to accommodate our changing needs. The process is straightforward: we'll simply copy and paste the existing query, replacing hardcoded values with dynamic data from our macro.

As we work through this exercise, it's crucial to double-check our work to ensure that everything aligns correctly. For instance, when inputting a text value for the order ID, we notice that it requires two input fields instead of one. To rectify this, we make a simple adjustment, changing the first input field from "text" to "1" and the second from "text" to "2". This minor tweak ensures that our data is accurately represented in our database.

Next, we'll examine the various fields present in our sales order table, each with its unique characteristics. We have the customer ID, employee ID, order date, ship date, freight, ship name, ship address, and finally, the charge ID. These individual components will be incorporated into our new row, allowing us to seamlessly integrate data from our macro.

To implement this, we'll make a few adjustments to our query, replacing hardcoded values with corresponding fields from our sales order table. We'll also add an empty string for the ship country field, which will serve as a placeholder until we have more specific requirements.

Now that we've created our new row, it's time to consider how we can query data from this sales order table. One of the most powerful features in Postgres SQL is the ability to filter results based on user input. To achieve this, we'll create a new resource query called "search sales orders," which will allow us to extract specific data from our table.

Within this search query, we'll utilize the text input field as a parameter, using it to filter our results. For instance, if we want to search for all orders with a particular order ID, we can simply enter that value into the corresponding field and press submit. The database will then return the relevant data, enabling us to access and manipulate it as needed.

Finally, let's discuss the importance of updating this search query in real-time. As users interact with our system, they'll inevitably need to refine their searches. To accommodate this, we'll modify our query to respond to changes in the text input field, ensuring that our results remain accurate and up-to-date.

In conclusion, creating a comprehensive sales order system using Postgres SQL requires attention to detail, a deep understanding of resource queries, and a commitment to consistency and standardization. By following these steps and exploring the intricacies of data management, we can build a robust and dynamic system that meets the evolving needs of our users. Whether you're an experienced developer or just starting out, this tutorial has provided valuable insights into the world of database management, empowering you to take your skills to the next level.

"WEBVTTKind: captionsLanguage: enHello everyone here on the freeCodeCamp channelwanting to learn how to make your own CRMwith Google Sheets or Postgres SQL, I sayoh as I will be showing you how to build aCRM with both. So if you are more comfortablewith Google Sheets, we will start off withthat. Or if you want to use a database I willbe showing you how to make in Postgres SQLtwo. My name is Andrew kooba, and I'm a softwaredeveloper and course creator on YouTube. Iam also your host for this action packed videofor all skill levels. It is my personal missionto show you that anyone can make a CRM orcustomer relationship management system andmake it customizable to them and their business.A CRM is essentially a way to manage yourcustomer data. For example, imagine having1000s and 1000s of customers, it will becomevirtually impossible to remember which oneof your customers but what, when, or whenthe last time that you email them what CRMsallow you to add this data to the system,filter through it to find customers basedon a variety of variables, and even automateyour processes such as sending thank you emailsor payment reminders. The main purpose ofCRM has traditionally been improving customerservice and driving sales. CRM software'sactually 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, andHubSpot, and fresh work for smaller businesses.Now, there are many reasons why you mightwant to build your own CRM. For me the mostobvious is that CRMs can be expensive, andoften you are paying for features that youdon't really care about that much. Using localsolutions, such as the one we will be usingin this course, allows you to create yourown personalized CRM experience totally forfree, or at least at a fraction of the traditionalprice. This CRM will allow you to sell yourdata in a visual way, meaning we will be creatingdynamic graphs and charts, as well as allowsyou to add or edit data from the platformitself. It will also allow you to performrefunds directly from the platform using thestripe API, as well as send out automatedemails based on templates of your choice.API stands for Application Programming Interface.They allow for technologies to essentiallytalk with each other and are essential toso many services that we rely on today. Theyare in fact behind most apps we use on a dayto day basis. And they can shape the informationpaths between one technology to another. API'sare everywhere. We are in fact going to beusing the stripe API and a mailing API inthis course, but there are so many you canchoose from to improve your CRM offering.This course is going to adopt what is considereda low code approach to creating platforms.This means there are no hard coding prerequisitesfor doing this course. Although unlike nocode, and understanding the fundamentals ofHTML, CSS and JavaScript are advised if youwant to take your project to the next levelin terms of styling and functionality. Tobreak it down for you. Here's what we aregoing to learn step by step. In part one,we will build out our CRM from Google sheetsin which we will fetch all of our data intothe platform, but then also learn how to addnew data to the existing Google Sheet directlyfrom the platform we have built. We will alsolearn how to edit data to so that it is savedback in the Google Sheet afterwards. Next,in part two, we will integrate stripe forprocessing refunds thanks to the pre configuredstripe API. After that, in part three, wewill learn how to send out automated emailnotifications with the bailing API. And finally,in part four, we will upgrade and transitionthe backend away from Google Sheets and toPostgres. SQL has scalability and to handlemore complex features. Okay, so what are wewaiting for? Let's get to building out ourCRM. Okay, so welcome to the Regional platform.This is the platform that we will be usingfor this tutorial in order to create a dashboardthat looks like this. Okay, so in order todo this, I'm going to start off by creatinga new project on the main dashboard. Hereare some previous projects that I have done,we're going to go ahead and just click hereto create a blank app. And let's go aheadand give this a name. So I'm going to callthis FCC CRM and just click Create up whichwill take me to this blank dashboard. Okay,so here we go. Here's our blank dashboardwith a table that has been created for usjust as a demonstration. I'm going to simplygo ahead and delete that table to truly makethis a blank slate. Now, we are going to beworking with some data now in any sort ofsituation. So you're a small business owner,and you have a lot of data that you then wantto use to create an app. That's the anglethat we're going to go with. Okay, so we aregoing to take this sample data from Northwind.Right here. This sample data will provideus with a table of categories, regions forour customers, territories, customer demographics,customer demographics, again, a table of employeesto show which employee deals with which customerand order, employee by territory, supplier,product, shipper, the sales order itself,the order details, and so on. And now we'regoing to insert all that data. So this isthe data that we are going to work with forthe first part of this tutorial, I have goneahead and put all of this data in a Googlespreadsheet for you. As you will see here,here, all our customer, there is a lot ofdata, we also have the sales orders, and soon and so on. If you would like to make yourown table, all you have to do is go to yourGoogle Drive or sign up if you don't havean account, create a spreadsheet here by clickingon Google Sheets, and start creating columnsand rows. You can even label the tabs downhere as previously seen and create new tabswith new tables. So that is just some backgroundfor you on Google Sheets and how to createyour own. But for now, and for this tutorial,if you want to follow along, please go aheadand just use the table with the Northwinddata I pre met. This data mimics a genericcompany that receives orders. These ordersare 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 linkto specific products that can be cross linkedto here, employees that deal with the orders,the customers that make the order, and soon and so on can also be linked, I would advisetaking a moment here to really understandwhat is going on in this database. So I willbe sharing this link with you in the descriptionbelow. Please go ahead and have a look atthat. Once again, here are all our orders,there are up to 831 orders that we are goingto be working with. So great a lot of datalet's carry on. So the first thing I'm goingto do is create a new resource. So let's goahead and go to our resource page. Here, youcan see all the resources available to us.I'm going to go ahead and select the GoogleSheet one on this occasion, as we want tobe able to connect the Google sheet we arelooking at to the platform. So these are thesteps you will need to do in order to makethat happen. Great. So now let's go back toour app. And don't forget to refresh the page.Now I'm going to make a new query. And asthe resource, I'm going to choose the newresource we just met, which is called NorthwindCRM data. So there we go, we have officiallylinked 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, thetable component to be precise from the UIpanel on the right, in order to map all ourdata onto it. Okay, so that is what we aregoing to do. If that helps, I like to thinkof 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 panelon the left, which is foreseeing the shapeof our data that comes back to us after wemake a query. I will be showing you this lateron. Okay, so now we can actually pick a spreadsheetID. And I'm just going to select Northwinddata as that is what we call a Google sheethere. And for the sheet name, well, the firstthing that I want to get is the order salesorders. So let's go ahead and select thathere, sales orders, just typing that in makingsure that it's spelt exactly the same waythat we did, right here, sales order, salesorder. Great. And just click Save and Run.So now we should see all our data coming updown here. Wonderful. I'm just going to renamethis to get sales or der data so that it'smore readable for us later on. Wonderful.So we are now getting all our sales orderdata. And to create a table with it. Well,all I'm going to do is just drag a table inhere. And there we go. We will see our tablepopulated or the sales order data. This isbecause we are on this query. Our query hereis selected so it knows to use this queryin order to populate our table. While we'rehere, I'm just going to get rid of this queryas 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 actuallyjust 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 clickon the columns here and rename the columnif I want. So for example, maybe let's callthis order ID like so. And that will updatein here, let's also get the customer ID. SoI'm just gonna go Cust, ID, just like so,and so on. So while we are here, I'm alsogoing to show you that you can format eachof the columns. So for example, the requireddate, what if I click on this I can even chooseDate Time, local timezone, or original timezone,it is completely up to you, whichever oneyou'd wish. Let's go with original timezone,like so. And that will format the date tobe a little bit more readable for us, as opposedto this format right here. Let's do the sameon the ship's date. So once again, let's goship dates, and just select Column Type, originaltimezone, and you will see that changing.And finally on the order date. I'm just goingto click here, Column Type, date, time, originaltime, so wonderful. So this is already lookinga bit better. While I am here, let's havea look at what else we can do. We can of course,also hide columns. So for example, perhapsI don't want the ship region, all I woulddo is simply go here and click on the littlei to hide it. And I'm also going to hide theshipper ID and the fruit number. Okay, sothat is looking better with the informationthat I want. One thing to note here is theyactually added this charge ID, this chargeID is from stripe, I have added it in thesales order right here. So this column hasbeen added by me. And I have done this sothat we can demonstrate how to issue straightrefunds on particular orders. So once again,this was not with the Northwind data here,this is a column that I have populated myselfand created myself. But don't worry, if you'reusing the link in the description below. Youshould see this too. Okay, so while we'rehere, I'm just going to rename all of theseas well. So let's maybe change this to ampID for employee ID. Let's also do order, date.Shipped, dates. Quiet, dates, ship name, I'mactually going to hide I don't really thinkwe need this ship address, I'm just goingto put ship address like so. Ship city, I'mjust going to give us city, postcode and theirbook postal code. And it's just leave thisUS country and the charge ID. Wonderful. Sothis is already looking much better for me.Great. So we have our table. This is lookinggood. Let's go ahead and add a few more tableswhile we're here. So I can go ahead and perhapslet's add the table of customers as I thinkthat is some good data that I would want asa start up. So once again, I would just gointo here, click new resource query, makingsure that I am on the Northwind CRM data.As that is the Google sheet that we have linkedhere. I'm going to read data from a spreadsheet.I am also going to select which ID so Northwinddata is what we call the spreadsheet. Andthen the sheet name. We know that is customerso I'm simply just going to type customerhere and click Save and Run. And while I'mhere, I'm going to rename this query so thatwe can keep track of them better get customerdata, just like so. Great. So now we can putanother table in however, I'm going to showyou a really cool way to organize your data,we can use the tabs component. So if I justgo ahead and whack in the tabs here, I cannow put one table and have one and anothertable on tab two. So I'm going to put allmy 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 caneven put some text here in order to make thismore obvious to everyone. So it's going tograb some texts. And I'm simply going to putsales orders. Like so we can also make thisbold, if you wish. We are using markdown tomake this bold. Markdown is a lightweightmarkup language for creating formatted text.Using a plain text editor is the same thingyou use when writing GitHub with Miss, youcan find all the syntax you need here, ifyou wish. So there we go. Now that's moreclear that up sales orders. And then I canalso do so on tab two. So on tab two, thisis going to be customers. So I'm just goingto put customers making sure that it's spelledcorrectly, like so. And making sure I'm onthe query, I'm just going to grab a tableand just put it here, in order to populatethis with the data that we see from my querydisplaying right here, after we have run thequery. So there we go. Great. Once again,if you wish, you can choose to hide certainthings, perhaps let's go ahead and hide afew 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 hidethe region, if I wish, it is totally up tome. So perhaps I will hide the region, I thinkthis 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 wealso have our sales orders. We can also ofcourse, rename the tab so I can do so righthere, sales orders. And then we have customersas well. So there we go. I'm going to actuallydo one more table before we continue addingother things. Another table that I would liketo add is the table of orders themselves.Because at the moment, we can see here theorder number and it gives us the informationabout the customer and the shipping date andthe status. However, I just want to know whatis exactly associated with this order. Sofor this, I'm actually going to use the orderdetail. So I'm going to get this table inhere. So once again, new resource query, makingsure that I am on Northwind CRM data, I'mgoing to read data. And I'm going to choosethe sheet Northwind data and the sheet nameis order detail. So I'm just going to go orderdetail, like so making sure that it's forcorrect and save and run. And there we go.So we have that here. Let's also rename thisto get order details. And I'm just gonna dragthat once again up here, so that we can seethe order details, just like that. Okay, wonderful.So this is looking good. Once again, perhapslet's put some text here, just to let everyoneknow that these are order details. And I'mgoing to get rid of that emoji. We can actuallydo some editing on here too. So for example,here is my tab, we can style the tab withall these pre configured things, or we canyou know, go fully custom. At the moment,I'm simply just going to choose to changethe header like sir. So I'm going to chooseto make this white. And that's already lookinga little bit more stylish for me, please feelfree to go wild. That part is totally up toyou. So there we go, we have now completedthe part where we put in all our tables. NowI'm going to show you how to create pie chartsand visually show this data thanks to thesecomponents right here. So I'm just going togo and use a component such as chart in orderto 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 tabsitem here. So tabs component, I'm just goingto drag that in so that we can display differenttypes of data on different tabs. So I'm justgoing to make this a little bit bigger. Likeso. And great. And once again, I'm just goingto change the header background to be white.So I can simply just drag this up here, orI can put in FFF, fff to symbolize weightby myself. So tab one, I think the top oneis going to be an overview. So let's makethis an overview. And it's going to just bean overview of the amount of orders that wehave shipped. So I'm going to use the tableright here, I'm going to use the ship datein order to show us how many orders have beenshipped. And I'm also going to use the tabledata here in order to show us how many itemshave been sold at full price. Okay, becauseyou can see discounts on implied to some,I will know exactly how many orders we havesold at full price. So to do this, I'm simplygoing to get the progress circle and dragit in here, just like so. So here we havea 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 usethe table data in order to make this dynamic.So as we said, we want to show how many ordershave been fulfilled, or another word shipped.So we are going to get our sales order datawhich is an array, and we are going to usethe JavaScript method of filter to filterout each order that has a ship date have notan empty string, as if it is not an emptystring, it means a ship date exists. And wecan say this order is fulfilled. Next, weare going to find out how many of those exists.And we will do so with the length propertyon the array, we will then get that valueand divide it by the total amount of ordersin the get sales order data array and multiplyby 100. As that is how percentages work. Okay,so fulfilled orders over a total of orders,multiplied by 100 will give us the percentageof fulfilled orders. So to do this, this isthe syntax for doing so in here, I need toget these two curly braces, well, we knowthat this table is made up of get two salesorder data. So let's get says order data getsales order data. And we need to get its data.So we can use this in order to see how thedata is formed. So he is get sales order dataand you will see that comes back as an object.That object has data in it, which is why weare getting data. And data is an array. It'san array of different objects. So let's filterour array, I'm going to use the JavaScriptmethod of filter to filter out each item inthe array, let's call it an order. So likeso 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, andthen just divide it over the get sales order.Data Data length, so the length of the entirearraythere was a spelling mistake here, let's goahead and correct get to sales order data,we go get sales order data. Now let's multiplyby this 100 Because that is how you wouldget a percentage so that we go a percentage.So just talking this through again, what weare doing is getting the sales order datathat we can see here. So as you can see here,this is the object that returns back, we'rethen going into data, which is an array essentiallyof all this information. And for each itemin this array, which we have called order,we're going to filter out by the Shipped Daydate. So this right here, and if ship dateis null, so it doesn't contain anything, wellthen we know that it's not been shipped. Andthen we're going to divide that by the lengthof this entire array. So by the amount ofitems in the sales orders are multiplied by100 Because that is how you'd get the percentage.So as you will see here, 97% of our ordershave 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 oversome text austraila over here, and let's doShift orders. Just so people can know whatwe 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 tellus exactly the percentage. So once again,I'm just going to drag that up here. And wecan do something like this, I'm just goingto get rid of that currently. And then wecan 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 canactually grab the value of this if we want.So this is progress circle one. So I can,instead of doing the calculation again, justget progress, circle one, and I can get itsvalue. So there we go. And we can use someJavaScript on this, to make it to two decimalplaces, if we wish to fix and then just passthrough a two, just like, so. Great. So thisis looking good. Let's just add a percentagehere. and wonderful. So now that we've donethat, how do you think we would make a percentagefor all the orders that have been sold atfull price? Well, once again, I would simplygrab a progress circle. So this is progresscircle number two, this time, just going toput that in here. And this time, let's getthe get order details. So get order detailsdata, that's correct and don't want the valuesix to be hard coded, I want to get the orderdetails data, get order details, data, whichis the array, and this time, I'm actuallygoing to filter bythe discount. So very similar to what we didlast time, you can use the filter JavaScriptmethod to filter out each order. So I'm justgoing to put order, I'm going to call everyitem 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, theorder, it's going to an item discount. Andif the order discount equals the string ofzero, because that is what we have here wehave strings of zero, not numbers, then Iwant to get the length of the array. So Iwant to get exactly how many have the stringof zero and the discount, and divide thisby the array itself. So get all the detailsdata dot length, and of course, multiply itby 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 havebeen sold at full price. So I'm just goingto copy that. And then once again, let's justget the progress circle to value this time.Currently 61.11% of all orders have been soldat full price. And then let's just make surewe have no spelling mistakes here either.And great. And once again, it's perhaps justgive this a title. This time saying full priceorders, full price orders. Just like so. Justmaking sure that they are all aligned correctlyto make them look good next to each other.So there we go. Okay, great. So we have nowlearned how to use the progress circles. Thenext thing I'm going to show you is how toshow a statistic. So let's go ahead and perhapsthis time show something like the total ofall the orders. So for this I'm just gonnaGet the unit price and add it all up. Okay,so that's what I want to do and multiply itby the quantity sold. So unit price by quantity.And for this, I'm actually going to use astatistics component. So we already have thishere, it's already sort of nicely styled forus to look like a statistic. So there we go.Let's also pop some of these down. Now, I'mjust going to move these down. Of course,the styling is completely up to you, pleasefeel free to go wide on the styling howeveryou wish, I'm also going to put in a divider.So just whack that in here. Again, this isjust assigning preference, please feel freeto start this up, however you wish once youhave got to grips with the retool tool. Okay.So just putting these circles back like that,that's making them smaller. Again, you couldjust fiddle around this forever. So pleasejust bear with me. And great. So here, I wantto show the total of all the oldest salts,I'm just going to put total. And, like sototal. And then here, well, we don't wantto hard code this, once again, let's get upour curly braces, because we want to get,we can also do it by table. So this is tablethree, let's get table three, let's get thedata in it. And we just want to go by selectedrow. So I can use selected row selected row.And then I can get the data. And what I cando is get its quantity. And then I just haveto multiply it by the table three selectedrow. Data Unit Price. Okay, so now whateverrow I click on, it will show me the total.So that is looking pretty good. Perhaps usmaybe make this clear what this is total ofselect tid. Row, and now we can simply getthe total just by clicking the row. So thatwas quite handy for us as small business owners,I also just want to perhaps get rid of thisas it doesn't really mean anything. So let'sget rid of the caption here. And I don't reallyneed this increase amount. So we don't wanta positive trend. I'm just gonna delete that,like so. Great. So there we go. Now we'vejust made a useful tool to essentially showus the total of the selected row. The nextthing I want to do is just show us the totalamount of customers that we have in this dashboardand the total amount of orders. Okay, so thisis really an overview, this tab has everythingto 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 allthe customers that we have. So I'm just goingto once again, perhaps I'm just going to hardcode, customers here like so. And then inhere, well, I do want to display the amountof customers that we have. So to do this,I'm going to use get customer data, and thenjust get the length of all of this data righthere because this data is the customer data.So if I just simply go by length of the amountof essentially rows that we have, that shouldgive me the customer amount that we have inthis dashboard. So there we go. Once again,if we look at this table, all I have doneis simply get the length of the array, andthe array symbolizes the rows. Okay, so eacharray item is essentially a row. So if I justcount the rows, it will show me the amountof customers that we have. Great. So that'sour customer data. I can of course, spreadthis out a little bit if I wish. And herewe can have the order data. So this is goingto be the amount orders. And once again, toget the order amount, well I'm simply goingto 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 ordersthat we have. We can also see the amount ofcustomers. And if I add orders to this table,this should change dynamically. So this islooking good. There's our overview. Let'scarry on. Let's have a look at tab two fortab two I want to show exactly what the workdistribution is by employee. So if we havea look at the sales orders, you will see thateach order, we can actually have a look hereas well has an employer ID by it. So righthere, employer ID, so we know what order isassigned to which employee. But we want tosee that visually. So for this, I am justgoing to use another component. This time,I am want to create a pie chart. So to dothis, I'm going to go to charts and I'm justgoing to drag this over like so. And I'm alsogoing to select pie chart. And this time weare getting the sales order data. That iscorrect. However, I just want to show thedata by its employer ID. So undervalue labels,let's select employer ID, just like so. Andthere we go. So now we can see all the workdone by employer ID. So essentially, whatis happening here is that we can see thatemployer number four, has the most work asthe most orders assigned to them. So thisis looking good. Let's carry on. The nextthing I want to do is actually show me theemployers name by this number, right, becausewe have the data here. If you look here, wehave the employees along with the employerID. So once again, I'm going to go in hereand click a new resource query, making surethat Northwind CRM data is selected. But thistime making sure that Northwind data is selectedto as the spreadsheet name, I want to getthe implore you making sure that it's spelledexactly the same as it spoke here. So I imploreyou, and just click Save and Run. And onceagain, I'm just going to get a table makingsure that on this query, let's call this getemployee data. And I'm just going to dragin a table right here. And that will showme all the employee data, just like so. Sothis is looking good, I can now get the employeedata. I can also hide things of course, likefor example, let's say I just want their name,I can do so very easily like this by hidingall the other tabs. Great. So now we havethe employee ID, we have their last name,their first name, as well as their title.Wonderful. And let's just rename this tabto distribution. So work distribution districtyou shouldn't like so I'm just going to makeanother tab. And this time, the tab that Iwant to create is a tab called team and atitle another one called Demo grow, to showus where most of our orders come from. Great.Now, for team well, I'm actually just goingto get the same table right here. And I'mjust going to show us all the data that wesort of had before. Because I think it's usefulto have you know all the employee data, notjust the first name and the last name, sothat we can filter through it. So this isgoing to hold all our team data, just likeso. And demographic. Well, as we said thedemographic, I want us to show exactly wheremost of our orders are coming from. So I cando so easily. Once again, I'm going to usea pie chart for this. So let's go ahead andfind a pie chart. And this time, I actuallywant to get the customer data, let's get thecustomer data, let's do it by where the customeris based customer data. Just like so makingsure this is a pie chart. And this time, Ijust want to get the country because that'swhat I want to display. I want to show whatthe breakdown is by country for all our customersto just like that. We can even position thishowever we want. So perhaps this is a goodway. and wonderful. You will see that mostof our customers come from the US just bysimply dragging a pie chart like so. Okay,great. So this is looking good. Let's moveon. I'm just going to drag this a bit lower.To make sure that's the same level. We willbe doing all the styling a bit later on. Thisis looking good for now. So great. We aredisplaying all the data that we have in theGoogle spreadsheet we are picking out whichsheets we want to use. And then we are alsogetting an overview of the data that we haveimported in here in various tabs that we havehave made, the next thing that I want to dois actually add data from a spreadsheet. SoI'm going to show you how to do this now.So as we mentioned, at the moment, we're justreading data, I can also add data to thisspreadsheet from this platform. To do this,I'm going to show you how to do this. Firstoff, let's actually create a modal. So I'mgoing to grab the modal, like, so I'm justgoing to drag it in here under the sales orderstitle. And I want this modal to pop up andessentially be able to create a new orderthat I add in here. So let's go ahead anddo that. A modal is a pop up window that weare going to see, to add the data to our spreadsheet,as we just mentioned, we will do this by populatingthe modal with form field inputs and a submitbutton. So first off, what do we want a normosall but we're gonna have lots of inputs, essentiallyinput all of this data. But first off, let'sjust start perhaps with a title, I want mytitle to say create an order as that is whatis happening here. So create an order. Justlike so, we can also change the background.So if I perhaps one it red. So like a redlike this, that is also possible to Okay,so there we go. And there is an option tocreate a form to make our lives easier. Solet's go ahead and find that form first. SoI'm just going to search for form. And dragthat in here. So now this is good as it comeswith all the button and the styling. And itjust makes it a little bit nicer for us tolook at. So here's our order form. It's draggingthis all the way up here. And let's just dragout the title as well. Great. And now let'sgo ahead and get inputs for all of this data.So all of the data you see in the row we'regonna have to collect. So the first thingthat we're going to collect is the order ID.So let's go ahead and do that here, perhapsthe value we're going to leave empty, andto value or we could have an order numberas the placeholder, it is totally up to us.The label, we're going to put order ID asthat's what we want to put in here, just likeso. And we can hide the label, we can showthe label, we could also make the label appearat the top or bottom, it is completely upto you. So that's my input for the label,let's carry on. The next input that I needis to get the employer ID. So let's go aheadand do that. Because the order date we'regoing to have to select. So I'm just goingto make that up here. So here's a label. Andthen for this, let's call this employee ID.Just like Sir, and once again, let's alsomake the same for the customer ID. So let'sput the customer ID. So customer ID employeeID, the next thing we do at some dates. Solet's go ahead and get a date picker up. SoI can do so quite simply just move this downa little bit perhaps. And this time, let'sget the date picker. And just drag that inhere. So here we have the date picker. I'mgoing to call this ordered by ordered dateand then we can select a date copy paste thatokay, so got the order date. The next thingwe need is the required by so required bydate. And then finally, we need the addressfields. So perhaps has put a divider in there.So I'm just going to drag a divider in justso you can separate the two visuallyand now let's get an input. So a text inputjust drag it all the way over here. This isgonna be for the address let's put that belowthe divider since contract the divider uphere once again gonna happen for the city,the country and the region. So here we havefour inputs or text inputs. If wherever reasonyou need a number input that is one specificallyfor numbers, so just bear that in mind. Sothese are all text inputs. So we've got theaddress the next one, I want to be city. Thisone I want to be country. So let's just changethe level of that. And this one I want tobe region. And we have one more thing. Sowe're not actually done in this address section,because we also need the postcode, so let'salso have the post code. Okay, so that isit for the region, or address parts. Onceagain, we'll split this out because we doneed some more things. Even though we can'tsee the ship ID and the shipper ID and thefright ID. And this shipped on date, becausewe hit some of them, we are going to haveto 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 themand just making sure we are collecting datafor all of those to be populated. So onceagain, let's just get the divider out. Let'sdrag that across here. And then let's go aheadand just put in some more inputs. So onceagain, I'm just gonna drag that below here.This time, let's perhaps make the label atthe top because we're gonna have three here.So one, oops, two, three. And then just positionthem a little bit. So label one, two, andthree. And let's have this as ship name. Let'shave this as shipper ID. And let's have thisas the fright field freight shipper Id notshipped. And finally, we're just going tohave a date picker to pick the date that thiswas shipped on. So once again, it's got thedate picker, and just driver in here. So justlike so and give us the label of shipped on.Great. So this is looking good. And of coursewe have the submit button as well. So nice.I'm really happy with how this is lookingso far. Okay, great. So we're collecting allthat data. But now we need to essentiallyget all this data and get this data to makea query with it to add data to the sales ordertable. So what I'm going to do is create anew query this time, this query, let's callthis ad sales order, date. Okay, so that'swhat our query is called. This will be toNorthwood CRM. And then we're going to appenddata to a spreadsheet, choose the spreadsheet?Well, that's Northwind data on the sheet wewant to add to his sales order. So let's justgo ahead and put sales order like so. Andin here, what do we need to do what we needto add data that looks like this. So let'slook in here. Let's look at the data. Okay,and we need to make an object that essentiallylooks exactly like this. I'm going to copythat. And I'm going to make an array. So openup the square braces, and then we could justmake an object and paste all the data likeso. But I think this is quite messy. So I'mgoing 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 inis the, we could start with the charge ID.Let's go ahead and do that to just make surethat everything's in order. So the first oneis charge Id always making sure to spell everythingexactly the same as you see it. So chargeID and for now I'm just going to put the stringof test, put a comma. And the next thing isthe Cust ID. So once again, let's put thestring of Cust ID and put the string of test.The next is an ID for employee ID. And onceagain, I'm putting test and you get it. Sofright is next test by a comma, it's gonnacopy this actually, because there's a lotof repetition fright order date then we havethe required date. Just paste. Next we havethe ship, address, and paste. Next we havethe ship, set do paste ship country is nextship, country. ship name we have this shippeddate. Ship for it. Shit, still code and theship region. Okay, so that is it, let's quicklymake sure that we didn't make any spellingmistakes, 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 goingto click save and run this. So I'm just goingto get this up a little bit so we can seewhat returns back to us. Okay, so that ransuccessfully, we saw the little pop up. Nowlet's have a look here and go all the wayto the bottom of sales orders as we did appendthe row to this table. So it should appearat the very end of this table. And there wego. I did press it twice. So two rows havebeen offended. And everything is working.Great. Wonderful. So now that the test isworking, let's replace these test stringswith actual values from our form field righthere. So all I'm going to do is match theinput value to the key. Okay, so there wego charge ID, well, we know that the chargeID at the moment, I'm going to hard code itbecause all the charge IDs are the same. Thisis just a test charge ID that I'm gettingfrom the stripe API that we will go into later.So this one I can hard code. So let's go aheadand do that and put that as the string. Thenext thing is the customer ID. So we knowthat that's going to be text input two's value.So I'm just going to replace this let's getour curly braces up. And I'm going to grabthis element which we called test input totest in foot two, and I'm going to get itsvalue just like so the should say text inputvalue, apologies text input value. Okay, great.So this is looking good. We can of course,if we really want to change these. So if Ireally wanted to rename them, I could. Sojust know that there is an option to do that.I'm not going to do this for this tutorialas that will take a lot of time. But knowthat if I wanted to change this, I could.Great. Now let's get the employer ID. So forthis, we're going to use text input three,get the two curly braces and go text inputthree dot value. Great. Let's do the samefor the freight. So here we go. That's textinput nine. Once again, let's just get ridof these quote marks. Let's get the test wordhere got our curly braces and this time weneed to get text input nine so text in porchnine dot value making sure to spell text inputcorrectly just like so. The order date whilethe order date is Date Time to so let Let'sgo ahead and replace the test value here withdate, time to dot value, the required date,once again, that is date time one. So I'mjust going to get the test and use the curlybraces to get date time one dot value. Soit really is this simple. That's all I'm doingthe shipping address or the ship address.In other words, that's going to be text inputseven. So I'm just gonna get the curly bracesand get text input seven, we can use the dropdown to make our lives a little bit easier.This ship city is going to be six. Countryis five, and region is eight. So let's tryremember that just going to copy this one.Ship city we said was six. ship country thinkwe said that was five but we'll double check.And the shipregion is eight. Do we have ship region here?Here we go ship region. So let's make thateight. And just a few more left, we need thepostcode which is four. So let's find thepostcode in here. There we go. And replaceit with text input four. And then we justhave the ship name which is 10. Ship ID 11.And I think that shouldn't be it. So shipname is text input 10. Ship ID is 11. AndI think there's just two dates left. So let'shave a look. Again back here. One date leftdate one is this shipped on date. So let'sjust go in here and this is date, one, dateone dot value. So this is looking good. Let'stest it out. So let's gonna save that. Andthis time to test it out. I'm just going toinput some data. So let's go ahead and dothis. Employee ID, I'm going to say to customerID three, order ID again, this is just fortests. Let's pick a date, I'm just going topick the date and the time for this one. Theaddress again, I'm just going to put testfor now, city test, country test, region,test, postcode, these three, ship name a,a, and then fry a and then shipped on let'sjust have a date for this one. Let's go withthe 10th of love. And then click Run. So nowokay. We did get an error. Why is this sowe can actually read right here what has happened.And you will see the line has not been addedthere was an error. And I think it is somethingto do with this. We have left a little apostrophethere. So that's gone. And I think we alsodon't have the order ID. So that is one wedidn't pick out if I'm not mistaken. So I'mjust going to put that in the front or afterthe order date. So just in here, just to makesure it's the same as here for readability.So order ID and the Order ID is going to bethe text input one. So text input one dotvalue. Once again, just make sure to formatthat correctly. So put a comma there. Andgreat. So now if we save this and run it andhave a look in here. Well there you go. Therow has been updated with the correct valuesin each column. Great. So that is now working.We have now figured out a way to add dataFrom this platform to our Google Sheet, Ijust need to do one last thing that is connectthe button right here so that it sends whenwe click this button. So I'm just going toclick that right here. So I've just selectedthe button if you can see that. So here wehave it. And we need on submit, we could makethe button forward if we wish. On submit,so that's the submit button. We also wantsome events. And I just want to trigger thequery, add sales orders, I want to run thisquery 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, justlike so. And we could also add other things.So for example, I can also add, instead oftrigger query just to send confetti so thatwe know that is done. And then as a thirdthing, 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 requestand get all of this data again. So I needto run the query to get sales order data.So I'm just going to do that in here. So inthe query rather than hitting the button,and I'm going to trigger the query, get salesorder data, and save that. So now, when wefill out this form, and click the submit button,I'm just going to zoom out a little bit, soyou can see the submit button. And when weclick the submit button, so let's do anotherone this time, this time, I'm going to putorder ID www at the end. And I'm just goingto click submit. So we are making a queryto add data to this Google Sheet, we are thensending the confetti, and we are making aGET request to get all the data in here. Whichmeans that now if I look here, and just goto the end, so let's go to the very end, I'mgoing to put in 76, that ah, that is the newline that we have just added. So I'm justgoing to zoom back in again. So you can seea little bit better. So great, this is allworking fine. I'm really happy with how thisis working. Of course on here, there are inbuilttools to download all the data, you can alsofilter. And you can also reload manually,if you wish. Okay, so these are all optionsthat you can do on the table as well. Correct.While we're here, I'm just going to changethe color of this as I think blue is a bitpassive, let's go ahead and make it red. Sojust like that. Wonderful. And I'm just goingto go back to the first page. So page one.Great. So we can now add data directly toour database here using the platform. Butthere are other things we could do as well.So I can actually also delete data from thistable so that it shows up here as well. Sothat is the next part, we've just added data.Next, I'm going to show you how to deletedata to Okay, so for this part, I'm actuallygoing to have to write a new query to deletedata. However, first off, let's go ahead andadd a button to this table. So I can do soeasily 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 myactions. And I'm just going to call this theDelete action. So here's my action I justcreated. And let's call this Delete. Okay,so just like so. So there we go, that hasnow 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 goingto create a new resource. And make sure thatit is using the Northwind CRM data. And nextI want to not append data or not update, Iwant 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. Andthe sheet name that I want to work with isthe sales order sheet. So making sure thatI'm on this sheet right here. And now I'mjust going to have to filter too much rowand let's go by order ID. So I'm just goingto select the order ID table making sure tospell it exactly the same as we did in here.So I'm going to filter by order ID and makesure that equals the table. So table. One,however it's not here, and I'm just goingto put selected row data order ID. So justlike that, so we're literally going to searchfor a match By order ID and we're going tofind that order ID and delete the entire rowif it equals the Selected Order ID of thisrow. 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 roomon, and this is the row that would get deletedfrom our spreadsheet if I hit run. Okay, sothere we go. And, of course, this is great.But we don't want to run this manually, wewant to attach it to the actions. So I'm justgoing to get that and run a query, the queryI 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'sdelete this row. So I'm just going to clickhere. And great. So now if we look in here,that rows now deleted, and of course, we needto refresh this data. So on success of this,I want to add one more thing, and I want totrigger a query. And that query is to getto sales, order data again, and just clicksick. So let's go ahead and delete anotherrow. So this time, let's delete this row.And I'm just gonna click delete. So now thatis deleted, you'll just see that disappear.And tada, this table refreshes with the freshdata from this spreadsheet. Great. So we arenow adding data, we are deleting data. Thisis looking awesome. Now, we have one lastthing to do, and that is process a refund.So for this, I'm going to show you how todo this using the stripe API. So to do this,I'm actually just going to create a containerso that we can store our refund in here, sothat it looks a little bit nicer visually.So I've just gone ahead and created that.And now, let's also create another form thatwe're going to put in here. So I'm just goingto put in a form into here. So let's again,just find a form that we can use. Let's goahead and put that in here like so, let'sperhaps also change the button color. So Idon't really like this button. Let's changethe color. Let's make it some stripe colors,perhaps. So I'm just going to use the hexcode that I have. Here, just like so. Andthen I'm also going to make this button thefull width. And not only that, let's changewhat it says I'm going to say refund, andthen also notify as we want to send an emailnotifying the person that the refund has beenmade. So great, let's go ahead and just putin some text so you know what's happeningin here. I'm just going to drag that in here.And this should say, create, we found justlike so don't need the exclamation point.Now let's put in some text. And then we'lljust make a little note so people know toselect a row from the sales order orders torefund an amount okay, because we want topick out all the information from a row isselect just like we did with the Delete inorder to make our refund. Okay, so there wego. The next thing I want to populate, soI want to now populate fields by me just selectinga row so we don't have to do much manuallythis is going to be the charge ID that wewant to refund. So I'm just going to use thatwe also want the refund amount. So for thisI'm actually going to use a number input asthis has to be a number so I'm just goingto drag that in here like so and put the labelas refund amount that we decide and then let'salso have a refund reason for this I'm goingto actually have a select because I only wantthere to be three or so reasons that you canmake a refund on let's name this we find isin and let's populate the reasons so somereasons could be you know, let's go aheadwith did not we these are the values actuallysaid these the values we're gonna pick themup by so didn't like I'm just gonna make themstrings This one is going to be too late.And this one is going to be can. So thoseare the values we are going to use in orderto pick out our reasons. But now let's havealso a way to read them a little bit better.So we have the values, let's also perhapshave some display values. So I can actuallyjust clear this. And let's just test thisout. So test test. So now there we go. Soit's either using these or we can write ourown and my own are going to be does not likeit, the second one is going to be arrivedtoo late. And the third one is going to beI would like to cancel. So just like so. Sogreat. Now we have our select, let's carryon. The next thing I'm going to do is justput in a text of the refund summary. So I'mjust going to drag in some text here. Thisis just going to be a refund. Summary. Okay,so I'm just going to display a little summaryof what we are refunding and why. So I'm justgoing to dry that in here, some more text,oops, making sure that this actually is abit bigger. Okay, and in here, I'm just goingto put the refund amount. So a little summaryof what we wrote above. And I'm going to usenumber input one dot value to display that.Okay, so just like so that's my little summarythat I'm building, we found a map helps usput some of these in here. So we've got therefund amount. And then let's also have areason. So again, I'm just going to put areason. And of course, it's blank for now,because we haven't picked one, this is selectone, so I'm just going to replace this withselect one. But if I now choose one, likedoesn't like it, that will show up here, okayas the value. So there we go. That is sortof a recap. And of course, if I just put ina number here that will show up there too.And lastly, I just want to be able to senda message, along with you know the refunds.So what we're what we're going to email tothe customer. So once again, I'm just goingto perhaps make this a little bit bigger.Just like so and just stretch this out a littlebit. And this time, I'm just going to usea text input. So text input, we could usethe rich text editor, however, I'm just goingto use the text area as I don't want anythingcrazy. I just want a message like so that'llbe sent over and an email. But of course,if you'd like to use the rich text editorto that as an option. Okay, great. Now forthe message itself. Well, I want to use valuesfrom here. And what I can do is make an emailtext. So I can go ahead and just write myown Java script here. So I'm going to usea JavaScript transformer to do this. And I'mjust going to use this to write some javascript.So for example, if I get the order ID, sowhat is the order ID? Well, we know that theorder ID is whatever row we've selected fromthe table. So I'm going to use selected rowdata order ID, and save that as the orderID. The other thing that I want to save isthe order amount. So let reef fund amountequal and this time, I'm just going to usethe number input one. So number input, onevalue, just like so. And now we can constructour message which is simply going to say doorder and then the order number and then Iwent ahead and made the refund of X amountyour card. If there's anything else you cando, please let me know. Okay, so that's allI'm going to write. So let's go ahead anddo it. Now, let's also write different thingsfor different reasons that we chose for therefund. So in fact, if I go let opening blurbequal And then I'm just going to use a switchcase. So I'm just going to make that a littlebit bigger for you, I'm going to switch outthe reason. So I'm gonna go select one dotvalue and switch it out. So if the reasonis too late, well, then the opening blurb,Nurb. Making sure to spell exactly as it'swritten up, that is going to be I'm makingsure it's back six, actually, I'm so sorryabout your order. In being late, I went aheadand refunded the value to your cart. And ofcourse, replacing this with the refund amount.So I'm simply going to do refunded and thenback tick plus refunded amount, plus and thenthe string again. Okay? So that's for thecase too late, make sure this is select one,okay, because we are using the value fromhere, select one. So that is one case, essentially.Okay, so there we go. And then let's put abreak. The next case that we're going to coveris the digit like, so making sure to spellit exactly the same as we did here. So didn'tlike did like, just like that didn't like,and what happens with this case? Well, let'sagain, change the opening blurbs or openingblurb to be backtick. I'm so sorry, you didnot like your order. So that's all I'm goingto do for this one, making sure to put a fullstop 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, theopening Blub this time it's going to be changedto making sure that is a capital B. I fullycancelled your order as you requested to fullstop. Great. So that will change the openingblob. Now what do we want to return? Well,let's just return order and then pass throughthe 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 IDand then let's also add a nother string tothis. This time I'm going to put in the Ohopening learn as it is after the changes withthis switch case, I went ahead and refundedand then we'll put in the refund amount foryou have also defined at the top refund amountto your card. And then let's finish it withbest wishes. really tall. But and save that.And let's rename this as email, text justlike so. Did email so there we go email text.And in here Well, I'm just going to replacethe default value with email, text dot value.Don't forget to put in the breaks betweeneach of the cases as well. So break and brake.Also case should not have the double dotshere. So just get rid of those. And therewe go. Let's try change the reason And great,that is updating as it should. Now I do wantthis to appear on separate lines. So I cansimply 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 nospaces 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 seeof course change the reason again, would liketo cancel, that text should now update to.And it's also updating with the refund amount,and the Order ID that we have selected. Sothis is looking great, we've got the textshowing up, this is looking good, I'm justgoing to make this a little bit bigger still,so that we can read the whole text when it'slonger. So just like that, wonderful. So thisis looking good. The next thing I need todo is just connect this to the stripe APIto process the refund amount on to the chargeID. So if I just select this, well, the chargeID, we need to actually show that in heretoo. So I'm just gonna get a nother text taglike so. And this is just gonna display theselected row. So once again, I'm going toget the table one selected row data. And I'mjust going to get the charge ID. So that shouldnow show up for us on whatever when we clickthe charge ID is the same as we are just usingstripes tests API, but just know that thatwill change. Okay, if it was different. Okay,so to recap, we have just used a transformerhere, I have used a JavaScript transformeras because we have three different messages,we want to send one for each drop down value,we to create a dynamic text field value thatwill populate with the correct message. Thisis a perfect use case for a transformer. Sogreat, we are collecting all the data nowto actually make a stripe refund. So for this,I'm actually going to create a new resourcequery. And this time, well, I want to createa new resource. And this time, I'm going toselect stripe. So let's call this CRM striperesource, 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 documentationthat we need in order to create a refund.So sign up to stripe gave your API key. Andthen we are essentially going to use thisto create a refund. And we're also going touse some test charges. Okay. So here is mystripe dashboard, I'm just going to sign in.Just like so with my email address that usedto sign up before. And on your dashboard,you will see some test data that you can usein order to test your API. So here, let'sgo to the dashboard. Here is the home page.And I can use the test data. Okay, in orderto process a refund, just to see it's workinghere is my key as well. So I'm just goingto copy that and put it in here. Please dogo ahead and use your own as this will notwork, I will be deleting this, and just createresource, just like so. So there we go, I'venow connected my stripe API to my stripe dashboardusing my secret key. Now let's go ahead andcreate a refund. So what I want to do hereis I essentially want to run a query whenmanually triggered, and I want to post thisto refunds, okay, so it's a post request.There we go. And in here, well, the Stripeaccount, I'm gonna leave that blank and leavethis blank to the amount where we know thatthis is going to be the number input one.So number input one dot value, because that'sthe amount I want to refund and the chargewe also know what the charge is. I can actuallyjust take it from here. So text 17 value,text 17 value, you could have also taken itfrom table data selected row charge ID, it'sup to you. I just think this is perhaps alittle bit more foolproof as we're actuallyseeing the charge ID here. So great, thisis looking good. Let's save that. And thenlet's rename this to post refund. So justlike so, and just hit preview, and there wego, you will see the response of my refund,you will see that refund has been done, wehave refunded the amount one, okay. And ifyou go to your dashboard, and just go to payments,so this is my test data, you will see allyour refunds done here at the moment, it wasjust a preview. Okay, so that is how you wouldsee it. And to get your charge ID, all you'dhave to do is create a payment. And then let'ssay we have a payment for 20 pounds. And thenwe'd have to just input the card information.So if I google stripe, test cards, here wego, this is exactly what I need. And I'm goingto make a fake payment. So let's just go aheadand use these test cards, I'm going to copythis. And I'm going to essentially make afake payment using this fake card data, itsays to use any three digits, so let's justgo ahead and do that as well as put any futuredate. 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 submitpayment. Okay, so we've just created a fakepayment on our dashboard. There we go. Okay,so here is our payment. And if I want to getthe charge ID as we need to get the chargeID in order to make a refund. Here is thecharge ID. So instead of just taking the chargeID that I have hard coded here, please goahead and essentially replace this with yourown charge IDs here. So just like so. Andjust literally just replaced that with allthe charge IDs I have here all the way tothe bottom, so that you can put your own chargeID in order to make refunds. So there we go,I've just replaced all the charge IDs withmy own charge ID from the stripe API Dashboard,click save, refresh this page, so we get thenew charge IDs showing up. Okay, so therewe go, that is now updated with the chargeID that we just put in here. And now if Imake a refund, so instead of testing thisout, I'm actually going to run this. So atthe moment I'm refunding the month zero, let'sgo ahead and make the refund amount one andclick Run. There we go, we have now refundedone to the 20 pound charge that we have here.And we look at this. So let's go ahead andlook at all the payments again, you will seea partial refund of one was made on to thispayment. So we have just partially refundedthis charge right here. So hopefully thatmakes sense. Please watch this a few times.If it does not make sure to replace all thisdata here with your own charge that you createdon the stripe dashboard. Okay, great. So weare processing refunds from this dashboard.Now, let's carry on. The next thing that Iwant to do is actually notify us when we createthe refund. So the refunds just been madeby also want to send an email. So I'm goingto do that next. For this, we are going tointegrate with Google Mail. So hopefully youdo have Google Mail. If you don't, you canuse the SendGrid API to however, this is justmy personal preference, but it is up to youwhichever one you choose. So to do this, I'mactually going to create a new query again.So just here, and this time, we're going tocreate a new resource and choose the ES TPAPI. Okay, so that is the one I want to use.Let's go ahead and call this G Gmail likeso. The host, the host is going to be SMTPdot g gmail.com. Just like so as we are usingGmail, the port is going to be 465 the usernameyou just have to literally use your own emailaddress for this. So I'm going to put Aniaat Free Code camp.org And the password isactually the password for your email address.So I'm going to go ahead and do that. putin my actual password in here, and just testthe connection. Okay, so my connection wasa success. So I'm going to create this resource.However, if you are having issues, you mightneed to do some extra configuration on yourGmail. So we might have to go into actualGmail accounts, I'm going to show you howto do this, this is something that I had todo in order to get this working and in yoursettings with fording and pop, I am a P, justmake 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 enableall apps like so. Okay, so you're sort oflowering, I guess, your security settingsand allowing third parties to use your emailon your behalf. Okay, great. So that is somethingthat you need to do in order to get this done.Hopefully, that works after you've enabledthose three things. And now we can get tohooking this up so that it sends an emailwhen we press the button. So let's continuewith this our query, well, let's use the Gmailquery just made the from email, we're goingto do Ania at free, code camp.org. The toemail, I'm just going to hard code this fornow to Kubo ania@gmail.com. And then the subjectis going to be your refund has gone through.And then I'm just going to get the whole textarea 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 Igot an email saying My Refund has gone through,okay, from Ania at Freako camp.com. So thisis looking good, I'm really happy with howthis is looking so far. Let's carry on. Ofcourse, we can also have an input for theemail that is completely up to you. In fact,I would recommend it. So just go ahead andjust drag an input tag. So for example, onceagain, I would do so like this, let's justgo ahead and put you know, email address thatwe want to send this to just drag it out likeso. And let's put the label as email address.And this just means that I'm going to gettext input 12 This time, text input 12 dotvalue. 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 makinga refund, let's just hook up this button becausewe 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 renamedto send email just for readability. So weare triggering that. But we're also goingto trigger the Send Email query to so twothings are being triggered. So this is lookinggreat. I'm really happy with this, I'm goingto do one last thing before moving on to usethe postgres approach. And that is just showyou how to use mapping in order to use thetwo databases. So for example, if I want touse the employee ID to show me the employeename. Okay, so I'm going to get the threeI'm going to look in here and I'm going toget the name I'm going to show you how todo this now. So let's go ahead and just createan overview of an order. So I'm going to dragthis in here like so. And let's call thisof view of order number. And I'm just goingto put in the order number. So it's goingto be the selected row this time. So I'm justgoing to go into table one selected row data,order d. Making sure number is here, justget rid of that. So we're going to show theorder number. And once again, I'm just goingto drive through some explain a text explainto the user what is happening here. Selectrow 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 toget some text here. And this is going to havethe customer name. Let's also do it for theship address. Let's also have the employeename. And then one last thing I'm going todo is just use this little cool thing. Soif I just go down here, there's so many thingsyou can use, of course, I'm going to use atimeline. So I'm going to use a timeline todisplay essentially the order details andhow far it's gone, and so on. So I'm justgoing 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 thetext component again, and just put it in here.Because what I want to do is essentially lookin the table. And instead of having the customeras an ID, I want to show the name by lookingin here, finding the customer ID and returningback the name. Okay, so I could use it easily,all I'm going to do is just delete all ofthis. 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 datafrom it. And I'm going to pause through thetable once selected. Row data, Cust ID. Okay,so I'm just passing that through, I need tomake sure that this is an integer, so I'mgonna wrap it and pass int to make sure is,and I'm actually just going to minus one fromit. So that's what I'm passing through andpassing through the index. And I'm gettingback the contact name. Okay, because I'm goingin here I'm looking in here, I'm passing throughthe index, and I'm returning the contactsname. And the index is whatever this numberis minus one. Okay. So that is how you woulddo that. That is how you would essentiallyjoin data from two different tables. Let'sdo the same for the employee name this time.So just down here, it's essentially all thesame thing. However, this time, I'm goingto look in Table three, I believe it's tablethree, let's have a look, is this table. Thisis table five, I can use Table five, or Ican use table four, it's completely up tome, let's go ahead and use table four, whynot? So let's look in Table four. And thistime, let's get back the first name becausecontact name doesn't exist. So first name.And I'm going to also get the making surethere's not a custom ID, but this time, we'regoing to pass through the employer ID. Sowe're getting the first name. And I'm gonnacopy all this because I also want to get thesecond name. So there we go. First Name andLast Name. So this is looking good, we canshow that that's a space and wonderful. Sothat again, is how you would get data fromtwo different tables. And the last thing we'lldo is just show the shipping address. Thisis actually really easy because all I'm goingto do is go look in table one, which is themain table at selected row. Data ship address,okay, because I'm looking table one, whichis this table and just getting the ship address.So here's a nice little overview for us. Andof course, the orders. Well, this is again,easy because I am simply going to get ridof these. I'm going to use the curly bracesto go into table one. So the main table selectedrow, data order date. So we've got the orderdate. The next thing we need to do is I'mgoing to change this to be order created aswell. Order created. And then let's have orderrequired by. And then the final one will beorder shipped or shipped date, it's up toyou. So, shipped date just so people can seethe status of an order. So this required whyso that should be required dates. And thisshould be shipped date. Great. So now whateverorder 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 employeefour. If we click it, we will see that customerID 34 is shy Colin and the employee is yellpolet. Let's go ahead and click another one.Let's do this one. Yeah, tech gelita. AndSoufan buck is the employee. Okay, and ofcourse, these are updating to. So wonderful,this is looking so so good, just a few finalpieces of styling, and then we are ready tomove on. So first off, perhaps let's makethis a tiny bit bigger. So I'm just goingto use some markup to do this and put threehashes here, like so. And I will immediatelyturn a lot bigger like that. And then perhapsus also have another one here. And I'm notsure what should we should call this perhapsis 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 salesorders in here, because I think it's a littlebit obvious, I'm going to move this up a littlebit. So move up the button. Let's move thisdown a few smudges. Let's also put this inline with the other thing. And this let'scall this sales order. My man meant, okay,so I think that's looking a lot more obviousas to what that is. And then let's also makea little header. So here we go, I'm just gonnaselect the header and make the backgroundcolor. Let's make this blue just to keep everythingsort of the same color. And now let's alsoput in some text. So I'm just going to dragthis over into here. And once again, let'sjust use this markup, Sergeant for US store,just like so. And then perhaps this will supporte commerce. CRM. Okay, so just making it superclear what this is, you can of course, ecommerceCRM, you can do so much styling in here, it'shonestly completely up to you, I am just goingto do the bare minimum. But there's literallyhours and hours of styling that you can doto this, perhaps this actually made this oneline, I don't think it should be too. So therewe go. Just like so, we can also add a profile.So depending on who's logged in, they willhave a different profile here. So I'm goingto go ahead and choose an avatar and justdrag that in. So there I am. Okay, so andthat will be for whoever the user is, we canalso have various other things. So if youdo choose to build this out even farther,we can also have a burger menu, meaning thatif you want to add different pages, or ifyou know, you're going to start moving thingsout, for example, if you want the refund pageto be on its own page, you can also have anavigation bar here, which will show you differentmenu items. And that will take you to a notherpage. So again, like you could have the refundpage on its separate page if you want thatis an option for you, too. So there we go.I hope you've enjoyed this. Everything isnow connected, everything is working. We canissue refunds. We can send emails, we canadd new data to this table. If it doesn'texist already on our spreadsheet. For example,perhaps you want to put any manual order thatwasn't taken online. You can sift throughcustomers using the filter tools. You canlook through all your orders, you can lookat team members You can look at demographics,you can have an overview of how many ordersare shipped, how many orders, you've soldit for price, literally pretty much anythingyou could want. Okay, so this is done forpart one. Hopefully you've enjoyed this section,if you want to learn how to use Postgres withthis database that is coming up next. Okay,now it's part two, in which we switch ourdatabase to be a Postgres database. Okay,so to do this, I'm actually just going toimitate a Postgres database by putting insome data in here using the Manage Databaseapproach. So all I'm going to do is simplyget 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 SQLNorthwind, dot SQL, make sure you are in thepostgres SQL section, and just copy all ofthis. Or if you want, you can literally justget the file as I have here. And then in thisfile, before we get to switching this out,so as you can see, here, we are creating adatabase, we are then creating tables, socategory, region, territory, and of course,sales order. So these are all the tables thatwe see here. We are essentially creating thetables here and then importing the data. Butas you know, we also need to create anothercolumn. And that is with the charge ID, thisis your charge ID, this is the charge ID thatyou should get from your stripe dashboard,as you should remember, from before, so ifyou go back to your stripe dashboard, underpayments, this is the 20 pound payment thatwe made by creating payment here in the testdata section. And all you need to do is getthe charge ID. So you will see the chargeID here. It starts with ch. So get that andwe are going to create another column anda table and input the charge ID. So we'regoing to do so in here, let's search for thesales order table. So here we are creatingthe table and just after ship country, I'mjust going to add charge ID. Okay, and thenin here, well, sure, let's just make it thesame as the customer ID. So just put in that,like, so. Great. So we've done that. And nowlet's actually go to inputting the data. Sohere, you will see we are inserting the data,we of course need to add a another field inhere. So what I'm actually going to do isjust find anything that looks like this, andthen replace it with ship country, but thenalso add charge ID. Okay, so let's go aheadand replace all of those. So that is now added,I don't think we use ship country anywhereelse apart from in this sales order, whichis why I thought it was safe to do this. Weindeed don't, it seems to be only in thissection. And of course, we also need to inputthe charge ID. So this is going to take alittle bit longer, because we can't use thesame approach. There's different data beingadded into here all times. So I essentiallyneed to make a comma, and then just pastethe charge ID. So let's get this charge IDinto here, like so. So there really like Isaid, isn't an easy way to do this. Let'scopy this, because I'm just gonna, maybe ifwe select everything in here, that is a parenthesiswith a semicolon, then we can essentiallyinput the charge ID before this. So I'm justgoing to copy all of these, this will takea while. So just bear with me. This is notan easy process. But we have to do it in orderto get our data to look exactly the same asit does on your Google spreadsheet. So thisis great. Of course, all this data that wasgiven to us, it's important to learn how tostart working with huge datasets like this,especially when you go on to becoming a softwaredeveloper, because this is likely the amountof data you will work with in a small startupfor example, or a bigger startup, whateveryou are thinking to join. Okay, so we arenearly that at the bottom of the sales ordersthat we are inserting. And now all I'm goingto do is just delete like a comma. And thenI am going to paste it needs to do that. I'mjust going to paste the charge as a stringlike so. And then I'm also gonna just shutthat off like this. So I think this is lookingokay, hold on. We seem to have messed somethingup here. So we've got the postal code, wethen have the ship country. And then we havethe charge ID. Great. So I am happy with this.Let's say this. And now I'm literally goingto get all of this data again. So copy allof it, everything that we have just altered.So once again, we have added another columnto the sales order table. And we have justinserted the charge IDs into the table, too.So now Yes, I am just going to copy all ofthis, and then go here. And let's create anew 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 intohere. So I'm going to paste everything thatwe copied, this might slow down your computer,so just let it do its thing, because we aregoing to insert data into the Manage Database.Okay, so just go ahead and click Save, andjust wait for that to do its thing. Okay.And once that is done, let's go ahead andrename this, I'm going to rename this to adddata. That seems to be an error. So let'sjust go ahead and delete the first lines.Okay, so just delete this line. And just clicksave. We made the character type too long.So where we create the sales order, let'sjust find that again, we're going to increasethis VAR char, we can make it 14. So makesure that we are giving it enough space. Andclick Save. Great. So that is how we add datato our managed database. Now let's use itbecause at the moment, we essentially haveall this data, but that includes all the tables.So these right here, I just want to get onetable. And that's the sales orders in orderso that we can replace it. So we can use thepostgres database on the main table righthere. So let's go ahead and create a new resourcehere. So resource query, and make sure thatis on the Manage Database. And I'm just goingto select all, because that's how you'd getall from sales order. Okay, so just like wewrote it in here, Sales Order has to be exactlythe same as sales order in here. And thenlet's click Preview. And there we go, we aregetting the table along with a charge ID thatwe have just added. Let's go ahead and renamethis 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 differentto the other get data query. So there we go.That's what I've saved as. And now I can simplyget this table and replace this with thisquery. Okay, so now we are using the postgresdata instead of the spreadsheet data. So thatwas super easy. We go, let's carry on. Sonow let's get the customer data. So again,I'm just going to make a new query. And thistime, let's call thisget customer data, Postgres SQL. And again,we're just going to do select all from thistime, we're going to go from the customertable. So just like so save and run. And thistime, 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 thatit's felt exactly the same. So let's justgo 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 willfix that in a bit. Let's just make sure wedo all of these first. Next we need to getthe order details. So again, new resourcequery. Let's call this get order. Details,PG SQL. And this time, I'm going to do selectall from Order Detail. Okay, and save andrun. So this is all looking good. Let's goahead and replace this with get Order Detailpeih Gee e g SQL same for in here get salesorder data, P G SQL. However, this time, weneed to go into the data. And we actuallyneed to get into the Shipped date, okay, andthen filter by order, or you can filter byship date, you can of course, rename thisto whatever you wish. So perhaps it shoulddo ship date. So for each item that exists,let's call it a shipping date. Okay, so therewe go. And then I'm just going to get thesales order data data. This time, I'm goingto go by order ID length. This is just becauseour tables are a bit different. Now, if wego into here, I will show you this. So allI would do is simply go into here and findthe query that I am making. So for example,that sales order data, once again, here'sour 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 theShipped date. Okay, so that is the Shippedaddress where the ship date ship date. AndI will just filter out by these. So it wasjust a little bit of a different structurethat you have to be aware of. But you canalso have a look in here, if you ever getstuck. So great. And this time again, we arenot filtering out by if it doesn't have anempty 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 getthe order details. Postgres SQL data willwant to go into the discount this time. Andthen we're going to filter by the discountitself. So discount. Discount. And if it doesnot equals 0.00, actually, we should do, thenwe'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 alllooking good. These should be okay, becausewe are working by table on some of these,so that's fine. However, this one again, thisshould be updated. So get customer order data,Postgres SQL data. And this time, we needto get by the customer ID, because that iswhat we want. We all know exactly how manycustomers and once again for this, so getsales order data, Postgres SQL data, and let'sagain, do it by order it. Okay, wonderful.So I think this should be nearly it's justa few more to go. Let's go ahead and updatethese right here. So again, get sales orderdata, Postgres SQL. And this time, I can actuallyjust select the customer ID here and hidethe order. Making sure this is also by employeeID. So that is now back to normal. And thisone, well, we need to get the employee data.So let's go ahead and do that. Let's makea new query. And let's call this get employeedata, Postgres SQL again. And this time, we'regoing to do select all from employ. He justlike so and save and run. And then once again,it's just a case of replacing this. So getemployee data, P G SQL. Great and the samefor here. So I'm just going to add PG SQL.And same for the demographic. So customerdata, PG SQL. This one however, we also needto do some additional things. So let's goahead and just make sure that this is by country.So then we will go to country and customerID. So wonderful. And like I said, these arethe last things we need to do. So in thisone, well, essentially, we just need to getthe, we don't need to get the contact nameanymore. So we just need to do that, becausewe 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 thenwe are going into the first name array andjust getting the value. So we don't need togo into any objects and the same for here.Last name. And then we can get rid of thislike so. Okay, we have one last thing to do,in fact, two more things, because we alsoneed to delete data. So I'm going to showyou how to do that, again, we're going tomake a new query. And this time, I'm goingto delete a sales row. Again, I'm just goingto add PG SQL. So it's clear how we're goingto do this. So this is not get this is delete,I'm going to do a delete a sales row. Andthis time, I'm going to write delete, fromSales Order tables. So let's get the salesorder table where order ID equals, and thenI'm going to get the main table. So tableone selected row. Data order ID. So this ishow you would essentially delete a row a completerow. Okay, so I'm gonna save that. And nowI'm going to get this query because we'regonna have to replace it in the actions. Sowhere we have actions here, don't forget thatwe do need to change this. So let's triggera different query now. And it's going to bedelete sales row. So that is updated. Andone last thing we need to do is the Add Row.What do we seem to have done something wrongin here? No, that's the other one. So deletesales row is now added to here to the action,let's just double check it is the correctone. So delete, and yes, delete cells rowis 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'scall this ad, sales, row, Postgres SQL. Andnow, to add a sales row, well, essentially,we are going to have to So I already havepre written this for us, I'm just going toshow you this. This is how we would do this.So here we go. We are essentially just copyinghow we did it when we inserted sales roseinto here. So sales order, like so we havesimply copied this. And instead of inputtingthe hard coded data, we are using data fromour macro. So let's just double check thisis all right, as something seems to not beliking this the order ID, well that is textinput one value, so let's change that thecustomer ID that is going to be text inputtwo. So let's change that text input to justlike so. The employee id is going to be textinputs three, text, input, three. The orderdate is date time two. So let's change that,which makes this date time one for the requireddate. The ship date, well that is just datefrom what I remember date one and then theshipper ID perhaps let's make this a littlebit smaller shipper ID is text input 11. Solet's change that. The fright is text inputnine. The ship name is text input 10 And thenthe last ones is ship address. So once again,I'm just gonna make the smallest we can allsee it ship address is text input toship city text input six ship region is textinput eight postal code is a four and thenwe just have ship country which is a five.And of course, the charge ID, which we'regonna hard code as this. That's right, solet's just go ahead and delete all this, I'mjust going to put an empty string, like so.And save that. Okay, so there's our ad salesrow, which means now, that's makes sure thaton this submit, we make the right query, sosubmits, I want to run the query ad salesorder, PG SQL instead. And great. So let'srun that. and wonderful. So cool. This islooking good. I believe that's it, I believewe have now replaced everything that we need.I hope you've enjoyed this tutorial, pleasedo let me know if there's anything that youwould want me to add to this, I will be morethan happy to do that. Hopefully, you've learneda lot in this. And you can really take thisand make things your own. One last thing thatwe can do is of course filter as well. SoI'm actually going to show you how to do thatsuper quick. So this time, I'm actually justgoing to make a text input. And this is howwe're going to query data from the sales order.So for example, if we have this here, andwe can such, I'm just gonna write a supersimple query for this. So let's go ahead anddo that now. So I'm just gonna click here,resource query. Let's call this search, salesorders, PG SQL. And in here, I'm going toselect allfrom sales order, where now we're going toget the text input 13. So text input 13 dotvalue, or order ID equals and then we're goingto get just put parse int and pass throughthe text input 13. So text, input that indot value, so this should be where it doesn't.So just put a bang there. Okay, so now I shouldbe able to filter let's just save this. Andthat should just run every time we changethe value in here. So if I just want to searchby order, I can do so like that. And thenI'll just show me the search for data in here.Now to update this in here. Well, we're gonnahave to change this up. So let's just switchthis out to searched search sales orders instead.So get sales orders, such sales orders. Justlike that. Okay, so now it's hooked up, andI can make the table listen out to searchfor any odor that I want. Wonderful. So hopefullythat makes sense. I hope you've enjoyed thistutorial. Again, if there's anything I'vemissed, please do let me know this has beena long one but I hope you've learned a lotand I can't wait to see you in some futuretutorials to thanks so much again, and I'llsee you soon.\n"