How to Publish MS Access Application To Web Using Weebly and Caspio

hello and welcome to today's video session in today's class I'm going to teach you how to take your existing access database application and bring it online as you can imagine as powerful as access is one of the biggest challenges is bringing this data to the web and if you're running a business and want to stay competitive ideally it would be nice if we can log-in maybe using a mobile device such as an iPhone or an iPad and be able to access the data online and all of our forms and all of the reports that we have in a locally stored Access database so that's what I'm going to teach you how to do today we're going to be using a company called caspia caspia is a database application framework that allows technical and non-technical users the ability to quickly build applications forms reports table foundations all of the relationships and within just a couple of days maybe even a couple of hours you can rebuild all of your necessary functionality to have all of the workflows available to you online to build our front end which is all of our navigation links and content we're going to be using Weebly Weebly just like Castillo is an easy way to build a website so if you've never built a website in the past Weebly makes it very very easy to create all of your pages your logo your navigation menus that way you can be up and running with your website in literally just a couple of minutes depending on how much content you wish to put onto your website let me show you my access application first that way you understand the schema and what exactly we're going to be recreating today so my access application is a very simple app it's to manage customer orders we have a couple of tables we have a couple of forms some queries and also one of the reports that I have created now in this existing access application I have a list of all of my customers I have a table that lists all of my products that were selling I also have an order table that's going to store all of the orders pertaining to our customers as you can see I have a primary key here customer ID I have a product ID also is a primary key and my order table is simply going to stamp those IDs inside it along with creating a new primary key for each one of the orders I'm also storing date submitted or date ordered quantity status which is going to be pulling the information from a status lookup table and here are my two look-up tables I have category what category are products fall under we have the status so we can save the order is new cancel shipped or in progress I have a simple query created here which is a combination of three tables customers products and orders and I'm basically merging those three tables together so that I can see all the data that I wish to see inside this query and I have three forms I have an input form to add new customer I have the order form so that I can submit an order for each one of the customers and all I'm doing here is creating a simple drop-down that Lix looks into my customer table and I stamp the customer ID in the order table same thing with the product ID and then we have date ordered quantity and of course we have the status that looks into our lookup table for status I also have a form that adds new product to the products table so if we're selling a new item we can just create that product here and last but not least I have my relationship set up just that the UI you guys can understand the structure and the foundation on how this is related so we have the orders table that links back into the customer table so customer can have many orders I have the products table and then we have the two simple look-up tables that link into my products table and also our orders table now let me quickly show you a live example because I have already replicated this application via the web I want you to see the comparison between the access application that I have on my local computer and what it's going to look like when it's finally completed so here is my live example this is my Weebly website and all of the forms and reports that you're about to see were built using the caspia platform so let's start with a new customer form so as you can see this is my new customer form it's very similar to my local access form that I have so if I open up my application again and I go to add new customer you can see the same labels and same fields the difference is that this form is sitting on my computer whereas this form is on the web and we can interact with that form as long as we have access to this URL I also have the ability to add new product which is just like the form that we have in Access database I can select my category using this drop down if I go back to my access application add new product you can see that I can select my category look up I can also manage customers so I created a report that allows me to search and filter my customers based on some criteria and then when I click search I am able to see my results of all the customers from within the database here I can actually have some editing options to be able to edit my data for example grid edit allows me to access each cell and make modifications I can also perform a mass update so if I select a couple of records here and click on edit I can now change the particular field that I want to have the mass update on I can also go to details here of a specific record and drill into details if I only want to edit this fields for that specific item here is my new order form which looks just like my access form so I can select my customer I can select my product I can say date ordered quantity and also the order status now I've also created two additional reports that I don't have in my Access database gasp you allows you to create very very robust reports so that you can visualize and see the metrics on your data so you can run some comparisons and analyze what you have so let's take a look at my report that shows a few orders again I have the ability to search and what I'm doing on the results page here is I'm grouping my data based on month I have all the customer information I have the product at the customer bot the quantity the cost for that specific product and then we're doing a very simple calculation here to take the cost times the quantity to give me the total I also have the grand total at the bottom so it tallies up the sum of all of my totals here on the right hand side so it's a nice report that gives me an idea what kind of sales were making for a given month and then I also have a grand total at the bottom so that we can see the performance of revenue and how we're doing in terms of sales okay my second report that I have is a pivot table and I created this report to generate the revenue in terms of what product is bringing in the most the most revenue so we have the products here on the left hand side we have the months on the top and then for example this Dell computer I can see that we didn't make any sales in March in April but however we had a big sale for the month of May same applies to all the other products that we have we have a grand total here at the bottom for each month and of course we have the grand total here across the aggregate of all of our figures here in the middle so this pivot table is very easy to build in Casio it takes just a few minutes you would normally build it just like you wouldn't Excel or some other similar platform that you have and I will be showing you all of that in today's class okay so let's go ahead and log into our Caspi account now and let me show you how we can quickly put together this application that we have in access and bring it to the web once you're logged inside your Castillo account you will be taken to the home page of your Castillo bridge platform this is where you're going to be able to see the list of all the applications that you have built as long as you have had your Castillo account now Castillo is an enterprise platform that allows you to build multiple applications for different departments different clients if eventually you want to become a reseller so there is a lot of ways that you can leverage the caspia platform and once you're logged inside your account and in this example I'm using a copy of 14-day trial account you're going to be able to see a sample application that you can explore but if you're ready to build your own application you can simply click on the new app link here at the top and from here you have two options on how you can begin you can start by importing data if you have some data in your Access database Excel spreadsheets XML or text however if you just want to build the application from scratch if you're not importing any data you can begin by building a blank application now in our example we have an Access database application so we're going to import the data and the structure into Castillo so we can save on time from having to rebuild all of the tables and all of the relationships and this is a very common misconception people usually tend to think that you can import your entire Access database including the forms and reports and that's not the case however you can import your tables your data and all of the relationships and that's going to save you a tremendous amount of time when it comes to building all the functionality later on once you have the tables and the foundation imported then it's very easy to build your forms and reports and eventually embed them into a web page now let me show you how we can import our tables and data so we're going to begin this way and let's give our application a name we're going to simply call it manage customer orders and let's locate the file in our computer so I have my Access database file saved on my desktop here it is I'm going to hit next and you'll notice what's happening behind the scenes cache view is creating the application container and it's in the midst of uploading that data file so let's click on next and you can go ahead and remove this file this is simply a copy a backup copy of your customer table here are all the tables that we wish to import from our Access database and what you want to do under the action column is you want to import as create new because that's going to allow you to create a brand new database table in your Castillo account if you already have these tables imported you can use a different action here to replace an existing table to append new data so if you're making changes offline your local access application and you simply want to append or attach those new records to your table you can use the append action and then we also have update design if you're simply just adding new columns to your table so let's keep the names as is and we're going to hit next and on the second screen before we finally import our file you can toggle back and forth between your tables here's our master table that contains all of the customers and you can see that kospi is going to show you a sample of your data set and for each table you're going to be able to see the original field name you can modify the field name if you'd like to rename it to something else here you also have the ability to include or exclude which column from that table you wish to import and finally you can change the data type of each one of your fields from any one of these options that we have now caspia is usually smart enough to recognize this for you automatically but from time to time you might have to make the manual changes here and click on import and don't worry if you miss a step here even if you forgot to change let's say the last name to a specific data type you can always make these changes even after you import the file ok so after you've gone through each one of your tables just to verify that everything is correct you're going to click on import and this should only take a few seconds we don't have very many records inside our database alright so you can see that all the tables were imported successfully we're going to close this screen now and this is going to take me directly into the framework to see all the objects here on the left hand side for this specific application called manage customer orders the way I got to this screen if I go back to the home page where I see the list of all of my applications all you have to do is simply click on open for this app and then you're going to be able to see those objects again now the overview screen is mostly informational this is for you to keep track of your progress and to store all of your notes just so that you can make sure you on the right track and you're progressing correctly by adding your notes and what you need to build next in terms of the functionality for example I can add a quick note here to say ok we can save that note and then we can keep adding notes again on the overview screen now to view the tables that were imported from Access you're going to go to the tables object and here you're going to be able to find all five of those imported tables now you can access each table either by clicking on open to view the datasheet mode or if you'd like to modify the fields and the data types you can click on design so let's take a look at my customer table and right away you're going to be able to see all of that data now if you wish to modify the fields you can go to table design and from here you can introduce new fields you can remove fields you can rename fields to something else and of course you have the ability to change the data type once your data is imported let's go back to the tables menu and I want to show you the screen called relationships this just like an Access database we can click on this link here and I can include all of my tables just like in Access let's include all the tables in our view and from here all you have to do is just rearrange the tables so that you can see the schema and the foundation according to your own preference I'm going to try to match this almost exactly like I have it in my access application so I'm going to move my orders table in the middle let's put the products table here then I'll move the customers table up here the order status was down here and then I have the category lookup which I'm going to move right over here so let me just rearrange this a little bit more just so that you can see a better visual and now in my Castillo application I can see the same exact Foundation and structure of all of my tables that way I understand what's linked to what and this gives me a really nice view of all of my relationships if I compare this with my access application that I currently have here is my relationship screen you can see the same exact thing here that I have in my access application let's go back to Castillo and now what I'm going to show you next is now that we have all of our data imported and all of the tables let's take a look and see how easy it is to recreate those forms and reports and we can be up and running with this application in just a matter of minutes let's begin by building a simple input form that allows us to add new customers into the customers table to build all of your forms and all of your reports that you will need for the functionality and the workflows you will need to go to the data pages object data pages is a Castillo term I also like to call them widgets because once you build them Caspi is going to provide a snippet of code that you're going to copy and paste into your own website builder it's just like embedding a YouTube video or one of those Facebook like buttons that you see almost on every website and you will see in just a few minutes how we can build a form and embed it into our Weebly website so to build a new data page all you have to do is click on this link at the top and this is going to launch gasp you spawn and click data page wizard by default the submission form is always going to be highlighted every time you launch the data page wizard but here you have the ability to select and create from various types of application interfaces that you will need for your own project so in this case I need to build a submission form so I'm going to select that data page type I'm going to click on next and using this data source drop-down this is how you're going to link your table to the data page that you're creating ok so again we want to build a form that inputs data into the customer table so that my customers table then needs to be my data source let's give this data page a name let's call it add new customer from here you can apply a different style this is for the aesthetics the look and feel of your form so if you wanted to change the color of the labels the color of the fields you can easily go to the styles object and make those modifications if you need to match the look and feel of your brand the localization allows you to change the language and the regional settings of all of your data pages for now let's go to click Next and here are all of your database table fields from that customers table so let's include them all into our form by clicking on the double arrow hit next one more time and then once you reach the screen configure its properties this means that you can select each field on the left hand side and you can make modifications on the right okay so now if I go to first name if I would like to make that field required I will simply click on this checkbox and now you cannot submit the form without first filling in that field if you want to see what this form looks like at this point you can preview and this is going to give you a good idea what the form looks like at this point if you wish to make this field its time stamp so the user doesn't actually see this pop up we can go back to the wizard select the date and time field and using the form element drop-down you have a plethora ways on how you can modify each one of your fields so I can turn this field into a radio button checkbox drop-down but I have also a special element here called time stamp it's automatically going to make that field hidden but in my table it's going to stamp the date and time of the submission so we can preview again and you're going to be able to see that we no longer see that date and time field now you can further customize the fields if you'd like but to speed up on time I am simply going to click finish to save all of my changes and here is your very first form and in literally just a few minutes we were able to recreate this form by pointing and clicking and making a few adjustments and finally clicking on finish to embed this form into our website now you can see a link called deploy the first thing you're going to do is enable access and cache view is going to give you five deployment methods this is the most popular method the embed model all you have to do is copy this snippet of code and paste that code into your site builder and then you're going to see that form embed into your website now I'm using Weebly I am already logged inside my account and I'm going to show you how you can navigate Weebly a little bit that way you have a quick start and a quick jumpstart on how you can build your website the first thing you're going to do is choose a theme for your website on how you want things to be laid out then you're going to create all of your different pages these all the different pages that I created from my application and using the build tab you can now insert all of these little widgets into the canvas screen to lay things out in position where you want the content to be on your website now I want to access my new customer page so I'm going to click on that link and I'm going to drag over the embed code into my canvas and by simply double-clicking on it I can now paste my caspia code and if you would like to see what this looks like on your website all you have to do is click on publish now and then Weebly is going to generate this URL for you and notice that my Weebly account is a free account that I have which includes weebly.com subdomain if you wish to remove the da tweet dot Weebly then you can purchase Weebly for a monthly fee and then you can have your own custom domain URL but a nice thing about Weebly is they give you a free account if you wish to choose to have their branding here in the URL so let's click on the link now and let's go to that page and you're going to see now how that form is seamlessly embedded in your own website and from here we just have to complete our application by adding and creating all of the other data pages and simply embedding them into the rest of our Weebly web pages alright so let's go back to Castillo now let's close this screen now that I have my form embedded and let's go ahead and now create our second form to add new product okay just to give you an example what I mean by that we want to create a form that allows me to add new product so then in caspia we're going to click on new data page to launch the wizard again we're going to select submission form because again the submission form inputs data into our tables hit next from the data source menu we're going to select our products table and let's give this a name add new product we're going to keep the same style and same localization hit next I want all of my fields in my form hit next one more time and from here what we can do is simply just modify the fields just like we did with the submission form to add new customer let's see if I want to make any changes here I do have a category lookup table so instead of this being a text field if I preview you can see that it's a text field however I want to turn this into a nice drop-down so then instead of a text field I'm going to simply select drop-down and you can either add your own custom values here or you can simply just look up a table and if you see here I have my category lookup table that I can select now what I want to list here is simply my category so I can view the actual category not the category ID okay for my description field right now it looks like a text field for description but we can turn this into a nice text area so instead of text view let's change that to a text area and then you have the ability to change the width and height according to your own preference if you need to see a bigger text area now if you're done here if you want to save your changes once again click finish and before I deploy it if I want to preview it I will just simply click on preview again and now I can look up my category and I have this nice text area that allows the users to add and see more characters as they're typing it let's close fear these preview screens and we're going to hit deploy again next to our add new product form and enable access and once again we're going to grab the code and copy it and in my Weebly website builder I'm going to access that web page I'm going to drag over the embed code and really quickly just paste my code save my changes publish now let's see what we have so far so now I have the ability to add new customer using this webform and I also have the ability to add new product using this form and once again we can select our category we have the description we can add the product name and also the cost for each product so hopefully you can see here right away how quickly Castillo allows us to build these forms so if you're worried about having to recreate these forms in Castillo don't be too concerned about that because caspia spawn and click wizard allows you to build these forms and reports very very quickly okay and in just a few minutes I'm going to show you how you can build a report that allows you to manage your customers ok so let's go back to our Castillo account and let's set up our third data page by clicking on the new link again and this time I don't wish to input data into my table okay we have two forms one that inputs new customers and one that inputs the products this time I want to build a report that allows me to view my data for my tables and Caspi is going to give you a couple of different layout and options that you can select from including the pivot table here which we're going to be building in a couple of minutes later in the video for now I simply want a tabular format here and if you look at the illustration below this is basically what you're building ok just use this as a reference so you have a better idea and better visual and what exactly you're building using this data page type so as you can see I'm building a search form the ability to view my records is a tabular format and the ability to drill into details so let's click Next here once again we're going to select our data source and I want to pull the data from my customers table because that's where all the information is inside the customers table let's give this data page a name we're going to call it view managed customers once again we're going to keep the same style and same localization hit next let's create a search form that allows the users to search based on some criteria hit next one more time and now what fields do you wish to use in the search form so let's search based on first name last name phone and email okay these are going to be my search fields of course you can include as many as you want that's completely up to you but just to speed up on time I'm going to have four fields I'm going to hit next and on this screen now you can configure properties of each one of your search fields by default every single field is going to be set up as a text field with comparison type equal I like to use contains as opposed to equal because if you leave it as equal that means you have to know the exact first name of the person that you're searching for but if you put it as contains it's more like a partial keyword type search so if you type in a letter it's going to return all the results that contain that letter now of course you have the ability here to change this form element into a different type of search form or search field so you can turn this into a drop-down list box we have some cascading options as well but for simplicity we're going to keep everything as contains now if you want to set up a more complex search form by creating and or or logical operators you can go to the logic tab here and you can fully customize how you want to search your results so if you wanted to search based on the first and last name or phone number and email you can configure by dragging these elements into your canvas screen and you can fully configure your own schema in terms of the search form okay so let's hit next now and now what fields you wish to use in the results page so after we click search I want to see the first name last name phone email and maybe a few other fields pertaining to my customers the rest of these fields we can include these in the details view let's hit next one more time and here do we want to have the ability to edit data on the website and this screen allows you to to make those changes so let's have inline edit this is just a precaution you know just to be careful on who you're allowing to edit the data on the web so let's hit OK create it and bulk edit inline delete in fact we're going to enable all of these options so that you can see what this looks like on the website I'm not going to make any changes to my results page although you have the ability here and flexibility to do so I'm not going to make any changes to my bulk edit fields I'm going to keep them all text fields let's show 25 records per page let's enable the details view and finally on the details view let's include all of our fields and on the configuration screen you now have the ability to keep all of the details page fields as display only in other words read only the users can't make any updates or you can simply just change from display only to any one of the four elements that Castillo provides so let's just say for the first and last name we're going to turn those into text fields the rest of these fields we're going to keep them as display only click finish to save your changes and in just a couple of minutes we have built a nice searchable report so let's go ahead and deploy this now enable access grab the caspia code once again go to your Weebly website and let's go to our manage customers page drag over the embed code and just like before we're just simply going to paste our Castillo snippet publish our website and I can probably close a few of these screens here let me just close this and now we have the ability to add new customer we have the ability to add new product and we also have the ability now to manage our customers so here are all of my search fields I can click search and this is going to show me all of my results that we wanted to display on the results page as you can see we have some editing capability here so I can enable grid edit I can access each one of my cells to make changes I can exit grid edit I can do a multi-select to do a mass update and we also have the ability to go to details to make our changes and the only ones that I've enabled so far are the first and last name now the beautiful thing about Castillo is that once you paste that code into your site builder you never really have to paste it again if you're making changes to your report so let's just say for example I wanted the phone number field to be editable as well right now it's display only I'm going to go to my report and click Edit now you can either click Next if you know where you're going or if you click on a double arrow you can quickly go to the configuration screen for the details page fields so that you don't have to hit next every single time and here's my phone number field it's currently display only I'm going to change that into a text field save my changes and you don't have to redeploy the code again all you have to do now is just refresh your website and you're going to be able to see those changes reflect automatically on your website as well so this is how quickly and easily you can put together your application using Castillo without having to code anything in Castillo it's just point-and-click using the data page wizard all right so let's continue building our application the next thing that I want to do is build a new order form so that we can submit new entries into our table of orders so let's go back to our Caspi account and click on new data page and if you've guessed this correctly kudos to you you we need a submission form here because we're going to be submitting new orders into our orders table we're going to go ahead and select our orders table because that's where we want the data to go and let's give this data page a name we'll call it new order form once again we'll keep the style and localization the same click Next and in this case I need all of my fields in my form hit next one more time here is my customer ID field okay so for what I want to do here is I actually want to create some kind of a lookup where I can see the customers name but in my orders table I want to stamp that as the customer ID itself okay so I'm going to turn this into a drop-down and I'm going to use either the lookup table if you select both this is going to this is going to give you custom values and lookup table so let's select both here and the first option that I would like to see is select customer make sure you delete the value because you don't want this to be stored in your table now the lookup table is we're going to select our customers table I want to display the customers last name and based on the last name I want to store the customer ID in my orders table now this is only showing me one particular attribute to my customer it's only showing me the last name however you can have multiple fields here so you do a cascading drop-down so based on the last name you can select the first name and then based on the first name you can select a company name and that's still going to give you the same effect where you stamp the customer ID in the orders table we're going to setup the same thing for our product ID so we're going to turn this into a drop-down both and I'll just say select product delete the value and in the lookup table we're going to go into our products table select the product name and automatically stamp the product ID in the orders table date order let's have a nice calendar pop up quantity is going to be my text field and order status this is going to be a lookup table so once again we're going to select drop-down we're going to do a lookup table and we have a order status lookup table that I can select from really quickly when done click on finish and here is our fourth data page let's deploy it by grabbing the code once again and inside our Weebly builder we're going to access our new ordered page drag the embed code so I'm just repeating the same steps as before we're going to paste the caspia code publish our website and let's go ahead and take a look and see all the stuff that we have so far I will delete this okay so let's go directly to that page and now you can see that I can select my customer I can select my product and I have the ability to set the status of that order select a date and the quantity ordered okay by the way the field that you see for order ID you can turn this into an auto number it doesn't have to be visible here on the form same thing applies to our new customer form notice how I'm exposing the customer ID you can hide this and turn this into an autumn auto number or some kind of a random ID that way you don't actually have to fill that in yourself manually it automatically generates that ID by changing the data type in the table you can accomplish that but I chose to keep I'm open just so that you guys can see how you can manually add the customer ID along with product ID and also the order ID now in this case what's going to happen if I select order ID let's say 10 and if I select my customer and I select this Dell i7 computer will input the date submitted quantity let's say 30 and we'll say this is a new order and hit submit once the form is submitted if you end up going to your table of all the orders now you are going to be able to see that latest order inside that table okay so here's the quantity 30 whereas stamping the customers ID we're stamping the product ID okay and I have the status set to new so this is the latest entry in my database table okay let's go back to data pages and what I want to build next is the ability to search and view the history of all of the orders so let's go back to our data page wizard click on new and this time we're going to select the report once again and I want the tabular format we're going to hit next and before I can actually build this I'm actually one step ahead here before I can build this report I need to be able to create a query that shows me the information from three of those tables that way it allows me to build my report based on that query because if I choose to build this report based on the orders table the orders table is just storing the IDS inside it so that's not really going to be very useful for me so let's go to cancel for now and to build a query in Castillo in the caspia platform that are called views and views allow you to filter data from your tables and also to join tables together using common values so let's go to views and click on new and this relationship name let's give it a name we'll call it customer orders of you and the three tables that I want to merge together are my customers table the orders table and the products table these are the three tables that I would like to link together so that I can have that query let's hit next and then Casspi automatically puts this together for you if you have created all of your relationships correctly and in the relationship screen where you see the visual schema if you've configured all of the one-to-many and one-to-one relationships Caspi is automatically going to make this joint for you and in this case it's an inner joint between these three tables okay we have the customer ID from the orders and customers table and we have the product ID from the orders table to the products table let's hit next and it's going to show you all the fields from all three tables here hit finish and this query or this view now is going to show me information from all three of those tables okay so it's an inner join it only shows me the matching records okay and now that I have my view built I can go back and build my report by clicking on new go to reports hit next and now instead of linking my datasource table I'm going to link my data source view that I just created and let's give this a name we're going to call this view history of all prior orders I can keep the same style same localization hit next let's have a search form and let's search based on first name and last name only but of course like I said before you can search on any one of the fields that you'd like and of course from here you can now choose from all three tables if you'd like to have criteria from all three of those tables just like before you can modify the comparison type and I'm going to set this to equal for both first and last name hit next one more time and now what information that we want to show on the results page so now you can get creative here on what you want to see so we're going to have the date of the order first since I can find it okay there it is I want the customers name maybe phone number I want the product title or the product name I also want the cost of the product and I think I want the quantity as well so let's include that too under the orders how many a specific item each customer ordered and I think that's all the information that I want to show let's click on next hit next one more time and let's enable some grouping here so let's group based on the date field so we're going to enable this check box and let's do a date roll up based on month so it shows me all of the grouping based on the month so that we can see all of the sales now in Casper here when you get to the results page you can do some fancy things with calculations I'm going to use this insert button to insert a calculated field and let's move that all the way down and let's call this let's give it a label of total and inside the screen you can actually get really really complex calculations put in here in order to perform complex calculations I'm going to do a very simple one for you today so that you can see by inserting my field quantity time's the cost of the product I'm going to format this column in currency so that we can see a nice dollar symbol of course you can change that to to a different one depending on the regional settings hit ok and I also want to insert totals in aggregation so at the very bottom of my report I can see the totals so let's insert totals and aggregation and I want to do that calculation based on the total column that I just created so we're going to move that to the right and we're going to apply the sum function so that we can see the total I also want to see the sum for every single month so we're going to go to the advanced tab and from here we can enable group level aggregation okay so let's see it take a look at the options here that we have and we're going to position the aggregation for Group group level aggregation none first group and let's just have the first group enabled here and let's keep that the way it is and hit next let's show 25 records per page once again we're going to hit next and for right now we can either enable a details page or disable it let's go ahead and disable the details page and hit finish and here's our fifth data page we're going to deploy this enable access grab the code once again go to our builder and access our view orders web page paste the code save our changes publish it okay so now I have the ability to search based on my customer information I can see the results and as you can see I'm grouping based on month and what you're seeing here is the customers name we're seeing the product name the cost the quantity and this gives me a grand total here so this dis amount times this amount gives me this and I see a grand total at the bottom which gives me a nice distribution and shows me the metrics in terms of month of March who had what revenue and who purchased how many items now there are plenty of ways to customize these reports like I mentioned throughout the class if you have some specific type of calculations they need to perform in your reports CASP you is very very flexible I'm not going to give you an example but I just wanted to show you here that if you are technical and if you understand SQL statements inside these fields you can actually add SQL where you're grabbing information from different tables to perform even a more complex and completely complicated calculation so to finish everything off we're going to build one last data page here which is the pivot table that shows me the revenue based on the product so we're going to build one last data page and let's go to reports once again and we're going to select our pivot table and the pivot table is simply going to be based on the orders table once again because that's where all the information is and we're going to go ahead and give this a name let's call it view or view revenue based on product once again you can have a search form if you'd like let's not build a search form this time let's just filter our data okay we're going to hit next and on this screen do you want to have any of your filtering fields included let's not include any filtering fields let's just show all the records and just like in Excel you're now going to see this wizard to modify your pivot table on what you want to see I've actually used the raw data source so it's a good thing that I caught myself here let's go back and what I need to use is the actual view that we just constructed because that view shows me all the information so you have to select that so let's go back to the configuration screen so in the column section what I want to see across the top I would like to be able to see the month so we need to go to the date ordered ok and then once again we want to group this based on month in my rows I want to show the product information so the product name so let's find the product name and finally here we want to include a total and aggregation so I have a calculated field and we're going to set this up the same way as before so we're simply going to say total and I want to include both of my cost of the product times the quantity ordered okay and let's take a look to see if there's anything else that needs to be done here so we have the sum we have just basically this calculation that I want to see and let's format this as a currency as before and when you're done you can click on finish and let's go to deploy our very last data page here so we're going to simply copy our code go to order summary and just like I've done a couple of other times in the past we're going to just grab our code publish our web page and let's go ahead and take a look and see what we have and there we have it so now you can see all the products on the left hand side we have the months on the top and we have the grand total here in the middle that shows me the summation of all of the products and the revenue of course if you had multiple months here you will be able to see them across the top and if you sold multiple products you will be able to see them here on the left hand side now one last thing that I wanted to show you here just to complete the whole application is to have a way to password protect our application because as of right now if I share this link with somebody all of these data pages are going to be public facing meaning they're not password protected okay and if you're putting your access application online one of the most important things if it's internal for internal purposes you want to somehow authenticate that using a login screen so let me show you in Casper you how to quickly accomplish that I'm going to go to my tables object and really quickly create a new table from scratch here and this table is going to contain all of the credentials for all of my internal employees that need to log in and view the data so I'm simply going to have the employee name I want the employees to log in using their email so I'm going to turn this into a unique field because you can only have one email per employee you can't have the same email twice inside this table and finally let's have a password field and we're going to turn that into a password type now in Casper you can have multi user level roles so you can have managers you can have admins employees but just to speed up on time I'm just going to create one user group here we're going to save our table and let's give this a name we're going to call it users and inside my users table I'm going to list just some demo information here so let's just have John Doe John @test calm and password can be tests if you have other users that need to login to the application you can list them here inside this table another nice thing about cast view is that you can have unlimited users logging in to your applications so once you have your table set up you're now going to go to the authentications object and to set up the authentication you're going to click on new and now from this drop down for data source you're going to select your users table because that's where all the credentials reside we're going to use the custom setup option and cache view is going to give you four ways on how you can validate your data so you can use the data source which is the table that we have you can use IDs services so if you want users to log in using Facebook Gmail or maybe an open ID account like Facebook or Twitter you can use a combination of both of them and sam'l which is not available on the trial account but if you have single sign-on through an Active Directory for example which is a way to log into Windows and you want to use the same credentials to log into the caspia Lapp location we have this feature available as well so today we're going to use recommended we're going to use the email and password azure to credential fields click on create and let's give this a name we're going to call user authentication okay and once you have this object created you can now easily apply that object to any data page that you wish to password protect now I want the password protect all six of my data pages so I'm just going to go through each one of them click on edit and really quickly just make these adjustments so access and security you're going to enable the checkbox and you're going to now apply that authentication that you just created using this object here so hit finish so the only data page that I'm password password protecting so far is my add new customer form so if we go back to our web page and if we go to add new customer you're going to see that login screen now on your application but if I go to add new product I don't have that password screen enabled yet so what I need to do is go through each one of my data pages and really quickly enable that checkbox so to speed up on time i have already enabled the authentication across all of my data pages by clicking on edit' and now when we go back to our website what you're going to see when you refresh is all of your data pages are now password-protected and in this example the only person that has access to this application is John Doe of course you can always go to your table and add additional users that you wish to grant access to the application I truly hope that you enjoy today's video you know we went through taking an existing local Access database application and using Castillo and Weebly we were able to recreate all of that functionality and have the application accessible via the web if you have any questions feel free to reach out to us directly by giving us a call or if you'd like to see some more in-depth tutorials and videos on how to use Castillo you can always visit our knowledgebase at how to Castillo comm thanks so much for your time if you like the video please like it and don't forget to subscribe for any of the latest videos tips and tricks on how to use Castillo for your projects thanks so much for your time and have a great day bye bye

Channel: CaspioInc

Views: 132,671

Rating: 4.867435 out of 5

Keywords: Access to Web, Publish Access Online, Publish Access to Web, Convert Access to Web, Online Database, Weebly, How to build a website, Microsoft Access to Web, MS Access Online, How to bring Access online, How to convert Access to a web based app, Excel to web, Caspio Bridge, Caspio, table relationships, web forms, access reports, MS Access forms, View Access Data Online

Id: 6M3rFbSySkY

Length: 55min 57sec (3357 seconds)

Published: Tue Mar 22 2016