The "AND"s and the "OR"s!

AI-generated Transcript by Loom.com

00:00 In this video we're going to explain how to fill in this grid. So let's give an example of an account list.

00:08 We're going to be filtering the account section because that's the only thing that's on an account list. And let's use addresses.

00:14 So this filter will pull any accounts, which have at least one address. We're in the let's say city field equals find down the drop down to make sure I spell it right and see it gives me a liquid.

00:29 I started type it in Lakewood click the green check. This filter will pull any accounts, which have at least one single address.

00:37 We're in the city field equals lakewood. Right, that's a simple enough account list. If we pull it, we'll see that all of these accounts have an address in Lakewood.

00:47 Lakewood Lakewood Lakewood. We see over here. There's one account here that just says Cedarhurst, New York. Let's see it's account ID two.

00:55 Let's check it up. So we see Cedarhurst, New York. That's because it's the default address. But there are addresses on this account in Lakewood as we can see over here.

01:05 And therefore account ID number two as well showed up on our report because we filtered for accounts, which have an address and address in the city of Lakewood.

01:17 In theory, we could filter only for default addresses addresses default only, but we just filtered for addresses with the city being like.

01:25 Let's start getting to the point of this video, the ands and ors. Don't just want to see an account list of accounts that have addresses in Lakewood, but you want to see accounts if they have an address in Lakewood or an address in Cedarhurst.

01:41 So what you would do is if you notice on top of here, it says or so you can add in Cedarhurst.

01:47 Let's click over here. We'll type in Cedarhurst comes up. Click this filter will pull any accounts, which have at least one address.

01:57 We're in the city field is Lakewood. Or the city field is Cedarhurst. Should we add another one? Let's add or Brooklyn.

02:06 This filter will pull any accounts, which have at least one single address. We're in the city field equals Lakewood or the city field equals Cedarhurst or the city field equals Brooklyn.

02:18 Now we're going to pull the account list. As we said before, we won't necessarily be able to tell why every account is pulled on the list because the account list by default is only set to show.

02:31 Default addresses. Let's hit preview. Now here we have Lakewood, Lakewood, Lakewood. Okay, all of these are in Lakewood. Let's scroll down a little.

02:39 Let's find one that's not Lakewood. Let's just search actually. Let's search over here. We'll search for Brooklyn. Okay, enter. And we have a few here.

02:48 Mr. Mrs. Nufftelli. Something. Mr. Mrs. Nufftelli Horowitz. They have their default addresses in Brooklyn. Let's find another one. Mr. Joel Rosenthal.

02:58 The address is Brooklyn. Cedarhurst search. And we have Dr. And Mrs. Avi Liner on page seven who have an address in Cedarhurst.

03:08 So this account list is pulling all accounts that have an address in either Lakewood, Cedarhurst or Brooklyn. If an account has an address in both Lakewood and Cedarhurst, it gets pulled as well.

03:21 As long as it has an address for Lakewood or Cedarhurst or Brooklyn, it gets pulled. So if it has all three, it has one address in Lakewood, one address.

03:30 In New York. So if it has a address in Cedarhurst, one address in Brooklyn, it will be pulled because it definitely fills that criteria.

03:35 So let's take off these. We'll leave them. Now let's say for your account list, instead of using that filter that we just did, instead, you want to add on more exact criteria.

03:46 So if we wanted to add on more criteria to make it stricter, we would use what's called an admire an and.

03:54 We use and it could be a little bit confusing because in kind of interchange the way we're using and and or here.

04:04 But the word or an admire makes things more lenient. It makes the filter less precise. It means they could have an address in Lakewood or somewhere else or somewhere else.

04:19 Whereas ends make the filter even more precise than it already was. So not only does the address have to be in Lakewood, but we're going to add more criteria about that address.

04:30 In Lakewood, but also other things. Now, or you just put horizontally across as many as you want and you put underneath.

04:40 So let's start our filter. Let's say we want accounts that have addresses in the city field being Cedarhurst and not only does the city field have to be Cedarhurst, but also the address has to have been entered into admire by a specific admire user.

04:57 Let's say we. That we're entered by a certain secretary. There was some some mistakes yet the day that she entered all those she did some that she made some mistakes and realized something and they're all messed up.

05:11 So we want to find all addresses in our system that have the city field of Cedarhurst and we're entered by a specific admire user.

05:19 So let's find the one that we're going to choose over here. We're going to find shared demo. It puts a number, but it's symbolizing the fact that I chose the user shared demo.

05:30 So let's go over what our filter is filtering for. So we have an account list and we want to see all accounts, which have at least one single address.

05:41 We're in the city field equal Cedarhurst and that address was entered by admire user 78 by shared demo. Let's click OK.

06:00 On this account list and that is account ID to Dr. Mrs. Abbie liner from Cedarhurst and it doesn't say so on this account list, but obviously the reason why it pulled specifically this one account is because this is the one account in this demo admire system.

06:19 This is the one account that has a city field of Cedarhurst that was entered by shared demo. So it's really important to get clear that.

06:31 Horizontal means or and down means and and downwards and limits even further than you already limited and or makes the limit less intense only addresses in Cedarhurst or even if the addresses like or even if the addresses Brooklyn.

06:51 Now we did say in our first introductory video in this series that a filter can never add information to a report.

07:03 A filter always limits and that is true that is a hundred percent true and it's very important to realize that saves a lot of confusion.

07:14 However, when you add an or to a filter, you are not truly adding a filter. In fact, you are lessening a filter.

07:24 The filter had said I only want Cedarhurst. You're saying no. No. Let's qualify that. Let's lower that not just Cedarhurst.

07:37 That's too strict even Lakewood as well. So you're not adding more criteria to filter by that would mean it has to have this and it has to have this.

07:49 For for instance, when you just say, oh, it had the city of Cedarhurst and also it has to be entered by 78 that is adding another criteria on to the filter.

08:00 If you would not add this row, you certainly would get everybody who was entered by 78 and everybody who was entered by anybody else because you never filtered for it.

08:12 So adding a filtering criteria on can only cut records out. It can never add records, but but once you are ready are filtering for addresses with the city of Cedarhurst, if you say also addresses with the city.

08:30 If you say or addresses with the city of Lakewood, you are making the filter more lenient and you certainly are adding more records to the report because you have not added a filter, but rather you have actually taken away a filter.

08:48 So there was a filter on the city of Cedarhurst, you said no. I'm going to make a more lenient filter.

08:54 I'm going to say Cedarhurst or Lakewood. Now let's get a little more complex over here. You said you can do as many or as you like.

09:03 You can also do as many and as you like. So let's say this filter will pull all accounts, which have at least one single address where in the city field equals Cedarhurst and the address was entered by user 78 and the date it was entered, let's say, was today.

09:23 So this would pull all accounts that have at least one single address where in the city is Cedarhurst. The address was entered by 78 and the date the address was entered was May 21st 2023.

09:39 Now we can of course do and and ores. So let's do we want to pull accounts that have at least one single address in the city of Cedarhurst or Lakewood or Brooklyn and it should be entered by 78 and date entered May 21st 2023.

10:00 Here's what you have to know. The way the filter is read as we've said a bunch of times, the filter is a sentence.

10:07 The way the sentence is read is downwards and then to the next column downwards and then to the next column.

10:17 We read the full column first and then we read the next column. So this actually would mean this filter will pull any accounts, which have at least one single address we're in.

10:30 We start with this column, the first column. We're in the city field equals Cedarhurst and the address was entered into admire by user 78 and the date the address was entered was May 21st 2023.

11:00 When in Brooklyn will come up on our account list even if the address that has Lakewood or Brooklyn was not entered by user 78 and was not entered on May 21st because the filter is read downwards column by column.

11:16 So what's downwards over here does not apply to here specifically applies to Cedarhurst and when we're done with all the downwards and we'll move on to the next one, which starts again from scratch or Lakewood.

11:30 Or by anybody or date entered by at any time and any date specifically or Brooklyn if we wanted to say that even the Lakewood addresses that we're pulling have to be entered by 78 will have to type it in.

11:44 The Brooklyn ones will have to type it in date entered we would have to type it in. So this filter means we want to pull all accounts, which have at least one address.

11:54 We're in the city field is Cedarhurst. The address was entered by a admire user 78. The address was entered on May 21st or the city is Lakewood and again it was entered by 78 on May 21st or the city is Brooklyn and was entered by 78 and it was on May 21st.

12:14 This of course means that we will not pull accounts that have addresses in Cedarhurst. If they were entered by anybody else or on any other date, we will not pull accounts from Lakewood.

12:26 If they were entered by anybody else or on any other date, we will not pull accounts in Brooklyn. Or on any other date.

12:32 We need all three of these criteria to be met or all three of these criteria or all three of these criteria.

12:42 So either Cedarhurst 78, May 21st or Lakewood 78 21st or Brooklyn 78, May 21st. One of those three groups of criteria.

12:52 One of those three columns. One of those three or's need to be met for the account to be pulled. You actually almost want this filter but for the Lakewood addresses, you don't need it to be entered by 78.

13:10 The Lakewood addresses you want anybody to have been able to enter them. So this would mean we're going to pull accounts that have addresses in Cedarhurst 78, May 21st or Lakewood from May 21st.

13:21 You could just skip this box. It doesn't matter. You're allowed to skip a box. It just will ignore it. Or Brooklyn 78, May 21st.

13:28 So in this case, the accounts with Lakewood addresses. Would show up regardless of who entered them into admire. Now let's say you want to do the opposite.

13:35 Let's put the 78 back and let's say you want to say that for Lakewood, no, you don't want it to be any user, but either user 78 or some other user.

13:44 Now, how would you do that? Where would you put another or on Lakewood? Well, you'd have to go to an empty column and let's start over Lakewood.

13:54 So it's Lakewood or Lakewood. That's fine. 78 here instead of choosing 78, we're going to choose someone else. We're going to choose two hack.

14:03 Whoever that is, I don't know. Okay, it's 49. And again, May 21st. So this would mean that this filter will pull all accounts that have at least one single address.

14:16 We're in the city is Cedarhurst and the address was entered by 78 and the date it was entered was May 21st.

14:24 Or the city is Lakewood and it was entered on by 78 and the date entered was the 21st. Or Brooklyn 78 21st or Lakewood again, 49 21st.

14:36 So technically this really means all accounts that have addresses in Cedarhurst 78 21 or Lakewood 78 or 49 21 or Brooklyn 78 21.

14:47 That's how you would have to do it. Now we can skip and add and double we can do whatever we want.

14:53 Let's skip these. So now we're skipping two boxes and we seven the eights three May 21st. This makes perfect sense.

15:00 This means is this account list will pull all accounts that have at least one single address when the city field is Cedarhurst and it was entered on May 21st.

15:11 The city field is Lakewood and was entered by 78 or Lakewood entered by 49 instead, but specifically on the 21st or Brooklyn entered by 78 May 21st.

15:25 Now will this filter pull accounts that have addresses in Cedarhurst. And we're entered on May 21st if let's say the user was user 49 or user 78 or any other user.

15:38 The answer is yes. We are not filtering in the column of of Cedarhurst by which user entered it and therefore we will pull all records regardless of who entered it.

15:51 This is actually a great example of where the filter only limits the filter never adds. That's not chosen to use the criteria on entered by when we're filtering for addresses in this filter.

16:08 When we're filtering by the city Cedarhurst therefore all users all really all answers to entered by will will come through and did by anyone this means the same as if we would delete.

16:30 And we're not going to have access to simple have addresses with the city of Cedarhurst would the filter care who was entered by of course not we're not putting a criteria on that has to do with entered by.

16:41 So over here where we are putting a criteria of entered by and we're even using it for accounts in Lakewood or Brooklyn or Lakewood again.

16:49 We we are not using it for cedarhurst and therefore in regards to the city of Cedarhurst we did not filter by entered by and therefore everybody will show up as long as the city is cedarhurst and it was entered on May 21st will show up or if the city is Lakewood and was entered by 78 it will show up

17:12 regardless of the date that it was entered or if the city is Brooklyn 78 21st or Lakewood 49 21st. Now what's funny about this now here's actually a very interesting one we have over here Lakewood 78 or Lakewood 49.

17:30 By 49 it has a date by 78 it doesn't that means that actually if the address is in Lakewood and it was entered by user 78 the system will not care what data was entered if it's Lakewood and 78 it's going to put it on to our report.

17:45 But the filter will also be pulling accounts that have cities in Lakewood without entered by 49 but in that case it will only pull it if the date that 49 entered it was May 21st because in this column where we're allowing in the 49s the date entered is also filled out so this would mean one last time

18:07 this would mean this filter will pull any accounts that have addresses were in the city is cedarhurst and the address was entered into admire on May 21st or the city is Lakewood and the address was entered into admire by user 78 on any date or the city is Lakewood and it was entered actually by 49 not

18:28 78 but specifically on May 28th. 21st or the city is Brooklyn and it was entered by 78 on May 21st now this of course is confusing because I shoved a bunch of random criteria at you on this video you don't need to memorize what I'm saying obviously I'm making things up.

18:44 The thing to understand is that the point over here is to understand what you're doing. I'm just trying to make the point that everything is interchangeable everything could be skipped all you have to understand is.

19:00 Red column by column first the left most column then the next one then the next one then the next one and they are red down so this column then this column then this column cedarhurst and 21 or Lakewood and 78 or Brooklyn 78 21 or Lakewood 4920 that's you have to remember now let's actually get simpler

19:28 now what I'm about to say you should technically already be obvious from what I just explained the past few minutes but I just want to say it out directly because it might be confusing.

19:39 So let's say again we just want I took everything out we just want to see an our countless accounts that have at least one address or the city field to cedarhurst and I want to say or the address was entered by user 78.

19:53 So where would I put that I can't put it over here obviously because this is the city field. Field of cedarhurst or something else but I am trying to do an or I'm trying to say I don't care only if the city is cedarhurst I want to see all addresses we're in either the city is cedarhurst or they were

20:14 entered by by user 78. So how would I do that well the answer is you would go over here and you would say entered by now if you put it over here well what would happen if you go like this we want to pull accounts that have addresses were in the city is cedarhurst and they were entered by in our user

20:32 78. The issue over here is that this is not an or this is an and because this is underneath and it's in the same column and we read column by column downwards what this culture is saying.

20:44 That the city has to be cedarhurst and the address has to have been entered by user 78. So we can do that so what should we do well we would put it over here and my point over here is that you could even skip the top.

20:59 You could even just have two criteria one in this column one in this column the rest of it is skipped and this is a perfect filter.

21:06 Because this would mean this filter will pull all accounts which have at least one single address we're in the city field equal cedarhurst.

21:14 Oh that's it city medical centers this column is done we're in the city field equal cedarhurst or it was entered by use now it also won't hurt you if you add in the same thing twice this will be the exact same thing.

21:28 This filter will pull all accounts which have at least one single address we're in the city cedarhurst or was entered by 78 regardless of the city or the city is cedarhurst.

21:39 This is a perfectly I mean it's a silly filter but of course it will work because anything that has a city of the other cedarhurst will also have the city field of cedarhurst and you'll get what you want over here.

21:49 It's more to talk about because this also has to do with the advanced filters and we're going to get there soon you see here it even says and straight out.

21:57 In the basic tab it doesn't actually mention that it's called and but on advanced it does okay now a bit more complex than everything else we mentioned.

22:06 So, if we're sticking with our account list and we have the filter section accounts that's the only one there and let's filter it by pledges.

22:16 So even though we're going to ultimately be looking at an account list and we won't see the pledges we can still filter the accounts by if they have certain pledges and then those accounts will show up on the account list we won't be able to tell why but those are the ones that will show up.

22:29 So let's say we want to only see accounts on this account list that have at least one single pledge for an amount between 500 to a thousand dollars.

22:40 Now, how would we do that there's no option of between the way we do between is we do less than and also more than so we do it sort of manually.

22:52 So the account has to have at least one single pledge we're in the amount of the pledge amount was greater than 500 or we could we could also do equal to or greater than if that if you like that better than 500 and we do and we're going down.

23:12 So this is going to be an end and and the amount is equal to or less than a thousand we want accounts that have pledges were in the amount is equal to or more than 500 and we're going down and of course we could have put the 1000 over here.

23:31 So what would this filter do we want accounts that have pledges were in the amount is equal to or greater than 500 or the amount is equal to or less than a thousand this filter is completely moot.

23:43 This is not doing anything every number in the world is either 500 or more because that includes everything from 500 and on or 1000 unless which includes everything from 1000 down.

23:55 But if you put it over here, this would mean we want to pull accounts that have at least one single pledge or in the amount is equal to or greater than 500 and also the amount is equals or less than a thousand so this is actually the same field name and there's only two criteria here it's not a complex

24:15 filter but you put it one line down and it becomes an end so this really is a way of saying between 500 to 1000.

24:23 Now, now of course there also are times where you could do an or that would make sense. Let's say instead of 1000 we would do or equals 395 so this would mean that we only want pledges 500 dollars and above unless it's 395 if it's exactly 395 then then 395 also should be pulled.

24:44 So this would mean we want account that that have ever given at least one pledge of 500 dollars and above or have at least given a pledge of exactly 395 dollars now this all is pretty self explanatory it's we need to get you said but once you say it's pretty obvious but sometimes things get a little

25:05 sticky sometimes an and doesn't make sense and let's discuss what we mean over here. So in our example we said between 500 to 1000 that's fine that makes a lot of sense even though it's the same field and you're putting different criteria in the same field.

25:26 It makes sense because the amount of the pledge can both fit this criteria on and also fit this criteria on at the exact same time even though you're putting a criteria on the same field so there's two criteria on one field it makes sense but let's say you say I want to see accounts that have pledges

25:45 to occasion name let's say book fee and also occasion name campaign 2018. Now of course if I put it in or then what will that mean let's copy this over to this one so this would mean I'm filtering for accounts I want to see accounts that have at least one single pledge that was given to book fee occasion

26:10 or even if they don't have that. If they have at least one single pledge that was given to occasion campaign 2018 whichever one but let's say I don't want it to be whichever one I only want accounts that have given to both when I would do then is go back to end I need to use and and this is where the

26:31 stickiness. Begins this filter won't work and there's a very logical reason why it won't work, but we need to wrap our heads around it so actually I'm going.

26:46 To just use an account list so that I can make the point that we could filter on pledges even though there's just accounts but I do want to actually show things here so I'm going to click OK and I'm going to go to an account list with pledges so that will be able to see things properly now.

27:01 And account list with pledges and we'll go back will edit the same filter that we were just on and we're just on the accounts we want to see all pledges, but we only want to see accounts have given.

27:13 To book fee and not only have they given to book fee but they've given to campaign as well so not just one of these but both of them if I click OK then I click preview will see it freezes it's thinking and then it starts going again.

27:26 This ticker is a good way of knowing if there's no data depending on the admire that you have your system right either tell you no data or you'll just see that it's pulling no data because it freezes it's thinking and then it stops.

27:39 Stops and then and then it continues moving so there's no data now why is there no data of course I could oh this is a filter that I certainly can create.

27:48 I most certainly I'm able to filter for accounts that have given a pledge to book fee and to campaign 2018 I certainly can do that, why is this not how I do it yes let's let's take answers from the crowd.

28:02 Okay, anyone oh there is no crap oh I'm sorry okay so I'll give the answer myself the reason lies in a part of filters that actually may be more of a favorite topic to me than filter sections now you'll know I'm obsessed with filter sections but there's one thing that I will I say all the time I keep

28:26 saying at least one single at least one single. Okay can this guy enough with at least one single okay it has pledges for occasion name this kid now what does this mean a single this filter will pull all accounts which have a single pledge and again it's confusing because it shouldn't say pledges it

28:55 should say pledge. It says pledges because you're picking the entity I think I mentioned this on a previous video you're picking the entity the entity is pledges you're going to talk about pledges so it calls it pledges but.

29:09 Really because we're in the middle of a sentence it shouldn't call it pledges it should say pledge this filter will pull all accounts which have a single pledge which meets the following criteria everything we put into this grid talks about a single pledge so what does that mean let's take this out 

29:33 for a minute let's go back to. More regular stuff occasion name book fee or occasion name campaign 2018 this filter will pull all accounts which have a single pledge or in the occasion name is book fee or the occasion name is campaign 2018 this is talking about a pledge does the account have any pledge

30:00 we're in the occasion name is this. Now, if we do and this means this filter will pull all accounts which have a single pledge or in the occasion name is book fee and the occasion name is campaign again did you hear that let's let's say it again.

30:23 This filter will pull all accounts which have a single pledge a single pledge which meets this criteria the occasion name is book fee and the occasion name is campaign 2018.

30:44 No, no, no, no, no, no, no, no, no, no, you cannot have a pledge to occasion name book fee and campaign 2018 that doesn't make sense.

30:55 If the pledge is to occasion name book fee then it's not to occasion name campaign 2018 this will not pull up anything because there can be no accounts in the system that have a pledge to two occasion names.

31:10 You can actually make payments to two pledges which is to occasion but you can't make a pledge that is filed away in admire as being to two different occasion names.

31:22 So you're actually a little bit stuck now you're not really stuck but based on what we've gone through so far in this series you would appear to be stuck.

31:32 You can't use or because if you say you want to pull all accounts that have a single pledge to occasion name book fee or campaign you're making your filter much more lenient.

31:45 You're saying that as long as it has a pledge to book fee or to campaign you should pull it up but that's not what you want to do you want to say it has to have a pledge to book fee and a pledge to campaign.

31:56 But you can't do it this way because we're talking about one single pledge you need to escape this beginning of the sentence you want to make another sentence just one sentence isn't going to do it for you and for that you need the advanced tab.

32:15 Which we are not going to the next one instead we're going to give a few more beautiful examples of when this single is going to get in your way.

32:26 But before we give more examples I just want to repeat again that this doesn't mean that you can never put an end on the same field as we just went over a few minutes ago if you were friends and doing a mount.

32:40 Or if you were doing a D before or after if you're doing between you can always put an end on the exact same field and that's actually how you do it.

32:49 So if we were saying a mount as we said before between 500 and 1000 including those two numbers as we did or equals to this would make perfect sense the amount is either equal to or above 500 or equal to or less than 1000.

33:05 It's the exact same field and it's one single pledge and it's true or it could be true if you have any of these in your system because an account can have a single pledge in the amount meets this criterion.

33:19 And in that exact same single pledge the amount meets this criterion and maybe meets another criterion but you need to think carefully does it make sense or does it not make sense.

33:30 If it's a case in name you can't have two occasion names for one pledge. So you need to think about what my sense now let's give a few examples let's say accounts that have addresses.

33:41 We're in the city now before we spent time on the city is liquid or cedars or Brooklyn. Now let's say no no no no we only want accounts that actually have both we want to see all accounts which have at least one single address we're in the city is cedar first and they also have an address where the 

34:05 city is liquid does this make any sense this filter will pull all accounts which have a single address that means one address we're not talking about have addresses again it's really address.

34:21 This filter will pull any account which has a single address we're in the city field is cedar hurts and the city field is liquid no no no you can't have the city be cedar hurts and the city be liquid.

34:37 That doesn't make sense you can only have one city for one address you can have a hundred addresses you can have five hundred homes and you can record all the addresses for all of your homes and admire but you would have to find a way to filter for that you can you can use the advanced have.

34:55 We'll discuss this in the later video but you can't do it here because if you did or then you're saying a lenient filter as long as they either live in cedar hurts or in liquid but if you're doing and nothing will show up.

35:10 Because no address has a city field of cedarist and a city field of liquid you can certainly put a different criteria on you can certainly say the date entered was some date you can pick a date the date entered.

35:23 Was may a this would make sense a single address can meet criteria about its city and can meet criteria at the same time about its date entered into admire, but a single address cannot meet to opposing criteria about what its city is.

35:41 Because its city can't be cedar hurts and also be late with and let's just see let's click OK and you'll see again will click preview it'll freeze and continue to go because it doesn't exist and thank you for listening.