Resource – Live Events- Automate More Workflows With Triggered Actions

Hello? Hello, welcome everyone. This is Ned from Caspio. Welcome to our latest episode of Caspio labs. My number one thing that I always do just a quick housekeeping item before I begin. If you’re able to hear me, let me know, and then we can proceed with today’s presentation. If you are able to hear me, please let me know and let’s go ahead and begin Didier. Hey, welcome back. Happy new year. That was going to be my opening line. You beat me to it. Hey, Brian. Happy new year, everyone. Hopefully everyone’s having a good start to, the new year. Hopefully we can expect some better things this year. only time will tell, right? Hey Katie.

Backed by popular demand, because we had a triggered actions in our, episode a few weeks ago, you guys wanted to see more and triggered actions. That’s why we brought it back. Today we’re gonna be looking at, if then statement, we’re all going to be looking at variables and a loop using do while, which was very hard to come up with a good example. But I think I came up with a decent one, on how you can implement that for your projects. Again, all of these examples are gonna be made available in a description of the video later on. You guys will be able to download each one of them you can study and hopefully be able to, use each of these for your projects in some way, as you begin developing or working on your application.

Don’t worry if you’re not able to follow along, they are gonna be made available for the description and you will be able to import them into your account and see how they were created that you can study. Let’s go out and begin. I am sharing my screen and Tet’s take a look at the if then example first. We’re gonna begin with the easy one. I’ll open up that example and let’s go to my tables. What I’ll do is I’ll show you what the trigger does first, and then I’ll explain how the trigger was created. Very typical inventory type style application, where you have products and you have orders. If you place the order, we want to be able to subtract that order from the products table and in addition to that, if the order falls below, let’s say a certain threshold, we have a checkbox in this table, Back order, which is simple. Yes or no, to check that item. If the inventory falls below a certain threshold, that’s the idea behind that trigger and using an if then statement we can accomplish that. Inside is stable. We have, let’s add another product here. Let’s have maybe laptop, sorry. Let’s do maybe keyboards, sample description.

And let’s say the initial quantity for keyboards can be set to 100. If I save that, you will see that it’s unchecked because what the trigger is doing is checking for anything 25 and under. If it falls below 25 only then check this box. now that’s contingent on the orders table. Let’s take a look at the orders table as well. Here it is. Let me copy my ID. We’ll copy this ID To the orders table. Let’s paste that here. Now let’s say we ordered about 80 of them, that should subtract 80 from a hundred and the other table. When I reload this table here, you’re gonna see 20 now. You’re gonna see how it checked that box because it fell below 25. let’s take a look at my trigger here.

Very simple, if then I, since you guys wanted to see that in my previous, episode of triggered actions, I’m using it if then else. Rundy action on insert and update of data event for the products table. Where we grab the top item inside that products table. And if it’s less than, or equal to 25, we’re checking the quantity field. If it’s less than or equal to 25 on that quantity field, then update the inserted table and set the back order to true. Meaning if it’s less than 25 or equal to 25, we wanna update the inserted table, which is the products table and we wanna set the back order check box to be true, or else update the inserted table and set the back order to false if it’s not below 25. Very simple way of using the if then L statement and then in my other table of orders. Let’s edit that as well.

Also very, very simple. Basically, when you insert an update, the orders table set the quantity inside a products table to be the product, to be whatever you order inside, insert a table and subtract that from what you have inside the products table. Very simple subtraction, right? The inserted table is the orders table. Remember how I added 80 into my orders table. We took that 80 and we subtracted from a hundred and that gives us the, now the new quantity inside a products table, which is going to be 20. Now you first have to join the two tables. Once you join the two tables, you have to have the product ID equal to product ID. That’s how it knows what product, quantity to update for what product very much like when you’re importing data into Caspio and you’re using the update action. You have to look for the destination ID and the source ID.

That’s how it knows what items to update in the table. Same thing with the trigger. You joined the two tables together, and then you have to map, match the fields. That’s how it’s going to know. I need to update the quantity for the keyboards, or I need to update the quantity for the, for the laptops or any other product that you might have. That would be the very first trigger using if then, let’s take a look at our second one that we have today, which is using variables, because you guys wanted to see that as well. This one’s gonna be a slightly more complex. We have three tables, let me open up each table.

I chose this example. Just that you can see if you’re building, let’s say a SAAS type application. If you have companies at one table and each company has multiple employees using one checkbox, you can update your next table to make all the employees that belong to that company, either active or inactive. Instead of going to this table here and unchecking one at a time, you have one checkbox that controls them all. If I uncheck this box for ABC, you can see right now we have, ABC has two employees and they’re both active. If I uncheck ABC in my other table, when I refresh, you will now see two employees that are inactive and they will no longer be able to log into the application. Then in the final table, I’m using a simple count,

To count how many are active and how many are inactive for the ABC company. If I refresh this table, we should now be able to see two and zero here. If I refresh because now we have two inactive employees. If I revert back, let’s say we make the ABC active. Once again, both of our two employees are gonna be active. In that final table we have again the opposite reverse. All right. Let’s see how that’s done and everything is contingent on this called company’s. When I’m, making changes to my company’s table, I’m updating both of these two tables, the employees and the employee status summary. Let’s take a look at the trigger. It looks overwhelming and daunting, but I promise it’s not, if you follow along, hopefully, it’s going to make sense. When you insert an update, Inside a company’s table, I want to be able to update the employee’s table, this one here.

That means that if the checkbox for status on the company table is false, set the status false on the other table, which is the employees or the opposite reverse. If the company is true, if it’s checked, update the employee table to update the status, to be checked, which is what we looked at in just a few minutes ago, when I updated the company, it updated the employee table and set those checkboxes to either be checked or unchecked based on the condition in the company’s table, which is the inserted table. This one here. Then I created two variables, a very simple variable that stores the value of how many are active and how many are inactive. My first variable is active and all I’m doing is using a count expression here to count the employee ID from the employees table. You have to join the two tables together where the status equals to true. Basically it’s counting all the employees that are true inside the employees table.

You can see we have two and that’s storing that value here in the variable to be two. We’re gonna reuse that variable in just a here in the trigger below, which you’re gonna see in just a moment, and in the opposite, we have an inactive variable here that we create to store that value, and it’s counting all the employees that are unchecked inside the employees table. You guys have used, have seen me used account when we use our basic SQL expressions on the data page, select count. It’s the same idea, right? We’re just counting how many IDs exist in the employee table. If it’s false count inactive ones, if it’s true count the active ones and now down below, you will see once again I’m using if then else. If this company does not exist in the summary table, when you add a company to your company’s table, okay, that’s a condition.

If it doesn’t exist in the summary table, then insert that company into the summary table, copy over the company ID and give me my active inactive stored inside the summary table, because this table here alhas active and inactive fields. It’s basically just placing that calculation inside the summary table that we’re getting from here. When you add the company, let’s say a new company signs up on a website registers, you’re copying that company information over to the summary table. Initially, because that company doesn’t have any employees, it’s gonna be zero and zero. As you add employees for that company, we’re gonna be counting them and then we’re gonna be placing that total inside the summary table or else If the company does exist, then only use the update. We’re no longer inserting the company information. We’re now updating the existing company that already has those employees and the company ID in the summary table. Again, you’re placing active and inactive. You’re joining the two tables where the company ID equals the company ID because it needs to know what company we need to update inside the summary table.

The top one. I can explain that. the top one, basically it just starts at the top and it counts the rest of the table. You have to start at the very top of the table when you’re doing the count.

Usually, you always end up having one here. You can even remove this. You, I don’t think it’s necessary, but I always include one anyway in there. Just select from the top, the very first row into table.

I know it can seem like a confusing thing to look at the way this triggers are set up. You know, sometimes I feel like this is reversed. I think where should be above set, but you know, that’s just the way the system works. You set the, status to false and then you look at the condition where the inserted status equals to false. To me it makes more sense to do the condition first and then set the value. The way this system is set up, it’s the opposite, right? we first set the status to that if the status equals to false.

Let me know if you have any questions on this one. It’s just one table that controls the end result of the other two tables. You know, how many employees are gonna be active or inactive. In the summary table, if you’re doing a dashboard, let’s say you can use that summary table to display on a data page. You know, how many are active and how many are inactive, An example of something that would be other than top one, just depends on where you want to begin counting in the cycle. You know, maybe you want to avoid the first record. If it’s an admin level user, you want to avoid them. Let’s just start at two. Because usually when you build a, user table, the very first user will be maybe the CEO and maybe you want to exclude that person from counting it. Maybe you just start at two as opposed to one.

Let’s take a look at the last example really quickly and then we’ll get to all the questions I promise. We’re gonna look at one more example today. I have three examples set up for us. Let’s take a look at that last one, which is the loop and let me close these two tables and let’s open up the, the sales table and the payment table.

You know, when you make a purchase and you wanna do installments, maybe you’re buying a, I don’t know, TV and a TV is gonna cost you $2,000, but you wanna make the payments over time. You can’t afford to, you know, pay $2,000 right away. You break down the payments in, let’s say 12 installments. Let’s say you’re buying a TV. Let’s just use that as an example. You want to be able to pay that TV off in about six months, cost of the TV is $2,000 and let’s just say the starting date will be under 12th.

Maybe you’re linking the customer ID. We don’t have the customer table, but let’s just say you did list, link that to maybe some kind of a customer ID.

In the payment table, what you’re going to be able to see is six different payments added because what the loop is doing is counting until it reaches that number six. You have the first payment then the second one that’s plus one, you’ll see how that’s done in just a moment. I’m just trying to explain how it works. The loop is basically looking at six, that number six, and it’s always adding a plus one until it reaches that six. And once it, once it reaches six, you no longer loop that. Right? now you see six different payments inside a payment table. You have the starting date and you also have each monthly payment that you need to pay.

Let’s take a look at the, the trigger and the loop. I have two variables set. The first one is the counter, which is just set to one. Just think of it, like adding another one plus one plus one plus one and my second variable is the starting date, start date. This is basically all on the payments table initially, and then we’re gonna do the loop. We first get the counter, which is the monthly installments. That’s the field in my table. That’s how many you’re gonna have, let’s say five or six or 12, however many. It basically is if is less than or equal to the monthly installments. Go ahead and add a plus one counter. Right. if you said the monthly installments are gonna be six, that’s what it’s looking for here. if it’s less than six, go ahead and add another plus one or another plus one until it reaches that six and then do this. now we’re gonna insert into the payment table. This is the sales table. We defined how many monthly installments we want to have. Let’s say six. We’re gonna be adding it to the payment table until we reach that number six. Select from the insert a table, which is the sales table. We’re copying over the customer ID range from the start date and then range to, from and to, Add one month to the start date. It’s gonna be six, right?

It’s gonna be total of six months. Payment month is gonna be the counter. Then, the expected payment, which is a very simple calculation is the sale amount divided by the monthly installments. That’s how we got that 333, 2000 divided by six. That’s gonna be the monthly payment. Here at the bottom, you’re gonna be able to see the counter plus one. Counter and then always plus one until you reach less than six or equal to six. It’s always gonna add a plus one and same thing with the start date. Add another month to your start. Original starting date. If the original start date was 1st of January, the second one is gonna be 1st of February, March, April, may, June six months. That is the loop that’s counting and adding a plus one to both the monthly, for the monthly payments and for the months.

It is a pricey TV. Yes. $2,000. If you’re buying a, a Samsung, Samsung TV. You always want to go at the top line TVs. All right. that’s really what the loop is doing. It’s just creating the total number of monthly installments based on a number from the sales table. one more time if I come back to my sales table and you specify how many monthly installments it just does, it just does divvies it up into six different payments in the payment table. If I add one more, maybe we’re buying a, I dunno, can’t think right now, what are we buying? <laugh> you can almake this for mortgage payments, too. You know, if you’re paying it over 360 payments, 30 years. let’s just do that house. monthly installments, 360 house worth 650,000 start date can be the first of next month.

Let’s say you link that to customer number three, and then in my payment table, we’re gonna get all those payments, all 360 payments with the monthly payment. Of course, you’re gonna be able to combine that with the interest and principle amount and all that good stuff, but that’s what it does, which is a very simple way of creating, using the loop in order to add multiple records in the other table, there are a lot of, use cases that this can be applied to, but here you’re seeing two different use cases for real estate and for just regular eCommerce type implementation.

Today’s class, as you can see very short class, we use three different examples. What I’ll do is I’ll export all of these tables and I’ll make them available for downloading the description. Today’s session was very short and brief. I didn’t want to build the triggers from scratch. I wanted to just go through them and explain. I thought that would be easier, but if you guys want me to in the future, build the trigger from scratch, if that would, if that’s more helpful, let me know. And then we can do that. because we are gonna have more sessions on triggers. And also if you’ve sent me an email with a question and I haven’t replied back yet, I do apologize. It’s been very hectic and I do have a lot of my plate these days. I will eventually get to it. and ask before, if you guys wanna send me some, recommendations on what you would like to get out of these live streams, please send me an email.

Alright. Brian has a question here. I have to make an SMS email sender, would I need to check if the date tomorrow has come up in the table? If the date tomorrow has come up in the table? what we’re gonna do with that is next week, we’re doing tasks. I can include that example next week, once I talk about tasks, because we’re gonna have to use that the task to run it daily, to check for that date. And then if, the condition here is, if the date has come up, then send that email or SMS. we’re gonna talk about, I can include this as an example next week on Monday. Cause next week is all about tasks. good question. case and if then they’re interchangeable. you can use one or the other. But because people wanted to use, see and if then example, That’s why I used it. But in a lot of cases you can get away with just using case when, okay, you can use one or the other. I can’t really think of benefits to one or the other, to be honest, I mean, both work. It’s just whatever preference you have or let’s, let me see if I can, maybe we can include that next week.

An example of case when, but to me it’s more or less whoops, not rename edit. Case when something happens, execute something else. I mean, if then it’s the same really? To me, it’s the same thing. I’ll try to think. I’ll try to come up with something for next week to see how we can use case when, but there’s really no major difference between one or one or the other, If you ask me, But maybe, I’m, maybe I’m wrong. I’ll double check with our product team to see if there’s something that I’m missing there. It’s a good question, Katie, Like you guys, I don’t use many of these trigger, triggers, that you see here on the left hand side, these elements I’ve used probably 30% of them in my examples that I put together maybe 40%. there’s still some of these that I haven’t used. I’ll be honest in all of my other examples that I’ put together demos that I used to showcase the platform. I have never used loops. But I put together this for you guys today, you can see how it can be used.

It’s just that I wasn’t building any use cases that would require that type of implementation. Like you, I am still learning. There’s much to it and it, I only use it if I need to use it. Right? if the use case calls for it, if it makes sense, I will use that in my demo. If not, I will either think of another way of doing it or some, some other way of accomplishing that same type of end result. What if I add more than one trigger? Let’s say, Let’s say one thing is changed in one table and another is in something else.

I think where we actually maybe showcased that today. My company’s table, when we, you know, we added the company, we’re aladding the company into the summary table, along with the active and inactive variables. Initially they’re gonna be zero and zero because that company didn’t exist previously in the summary table and then when we make an update, let’s say you did add the employees into the employees table for that company. When you check the box in the company’s table, it’s, it’s affecting both the employees table and the summary table at the same time. Right? if you check the box to make the active company, all the employees for that company will be active. In the summary table, we’re gonna be able to see how many are active and how many are inactive. Then, we have more than one triggering that table, because we’re manipulating both the employee table and we’re manipulating the, summary table. What if I have more than one trigger? Let’s say one thing is changed in one table and another in some you mean like a ca I thought I was, I thought I, you wanted to see like a cascading, right?

If I make an update in one table, something gets updated into the second table and based on that update in the second table, something is updated into the third table. I forgot about the cascading for today’s live stream. I will make sure I include that in Monday, next week on Monday, next week. I’m gonna write that down here for myself to make sure I have a cascading, available to show next week. Thank you for reminding me. That alluded me completely for today. I will make sure I have it for next week And that’s actually very good because you can have that chain effect. Right? if something changes in one table, all the cascading tables, it’s like a chain effect. All of the other tables will, execute something based on the prior table. Thanks Brian, for reminding me. I do remember talking about that in the first class we had with triggered actions, but it’s been a few weeks, I will make note of that and have it be ready for next week when we talk about tasks. Sounds good. Thanks Brian.

Let me know if there are any questions again, I didn’t eat up too much of your time today. Thank you much for coming back. I appreciate it. Good to see many of you, and again, as promised, when you check back later today on the YouTube video will be available and I will have the download links for each one of these examples. What I like to do is I like to save these on my desktop. If I need to reuse it or I’ll import it into, I’ll create like a template application in my account, I’ll call it “triggers”. Then, I will have all of my triggers inside that application that I can always come back and use it if I need to because for me, if I don’t use it, I lose it.

Even though I, I do something like this, maybe three months from now, I can forget how I did that. Because there’s a lot of steps to it. If I have it available that I can just into some kind of a repository where I can go back and pull it up. I know what I did and I can just copy my work. All of my triggers are basically inside one account where I saved them. I recommend for you guys to do the same, download them, import them into your account. If you do need to use them, you can always use it as a guide. When you’re building something. With that, thanks again for your time. Have a good week, stay healthy. I hope to see many of you, next week as well. I did not think you should run more of these webinars question mark. Sorry. I can’t trying to understand the que I did not think you should run more of these webinars. Thanks Brian. You as well, Katie, I know you were there too. Thank you much. King Kapo Era Lee. Steve.

No, no, no, I’m definitely gonna keep these webinars going. I am not stopping with the webinars if I said something otherwise, let me know.

I can’t remember what I said. I’ll have to watch that video again, but no, there’s no intention to stop these webinars at all. We’re gonna keep going next week. We’re doing tasks. If I get requests from people on what they would like to see in these webinars, I’ll definitely, try to include that. It’s just, sometimes the webinars will be one hour. Sometimes there’ll be 30 minutes depending on the content. Sometimes they’ll be as simple as, how to build a demo application. A lot of people liked the fact that I built a SAAS type application last time and in one hour or two hours, because we did exceed that session by one hour, we were able to build an entire application using Caspio’s no code, low code platform very quickly. If everyone likes that type of concept, you know, I have many examples that I can build in less than one hour. Where is my, app examples here, app examples. Here under healthcare, for example, patient portal, I could maybe use one hour to build something like this where patients can log in and then upon logging in who is my patient here? How is it John?

You know, we are able to see the dashboard for the patient and then we can tab over to messages appointments. Something like this, I can squeeze in one hour. I can build this whole entire workflow in one hour and show you how to deploy this to a website template. That seemed to be a popular, feedback and reaction I had from people.

Hey Tracy, I would love some more complex examples and calculated fields. Give me a second. I’m gonna note these down. More complex examples, calculated

Fields.

Let’s see. 7:00 PM in Denmark. Oh, it’s late for you guys.

That’s a good topic, Katie. If you want to see a specific formula, be added to calculated fields, I would love to because I’ve done some complex things like calculating monthly mortgage payments, car loans. What else have I done for complex calculations? I’m thinking just your basic mathematical operators using parentheses plus, minus, division. I’ve done that I have used in the formula data type. I’ve done some case if then type statements, because not all SQL is compatible inside that it’s compatible with the formula data type. Just some basic SQL expressions are compatible like case. If you wouldn’t be able to use select or some statements inside the formula data type, but you can use that in the calculated field, on the data page level.

I’d love to get an idea of what type of a formula you would like to see. Love to take it on as a challenge to see if it’s doable. If not, I’ll definitely let you know if it’s not possible or if it is possible. I know somebody in the last live stream comment and something about my chess, poster that I have back there. I’m sorry, I didn’t catch the name of who asked about that, but that they asked me what’s the position on the poster like the opening position chest, that’s the Ruy Lopez, opening. If you’re in today’s live stream, I just wanted to make sure I answered that question because it went by very quickly in the chat I didn’t get a chance to answer it because we had many questions that came in. I have something I got help with the, with that used lookups. Alone that redirects based on that field. I’d love to understand them better. Do you have my email, Katie? If not, I can send you an email and then you can shoot me an email. Let me just make sure I write something down. Tet me send you an email. Then, if you have my email, send me an email first, if you beat me to it, but I’ll send you an email and then reply back.

What you would like to see for calculated fields because we did a live stream on calculated fields and formula data types before. I dunno if you had a chance to You don’t have my, I’ll send you an email first and then reply back and let me know what you would like to get out of that session. If it’s something that we already did, I can point you to that video. Again, thank you guys much for coming back to the live stream. I can’t express my gratitude enough for you guys coming back really means a lot to me. Hopefully we can build a community and have more people in the following weeks. My goal is to reach 100. tell your friends if they’re interested in low-code no code movement, for their projects. Thanks, Katie. Thank you to both Brian’s have a pleasant evening. I know it’s 7:00 PM for you and Tracy, good to see you as always. See you next week. I’ll be here. We’re doing tasks. I’ll come up with a few examples on how you can be used. Tasks are very helpful when running event based operations. if you’re checking against something in a database daily or weekly or monthly, then you can combine that with the triggers. If something is passed due, it checks on that date and it executes the trigger. If it’s passed, if it’s a pass due item.

Have a great day guys. I will end the video now, but I’ll keep the chat running for a few minutes. I’ll be here answering your questions if there’s still any last minute questions with that, have a good day and I’ll talk to you soon. Bye bye.