Watch the recording by clicking on the play button
Transcript
0:00
Okay, welcome everybody and thank you for attending, just a few housekeeping will start with so all attendees will remain on mute for the whole webinar. If you do have a question, please use the question box if it’s related to a topic, or the chat box, if it’s related to the actual webinar. So for example, you can’t hear we will answer questions after the demo, but if we do run out of time or if the answer does require a bit more time to be spent on it will answer directly offline.
0:30
Please note that a link to view, the webinar recording will be sent out to all attendees and shortly after the demo.
0:44
Apologies. That was our fire alarm test. So this is the 18th in our series of monthly webinars on key areas of NAV and Business Central. You can sign up for as many as you like on the website and I’ll show you the list of upcoming ones at the end of the session. We also send out regular emails to register for the next one. My name is Karen and I’ll be facilitating the demo today. I’m an account manager here at TVision and I’ve worked here for two years and my background is 18 years of software management experience working for ERP companies.
1:08
Ian will be doing the demo, and he’s a support consultant here at TVision and he’s been working here for three years and it’s been using NAV since 2000.
1:22
Okay. So on the subject to Smart Filtering, filtering is a great way of utilizing the data that you have in NAV or Business Central in order to answer key questions, find out key information or to produce a report. So they’re many ways to filter the data depending on what you’re trying to do. It could be by date range, value, or dimension. For example, you may want to have a view of your top 10 customers. You may know who they are and just want to have a list and pick them manually.
1:51
Or you might want to determine who your top 10 customers are by sales revenue, or for example, those which pay on time. Or it could be that you want to use a combination of two or more values. So do you want your view to change a certain parameters change? So using the previous example, if your top 10 is decided by sales revenue, then this list is likely to change if your customers purchase more of your products or services, or if indeed you get some new customers on board.
2:21
Once you filter your list, you may want to save it for quick access. You don’t have to go through the same process again. Another good thing about filtering is that the keyword or value that you’re looking for doesn’t have to be at the start. So by using a wild card or typing in an asterisk at the beginning, will allow you to search for that, bat you anywhere in the field.
2:45
Okay. So these are the top 5 things about smart filtering that we’re going to discuss and show you today. First of all, the terminology. When you’re using filtering there is a certain terminology that you do need to understand and use, so Ian will be talking about that. Then static versus live data filtering. So do you want the list doesn’t change or do you want it to change as a when changes made in the system. Quick versus advanced filtering so you can have a quick basic filter, or you can get me a bit more advanced and start using filter combinations.
3:13
We’ll show you how to save views and where they’re saved, and finally transactional filtering. So filtering at transactional level is great if you have an invoice query or to get key questions answered by people from sales or management or in other departments you come up to you and ask you for something very specific and they want it quite quickly. Okay. So before we do the demo, I’m going to launch a quick poll just to see how much you’re currently using filtering.
3:42
And so if you want I’m putting your answers in and once I found out the results, I’ll let you know and then I’ll hand over to Ian. So I’m just going to launch it now.
3:54
If you wouldn’t mind putting in the results, that will be brilliant.
4:00
Okay, they’re just coming through now.
4:09
Just probably wait a few more seconds just to see who comes through.
4:15
Okay, so it all looks like a lot of you use it every day and it’s essential, which is brilliant. So hopefully you can add just a few more tips and tricks to use. So 80% are saying every day, 20% are saying just for ad hoc queries and reports only.
4:36
Okay, no problems. So I’m now going to hand over to Ian who’s going to show you the top five things about Smart Filtering. Thanks Karen. Hi everyone. Today I’m using Business Central. I’m using the latest version. I’m using the on premise version today just to be a little bit different. I’ve used the web in the previous few, and this is a standard installation. So there’s nothing fancy going on here.
5:06
And for filtering, I’m going to start off by kind of going to my customer’s page.
5:12
And one of the important things is filtering, we use on any screen in Business Central or NAV where you have a list of information. So here I’ve got a list of customers, I can use filtering, if I had a list of vendors, a list of bank accounts or even my chart of accounts, which is really just a list of general ledger accounts – then filtering is enabled.
5:35
If you just in one record, so you’re in a customer card, then you don’t really have filtering capabilities. The other place where you can find filters is on reports. So whenever you run a report one of the first things that it does, it asks you some questions and these are actually filters. And the filters that you see here are the same filters that we will see on our pages. So whatever you do here you can do in reports and vice versa.
6:10
So just looking at our customer screen and usually I expect to see my filters in this area somewhere. If you don’t see them, you can turn them on by putting in the ticks here, or, you can use this up and down arrow, collects and expend to show the filters.
6:30
Now you got a few types of filters in here. So this one up in the corner. This is your quick filter. So if you just want to filter on one field very quickly, you can type it in here hit the little button and that filters your list.
6:48
You’ve got your advanced filter on this side. So if you want to do multiple filters, you can do them in here. And this will filter on any field that’s visible in your display, plus some background fields. And we also have a limit total filter. And the limit total filter, that’s got a very reduced list of things that you can filter on.
7:15
Currency is usually there, your date filter is usually there, your two Global dimensions, in this case salesperson and team, they’re also there. So if you’re wanting to filter by date by your Global dimensions or by currency, you might need to look in the limit totals area. So those are the three different areas of filtering – these two are the same ones that are available on reports.
7:42
This one is unique to the screens on the system. One nice thing with the quick filter, if you have a list, and let’s say I want to see all the customers related to location yellow, if you right click on that you get a filter to this value option and that fills in your quick filter for you. And you can immediately see I’m only seeing customers related to location yellow. To turn off your quick filter, hit the little X and I see everything. One of the things that Karen mentioned, was the difference between static and dynamic filters. So what do we mean by that? If I filtered this list of customers, and I chose to view let’s say these five or six customers. That’s a static list. It doesn’t change these six customers are always these six customers.
8:44
But what if I chose to filter by something that does change? If I look at the balance for these customers and if I chose to filter by everyone who had a balance of 1499 pounds and three pence. As soon as any transaction is posted against Progressive Home Furnitures, they don’t have that balance anymore, so suddenly my list changes every time transactions are posted. So that’s the difference between static and dynamic filtering.
9:14
They’re both very useful. But this dynamic filtering comes in really handy a little bit later on when you want to start saving interesting views. The other thing is, if we look at the list of fields which we can filter by – number, name etc.
9:34
If I look at that drop down there, I can see there’s a good dozen fields that I can filter on, and you will notice that it’s the same as the column headings that I’m seeing here. So one of the bottom of the list is search name if I scroll to the right to my page. My last column is search now. If I change the columns that I’m viewing here, and I remove search name.
10:07
So it’s not there anymore. It’s disappeared from my columns, and I will notice it’s not available as a field that I can filter on anymore. Not to worry. We’ve got a little all button at the top, and this shows you all of the fields that it is possible to filter on, and you will notice there are an awful lot of them, and this includes hidden fields in the background. Fields that you’ll never see as column headings.
10:36
So if I scroll right down this list I can see there are one two, three four different fields that have got something to do with that that I can filter on. But if I looked at the columns that are available for me. I only have one column that I can add to my report that’s got something to do with that. So those hidden columns that you can see, you can filter on them, by going to the all button to find them.
11:04
Right. So let’s start off by creating some nice sample filters. I’m going to go to a different list, which is a bit more interesting, Custom Ledger Entries.
11:17
You’ll notice that when I search for something, I don’t type the entire thing in up here. I just type in the first few letters of each word. That usually gets me to where I want to go.
11:26
So if I’m looking at custom Ledger entries, this is more interesting, because we’ve got some columns of numbers, some columns of data etc. As I said if we want to do a quick filter, we just wanted to see all the credit memos, for example, we could go up here. We could type in credit memo in the document type and hit the little arrow, we can right click on credit memo and save filter to this value, either way the quick filter very quickly knocks down your list for you.
11:56
But if you want to do something a little more advanced, and let’s say, we want to search for things based on a posting date. So where the posting date is equal to the 18th of the first 2021.
12:17
As soon as I hit tab or enter, whoops. Oh, dear. I’ve put in a very bed value there. 2021 that’s better.
12:27
I see all the transactions for that date, but there are some little formulas that you can put in up here that expand, rather than just looking at a date. Whenever I’ve got a date I can put two dots behind it, and that means everything from this date going forwards. As soon as I press tab, now, I’ll see every transaction from the 18th of the first and after, if I did it the other way around, and I put the dots in before the date, that means from the beginning of time up until the 18th of the first.
13:05
And now my list is changed and I’m starting to see the stuff from prior to the 18th of the first. You can also give it a start and an end date, so I could take it from the first of the first for example.
13:22
And now we can see everything in between and including these two days. So that’s very nice on the dates, so I can do for a specific date, for a range of dates, up to and including one date, or for one date onwards. But there are some nice shortcut keys you can put on the dates.
13:45
For example, if you just put in the letter T, that gives you today’s date. And that’s the date according to your computer – so the 17th of the 9th 2019. Don’t have any transactions for that date, but the nice thing is if I click there, it remembers that I put the T. What I could also put there is a W. W is my working date, which you can see the bottom of my screen I’ve set to the 29th of January 2021. So as soon as I tab off that one, now it shows me transactions for the 29th of the first 2021.
14:22
So there’s two very useful shortcut keys, but there are a few more that people probably don’t use very often. If I put in an M. It will find the Monday of whatever week this date is, and we’ll put in that date. I checked earlier, and the 25th of January 2021 is Monday of this week. So it’s showing me transactions for Monday of my current working week.
14:52
If I wanted to get Tuesday, I’d be tempted to put a T, but remember that gives me today’s date. So it’s at TU gives me Tuesday’s date, the 26th. W gives me the workday, but WE gives me Wednesday’s date, TH Thursday, F for Friday, SA for Saturday and SU gets me Sunday.
15:22
Based on my working date, there is another shortcut. CM. CM gets the ending date of the current month. And again, that’s based on today’s date, not my working date, so it’s going to give me the 30th of the 9th 2019. So this months ending date and CW.
15:43
Gives me the end of the current week based on today’s date. So that’s very nice. I can get to any one of those dates. But again I can use those dot dot filters and I can extend them. So instead of just looking at Monday’s transactions, I can say show me all the transactions from Monday going forwards.
16:08
And suddenly I’m seeing everything from Monday and after. Or I can do everything up until Sunday from the beginning of time up until this week Sunday.
16:21
I get the list. I’m very nicely it remembers what I typed in it doesn’t convert it to date for me. So with the dates there’s those little shortcuts of using the first letter or the first two letters of day. CM for the current month. W for your work date, T for today and you can use those dot dots with the shortcuts.
16:49
So we’ve got this list. I’ve chosen everything up to and including Sunday, but we can do something a little bit more interesting than that. We can combine filters together. So let’s go choose all the ones where the value was for more than 1,000 pounds. Let’s use the amount local currency.
17:13
Simply add a new column, a new filter, find the amount local currency, because I can see it as a column, I know I’m going to find it in this list.
17:25
And everything where the amount is greater than. So I use the greater than symbol, 1,000 pounds. If I wanted everything less than a thousand. Guess what, I’d use the less than symbol. I want everything greater than.
17:46
I also want anything that’s equal to a thousand, so greater than or equal to 1000, show that to me. As soon as I press tab, now I’m seeing everything up to and including Sunday where the transaction value is more than 1,000 pounds.
18:03
We can go further, we can add more filters, and we’re only really interested in the ones which are in US Dollars. So we simply add another filter. We find the currency column, which is, there is it, can’t see it for looking, I can type in USD or I can search in the drop-down box, but this is easy. I’m just going to type it in.
18:32
As soon as I press tab now I’m seeing transactions up to and including Sunday for more than a thousand that are in USD.
18:41
So very easy to combine things.
18:44
But sometimes it does get a little tricky. What if I only want to see the currencies which are blank? So they’re in in GBPs.
18:54
If I add a filter and I say where the currency code is blank, I would expect it to give me the blanks, but it doesn’t it gives me everything. There is a specific way of showing it that I’m interested in the blanks, and that is to open a single quote and then close the single quote without typing anything in between just so the system knows I’m looking for nothing.
19:21
And as soon as I press tab, now it shows me all the blanks. So there’s a nice little shortcut for finding the links. On the values, you can use just put in the number 1000.
19:33
It can find those transactions exactly equal to 1000. You can use less than, less than equal to, greater than, greater than equal to. And again, just like you did with the dates you can use the dot dot convention, so you can see everything from 1000 up to 2000, and you can do the range if you want it to. So now I’m only seeing between 1000 and 2000. So you got a lot of variety and what you can do on each filter by itself, and then you can combine filters together to make it a little bit more advanced, and if you want to get really advanced, you’ll notice I’m only showing my advanced filters here.
20:18
Let me turn on my limit filters to. And we can go and add where the date filter, which is going to be the remaining amount I believe, you can put in a date up to which point it needs to add these numbers up. So you can start combining a quick filter with advanced filters with a limit totals filter if you really want to.
20:50
And as Karen mentioned, very good for answering an ad hoc query, so somebody comes in and says oh up until Sunday all the transactions between 1,000 and 2,000 pounds that are in GBPs, can you tell me what they are?
21:05
Filter my list, highlight it, copy the rows, paste it into Excel and send it off to that person.
21:13
Okay, let’s get back to a customer page, and we’ll have a look at some other fancy things we can do with filters. So I’m looking at customers now. I’ve just got a limit totals filter set to my work date, and you can see its effect. The customers have a balance that balance due up to and including this date. That’s what my limit total is doing.
21:42
We can do some fancy filters where we can select more than one thing at a time. So let’s say I wanted to see just customer 10,000 20,000 and 30,000, now you probably know how to do this. You type type in 10,000 you put in that little pipe symbol.
22:07
Twenty thousand and again the pipe symbol, 30,000, As soon as you tab off you’re just seeing the ones that you selected in the list. This is a static list, those customers, are those customers and they’re not going to change. Whereas if I’d done the filter on the balance the balance changes remember, so customers may come on to fall out, based on my filter if I start thinking on these columns.
22:39
There is also a wildcard filter, which you’ve probably used before.
22:48
The asterisk or the star, whichever you want to call it. So this is saying show me all the customers that start with one with anything after that.
22:59
Now there’s only one customer who starts with a one and that’s 10,000, if I said all the customers who start with two, with anything after the two, it gives me all the customers who start with two.
23:13
And again, we use that dot dot convention on amounts. We use the dot dot convention on dates. We can also use it on customer numbers. So everything from 10,000 up to 20,000.
23:33
I only get two in my list. Let’s take it up to 30,000.
23:39
10,000 20,000 up to 30,000. Now, you might be a little bit upset but it’s brought through these numbers here which are much bigger than 30,000. This is where we have to remember the way that Business Central stores our information. It’s not storing these things as numbers. It’s storing them as text. So 30,000 is bigger than all of these because it starts with a three.
24:06
So that’s the logic that Business Central is using. So the sort, order, and the way that sorting is important.
24:17
Now let’s put all of this knowledge together. Let’s say I’m in sales. I want to get a list of all the posted sales invoices for this week, a question that someone might ask. First thing go to my posted sales invoices.
24:37
So currently I’m looking at all of my posted sales invoices. I want to get all of the ones for this week. So I’m interested in posting date, which I can see here. Nope. It’s not here on my list. So it’s not one of the columns that I can see. Don’t worry we go to the all fields. We scroll down to P for posting.
24:59
Posting date is there. So just because we can’t see it, doesn’t mean we can’t filter. I want everything for this week, so I could type in this week being January 2021. I could type in 25th of the first 2021 dot dot, but that’s hard work. I know I don’t like hard work. So let me know the type in everything from Monday, up to and including Sunday. Much quicker way of putting in my dates.
25:34
I now have a list of all the cost of sales invoices for this week. What I can do with this list. This is something that I want to look at every day. I’m going to look at this two or three times a day. I want to save this so I don’t have to keep typing this in.
25:53
I come up here to my heading, I click save view as, I give it a name: my sales list, for example, I choose where I want it to go. So at the moment I’m saying goes on the Home tab. Boom. So it’s up on this list. I can choose any of these menu options down here, but I’ll leave it on home, I say, okay.
26:23
These effects will come into place next time we restart some just restarting quickly.
26:29
And now on my home tab a new element has been headed called my sales list.
26:37
And there it is.
26:38
I’m looking at all of the posted sales invoices from Monday to Sunday of this week that I’m interested in. I can if I wish, come and save this and if I click there even remembers what I typed in Monday to Sunday if I change my week to Sunday to Saturday, for example. So that puts it all together next week when I change my working week and I come and open this, it will have different dates here. It will recognize Monday of whatever date I’m working with. So this list will keep changing according to today’s date.
27:23
So again, that’s another way of getting a nice Dynamic list that you can use again and again. And that’s it from me guys. I’ll pass back to Karen unless there are any questions. Okay. So if you do have any questions for Ian about what he’s shown you so far or anything else with filtering, you just want to submit them in the question box has now we’ll just wait a couple of minutes of people to do that.
27:56
Okay, we have one at the moment. So are there any limitations with filtering? Are there limitations? Well, yes. There are and no there aren’t.
28:12
So on my advanced filter, I can add here as many of these as I want the systems not going to complain but in all seriousness if I end up filling the screen with filters and I put a value in even each one. It’s going to give really slow and if you are putting in 20 filters to try and answer a question, you probably looking in the wrong place and maybe we should take a step back and look somewhere else for the information. There is another limitation. So if I choosing a list of customers I can put in up to 250 characters in this field. So if it’s going to get bigger than 250 characters, we’re going to have to find a different way of filtering our list.
28:55
But apart from that there aren’t many restrictions. There’s another one here. How is filtering better than just using standard reports or account schedules? Well, it’s not necessarily better. Sometimes it’s more convenient. For instance.
29:17
If you don’t have a standard report that gives you this week sales, you can get it very quickly without having to ask someone to write a report for you. And also remember when we’re looking at the standard reports on the system. So let’s see the customer order summary report. That’s same stuff that you learn about filtering your lists on screen. You can use them to filter your reports. So you can start using filtering hand-in-hand with reports. They actually make your reports more meaningful.
29:55
Another very useful feature, is if you’ve got hundreds of thousands of transactions, you want to head that copy and paste into Excel hundreds of thousands of rules. I think when it gets to about 65,000 your computers and stop complaining. So sometimes you have to filter that this down to a smaller number before you can move it to excel so I wouldn’t say its better but they work hand in end and sometimes it just helps you to get a quick answer really.
30:25
I’ve got another one here. Can you use all ‘or’ or ‘as well as and’ as an argument? Technically, yes, so that’s actually a very good question. So when I chose that list of customer 10,000 20,000 30,000 I was actually saying ‘or’ 10,000.
30:47
‘Or’ 20,000 ‘or’ 30,000. So where the customer number equals this number or that number or the next number? It gives me those three. We can use ‘and’ but you will not get the results that you expect.
31:10
So if I put in the ‘and’, it doesn’t give me anything. So what I’m saying, there is show me where the customer number equals 10,000 and 20,000 and 30,000 at the same time. And there is no customer who has all three customer numbers at once. So it works slightly differently to the way our normal logic works, what the ‘and’ is really good for is for a backwards kind of filtering.
31:39
So when we did that pipe, we added new elements to my list. So I said 10,000, pipe 20,000 added another one to the list. Sometimes you want to take things off your list. So I’m showing all the customers I don’t want to see.
31:59
Ten thousand now 10,000 has been taken off the list. So it’s where the customer number does not equal 10,000. If I don’t want to see 20,000 as well.
32:12
Instead of using the pipe, I have to use the ‘&’ sign and not equal to 20000 and we should notice 20,000 disappears and the same thing to remove 30,000 I’d say ‘&’ not equal to thirty thousand, so the pipe which is actually ‘or’ add things to the list, the ‘and’ which is an ‘&’ is used when you want to take things off the list. Okay. Last one is are there any shortcut keys as in the earlier versions of NAV?
32:42
Off the top of my head I cannot remember the shortcut keys from the earlier versions of NAV, but they should probably still work. And whoever’s asking that question if you want to drop me an email, I will have a look into that for you and get back to you afterwards. Okay?
33:07
Okay, so I’m going to leave you with the top five takeaways about smart filtering which we heard will assist you. So smart filtering is as powerful as information you put into it, as you’re filtering on a specific field or value in that field, then the more information you can have about the customers’ vendors’ accounts transactions more granular you can actually filter by.
33:28
It’s a live versus static data views. Live is dependent on a value. So it will change, whereas static allows you to pick what you want. So really you need to kind of understand what changes what doesn’t, and have a good think about what you’re trying to do what the end result is before deciding if you want to live or a static view. Now remember there are hidden field in every Business Central that you can filter by, these are background fields which aren’t visible in the standard pages and some of them may be system calculated.
34:01
So when you are filtering have a good look at all the fields that you can filter by some of them might be very useful for you. And think about how much time smart filtering can save you rather than an Excel data dump. So for example, how long do you think it would take to dump out 80,000 transactional queries into Excel for having to work on the custom formulas. It might also slow your system down. So then realize how much quicker and easier it could be to filter on the data first and then export if needed.
34:32
Okay, so we hope you found this webinar interesting and informative. This is the 18th in the series webinars as previously mentioned and we will be sending out emails soon to register for the next one. We’ll put a reminder. We do have a plan for next year’s webinars as well. So have a look at the website and you can start registering for those too.
34:52
Okay. Thanks for attending. I will close this section and Survey will appear. It would be great if you could respond with some feedback or any further questions that you have. If you do want to respond afterwards, and feel free to email me at marketing@tvisiontech.co.uk. Thanks very much. Bye bye.
You can view another of our webinars in the series here. Alternatively click here to learn more about Business Central, or get a quote with our Estimate Generator.
