Using the Advanced Filter

AI-generated Transcript by Loom.com

00:00 Hi, welcome to our seventh filters video. In this video, we will be continuing the discussion of the filter sentence, but in a very big way, moving on discussing the advanced filter.

00:13 Let's continue using the account search screen accounts, search accounts, and we could stick with account look up standard for now, maybe we'll switch that and let's click filter and let's build a new filter.

00:27 Call it seventh video. So, we have the account section basic. Now, we're going to talk about the advanced section, but let's start off with the basic filter and we'll explain why we would need to go on to the advanced section.

00:43 So, as we know, the filter is a sentence. Now, let's put a really classic filter on this filter will pull any accounts, which have at least one single, we're just going to do addresses again, it's just a simple one to use, which meets the following criteria.

01:00 Let's say, forgive me, I'm just going to do city field equals lake wood. Okay, this filter will pull all accounts, which have at least one single address for in the city field equals lake wood.

01:12 We have a lot of things that we could do on this basic filter tab to adjust this filter. We can say, or even if the city field on an address that the account has is cedar, or even if it's Brooklyn, we also could make the filter stricter and say, that let's say the zip code has to be a specific zip code

01:36 , just making these up. And this would mean as we've discussed 100 times ready. Sorry, this filter will pull any accounts, which have at least one single address.

01:47 We're in. Let's go to our first column. We're in the city field is lake wood and the zip is 11857 or we're in the city field is cedar, and the zip is 114, and the city field is Brooklyn and the zip is 74521.

02:04 Now, there's a lot to do. You could change the operators. We could say this is not equals to all the stuff that we've discussed so far.

02:13 We can do all these things on the basic tab. Now, when would we need the advanced tab? So if you notice all the adjustments, the leniency is the stringent sees, whether it's an or whether it's an and all of them happen within this grid.

02:28 But let's remember. The filter is a sentence and the sentence begins here or if we have a little period here.

02:39 So we'll call it two sentences. Okay, let's just pretend that period is not there. It's it's one. Okay, let's not call it a sentence.

02:45 Let's call it an instruction in normative English. Okay, I'm just going to call it a sentence for argument's sake. So the sentence begins here.

02:57 This filter will pull any accounts, which have a single address, which leads the following criteria. Now, while we could do whatever we want in this grid, we can't change the beginning of the sentence.

03:11 This whole part. This filter will pull have a single address. We can't change that whole first part once we get more normal for now because we're going to go on to the event.

03:22 So let's just take out this whole row and and let's take out these ores. So now the filter says this filter will pull any accounts, which have at least one single address when the city field equals liquid.

03:35 Let's say I don't want them to have any other address. I don't want them to have an address anywhere outside of liquid.

03:42 I only want people who only live in like what all their addresses are in liquid. I don't necessarily care in this filter.

03:51 How how many addresses they have in liquid. They could have 10 houses in like good. But I want them to have at least one address in liquid.

03:59 And I don't want them to have an address that's not in liquid. But how can I get to do not have now if I try over here.

04:19 Okay, have addresses where the city field equals liquid and what what what what I do. But we don't just want to switch this to do not have addresses.

04:30 Where the city is not liquid. Right. This would mean this filter will pull all accounts, which do not have any address.

04:39 We're in the city is not liquid. Now, the reason why we don't want to do this is because this wouldn't tell us what it does have.

04:46 As far as this filter is concerned if the account does not have an address that's other than liquid, it'll pull it.

04:54 It might have no addresses. It'll still put because all we're saying in this filter is that we want the accounts to be pulled.

05:00 If they do not have any address where in the city field is something other than liquid. So we're stuck. This filter will pull any accounts which have addresses where in the city field equals liquid means that they have an address and liquid.

05:16 But it doesn't tell us whether they might have an address also in Cedarhurst. And if we would do this filter will pull any accounts which do not have addresses that are other than liquid.

05:27 This would tell us that yes, there's no address that's other than liquid, but do they have an address and liquid?

05:34 Maybe they have no addresses. So we need to really escape the beginning of this sentence because we can't have a criterion that needs the word have in the beginning of its sentence at the same time that we have a criteria that needs the word do not have at the beginning of its sentence in the same basic

05:54 filter. That is the first reason why you would need an advanced filter. So once you realize that the basic filter is not going to be enough for you and you're going to have to escape the words in the basic filter, you'll click on advanced to add another part of the filter which will let you escape the

06:14 sentence. Now it's very, very important to realize that since based on the filter I'm going to put accounts that have addresses other than liquid will not show up.

06:29 I really shouldn't need to put this exact filter as my first step of the filter because if I'm going to say that the accounts that show up, cannot have an address that's other than liquid, that means that if they have an address it will be liquid.

06:53 So technically I don't even need this road. I could delete that row and just say that they have addresses and then I'm going to filter as I just said for not having addresses that are other than liquid.

07:06 Now, why am I not going to do that? Because actually you could technically fill out an address without filling in the city.

07:14 You can put an incomplete address into admire the city fields could be no, no is just the absence of any.

07:23 When we're going to say on our next step of the filter that we only want accounts that do not have a city field that's other than liquid, it's still going to let in counts that have addresses with city fields that are empty.

07:38 No city is not called a city that's other than liquid, it's just called nothing. So I really do want to make sure that the accounts have addresses in liquid.

07:52 Now we're going to see it's going to get even more complex in a few more minutes, because we're going to see that even after I indeed write what we're going to leave it here as that the accounts have addresses where the city field equals liquid.

08:10 And then we're also going to add soon do not have addresses where the city field is different than liquid, even so we're still going to get accounts that have addresses with no city field.

08:26 As long as they at least have an address in liquid. So we're going to end up actually putting another filter, which is going to say that they can't even have any address without a city field.

08:41 And once we do that, actually, if you think about it, we really won't need to have this line of city field equals liquid, because if we say that accounts are not allowed to have addresses with the city field other than liquid.

08:57 And they're also not allowed to have addresses with no city field. And we say over here, if we skip this line, and we say that they have addresses, then obviously they have addresses with the city field of liquid, because the city field can't be empty and it can't be other than liquid.

09:13 So it must be liquid. So technically once we're all done, we'll have theoretically been able to not filter for city field equals liquid.

09:22 I'm going to leave it in for now. I'm just trying to explain how What you leave in and what you leave out makes a lot of difference.

09:31 Sometimes it's like different sometimes a big difference. Sometimes you care sometimes you don't care, but it's important to realize that every single part of the filter makes a difference and changes what you're going to end up saying.

09:41 So for now, let's go back to have addresses with the city field equaling liquid and we'll take it from you.

09:50 You don't have to start with the basic filter. You can start with the advanced tab in the first place and make all your filters there, including the basic one as we'll see soon.

09:59 But often you'll start off in basic and then realize that you need an advanced filter. So you could leave the basic filter as is.

10:06 It's perfectly fine and then you'll add more in advance. Now let's see how that all works. We're just in our basic tab and we're saying that we want this list of accounts to populate with accounts that have at least one single address where in the city field is liquid.

10:24 So they definitely have an address in like would let's click on the advanced tab. Now let's get familiar with what this tab is.

10:33 So this tab is really a whole list of filters. You can put a bunch of filters each of these boxes will represent one whole basic filter, one of these.

10:46 And basic filters could be extremely long. They could have a bunch of ends and a bunch of or is that a bunch of different ends and or is depending on which column they're in and each one of these boxes will represent one full basic filter.

10:58 So of course, what you can do with an advanced filter is really much more than you can do with a basic filter.

11:04 Now, we have to understand how do you read an advanced filter because remember the filters are a sentence. So how would you read the advanced filter on the basic filter the sentence is displayed for you on the advanced filter it's not.

11:21 So what is the sentence. Well, the sentences starts with the beginning of the basic sentence. This filter will pull all accounts.

11:31 This word is not going to change any section that you use will have a basic filter option. You know, you don't have to use it, but you can you can use a basic filter and an advanced tab.

11:45 You first need to pick the section and then you could click not filtered basic advanced. So we're in the accounts section and we're in the advanced tab and our sentence starts with this filter will pull any accounts, which and that's the end of the beginning of the sentence for the advanced tab.

12:04 This filter will pull any accounts, which and then you'll go to each of these filters that you added and you'll open them up and let's say on some of them it will say have a single address.

12:19 We're in the city field equals language. On other ones, it will say do not have a single pledge wherein something something or have a single pledge.

12:31 Something something something so each time we're going to start off with the words this filter will pull any accounts, which and then you have a blank check to complete the sentence.

12:43 However, you want in all of these boxes. And we're going to talk about this, of course, more in depth right now.

12:49 So since we started off with the basic tab and then we moved on to the advanced tab. Our first boxes already filled in.

12:57 If we would leave this as is we really would not have added an advanced filter and this first box symbolizes the basic filter that we already made.

13:07 So it's there and that is the only filter that is currently active on this filter. And what was that basic filter was this filter will pull all accounts which have at least one single address.

13:19 We're in the city field equals liquid. Now, we said that not only do we want the account to have an address in liquid, but we also don't want the account to have any addresses that are not in liquid.

13:31 So we would create a new criteria set a new which will say that the account should not have any addresses that are not in liquid.

13:43 Now, where would we put that? So we have the exact same ends and ores that we have in each basic tab.

13:53 Here it even says end and end and these are all ores and we can have a bunch of columns. If we need it, it'll as soon as I add something here, it'll add another one.

14:04 See, before I even added it, it'll add another column. It'll add as many columns as we want. So these are all going to be ores and it's going to be the exact same thing.

14:12 All columns will be read one by one will start off with the rightmost column and we'll read it downwards for all the ends and these are things that make the filter more strict.

14:22 And we'll say the account should show up. How do we start the advanced tab sentence. This filter will pull this whole filter will pull all accounts, which this and this and this and this and this and this and this and this and this.

14:47 So let's add in that not only as this basic filter says should the account have an address and liquid, but also the account must not have any address that's not in liquid.

15:01 So we are making the filter stricter. We're saying not only does it have to have an address and liquid. It has to also not have addresses that are not on the liquid.

15:10 So this is not an or this is an end. So we're saying address and liquid and no address not in liquid.

15:17 So let's go to the first box over here. We'll right click and we'll select new criteria or we could just click new criteria over here.

15:25 And if you look over here, we actually have a complete new filter. We have the whole sentence. We have have a single we could pick the entity and we have as many answers we want and as many or as we want.

15:38 So let's call this no non liquid. Now, I really wish this first part of the sentence would not be here because this filter will pull all accounts is already the beginning of the advanced tab sentence.

15:52 So really this box just continues the sentence that we already started over here with this filter will pull all accounts which meet this criteria.

16:03 And then it starts over here and which and then it depends on what we're going to fill in. So let's say and which do not have even one single address where in the city field is not liquid.

16:16 Now, it didn't show up. Sometimes it shows up. Sometimes it doesn't you could go to the arrow scroll down and you'll find the one made no non liquid.

16:27 Now, if we want to view now what this advanced tab is doing. We could right click on these filters one by one and read what they say.

16:36 So let's go through this. This filter will pull all accounts which have at least one single address in liquid and which do not have even one single address.

16:52 That's not in liquid. Now, let's actually pull this. Now, before we do that I just want to say one thing.

16:58 I just want to mention two quick points over here. One is that technically you can invert the ends and the ors on the advanced tab.

17:06 I would definitely I've never seen anyone do that. And I would definitely not advise doing it because it's just too confusing.

17:12 Maybe there's a reason why it sometimes makes sense. I really don't know. But I've never even seen it done. But in theory, what invert means is that the ors become the downwards and the and becomes the horizontal.

17:24 But let's not do that. I guess maybe it could help if you if you put in a bunch already and it's instead of having to delete each one and select it again from the drop down on the other side.

17:35 You just want to switch it because you made a mistake with the ends and the wars. Okay, maybe. Anyway, it could it could be very confusing though.

17:43 So the other thing I'm just going to mention is that once you're on the advanced tab and you put in more than just your original basic filter criteria.

17:53 You don't want to move back to the basic tab because if you move back to the basic tab. Let's do it basic and then you go back to the advanced tab.

18:03 You will have lost whatever you put here now it's not really lost you don't have to build it again you just find it in the drop down, but it's very annoying, especially if you add a bunch to have to put it back so.

18:13 There's really no reason to ever click on basic again because if you want to edit what you had already done in basic.

18:18 You can right click over here and edit the criteria and you'll be able to edit the basic filter that you had already made.

18:25 In fact, as I said you don't even have to start with basic you can start off your whole filter by clicking this section accounts and click advance and just add in a criteria on here and then something here.

18:35 But if you did start with basic you can always edit it here the same way that you can edit this one here by right clicking and clicking edit criteria.

18:45 So now we're going to pull this list this filter will pull all accounts which have an address in liquid and which don't have any address that's not in liquid.

18:56 Okay, let's actually switch again to account look up with all address like we did in our last video so we'll be able to see all addresses search.

19:05 And we don't even have to organize the account right now because we can just go to the city state zip column that has the city will click and we'll see that there is no address that has a city that is anything other than liquid.

19:18 It organizes in alphabetical order and groups all like entities together so we're clicking and we're only seeing like what that means there is only liquid the top and the bottom is only the now actually these four addresses have nothing in the city state zip.

19:34 Why why are these four showing up so what did we say in our filter we said that the filter will pull all accounts which have an address and liquid and don't have an address that's not liquid.

19:47 So these are not considered an address that's not liquid so the account could still come because if it doesn't have a city.

19:55 Then it's not an address that's not liquid but what about the fact that they have to have an address that is in liquid so we could look up all these accounts and we'll see that they all do have an address in liquid they have to or else they wouldn't have come up at all.

20:09 So let's see the first one is account 709 let's organize but I count ID and let's find 709 here we go and indeed it has to will highlight it.

20:18 It has two here is that blank address that we had and this one isn't liquid so there isn't address on liquid so 709 is coming because it does have an address in liquid and it does not have any address that's not liquid because the system is saying if it's just nothing then it's not not liquid.

20:36 And we would find the same thing if we would check the other four addresses that we just saw over there now if you want of course you can narrow down this filter even more and you can say hey I don't want to see accounts that have an address with.

20:52 No city and it either I want accounts that only have addresses that have a city and the cities all have to be liquid so the account has to have an address with the city field of liquid and it has to have no addresses that are not liquid and also has to have no addresses that don't have a city so.

21:12 Let's see let's go back to our no non liquid will right click click at a criteria this filter will pull all accounts which have an address in liquid and which.

21:24 Do not have even one single address we're in the city field is different than liquid or the city field is no now once you say is no it ignores the operator it just means the city is no.

21:40 Now we don't need to memorize why every cases different and sometimes we're allowed to do things in this column and that row or think about why method.

21:49 All we need to do every time this is the main lesson if we didn't do any of the filters videos in this whole series, the main thing to realize is all we need to do is real and it's very complex but would you need to do is.

22:05 Because and think really carefully if in your particular case with the particular criteria and options that you chose in the sentence that you created think about if it makes sense and if it's doing what you want it to do let's read the sentence so we're in our advanced tab.

22:28 This filter will pull all accounts which have a liquid address and which do not have an address we're in the city is not liquid.

22:42 Or the city is no so we're reading it properly we are at as we said in our previous video we have to read the field name again so and they have to not only have an address and like would but they also have to not have any address we're in the city is not liquid or.

23:00 Where the city field is no as I said a few videos ago the best way to do this is really to first calculate what this would mean with have.

23:11 And then just say instead of have it's really do not have it's just easier than the whole double negative business so if it would say have then it would mean accounts which have an address.

23:22 We're in the city field is not liquid so something other than like would or even if they don't have something other than like what we're in the city field is no so do not have what mean accounts which do not have.

23:35 And address we're in the city is not liquid or even if it is not considered not liquid it's no meaning we have now closed the loophole only.

23:49 And we have addresses and late would can come now of course we said they have to have an address because we said over here in the basic filter that they have to have an address and liquid.

24:00 So accounts that have no address won't come so only accounts that have addresses and liquid and nowhere else not something other than liquid and not know because if we look again city field that's other than liquid.

24:11 Or even if it's not considered other than liquid it's no do not have that means that the account cannot have any address we're in if you look at that address you think oh this address has a city field that's either other than liquid or no.

24:29 Click OK and we'll see that we will have taken off all the all the accounts that have no addresses and we will see that only liquid city fields will show up no blank fields and no other fields only liquid.

24:42 OK OK let's click search and if we sort over here we will see a type column of only liquid now we just explained what you would do if you needed to escape the beginning of the sentence.

24:56 And you needed to say do not have because you already had a filter on have because you needed the accounts to have address escape that and add another criteria on saying they have to not have addresses that are either other than liquid or or have a null city field.

25:13 So we said that the advanced tab lets you start again from which in each box so this filter will pull all accounts which this and this and this or this or this or this and each time you'll have the opportunity to select again have do not have a single something so now let's talk about.

25:36 A different example and these I really just want to run through quickly because we already explained the advanced tab at length really I just want to give examples for the other two reasons why you would want to escape the beginning of the sentence so let's say you don't want to do do not have.

25:52 This this filter will pull all accounts which have addresses in the city field with the city field equaling liquid and you want to add another criteria on.

26:03 Not do not have but you just want to talk about something other than addresses you want to say also, or you could say or whichever one also let's do an end so also the account has to have a certain email address.

26:17 Well, once you did have addresses were in the city field equals like would you can't talk about an email address and there's no email address here and even if there was it would have to do with.

26:28 The address so so what would you do so again you would go to the advanced tab let's just show a quickly go to the advanced tab you would click on the box you would say new criteria you would say let's just write test which have email addresses we're in the email address is.

26:48 Like they will contains the letters a well is like a well so it could have anything or nothing and can end with anything or nothing but at some point in the email address it says in this order.

27:01 What you know spaces a oh so let's click okay you didn't show up so let's find it over here on the drop down we call the test you should give it a unique name each times that you could find it so now we're saying this filter will pull all accounts which have an address and like would.

27:20 And which have an email address that contains the string of letters a oh we click okay we click okay let's click search and we only have account 524 shown and if we scroll down to the emails we'll see a well in the email.

27:42 So now we just showed two different reasons why you might want to escape the beginning of the sentence we showed you had have you wanted to also put do not have.

27:53 Right we had have liquid and do not have other than liquid or no and now we just showed it's still a have but you need to switch addresses for email addresses so.

28:05 And if you have addresses in liquid and have email addresses with a well now you also can do this as an or do the AOL filter as an or so have addresses where in the city is like would go to advanced tab and instead of putting the criteria here as an and we'll put it over here or have at least one email

28:28 address that contains the string of letters a oh. So, so in this case we're not saying that the account has to have an address in liquid and also an email address with a well but we're saying either an address in liquid or AOL so this is more lenient but this is the exact same idea as the end and the

28:50 or. And so, so it's the exact same thing it's just that instead of fields were actually talking about entire filtering sets entire criteria sets that are going into each of these boxes but they're either and or whichever they are they're read as in the advanced tab.

29:08 This photo will pull all accounts again cuz we're on the section accounts whichever section you're on this photo will pull all accounts with.

29:16 This and this and this and this and this or this and this and this and this or this and this and this and this and this and this now there's a third reason why you might want to escape this sentence.

29:28 This beginning of the sentence and actually we've mentioned it in previous videos, although we didn't explain how to do it because we hadn't been showing the advanced tab yet.

29:38 But you also might want to escape this first part of the sentence, even if you want to keep have the same or do not have the same and you want to keep the same entity but you need to escape the words a single.

29:52 So we discussed this right if we want to say that we need accounts that have an address and liquid and they also have an address in Brooklyn only accounts that have an address and liquid and in Brooklyn can come on to our list.

30:27 So we said that if we do liquid or Brooklyn that wouldn't work because this means. Any accounts which have at least one single address were in the city field is Lakewood and the city field is Brooklyn well the city field of one single address cannot be both Lakewood and Brooklyn those are opposing criteria

30:53 so we need to escape the word a single well in the advanced tab we could because the advanced tab starts off as we just said.

31:03 This filter will pull all accounts which and in each box you get to start this part of the sentence from the word have or the words do do not have.

31:13 You get to start this part of the sentence again so if we want to only pull accounts in this list that have an address and liquid and also have an address in Brooklyn we would use an advanced filter and let me just show you exactly we would do it.

31:26 So this row so our first filter is which have at least one single address when the city is liquid let's go to advanced and not or and make a new criteria and which have at least one single address we're in the city equals.

31:43 Brooklyn so this means this filter will pull all accounts which first this one which have at least one single address we're in the city field is liquid and also not only do they have an address that has the city field of liquid.

32:02 And also the account has at least one single address we're in the city field is Brooklyn now that makes perfect sense because we're not saying a single address we're in the city field is Brooklyn and one single address can't have two opposing city fields we're saying the account has at least one single

32:21 address we're in the city field is liquid. And has at least one single we're in the city field equals Brooklyn we click OK and click search we'll see we only actually have one account showing up here let's see account.

32:35 Six six four let's see why it has liquid so it does have an address and like what an as an address in Brooklyn it also has an address in for our way but our filter did not mention anything about having or not having an address in for our way so it has like what an has been there for it showed up of 

32:52 course you might sometimes not only be escaping the have or the a single or the entity you might be escaping all of them you might be switching from have to do not have and also switching the entity of course that doesn't matter the point is that when you're in the basic tab once you select the have 

33:10 do not have and you select the entity you're stuck with that everything that you do still starts off with this part of the sentence whatever you put here and you could do a lot.

33:19 Not really so basic you can do a lot of and a lot of wars a lot of operators but whatever it is in fact you can even do sub queries in a basic filter sub queries is one of the most complex parts of filtering but what you can't do is you can't escape the first part of the sentence so.

33:37 The advanced filter just starts off the sentence with this filter will pull any accounts and then allows you to restart this part of the sentence as many times as you want in all the different boxes have an absolutely.