MORE about the "AND"s and the "OR"s

AI-generated Transcript by Loom.com

00:00 Good afternoon everybody for today. Let's change things up a bit. Let's make our filter on the accounts screen accounts search accounts and over here we have sort of an account maintenance screen.

00:12 So we pull up the information here in spreadsheet style. If we would click search, we would get a list of accounts in the system.

00:20 If we don't filter and we could choose first our report ID as we discussed in our first video and we could choose the exact information that we want to see.

00:31 So let's say it's set to account look up standard. Let's just use account up standard. We could switch it up course.

00:37 We could pick another one. We could even mark it as being the new default. And then if we go out and we open it, we'll see that that's the new default.

00:47 You see, either way, let's see what's that. So let's say we click search and it pulls up the information that the report ID is telling it to pull out all these are different accounts.

00:56 These lines are different account. We want a filter of exact accounts. That we want to be pulled up on this list.

01:03 So we would click the filter to make it active. If we don't click that even if we put a filter, it's not going to actually use it.

01:09 So we'll click the filter and let's create a new filter. So what I want to do is really double down on this concept that we've been talking about.

01:16 We especially spoke a lot about it in the end of our last video. The filter reads as a sentence. So let's go back to a simple example.

01:25 This filter will pull accounts. Right. Cause we're on the account section, which have at least a least one single address.

01:32 We're in the city field equals Lakewood. So this would mean we're only going to see accounts that live in Lakewood or that at least have an address in Lakewood.

01:43 And we said we could say, or Cedarhurst that count has an address. We're in the city is either Lakewood or Cedarhurst.

01:52 And we said you cannot say that you want to only see accounts that have an address. We're in the city field as Lakewood and the city field as Cedarhurst.

02:00 Hurst is again, across as or down as and so you cannot say that you want to see all accounts, which have addresses.

02:10 We're in the city field as Lakewood and the city field as Cedarhurst because the city field of one address cannot be too opposing things.

02:18 It cannot be Lakewood and be see the rest. Now we said there are times when you could use the same field twice in one basic filter.

02:27 For instance, with amounts more or less. But if you're a public student, you can't say the city equals Lakewood and also equals something other than Lakewood.

02:35 Now let's say we would want to say that we want to see accounts as long as they have addresses that are not in Lakewood or in Cedarhurst.

02:46 So let's let's start again. So you might think that this is what you're supposed to do. This filter will query for any accounts which have at least one single address.

02:56 We're in the city is other than. Right. This means not equal to the city. Is other than Lakewood or Cedarhurst.

03:03 This filter will pull all accounts, which have at least one address when the city field is not Lakewood or CEDEREST.

03:11 Now is this the proper way to create this filter. It looks pretty proper right we're even saying or this filter will pull any records which have at least one single address when the city field is not Lakewood or CEDEREST.

03:25 We're supposed to read the first column first and then the second column and the third column. We have the columns down there's no ends here but in theory if there were ends would be not the city is not Lakewood okay.

03:37 Or CEDEREST now let's actually run this filter let's click okay we're actually going to pick a different report idea over here we're going to pick a count look up with all addresses and this will show us all the addresses at once it'll be very easy to see if we have accounts that have at least one address

03:54 that is not a Lakewood or in CEDEREST. So it might have an address in Lakewood and in CEDEREST it might have a bunch of addresses but it has to have at least one address which is not a Lakewood or in CEDEREST so let's see if that holds true let's click search and let's organize over though let's look

04:11 at. So if you have a question why did a count ID two and many more of these accounts actually why did they show up let's open up our filter again didn't we just filter for accounts which have at least one single address.

04:39 Where the city is not Lakewood or CEDEREST but a count ID two does not have one single address where the city is not Lakewood or CEDEREST in fact it had three addresses one was in Lakewood another was in Lakewood and another wasn't CEDEREST none of them had cities that were neither Lakewood or CEDEREST

04:57 so the reason. This filter doesn't work is because while we need to read the sentence we need to know how to read the sentence and this also is going to be very very negated to advance filtering.

05:12 Knowing exactly which part of the sentence is red where so the sentence is not read as we tried to read it this filter will pull any accounts which have at least one single address where in the city field is not Lakewood or CEDEREST which would mean we're in the city field is neither Lakewood nor CEDEREST

05:33 than many or most of these accounts would not have shown up rather. The filter means this filter will pull any accounts which have at least one single address where in the city field is not Lakewood or the city field is not CEDEREST so it's not where the city field is neither Lakewood nor CEDEREST it

05:52 you're allowed on to. Including CEDEREST as well as not like what so if you have a city field of CEDEREST that means you have a city field that is other than Lakewood so anyone that has a city field to CEDEREST will show up hence account number two showed up.

06:22 And what a CEDEREST mean a city field that's different than CEDEREST all cities in the world are different than CEDEREST besides for CEDEREST including Lakewood so if you have a city name of Lakewood then you already have a city field that's other than CEDEREST so you'll show up so double E HENES.

06:39 And what a CEDEREST is like an account ID number two is showing up because account ID number two actually meets both of these criteria doesn't even have to each column is sort of like its own filter.

06:48 It could meet one column. So what would you do you would use an and. Now let's dissect why. Why would you use an and over here.

06:55 Now we said that if you say this filter will pull any accounts which have at least one single address where in the city field equals we would do equals when the city field equals liquid and.

07:07 The city field equals CEDEREST you would get nothing because no account can possibly have an address. We're in the city field of that particular address is liquid and is also CEDEREST that doesn't make any sense every address has to have a unique city field.

07:20 But when you're saying does not equal to think about it carefully it does make sense let's spell out the English sentence this filter will pull all accounts which have at least one single address we're in the city field is not liquid and not only is the city field not liquid, but the city field is also

07:46 not CEDEREST that makes perfect sense. Those are not to opposing criteria those are actually two complimentary criteria we want accounts that have at least one address we're in the city is not liquid and also the city is not CEDEREST now let's not get confused.

08:03 If an account has an address in liquid or has an address in CEDEREST they won't be excluded we never said what they don't have so we could do that.

08:13 But over here we're just saying what they have whether they have like what in CEDEREST they have to have at least one address we're in the city is not liquid and also the city is not CEDEREST.

08:28 So this really means what we were trying to do we're in the city is neither liquid nor CEDEREST. Now let's see how many accounts come up here let's click OK search we have much less accounts for look over here on the bottom count is 117 before it was in the 700s let's organize our account IDs again 

08:48 so if we look at account 13 account 13 as an address in Duke Bay I think I made that up I don't think Duke Bay is a real thing.

08:57 I think I made up that city anyway so has an address in Duke Bay it also has an address in Farakway so account 13 would certainly come because it has two addresses we were in the city field is neither like with nor CEDEREST let's scroll down so account 325 and if we want to be able to look at it easily

09:15 altogether we can just highlight them off we look at account 325 we see it has two addresses in liquid. So that's actually not going to get it on to our spreadsheet now based on our filter but there's nothing wrong with having an address in like would we never said it can't.

09:33 It just needs to have at least one address which is neither CEDEREST nor liquid and it does because it has an address in Muncie interestingly it's actually an in active address.

09:42 Well, we could have adjusted the filter to say that we needed to have an active address in something other than like would receive us but we didn't do that that's another step.

09:53 But here because we didn't filter those out we have pulled account 325 because it has an address in Muncie so if we go back to look at our filter then portent nature member is.

10:04 And this applies across the board the only reason why I've been giving these examples is just to show how it applies.

10:12 One is not to memorize what you have to do for each one if it's an and or the point is simply to realize that if you think about it you'll get the answer.

10:21 It's all about reading the English sentence properly that's all so it actually could be confusing i'm not trying to imply that it's not confusing.

10:29 It could be confusing but think about it carefully each time what is the sentence that i'm looking at what we really were showcasing though in today's video.

10:41 Is that sometimes the sentence is not just what looks like it might be an English sentence in the manner that people speak columns.

10:49 And this is really the main point of today's video columns are not simply read as you read the first one you read it all down and then then you or the next one.

11:02 But what is in these columns now if you think about it of course you're not really reading it straight down but without any additional words because here it would make sense we want.

11:12 And the accounts which has at least one single address when the city is not liquid or see the hers that would have made sense but think about it let's say you had the state also so the state equals.

11:23 New York and let's also put New York over here so let's see this filter will pull all accounts which have at least one single address.

11:32 And the state is not liquid and also the state does equal New York for see the hers New York what or see the hers New York what does that mean what do you mean or see the hers New York this filter will pull all accounts which have at least one single address where in the city field is not liquid.

11:49 And the state is New York or Cedarhurst New York are are we just talking gibberish what does that mean or see the hers New York so it made sense if we ignore for a second this last row that I just added when we just said we want to pull all accounts that have at least one single address where in the

12:08 city field is not liquid or see the hers. That made sense in a theoretical sentence that a person could say but it wouldn't make sense this with the filter has to be able to be read with as many and going down the line as you can put a you can put a bunch so if you think about it.

12:26 It's obvious that it the filter can't really be read the city field is different than liquid or see the hers because that makes sense there but it wouldn't make sense here.

12:35 From the city field does not liquid and the state is New York or see the hers New York that just gibberish doesn't make any sense so of course the way to really read the columns is each of these boxes.

12:49 Is read with its field name so let's add another one just for emphasis just just for this explanation this would mean this filter will pull all accounts which have at least one single address.

13:02 We're in the city is not liquid and the state is New York or the city is not Brooklyn and the state is New York each time if you notice I'm repeating the field name and the operator symbol so this filter will pull all accounts which have at least one single address we're in.

13:32 We'll start from right over here we're in what does Lakewood mean Lakewood means the city is not liquid now what does New York mean the state is New York or.

13:50 The city is not Brooklyn and the state is New York so this would mean this will to will pull all accounts which have at least one single address we're in the city field is not liquid and the state is New York or the city field is not.

14:04 The city field is not Brooklyn and the state is New York now as we said this would not generally be the right way to filter for accounts that have addresses which are not in Lakewood see the her store Brooklyn because these are ours so if we go.

14:22 But so if we go back to how we had it before this was our first try this is incorrect because if you think about it, this does not mean this filter will pull all accounts which have at least one single address when the city field is not liquid or cedar hers.

14:37 That's not how we read filters what this does mean is this filter will pull all accounts which have at least one single address where in the city field is not liquid or we're in the city field is not cedar herst so you're basically going to get everything because you want accounts that have a city field

14:56 . That's not liquid sure but you also said that you're okay with them coming or right you said or you said you're also okay with them coming if their city field is not cedar hers well liquid is not cedar herst so all the accounts that just have liquid are going to come too that's why what we really have

15:15 to do. Is say the city field is not liquid and also the city field is not cedar herst and then as we showed before we will only get accounts that have we don't care what they don't have.

15:36 Nor see the herst have a.