Servoy tech webinar series 9: filtering data

All right, good morning, everyone. I am Steve Portanoi, a member of the Servoy US-18 from Los Angeles, California. Thank you for attending this, the ninth webinar in the 30 minutes with Servoy Series. Last time I failed to mention Servoy World 2017, so I wanted to get that out of the way early. Servoy 2017, Servoy World 2017 will be from May 17th to the 20th at Picasa 400 Hotel in Amsterdam, and the link you see is the link you can follow to find out more information and purchase tickets. So that’s out of the way. Again welcome, all the way planned to debut the sample solution solution that we started if the company hackathon. It’s not quite ready. So instead, Sean Devlin has prepared a presentation today about data filtering best practices. So I will hand that now over to Sean. Okay, thanks Steve. Can you hear me clearly? I can hear you fine. And you can see my screen. Oh, also, also I failed to mention if you have questions, there is a questions panel. We should have a questions section in the go to webinar control panel. Enter those there and at the end of the webinar, we will do our best to get your questions answered. Okay. So today we’re going to be talking about just a little bit about best practices in filtering data. We’re trying to rotate the topics for the series between updating on new web components or modules or plugins, et cetera, also doing some topics on best practices. And so we haven’t done one or while. So I think it was a good time to do one. So I work with customers on a weekly basis doing code reviews and helping them plan how they’re going to execute certain projects and some things that come up then that I see common problems or challenges. They make for really good topics for the series because we’re seeing something in our customer base and then we can we can address it and bring it into a webinar and make a sample solution out of it. So the topic for today is sort of how to filter data and we have a few examples. Basically, we’re going to do three demos. One is basic found set filtering. This is if you’re new to to serve, boy, this is a good place to start learning about how to how to filter data. And then we’ll move on to a couple more advanced topics. One is using the query builder API to filter based on aggregate criteria. And then in a third one, sort of imagining an analytic kind of scenario where you’re doing filtering, but you’re also doing grouping and looking at some, you know, KPIs or metrics in some dashboard or something. So let’s get into it. First demo is I’ll have to launch it. I had it up. I closed it right before we started. Okay. So basic basic filtering example. There is an API that allows you to add filtered parameters to a found set, which is a sort of a data set of records that you’ve got on a form. It allows you to use certain operators like equals greater than less than between likes, and in addition to that, you can do an in operator. So if you have a list of a list of values, you can filter on that. So I’ll just show you take you through a brief example. Let’s say we’re looking at a list of orders and we want to filter it starting with, let’s say dates. So I’ll come in here and do New Year’s 1996 to start with. You can see that the orders changed a little bit. And let’s give it to the end of the year. So do 1997 New Year’s. There we go. So everything in this list, you can see I, my orders went down from about 800 to 149, and everything in this list is going to match that filter criteria. Now I can continue to add multiple different kinds of criteria to my found set. So the next example I’ll show you is if we were to do some in filters where let’s say we have a foreign key on our table like the sales rep ID or the customer ID. And we want to see sort of as we add values, we actually want to see the list of results expand where we have one customer and then two customers and three customers. So I’ll use a tokenizer here. I guess we don’t have any in that date range for that customer. Pick a different one. There we go. So you can see that within that year 1996 for this particular customer, we have two records. But if I’m adding another customer, I’m going to expand that list because this is in in filter. So now it’s looking for between those two dates where the customer ID is in those two customers. I can add, let’s add one more so we have enough records. All right. And now what I can do is start to constrain that now with another in filter. So I’ll pick start to pick some of the sales reps. So now we’re just looking at say there’s no one for him. But do you get the idea? There we go. So now we have the two sales reps, the three companies and the date range. Let’s take a look under the hood at how it works. So coming into my my form here, what I’ve done is looking at the code behind this form. I’ve created a couple of variables to store the criteria. So the customer IDs, the start date, the end date and the employee IDs. Those are the four areas that I was filtering on. And you can see that in the in the form here, each one of these is bound to that data provider. So the start date and the end date, you can see the data provider is that form variable. So there is data binding going on, but it’s not that the table, it’s just to the form variable which keeps the state of what filters I’ve selected. Now one thing I’m doing is I’m handling the on data change of every one of these. And if you look at the code behind that, I call this method filter date. So we’ll look at the date one first. So the main part of the API is adding and removing found set filter parameter. So you can see it here and you can see it here. The first thing I want to do is I want to remove any prior date filter that I had. So I give my filter a name because if you want to later remove a filter, remembering that you can add multiple filters to the same found set, then you have to give it a name so you can reference it when you want to remove it. So first thing I’m doing is removing it based on that name because it could be that the user cleared the values and then I just want to remove the filter. So if there was a start date, then I’m zeroing out the hours to start at the beginning of the day just to be exact so that I don’t miss something if the value was. How does it does have a time value? So if it was later in the day, I want to get everything that day. And then here’s the main part of the API. So the way this works is you can supply the data provider ID or the column name. You can supply the operator and the list of operators available are basically all the comparison operators, plus a string like and also the in the in filter that you saw. And then you want to give it the value which is coming from my form variable, which is date object. And then this is important. The fourth one is an optional argument, but I always provide it. It’s a filter name because you saw prior to this, I wanted to remove it. So I got to specify that filter name. I do the same exact thing if I have an end date. And then the last thing you do is you have to apply the filter. So merely adding it to the found set, it sets it up, but it defers the application that filter to the next time the found set is loading records. This is nice because if I’m going to apply multiple filters, I don’t waste query. I can save it till the end. So calling found set load records finalizes that and applies applies the filter. So that’s just one, one of the filters, which was the date and it works on both of those criteria, the start date and the end date, just by pointing it in between. I should point out that I could have also added up between operator, but the reason I did it this way is because I want to support having a start date with no end date. So everything after a start date or the vice versa of that, everything up until an end date. And I can do that nicely by just putting that if on each one of those to make sure that it’s there. So how about the infillters? Well, again, I have, I have these form variables for the customer IDs and the employee IDs. And if you look back on the form, I’m using one of the, one of the Servoy extra components that you can download from the web package manager. It’s a string tokenizer and I’ve hooked it up to a value list, which gives me that that list of customers here. From there, it just basically returns all of the IDs that that I’m selecting into that data provider as a carriage return to limited list. So again, I’m handling that data change event. And this time I’m calling my method filter customers. This is actually pretty simple to do. Again, the first thing I’m doing is I’m, I’m removing my customer filter parameter. And then if there are any values, it may be that the user has deleted all of them. And then I’m just basically removing the filter. But if there are any values, what I have to do is I’m splitting that string on the carriage return to get an array. So that’s just kind of one little nuance of this. But once I have an array of values, it’s the same form. I have the found set filter parameter. I want the customer ID and the in operator. And then when you use the in operator, there’s two things you can do for the value. You can pass an array of values. Or if you get pretty large, right, there are limits depending on different database vendors to the number of values you can do in an in clause. So if you have quite a list of IDs there, you might want to change that to a query. You can actually put in a query object for a sub select there. And that will allow you to get past that limitation. But this is a simple example. We got a few items from a pick list. So the array works nicely. The employee was the exact same setup. Scroll to it here. Oh, it’s a write up above. Sorry. Same exact thing. It looks identical except that the filter name is different. The data provider that we’re filtering on is different. And then the the IDs come from that employee IDs form variable. Everything else is the same. So it’s pretty easy to set up. You may notice that I can clear any of these two. Like I’ll just clear all of these. And it removes the filter. Removing a filter is in the way that I’ve set it up is no different. I have a method down here, you know, clear customer filter. I just set the IDs to null. And I call that method again, because remember, it it first it removes the filter. And then it checks if there actually is a value. And if there if there is, it applies the filter. But since there’s no value, I’ve just removed it. It just removes the filter and quits. So that’s the basic filtering example. It’s really easy to do. I should also point out that once you apply a filter to a found set, that the filter stays on the found set. So you can add other filters, remove other filters. You can also search within that found set. So that you maybe you have some custom criteria that user has typed in or something else. And then it will apply your filter plus whatever additional search you’ve added. And then if you call load all records and remove that search, you’re back to your filtered state still because you haven’t removed the filter. So they are in effect until you remove them. The other thing I should point out is that we don’t support adding filter parameters to related found sets. And the reason for this is that a relation doesn’t really make sense to use with a filter because it’s based on the selected parent record like customers to orders. So if you filter customers to orders, it doesn’t make sense because every time you change the parent record, it’s actually a different found set instance. So you think you’ve applied the filter to all of the customer orders when in fact you would have applied it to one found set only and then you switch the context and everything is gone. So for that reason, we don’t support adding filters to related found sets. In that case, you always have to just do a full search. Okay, let’s look at our next example. So adding the filter parameters are limited to just the data providers in the primary table that you’re filtering on. It doesn’t let you filter on aggregates, aggregate values or values are coming from another foreign table. So there are some limitations. So the next scenario I’m looking at here is a bit more advanced. Let’s say we’re looking at customers and I’m interested in knowing, you know, I want to find customers that maybe have total sales, which is an aggregate value. It’s not a stored value on that primary table. I want to I want to filter them by, you know, total sales or something. So in this case, let’s say I want to do, you know, between 10,000 total sales as a minimum and 50,000 as a maximum. So now you can see that my list of matching records went down from about 90 to 38. And you can see that the total, let me sort to send you here. The total sales is going to be limited to those two ranges, those aggregate values. If I remove one of these, then you can see that that I’m now getting values above 50. So reapply that. And then what I’m doing, what I’m going to show is that that’s done with the query builder, but I can also use a basic filter from, like I did in the prior example and say, okay, but we’re also interested in, let’s say, you know, one country or something, two countries. So this is, this is how you could combine filters with the query criteria. So this one is a bit more advanced, but it’s really not that hard. It’s a different API. Let’s take a look at how it works. So if we go over to the customer query form, you can see that, and I’ll put this in that HTML5 preview. You can see that again, I have these set up hooked up to form variables, like min sales and max sales. If I jump into that, it’s just a form variable to capture the criteria. And then on data change for one of those, I’m calling this run query method. And so it’s a little bit more complex here. We start with the query builder API, and we can get that off of any data source by calling create select. And then we get this query object. And this is essentially shorthand for writing SQL, but there’s so many more benefits overdoing this than actually writing SQL, one of which is that that you get code complete across your whole data model. So I can say, look at the columns that are, you know, in this table, and I get code complete on that. Another thing, I don’t need this. Delete that. Another thing is that I can do grouping, which is, we’ll look at in our last example, we’ll look more grouping. I kind of have to do grouping because if you know some a little bit more about SQL, I’m actually querying on an aggregate. So I actually need to have a group by to support that kind of query. So at the end, I’m giving this query to my found set to load records. But so I have to group by the customer ID, but really I’m just getting a new, a unique list of customer IDs that match my query. But let’s get into the aggregate part. So the first step is, is I wanted to find what joins I need here. And so very simply, I can bring up relations that I’ve already created. So you’ll notice that the query object has a joins property and it can see any relations that I’ve made in my data model. So I don’t actually have to write the join, like join this column on this, you know, in this table on this column in this table, I can just bring up, you know, my customers to orders, join. And then I can, I can walk my data model. I can look at columns over there and get go complete on that. So it’s way more productive than trying to write a query yourself. It’s also safer. You’re the last product to make mistakes. So I wanted to mention one thing, though, if you can do joins using a relationship, it also is possible to do joins and specifying tables and on and type of join and that kind of stuff. So it is very easy and can also be very, you know, can be very manual as well. Yeah, I’m glad you pointed that out. I’m showing how you can leverage existing relations you’ve created, but if you need to define a join that doesn’t exist, it’s pretty easy to do. Exactly. So once I’ve created my joins, now I can go and figure out what kind of aggregates I want to check. So in this case, we wanted to do the min sales. We want all customers that where their total sales is above a certain value. So if they’ve specified that, I jump into here and I’m creating right here this variable, I’m creating an aggregate object and it has a couple steps and the API is a fluent API. So you can chain together lots of calls, which I really like some people they find it a little bit disjointing. You don’t have to do that. You could store every single call into another variable. But I’m creating this total sales aggregate and I’m starting with the quantity column on my order details table, which I’ve created the join for up ahead. So we have our orders and all the line items on that order and basically we want to do the quantity times the unit price and then some all of those together. So another advantage of using the query builders is I can actually do SQL functions like multiply. So you can see that I get code complete on all of this as well. If I was typing this from scratch, I’d get order details, which is already a join that I’ve made in columns and then I get the columns in that four in table and I can get quantity, go to meeting really slows down my code complete. And then I get all of these functions like absolute value, average, ceiling, floor, anything that you can imagine that’s supported in this equal standard. So that’s where I pulled the multiply function. So now I can multiply two columns together and then that function can be aggregated because SQL supports it, the query builder API supports it. So at the end of that, I can call more functions on it and I can chain a bunch of functions together. So I just called the sum, which generates that aggregate object that we’re storing into this total sales here. So actually, I’ve created the aggregate, I want to specify that I’m only interested in the customer records that have that total sales aggregate above the min sales value that I specified. So I created a condition here using that GE function greater than or equal to min sales. Again, I get code complete on the aggregate I made to do another function which creates a condition. So as this caveat that you can’t put aggregates in the where clause, you have to use the having clause, which is semantically different than the where clause. So just keep that in mind if you ever want to do a query based on an aggregate value that’s actually that you’re checking against, you have to use this having. But it’s really easy to do. You just come in here, the different parts of the query come right up in code complete and you do q dot having dot add. And then I just plug that condition in. So we do the same thing for max sales as well. It’s just that it’s a less than or equal instead of a greater than or equal on the max sales form variable. Once you have your query assembled, you can just give it to your found set. You can see I’m printing out some of the SQL that gets run just so you can look at what SQL you didn’t have to write. So you just pass it into the found set load records as long as you have the primary key values in the result, which I’ve done up here by in the group by, then it’s going to accept it and it’s going to load those records. Now one thing that was pretty interesting is that I’ve done that sort of custom query for my range of aggregate values that I want to look for in combination with a filter. So and this is the point I was making in the prior example. Down here I have my filter country, which looks a lot like some of the other examples I just showed you. It’s another in filter just based on the country field on the order. Sorry, I’m the customer table. So I’m combining that and you’ll notice that instead of calling load records, load all records like I did in the prior example, when the filter changes, I’m actually just calling rerunning the query. So in the filter, it sort of supersedes all the other activity that’s going on in the found set and so I’ve applied the filter and then I can change my query that I’m loading the found set based on the aggregate range that I want, but the filter stays there until I remove it. So it’s quite handy, I can combine filtering and searching, which are slightly different. We have enough time for one other example and it’s kind of a fun one. I just wanted to show how you could take the query builder a bit further because we show that it supports aggregates and all kinds of SQL functions. It also supports grouping and sub selects. I didn’t do a sub select example for today, but I did do one with grouping and I thought a nice example and this is one that I’ve seen working with several customers is being able to sort of do queries on aggregates and drill down looking at different groups of data with the aggregate values they’re in. So let’s jump into this example and this is all on the same example database. We are now looking at total sales for all of my orders and this is a pie chart, which is showing the dollar amounts of product categories that I’ve sold. You can see that I have, it should be called a group, not filter, that’s a typo. You can see that I’m grouped on categories of products right now. So you can just see that it’s showing the relative values and if I mouse over, I get the absolute dollar amount which is a SQL aggregate for that group. The cool part is is that if I say I want to click on what we got here produce, I’m going to jump in now into all the orders I have where, basically all the products I’ve sold, I should say, where the product category is produce and now I’ve changed my group to be grouped by product and so I’m showing all the different kinds of produce products that match that category and the dollar amounts and total sales of them. So if I go into the skinny little sliver for my long life tofu, now I can see all of the customers that have ordered long life tofu and what the total sales of that product in that category are broken down per customer and I could keep going like that. Maybe the next click takes me into a grid that shows a list of those individual orders or products or something like that. And then I used the breadcrumb component which we demoed in the last tech webinar to show how I could step out of this so I could go back to my category produce or I could clear the filter entirely and now I’m back at looking at the aggregate view for all categories. This is not some BI solution, this is just query builder with a chart and I’m just passing in the aggregate values into the chart. I did it this morning, it’s way easier than it looks, so let’s dive into it. So I made this form product query and I put the breadcrumb up here, I put the web component for the chart over here and I put the selected group over here just to show where what we were grouping on. But essentially what happens is when we start, we’ll dive into the code, we start by running this query. Again I’m creating a query builder object, this time off of, I picked the order details because that’s the most low level group of records that I have and then I’m going to aggregate out on that so that’s an order details, a line item on an order, it links to a product and it links to an order. So it’s very easy to build from that. I established all of my joints here so I want to know about the orders, the customers, the products, the categories, maybe the suppliers. Then I’m just using the relations so I can just walk my data model, I get code complete on all of that, it’s just bang it out, it’s really easy. The next part is the grouping which is one of the key parts of this example. I have a form variable called selected grouping which I’m keeping track of here. It starts off initialized to category. I also have sort of a path that we sort of drill down through and so I store that in an array here, category, product and then customer. So because when I click on one of the wedges I advance the group to the next one, excuse me, I also keep track of what filters I’ve applied and this is just a simple name value pair that I’m sort of stashing so that as I’m clicking on stuff, I’m applying filters, I’m regrouping and I’m keeping track of it and showing it in that breadcrumb. So first we want to determine what’s the selected group. If it’s category then I’m doing my group by on the query object. So again that’s one of the sections of the query, you just do query.group by dot add and then you add the column that you want to group by and it can be on any of the ones that you’ve built a join for. So I’m grouping by the category name and then I’m adding that as the result, the category name. I want to select category name, group by category name, I have to do group by because I’m using aggregates. So I determine my group it’s going to be one of those. Then I add my aggregate which doesn’t really change. I’m just doing again this is quantity times uni price and then I’m summing that together. So that’s the second, I’m returning two things, the group name that was selected, you know like the category name or the product name or the customer name, etc. And then the aggregate value for that group. So those are the results that I’m selecting. And then finally what filters have I applied. So at the beginning there were no filters but I clicked on a produce and so what I want to do is put that into my wear clause that I’m interested in all of these aggregate values, per group like I’ve set up. But I also want to say where the category is produce. So again I just do a little switch statement on the filter that I’ve applied which I’ve stored in that form variable. And I can have more than one that’s why I sort of iterate over those. If you look at this it’s not really an array, it’s an object. So when you iterate in JavaScript over an object you can sort of stash name value pairs like that. So basically I get the group name, I get the value and then depending on what the group name is I set my wear clause. So it’s really three parts, it’s the well four parts you do the joins, determine the grouping, add the aggregate, that’s the same every time it doesn’t change. And then determine what filters you’re going to apply so which wear clauses you want to add to limit the results. Finally I run the query, this is a bit different in the last example. I was working with a found set so I said okay found set here’s the query that returns the primary keys and it just it just loaded the records. Here I’m actually just running the query and getting a data set as a result. For those new to serve boy, JS data set is a simple object that basically stores rows and columns in memory. Remember that data set is really the group name and the aggregate value or the group label and the aggregate value. And so I have that list and then I’m just getting the two columns as arrays and I’m passing that into my update chart method. I’m not that concerned about showing you this because that has to do with the web component and how it renders it. I’m just passing in the labels and the values and having the chart render pretty easy to do. Finally and I know we’re running over time so the last part of this was the on the form the chart itself. There’s an on click. So when we did the drill down and this has less to do with the grouping so sorry the query so if you don’t get this it’s just sort of how I made the form work and less about the query itself but when they click on the chart it passes in the label and the value that were clicked and what I’m doing is I’m adding a filter there to my filter form variable which keeps track of those name value pairs. So if they clicked you know product category produce then I’m just saying you know product category equals produce. And then I’m also advancing to the next group in my path so I just go from if I was grouped on category now I’m grouping on product and then I just rerun the query. So it’s the same query just I have I tweak the options and then I rerun it every time I go back to this method. So it’s really this method that does all the work and passes the data at the chart. Okay Steve we’re a little over time any questions. Yeah there’s a couple the first question is is this solution available via GitHub? Yeah so it will be as soon as we hang up I didn’t have time to push it this morning but it will be on the same repository so I will spare you guys the two slides that I had about what we just looked at since we’re out of time but it’s nice to put this up as we end the webinar so some useful links there is the home page for all of the tech series webinars we’ve done also if you have comments or feedback for us and we love feedback because it helps us decide which topics to do go ahead and post that on the forum there’s a special spot on the forum where we’ve been announcing these and receiving feedback and also posting follow up material so after every webinar I go on the forum and I make a post and it has a link to the slides it has a link to the example solutions and whatever else we need so it will be on GitHub in the same repository example solutions where we’ve done a few others for this series so give me a few minutes after this call and then it’ll be it’ll be up there. There was one other question from the second demo can the table be sorted by the total sales column? Who asked that? Can the main sorted by the sales column? Okay so we’re looking at this one where we were going between the two men and max sales values with also with the additional filtering so someone might have noticed that I sorted by the orders which is that a simple aggregate that I put I don’t want to get confused you can also create aggregates as these design time objects and Servoyand you can put them on forms you can sort by them the total sales is not sortable immediately you can see that it’s not really doing anything however you can handle the on sort event of any form in this case that grid form and then you can sort in memory so if I had excuse me if I had 10,000 results in my in my grid here or potentially 10,000 it wouldn’t all be loaded at once I can only sort it what’s cached in memory if it’s not a real column so now I could sort I could put the sort from the query into the found set so I could pre-sort it but once it’s loaded I can’t click sorted I have to do that in memory so answers yes but it’s limited to just what’s loaded in the grid in memory

Close