Resource – Live Events- Embed DataPages Inside HTML Blocks Using iFrames

Video Synopsis

Speed-up the import and export of data from online repositories with scheduled tasks.

• Introduction
• Import and Export to Google Drive
• Pop-up Forms
• Import and Export to Dropbox Continued
• Send an Email Report
• Conclusion
• What is the Best Export Format?
• Can You Export Only Updates Since the Last Export?

Transcript

Ned Pajic: Hello, welcome everyone to another episode of Caspio labs. This is Ned from Caspio. Let’s get all of the quick housekeeping items out of the way before we begin today’s session. So if you can hear me, okay, please let me know in the chat window and we can proceed with today’s class. Once again, if you are able to hear me, please let me know in the chat window and we will continue. I just wanna make sure my audio’s working before I move on. So if you can hear me, please let me know. And we will begin. For those who are coming back to these live streams, welcome back. I do appreciate that, for those who are new to the live, the live streams, uh, we have these on weekly basis every Monday at 10:00 AM, California time, and we’re always introducing a new topic from Caspio and teaching you how to use the platform so that you can improve your knowledge and get better at building applications with the platform. All right, I’m not getting anybody to respond to the chat window. So please let me know. Just say something. If you are able to hear me and I will begin today’s session, as soon as I get confirmation from you, that you can hear me okay.

Let me send a chat here as well and loud and clear. All right King Kapo, welcome back. Thanks for the confirmation.

Introduction

All right, so let’s begin today’s class. Thanks so much for joining again, good to see many of you! Today, we’re gonna talk about how to import data into Caspio using scheduled tasks, our data hub feature, which allows us to speed up the import process of data and also to export data from Caspio. So if you’re using an online repositories, such as Dropbox or Google Drive or FTP, if you have your files inside, let’s say Google Drive, you’re gonna be able to send Caspio to Google drive securely, to bring that data into your Casio table automatically all behind the scenes. And you can set up a frequency how often you want Caspio to go to Google drive to bring that data. And today we’re gonna look at three different examples.

We’re gonna look at Dropbox, we’re gonna look at Google drive because that came requested last time in our live stream, and then also we’re gonna look at how to export data from Caspio via email so that a specific user or users can receive the email with the updates.

Import and Export to Google Drive

So let’s take a look at our first example, we’re gonna look at Google drive. I’ve already set everything up on my end. Let me just shrink the screen and show you what we’re gonna be doing here. So I set up an update inside my Caspio account, basically what Caspio is going to do, it’s gonna go to my Google Drive account, find the updated data and append that data to my existing table inside Caspio. Okay. And we’re gonna be looking at this Excel file here that I have offline. So that means every time I make a modification to my Excel file and I save my changes and I move that file inside my Google Drive folder, I downloaded it locally. You can run Google Drive from the browser directly. If you want to, or you can download it locally for your computer. And now every time you move your updated files into Google Drive, it’s gonna sync up and then Caspio is going to run that frequency depending on your setup. And now the updated data is going to be available inside your Caspio table as well. So let’s go to that application quickly. So here’s my Google drive application and here’s my table. Let’s open it.

And here’s my data. It’s the same exact data set as I have offline in my Google express, Google Excel file. So here it is. So what I’ll do offline, is I’ll make a few changes to my existing data. So let’s just say Rihanna becomes Sarah. Let’s say James Mann becomes James Wong. Okay. And let’s also maybe add one more customer to this Excel file. So we’ll say, we’ll just say maybe Steph Curry. Okay. That’s gonna be 59. And then we’ll say actually, I won’t even add an ID. Let’s just leave that empty. It should create one for me automatically. So we’ll say Steph Curry. Uh, we’ll say admin, I think this is the title cell.

Yeah. And then we’ll say company name, Company A, just some random data here. A we’ll save that file. Okay. Let me see if I need to create the ID for Steph. So let’s just create the ID. Let’s save that. So we have three changes. We have change Sarah, uh, Rihanna’s first name, um, James’s last name. And then we added one more customer to our Excel file. I saved my changes. I’ll move that file into my Google drive folder. I’ll replace it. Okay. And now back in Caspio, let’s first take a look at the task so you can see what that looks like.

So let’s say edit. So I’m importing data. Okay. And I would like to be able to make a change that is site, that I created update contacts, my file, path and name. Okay. So this is my Google drive folder and this is the file that I have inside of Google drive, which I just replaced just a moment ago. Okay. Now you don’t see on this screen is for me to be able to connect to Google drive. That’s the first step, because I’m already connected. I don’t have to do that, but here you would see authorized Google drive. There’s gonna be an additional button here for you to click on.

Uh, you can barely hear me. Uh, let me take a look if it’s on my end. I think I have my volume all the way up. Let me just try something here. Um, is everyone else having an issue with volume? Are you able to hear me? Okay. Uh, someone can hear fine. Okay. Tracy’s okay. Um, let me know if the volume changes, uh, I haven’t received any other, anybody else, um, mentioned anything about volume. So just let me know if it persists and I’ll take a look at my note issues coming in. Perfect. Okay.

Like I was saying, you would see an additional button here to allow Caspio, to gain access to your Google drive account, where you input your credentials. Okay. So once you input your credentials, then Caspio is gonna be able to securely go to your Google drive account, find those updates and update the Caspio table as well. Okay. So once you figure that you’re gonna hit next.

This is my destination file. Okay. And what I’m doing is using an action called update. And I’m updating my table that I have inside Caspio. This is my contacts underscore GD, which stands for Google drive. And it’s just going to simply update that table with my updates inside Google drive, we’re gonna hit next. Then you’re gonna map out your main primary key, which is the C I D and the source table. So in Excel file, you will see, I have a field called C I D. And then in your destination file, you’ll also have that field called C I D that’s how Caspio is going to know what changes to make for each/which row. Okay. So it’s gonna scan that file in Google drive find and locate the ID, match the ID from the source file to the destination file. And that’s how it’s going to know where to make those changes.

Okay, we’re gonna hit next. And now you set up the frequency. How often do you want Caspio to go to Google drive securely, find those updates and make those updates into Caspio. I set mine up for daily. You can also do monthly, weekly, usually the way most companies do that is they set it up daily and they will typically run that at midnight, but most people are asleep. So when people come into the office next day, they log into the application. They’re gonna be able to see those updates inside the app. Now, once you’re done, you’re gonna hit finish. I’m just gonna cancel out of it since I already have mine created. So here’s that task running in the background daily. Okay. Now I’m not gonna wait until midnight to show you the updates. I’m just gonna run that now on demand. And it might take a few seconds for the changes to effect or reflect back into your Caspio table.

So let’s go to our table now, inside that application, let’s open the table and let’s see how long it takes and I can already see my changes. So here’s James Wong. You can see, we change Rihanna to Sarah and at the bottom, I should be able to see Steph Curry added as the new customer. Now, if you build some data pages on top of this table, you would now be able to see that data reflect on the web as well inside one of those interfaces. So, very nice. If you’re working offline, you have your Excel files, you’re pulling data from somewhere else. Once you pushed out those files into Google drive, you don’t have to worry about anything else. Then Caspio will, you know, go there securely daily, weekly, monthly, depending on the frequency. And then you will always add the new data set, updated inside Caspio table.

Import and Export to Dropbox

Okay. So that’s one example. The next example that we wanna look at is Dropbox. So with this one, what I’m doing is basically refreshing the original data from the Excel file into my application. So if somebody makes a mistake to my data online, I always want to be able to refresh that data on demand whenever I want. To just to give you an example of a use case of why we may want to do that. We have all of our RMAs on our website, and I don’t know if you’ve ever demoed any of these RMAs, but when you log into an RMA, let’s go ahead and log in as a user. And we’ll just take the credentials very quickly, paste that and log in, say, you’re adding a new article and you make that submission. And let’s just say, you selected a department, you put some dummy data, you hit submit the next person who demos this application is going to be able to see that data.

Okay? And we don’t want testing data to be available inside these RMAs. So what we do is we do a hard refresh every hour on these RMAs to bring back the original data. So people, when they’re demoing these applications, they don’t see testing data like you, you can see right now. So if I submit this, the next person who demos the application is gonna be able to see that. And it doesn’t look good on the front end when the customers are demoing the applications. Okay? So we use the data hub to locate the original data. In this case, I have the original data inside my Dropbox account. Okay. Now. Okay. And every hour, we can replace the new data on the app with the data that we have inside this Excel file in Dropbox. So let me show you, inside my original data here in Dropbox, I can open up my Excel file and you can see, I have four different tables.

We have table of reps, which has the name, the email, the password, which is encrypted. We have the table of sales, table of customers, and, the log table. Okay. Inside my Caspio account, let’s come back here and let’s go to that application, which is sales, CRM. I have the same exact tables, four tables, and the data at the moment is exactly identical to what I have inside my Dropbox account. Okay. It’s the same exact data set. Now, if you’re on the web and let’s say, you’re making modifications to your data, you can see I have 10 customers. Okay, exactly. 10 inside my Caspio table here I’ll have 10 customers. Let me open it. And you can see, I have 10 customers. Now, people are making changes online. Let’s say they add a new customer. They open up this form. They fill out the form. I cannot fill out the form because this application was built for demo purposes.

But if you were to hit submit, you’ll be able to see that additional customer listed on the list. And then you will see 11 customers.

Pop-up Forms

Now, before I move on, I did have a request from somebody last week on how to actually create these popups, which I do admit is a bit of a cleaner look, um, to building forms inside the applications. I could have also created a new link here on my left hand navigation menu to say new customer. And if I click on that link, I can see my form. That’s a much easier way of doing that. But if you guys want something more dynamic where you click on the button and it opens up a Caspio form, and then upon submission, this will close. And this, this will reload. Let me know in the chat window, if you guys would like to learn how to do that for your applications, I can simplify this to the point where you just need to add some code to your data pages, and you don’t have to worry about any external files that you need to add to your HTML document. So everything will run directly from within the data page. If you guys want something like that, cause right now this data page inside my account, the button that you see here is part of my data page. So the button is actually inside the data page.

Okay? So when you click on that button in the data page, it’s gonna open up a caspio view and believe it or not, everything that you see here is actually built inside the Caspio data page framework. There is nothing in my webpage. Okay. It’s just a lot of code, not a lot of code, but a lot of coding done in the data page side. And if you guys want, I can provide you with all, all the code. We’re not gonna worry too much about what the code does, but as long as you copy and paste in the right place, you can get the same type of effect. Just note that if you guys do wanna see that next week, I can show it to you, but it’s gonna be highly technical because it does use JavaScript, HTML, and CSS. Okay. You don’t have to do it that way. Okay. But if you want to do some low coding, uh, inside a data pages, we can definitely accomplish that. Okay. How many people would like to see that for next week? Second that? Okay sounds good Brian. I think I have enough people who would like to see that for next week. So let’s go and do that. More dynamic buttons? Okay. Sounds good.

Import and Export to Dropbox Continued

Now, as you can see, I cannot add a customer, uh, with my form because I’ve disabled my button. Okay. So I’m gonna go directly into my table in Caspio and I’ll add a new customer. So let’s just say Steve Smith, maybe abandoned phone number here. Okay. And we’ll hit submit. So now I have 11 customers inside my table that should also reflect on the front end if I refresh and there’s Steve Smith. Now I want to be able to revert back to what I had before. So I want to bring back the original data and update my application. So I created another task in my account. Oops. Wrong place. Let’s come back here.

Okay. Refresh original data from the Dropbox account. So I’m gonna hit edit. Okay. I’m importing data. Once again. I just name my site. Refresh original data. Here is the file path in the name. So I have a folder in Dropbox. This is the name of the folder and this is the name of the file inside my Dropbox account. So let me go back here. You’ll see. That’s the name of my folder and that’s the name of my file. So that’s the exact path that we need to add inside Caspio. Okay. And again, what you don’t see on this screen is the ability for me to connect to my Dropbox account. So that’s, what’s missing because I’m already connected. So I don’t have to do that again, but typically you will see another button here to authorize Caspio, to gain access to your Dropbox account. And I just have to simply allow it and then you’re gonna be connected securely. Cause you still need to add your credentials to your Caspio for your Dropbox account. Let’s hit next.

Okay. Interesting. Never seen that before. I don’t know why it’s doing that. No, since I already created this yesterday. Okay. Let’s cancel out of it for a second. It was enabled yesterday, but for some reason it’s not allowing me to go through today. So let me try that one more. I’m just trying to get to my frequency screen. But for some reason, I don’t know why it’s doing that today. That’s odd. Hopefully it still works here in my Dropbox account, um, which should be able to see the original data. So in my customer account, I still have 10. I don’t have, as you can see Steve Smith. So if I run this task, let me come back here, cancel and let me go ahead and run that. So I don’t know how long that’s going to take cause I’m I am having some connection issues with my OBS software here on my computer. Let me know if you guys are still able to hear me. Okay. Sorry about that. It looks like I had a disconnection here with OBS, which is the system that I use to live stream. Let me know if you guys are still able to hear me. Okay. Unfortunately I’m having some issues here. Um, let me type something inside a chat.

Um, looks good. You can still see me and hear me. You can see my face moving. Everything looks good. Okay. Yeah. Okay. Sorry. I had a pop up here on my end saying that the OBS, um, was disconnected for some reason. All right. So let’s go and try to run this. I actually did run it already. So let me go to my Caspio table and let’s open up, uh, wrong application. Let me come back here. Let’s go inside the sales CRM, and let’s open up the customer table and hopefully back down to 10 now instead of 11, let me refresh.

Maybe it’s still running into background. Let’s give it a few seconds. I will open this up in a new tab. If it didn’t actually successfully update my data, it would let me know here in a notification if there was, if there was an error, but I ran this yesterday with no problems. I’m surprised that I’m having issues today, but let’s open this up in a new window. Let me take a look. Yeah. I did have an issue. See history. So let me, let’s take a look at the history. Yeah. I’m having a connection failure. You can see, I ran this yesterday with no problem, but today for some reason it’s not working correctly. Um, Alright, so let’s try it one more time. Let me delete this so you can see how I connect the Dropbox and all of the steps. So let me remove this and let’s reconnect. So let’s schedule a new import, create a new site, Dropbox, and let’s authorize Caspio to my Dropbox account. Okay. Allow name for this site. Refresh. For original CRM data.

Choose file. Okay, so here’s my file. I can select it. Let’s hit next. Okay. So there are all my tables that are inside Dropbox. Okay. I’m gonna use my replace action. I’m gonna replace all of the tables we’re gonna hit next. Uh, you can look at all of your tables to see what data you’re replacing. I don’t need to make any changes here. We’re gonna hit next. And then you can choose how often you want that to do that. Um, on the Caspio site for RMAs, we use hourly. So every hour that data reloads and goes back to the original data set. I’m not gonna do that. I’m just gonna do on demand and we’re gonna hit finish. Let’s give it a name. So refresh. Original CRM data.

And hit finish. Okay. Let’s try to run that one more time. So I’m gonna run that, because I know in Dropbox I only have 10 customers, so it should revert back to 10 inside my Caspio table as well. So let’s have that run in the background. Let’s go to our table, uh, customers refresh, refresh, and now we’re back down to 10 or nine. I think I may have removed one more by accident. <laugh> okay. So now you can see how that’s done. Let me, yeah, that was odd. I don’t know why I went, I went back down to nine when it should really be 10. So there we go. So the task ran successfully. So imagine now if you were doing this hourly, uh, this is how you can revert that to the original data. If you ever need to just a simple way of bringing data or if you wanted to archive the original data, if somebody goes to your Caspio account and deletes the data, you can always bring it back on demand or monthly or weekly or hourly.

But yeah, that’s uh, another way of how you can use the scheduled task, uh, to your advantage to bring data back. And the last example that we’re gonna look at is to export the data.

Send an Email Report

And we’re gonna look at how we can send an email, uh, of a report that has all the incidents. So in my account, I have an application which I’m building at the moment, construction safety reports, where construction workers can log in and be able to submit a safety report, an incident report. You know, if it was a near miss, if it was an accident, if it was fatality, hopefully not, but you can list out all the details of how, um, you got injured and all that good stuff. And in the table of incidents, I have two submissions. Okay. Which is just testing data. Okay. And what I would like to do is send an email to maybe a manager or somebody else at the company of all the incidents within this table.

Okay. So I created a very simple task to send an email. So let’s edit and I’m sending an Excel file. Okay. So I’m exporting data. I’m not, I’m not importing anymore. So we’re gonna export the Excel file, um, compressing that in a zip format. And I also pin the date and time to the file or the date to the file made. We’re gonna hit next. Uh, I selected the tables that I would like to export, which is the incidents table. I could also export additional tables if needed or views. And then I’m doing that daily. So I’m notifying, um, yeah, in the previous screen, you don’t see that, but here also you would select what emails you want that to go to, right over here. You don’t see it because I’ve already set that up. But I’m including my own work email. And I’m sending that email daily. Okay. At this time with the latest updates. So when you click finish, let me cancel out of it. I’m just gonna run an on demand.

Okay. I should be getting an email here very soon with the incident report. Still running. Okay. I think it just arrived. So let me open it. Let me bring that over here. So here’s the email and you can see it came as an attachment. We can open it and now I can open up that Excel file locally and be able to see the incidents. Now you can create a loop here to go back to Caspio. If you make some changes offline now, and you have another task running to go somewhere else. Now you can send this data back to your Caspio account. If you would like, okay, so it’s a two-way street. You can import and export as well.

Conclusion

All right. I hope you enjoy today’s content today’s live stream was also less than 30 minutes. Same as last time. I’m just going to bring back my YouTube chat to see if there are any questions. Yeah. I hope you enjoyed the content. Uh, if there was something you were hoping to see on the import task that you didn’t get a chance to see in today’s live stream, let me know. We can definitely bring this topic back. Uh, maybe in the future, we can look at FTP as well, uh, which is another way to connect. So if you schedule an import, right? So let’s say we do an import create a new site, FTP. You have to provide your Ft FTP information here. So when you open up an FTP, uh, let me log in. Okay. Uh, you just have to provide the address, your credentials, and it’s the same as Dropbox and Google drive. Then you’re gonna allow Caspio to gain access to your FTP site. And then you can pull data from here as well. You can see, I have the, uh, my original data set here inside FTP. It’s just another way. So if you move your files locally to your FTP, again, Caspio will go there, find out, find those updates and bring those updates to your Caspio account. Okay, so it’s the same idea.

All right. And then for next Monday, we’ll do a session on how to create that button and how, when you click on that button, uh, you can have a popup window that contains your Caspio data page, which is a little bit more fluid if you ask me, but that’s not to say that you can’t in your application, which most people do. They have a list of customers and they have another link underneath that says new customer. So when you click on that link, it actually opens up this form, but this form is going to be embedded inside the webpage as opposed to this popup window.

What is the Best Export Format?

Okay. So the question here is what is the best export format for Caspio table in the situation that you need to import it, XML CSV et cetera. So if we’re talking about the entire application, I recommend XML. It’s just, uh, one file. If you do, CSV are gonna have multiple files. Um, I mean, both methods are safe. Um, I don’t see an issue with both methods, but usually I’m used to exporting as a Caspio XML if I’m exporting my entire application, if I’m backing up my application to my desktop, but if I’m modifying data, then I would use CSV if I’m modifying data offline, because XML is not gonna allow you to do that. XML groups, all the objects together into one file, but CSV, you can open up the, uh, downloaded file.

It’s just a few tables and yeah, I recommend CSV in that case gives you more flexibility because not only can you edit the, the column headings, you can also edit the data that you export from Caspio. if you want to. If you’re exporting the entire application and importing the application to another account that you might have, then I definitely do XML. Yeah, no worries. Good question. Yeah, I think for next week, we’re gonna do that pop-up window. I’ll simplify it for you guys. I’ll give you all the code that you need to inject into your data page, so that as soon as you put it together, you’re gonna be able to see that pop up on the web without you having to worry about adding any external sources and file names to your heading of your HTML document. Sometimes, uh, there is script and CSS that you need to add in between your inside your head document on the webpage. But I will show you that in Caspio you can add all of that inside the header of the data page to do the same thing. So you don’t have to worry about any external files.

Can You Only Export Updates Since the Last Export?

Uh, is it possible to only to export only updates since the last export? Hmm, no, not that I can think of. Just thinking, um, in my head at the moment, um, only the updates. Well, that’s gonna include all the updates, even the previous updates, not just the last updates. I was thinking, maybe do a trigger. And when you make an update, send those updates to a separate table and then just export the data from that separate table, not from the main table, but that table is also gonna have the previous updates as well. I can’t think of that right now, but let me give that a little bit more thought, offline. I’ll double check with a few more people at Caspio and see if we can do that. Um, and I can bring you the answer next week. If you do come back, remind me, uh, I’ll have an answer for you next week.

Uh, Ellen yeah, “In your construction log example, the file may get to be very large.” Uh, it may, I see what you’re saying. I see what you’re saying, because then every time you’re sending that email, it sends the entire table, including the updates that I may have seen before. Right. And you only want the latest incidents to be sent via email. I see what you’re saying. So let me see if I can tamper with that slightly offline to figure out a way there is a way to just send the latest updates and not the entire table. That’s a good point.

Cause why would you want to be able to scroll all the way up and down to see the latest incidents? I mean, I could sort the data before I export it. I can sort to see the latest ones, but then it’s also gonna include all the other ones as well. Okay. I’ll look into that. I’ll find a way if there’s a way to only export the updates. That’s a good point. All right. Let me know if there are any more questions. Once again, thank you guys so much for joining these live streams, just a heads up next week is gonna be slightly technical. So if you’re expecting no code, you’re not gonna see that we’re gonna use some low code inside a data page, which is very helpful because you know, most of the things in Caspio could be done using no code. That’s why we call ourselves the no-code platform.

Uh, but using a little bit of code really removes a lot of, you know, imagination, barriers, you know, like you can do so much more if you just do a little bit of coding in the data page, not extensive coding, because most code is already provided for you. You can just Google it and find it and just copy and paste it into the data page. You just have to slightly modify it. But by using these traditional standard languages, you can do so much more with the applications and make the applications so much more dynamic and user friendly. Yeah. Thanks, Brian.

Uh, separate table that gets cleared after export could be one approach. Yeah. I, I just wanna be, I thought about that. I just wanna be able to see if we have a way to somehow tell the system when the export happens, go back to it and clear it. I know with backend development it’s possible, but I want to be able to see if you, as a customer will have the ability maybe using a task or using a trigger to do something like that. Okay. All right. I promise you, this is not gonna be too overwhelming to do. Okay. This is just a simple button that calls a model that we have, uh, inside a data page to open up that window. And then inside there, you, we have a little bit of script that calls out the data page app key. So on the data page side, each data page has a unique app key, right? And all we’re doing is grabbing the last well, we don’t grab the first eight, but after those first eight digits, we grab this app key to call that data page inside that popup window. That’s how it knows what data page to load inside the popup.

Okay. Yeah. You’re welcome. Thank you so much for coming back. Really, really appreciate all of you. Uh, who’ve been with me for the past, I don’t even know how many sessions we’ve had maybe almost close to 20 sessions now. And I know many of you have been there since the beginning, so thank you so much for continuing to come back. Um, as always, if you guys want to see something that we haven’t done, you can always shoot me an email, something that you’re thinking of doing. And I’m gonna try to incorporate that in, into my livestream. Maybe not as a main topic, but as a side topic, if we have the time, I’ll bring it in and we’ll talk about it because I guarantee you that you’re not the only person who probably wants to see something like that, or maybe they haven’t thought of that, but it would be helpful for them as well for their applications.

Okay. So I will try to figure out a way to, you know, only send the latest updates when you’re exporting the data and not the entire table. I’ll try to figure out if there’s a way to do that. And I’ll bring that back in addition to doing, to pop-up on the next live stream. Uh, my email just don’t abuse my email <laugh> um, that’s my email right there. Um, I only take topic suggestions. So please don’t ask me for help on how to do something only because we have people at Caspio who can help you with that. I made this mistake in the previous live stream. I provided my email and I receive a ton of emails with how to do things. But if you have a suggestion, if I think it’s a good suggestion for the live stream, I will let you know via email and then we’ll include it because the pop-up button was actually a suggestion suggestion from somebody else in the chat. And we had an email exchange and I said, let me ask the chat, uh, today to see if they would like to see that. And since many of you want to see that we’re gonna include that next week.

All right. Thank you guys so much. I will end the live stream now. I’ll keep a chat running for a few more minutes. If there are any last minute questions and then I hope to see you next week for some more advanced low-code tutorial. Okay. Which again, in the description of my YouTube video later on, I will give you all the code and you can just copy and paste. You don’t have to worry about what the code does. <laugh> I will let you know if it’s borderline abuse <laugh> in a polite way. Okay. Thank you for the laughs. I will go ahead and end the live stream now and we’ll see you next week. All right guys, have a good and safe week. Bye-bye.