Running Queries in AbacusLaw
Learn how to master Query Management, how you can use query mode to retrieve only the records you want, and be presented with exactly the data you need in custom reports, in this Free Training Friday webinar for AbacusLaw.
About Free Training Friday: Since the beginning of 2017, we have been holding these free, 30-minute training hosted by our industry-leading experts and innovators who will teach you about AbacusLaw, Amicus Attorney and the Abacus Private Cloud. Since then, we have expanded to also provide free Results CRM and OfficeTools Software trainings. If you would like to request a topic, please email webinars@abacusnext.com.
Video Transcription
We’re going to be talking about queries and I get asked this question a lot when I'm doing trainings; "What are queries exactly?" For those people who haven't ever used a database program or a case management program in the past, it can be kind of foreign to them. Queries are basically search filters, it's a way to filter out the data that you’ve seen in a report. It's also a great way to filter the data that you visually see within your databases.
If I'm an attorney at a law firm that has ten other attorneys and maybe I only handle the divorce cases, another attorney handles all of the adoption cases. It's quite possible that whenever I access my matters database, I only want to see cases that are assigned to me. We see that with larger firms. Also, we're creating some sort of a report inside of our report window. Maybe we're doing some sort of matters report, where we want to see only active cases within a certain date range. We can create those queries, so that's what I mean by queries. They're kind of like search filters.
We're going to focus specifically on queries from our databases, our names and matters database, but just so you know, it's the exact same steps, exact same logic if you are creating queries within your names, events or matters with this. The steps are going to be exactly the same once you get into that query building phase. For the sake of time, we're going to focus within our actual database from our cases here.
When you open up your matters database, like I have opened here, you'll notice in the top right corner, there's a button that says query and there's a little field next to it that may or may not have some text in it. Mine has text in it, has the word open, that's because I have a query set by default called open. What that does is only shows me open cases. If I have a bunch of cases in my matters database that are closed, I don't have to worry about scrolling through those cases here or searching within the closed cases as well because my system right now is only showing me open cases.
If I want to change that, or if I want to set a new query, I have my little query button here and in this query button, I've got two options that we're going to focus on today. The first one is our quick query, the second one is the query manager. The quick query is a way to set a quick filter, that's why we call it a quick query. When I select quick query, what it does is it opens up a blank matters window for me where I can set some criteria. For instance, if I only want to see cases that are assigned to a specific attorney, what I can do is click my query button, choose quick query, and set in my attorney field, that specific attorney's code. So I would choose that person's code, and notice how it now puts that person's initials in that field. When I click done down here, that is going to create a temporary query.
If you happen to have multiple queries going at the same time, you'll get this little warning here that's basically giving you some information about combining queries. It's asking you if you want to combine your queries or if you just want to use the new ones. For this case, I'm just going to use my new query where I'm targeting a specific attorney. Notice how the volume of my cases changed now. Right now all I'm seeing are my cases that have SH assigned as the attorney. That's my way of filtering the view that I'm seeing. Also, notice up here in my box, see how that switched to say SH temp? That's because we just set a temporary query.
If I want to clear that query out and see all of my cases in my database, I can click that query button again and we have the option right at the top of our list to clear the current query. That clears it out, now we have no queries set and we're viewing every case in our system.
The quick query option, again that's the second option in the list, is going to open up that window for you where you can set different criteria. Maybe I want to see only specific attorneys and only cases within a specific phase. I can do that, all I have to do is set those two fields. Any field that you have on this screen, you can make a quick query off of. That's quick query, short temporary queries to quickly show us the proper cases. The same applies for our names, if you're inside of that name window, your name database, you can do the exact same thing here. You still have a query button, you can click that, you can click quick query here and you get a name record where you can go in and maybe set something like zip code. If you want to see contacts that are from a specific zip code. We can punch that in, click done and I don't have any matching records in this one, but if I did, they would pull up.
It's the same logic no matter whether you're accessing from the names' database, the matters' database, or even the events database. I'm going to go back into my matters database, and we are going to talk about the actual query manager. We talked about the quick query, which like I said is temp. The query manager is where you can create static queries that you're going to be using over and over again. Usually you're building queries in the query manager that are a little more elaborate. Maybe you want to create a query that says show me all of Johnny's cases that are divorce cases that started in January. Those are three pretty specific criteria, so we would want to probably create a static query using the query manager here. If you go into the query manager, this is going to open up a list of all of your current queries that you already have created. My list is probably going be much more elaborate than yours just because as a trainer, I train on quite a few different versions of the program, but you'll have a pretty decent list in here.
One of the queries you'll definitely have by default is called open. This is a query that comes with your program. If you just want to see open cases, in other words, you care about all those closed cases in the database, you can just set that, click done, and then it's going to ask you which record in the set you want to go to. You can say first, last, next, whatever. Usually people say first. That's going to show you all open cases. What I invite you to do, if you've never been inside of the query manager before, get to know the queries that came with your program, take a look at them, understand the logic and the idea behind what they actually do. A good way to see behind the scenes of the queries that come with your program, is to just highlight it and click edit. This is going to open up the query builder and show you the fields that the system used and the comparison that system uses. We're going to dive deeper into that right now because eventually what's going to happen is, you're going to want to modify these queries, or even more, you're probably going to want to create your own.
When you create your own, and you're in this query manager, you have an add button, top right corner. If you click that add button, it's going to open up a blank query builder window. In this window, we want to obviously give our query an ID, give it a description, another thing, too, you want to make sure that you're doing here, and I tell people this for anytime you're creating something in the system and other people could potentially be using it, be thorough in your description. Be thorough because you're not the only person who's going to be using these queries. It's always nice to have a nice description in there and if a new hire comes in, a new attorney or somebody like that, they can look at the description and they can be able to tell exactly what it is.
Once we give the query and ID, the query a description, now we need to go down here to our actual expression. If we think about this logically, what we're doing is basically telling the program what fields in our system we want to create our queries off of. If we either double click on this line here or click add, it's going to open up our expression editor. This is where we tell the system, first of all, what fields do we want a query off of? I would choose from list of available matters field, when I click that little arrow, it's going to open up all of my fields from my matters. You'll need to go through this list and choose the field that you want to start your query on. I may choose something like attorney, or something like that. Then, I need to set my comparison. The comparison is your way of telling the program how do you want the system to judge this field.
My comparison is pretty straight forward, I'm going to say something like “is exactly equal to, is greater than, is less than, is empty”, words that start with, different things like “that” or maybe, “not equal to, not empty”. If I'm looking for cases that are assigned to a specific attorney, I want to first put in my attorney field and my comparison would be is exactly equal to. Then I put in the value. The value is what we want to see in that attorney field for this to be true. I happen to know that my attorney's initials are SH, so I would put SH in the value. With this expression right here, what the system is going to do when we run this query, is it's going to go to every single matter record and it's going to look at the attorney field. If it is exactly equal to SH, then that matter is going to show.
Now we click done, that's one log. You're going to be building out queries that have multiple expressions, comparisons and things like that. You can add additional lines to each one of these queries. Just click your add button, and when you click the add button, it's going to ask you "How do you want to combine the previous condition with the next?" In other words, is it an and” or is it an “or”? If I'm looking for all of the personal injury cases assigned to attorney SH, both of those things have to be true. It has to be assigned to SH and it has to be a personal injury case. For that scenario, I would click and. When I click and, it opens up my expression editor again, where I have to then put in the rest of the expressions. What field am I looking at? I'm looking at case code, which is also the type of case. Then I do my comparison, now for this one, if I want it to be PI injury, then what I would do is exactly equal to and then I'd put in the value; P-I-I-N-J. Then I click done, now I have both levels to my query.
First thing that has to be true is the attorney has to be SH, second thing that has to be true, is the case code has to be equal to PI injury. You can keep building these up, and's, or's, just remember your and's, whenever you have an and, those things have to be true. Whenever you have an “or”, it's one or the other. You'll usually have your “or’s” at the end. I'm going to go ahead and click done and there's our query. If you want to get a feel for how many records meet the criteria of this query, you can actually highlight the query here and then click the little button that says counts in the bottom left, and it will give you a count of each one of those. I don't have any that happen to meet that criteria, that's all that means.
If we go up here to open, see how there's a count of 36? That just means that there are 36 cases in my system that are currently open. Whenever you hit count, that's actually a great way to identify whether your query was built accurately without even running it. If you happen to know that you've got 150 PI injury cases or somewhere around there in your system and you want a count and it comes up zero, you know that there's something wrong in your query, so you'll need to go back to that query editor and maybe make some changes.
All we have to do now that we have the query built, is select it, click done, and now that's the query that's in play. If this is a query that we always want to stand, we want it to be the default query, which is very popular for the open query, there is an option right here in your little drop down menu to set the current query as default. That way, every time you open up your matters browse window, that query is always set unless you change it. Very popular for the open query, and that's very popular for the query that you build for specific attorneys.
That's basically queries in nutshell. Quick query is going to be one you'll adjust quite often, just as a recap, that's where you can just enter in your filters using the fields here. The query manager is what you're going to use for your static queries that you're probably going to be using over and over throughout time. Create those here and you'll be able to apply them whenever you need to.
Q & A
How would you create a to-do list?
That would be more from a reporting type function. You can actually just print your to-do list directly from your calendar if you need to. There is a print button there and when you click print calendars, there is a to-do list right here that you can set up by date. There is a set-up option in here as well. There is a setup option there as well, just to show you that again. Select to-do list, go to set up, and in here you can set different types of who filters, what filters, time filters and things like that. That's one way if you're just trying to print a quick to-do list, how to do it.
If you're speaking more from a report standpoint, you want to actually print out an elaborate report, you can go into your report menu for events and in here, under your query manager, you can create all kinds of different to-do's. We've got some that already come with your system, you'll want to take a look at those. We have quite a few different events queries as well. There should already be some that are created for you.
We have many old queries on our system. Would it be alright to delete all and start over, or do I need to know what originally came with Abacus and leave them alone? We have had Abacus for over 20 years.
Wow, thank you so much for being so loyal, we definitely appreciate that. To answer your question, if you delete a query from your query manager, this isn't really going to break your system. It's not going to delete any case data, it's not going to hurt anything like that. If you accidentally delete one, the worst thing that would happen would be you'll probably have to rebuild it. The short answer is no, it's not going to hurt anything if you want to delete old queries that were originally in your program, you don't ever use them, not going to hurt a thing. Go ahead and get rid of those. You'll see this a lot with the queries that come in our specialty versions, maybe I am a family law firm, but I only do divorce, I don't do adoptions, I don't do paternity, I don't do any of that. I may come in here and get rid of things like paternity. All you have to do is just click delete and that will remove them from the list. If for some reason you delete something or maybe down the road you say, I really wish we wouldn't have deleted all those queries, we can always reinstall those things for you. You can just contact our tech support and we can get it done for you. Short answer, it's not going to hurt anything if you delete a query.
How do queries work when using extra databases?
It's going to depend on the database, but really they work almost exactly the same. If you want to get together, we can dive into that. It's a little bit more elaborate than what I showed you today, so maybe you and I can schedule some time to get together and talk about that, we'll have a little session and go over it. The logic behind it is really the same way.
Can you create a query on the accounting side?
You can't, queries are really built specifically for Abacus law, but I'd be curious to know what you mean by that, like what information are you looking to get, if you could just give me an example. Feel free to just follow up in the chat and if I don't have an answer for you after that, I'll follow up with you afterwards and we'll talk about it.
When we use queries under setup > reports > matters, can we include fields from names window (database), as well?
That goes back to the cross-reference question, which is kind of in line with the extra database question. That's something that you can do, it depends on the report. If you're inside of your report menu, let me pull that over for you, let's say we have a matters list report and we click edit right here, there is a sub report option and also notice there is a query option built in to each report that you can set. If you have sub reports that include linked names with your matters, absolutely, you can set embedded queries for each one of those. Basically you're just harnessing data from the matter database, and you're merging it with the related data from the names' database. What I would invite you to do is inside of your matter report control window, don't necessarily look here at the query button, but look more here at the report button, and then highlight the report and edit and take a look at those sub report options and take a look at the query options that you can list as well.
That's a very good question. One thing I want to point out to everybody, if you're going to be modifying reports, modifying existing queries and you're not 100% confident in your skills, that's okay. There is a close option in each one of those windows. If I'm going to go in here and I'm going to start messing with the default queries and the sub reports for these different matters, it may be a good idea to clone it first, give it a new name, Scott's matters report. That way, if I mess up, or if I don't like it, I still have my original in place. That clone button can be a life saver.
Can you specify a query, like all open adoption cases, opened after January 1st?
What you're going to do there, that all comes down to your comparison, which is really what that comes down to. If we look at any type of query that involved a date, for instance, opened in 2016, notice our comparisons here. Opened, greater than or equal to 1-1-2016, open, less than or equal to 12-31-2016. That's going to show me every case within that date range. If we apply that same logic to all option cases opened within a date range, or after a certain date, all we have to do is just mimic this exact query here and just add another line to it that says case code equal to adoption. I would say case code is “equal to” adoption and open date is “greater than or equal to” January 1, 2017. Great question. The comparison's very important when you're building out those expressions.
Can I do a query to know what all the e-mails are for a specific day, week, etc.?
That's one that we've actually gotten questions on a few times before. There isn't a way to do that right now. That's just because e-mails are actually an external source that get linked to a record. There's no way to do it default out of the system, however, what you could do is customize your fields in your screens and have some sort of identifying marker, identifying field. Whether it's a field or a check box or something that says, that e-mails have been linked to this case and then you would build your query off of that. There might be some workaround ways to do that, but as of right now, just going into this little linked e-mail window, no, there's no way to really create a query report based off of that just because the e-mails themselves aren't actually stored inside of Abacus. It's really just a link.
How do you display the query properties in a report?
Great question. If you're under file > reports, and say we're wanting to look at matters. It's the same no matter what you check here, doesn't matter. When you go in, you'll have your query button here. I guess this is a two part answer. If you want to see the properties of any of the queries in your query manager, just click query manager, highlight the query and select edit. That's going to show you how that's built out. If you want to see the query properties of any query embedded in a report, you need to click this reports button, find the report, click edit, and then there's a query button there. Anyone that's checked here, that will be the one that's applied to that particular report. I don't think this one has one applied, but you would see a check right there, all you would have to do at that point is just highlight it, click edit and you can see what that query actually entails.
Want more Free Training Friday? Register for upcoming webinars here!