Name: Queries Everyone Should Know How to Build in eTapestry
Uploaded: 2025-07-16T18:30:46.693Z
Duration: 1 h 9 min 16 s
Description: Queries Everyone Should Know How to Build in eTapestry

Transcript for "Queries Everyone Should Know How to Build in eTapestry": Hello, everyone. Welcome to our session this week. And, hello, George, out there in Boston, Massachusetts. I've been to Boston about twenty six years ago. I was had a wonderful time there. Even had a beer at Cheers, which was, really important to me because that's my all time favorite TV show. Yes. Hello from, from Los Angeles. We've got Ontario. We've got people, filing in now here. Of course, we have our folks, out there in The UK, working a little overtime today. That's great. I saw somebody here in Indianapolis. That's great. That's where I'm at. So, so, yes, welcome to everyone here. And, what we're going to be talking about this time for this session is going to be, five queries that I think every eTapestry database needs to have. And I'm going to admit that this session is, might be a little dry to some, might be a little basic to others. But I really kind of wanted to you know, here we are in July. We're halfway through a year. Some of you have been coming to these sessions all year round. Some of you are relatively new to eTapestry. I thought, well, this would be a good time for us to maybe take a little higher level look at some of the most important stuff inside of eTapestry and that being the queries. And there are five types of queries that I think every single person needs to at least have the basics on. Now some of these queries I'm going to talk about can have things added to them. They can mix. You know, I I may talk about one query at the start here that we that you could end up mixing with later queries. But, the point here is that I really wanted to do that kind of high level, look at queries and some of the most important basic queries that that there are. But before I dive into what our, topics are going to be, I wanted to, do a little bit of webinar housekeeping. First and foremost, audio for this webinar is broadcast through your computer speakers. You should be hearing me, nice and clear, hopefully. And, if you do have any kind of audio or technical issues, like maybe sometimes things can get a little bit jumpy with the video or something like that, not that I'm really going to be doing much of anything other than going through a slide deck. Oftentimes, these types of technical issues can be resolved via just simply, refreshing your browser. At worst, maybe closing down and re, reentering the, the session through a new browser session, that usually will help as well. But it's usually just as simple as just refreshing the browser. Now you might find, with the, next to the chat section, you will see a section called docs. Click on docs or documents. That's where you're gonna find some resources. The key one that I really always share is, of course, the slide deck here. And we're going to talk, really what you're going to see in these slides are going to be a lot of, like, instructions on screen. I really wanted to do that this time around so that we can, so that we can kind of really understand what we're selecting Feedback is always important. There should be a survey, that will be found either in the follow-up email or at the end of the session. Always appreciate any feedback that you have. It also helps me sometimes find what are, you know, some of the most high important and key, topics that you might wanna see covered in the future. Now, of course, there is the chat as well. That is a great place for you to interact with me as well as the other, people that are in the session here. And, one last thing that I wanna do before I do the formal introduction that I always do at the start of these sessions. This is being recorded. So if you have to leave early or if you want to revisit this session, these do get recorded. You will get a follow-up email in about twenty four hours, after the at least twenty four hours after the conclusion of the session. And you'll be able to get in there and, take a look at that recording and follow along once again if you'd like. Now introductions. I'm Geoff Arbuckle. Most of you I recognize many, many names in here, so most of you probably already know who I am. I'll leave it short and sweet. I'm a customer success manager here at Blackbaud. At the November, I will be celebrating twenty years. I have spent the entire twenty years working with eTapestry in one way or another. I'm located in Beech Grove, Indiana, which is a tiny little village inside of Indianapolis. I'm a big comic book fan and a, lover of, animals. I have two cats. Kirby and Sadie are my two cats. But and I think Cindy is about to tell me that she's going to, Comic Con next weekend. I am jealous. I am going to a Comic Convention in the Chicago area, next month. Actually, four I I drive up there four weeks from today, so I'm excited about that. Yes, Kim. I will, yes, Tammy. And, Kim, I will, send along hellos to, Kirby and Sadie. They're they're taking naps right now. So here are the five different types of queries that we're going to talk about here. And I know there is one question in the q and a that, that was sent that I will cover here in just a moment. But we have, these are the five types that I think every person who is going to be working with queries should have some idea around how to build. An account type query, so these are, like, segments that will, base your results on the account types like individual, business, and so forth, because sometimes you need to communicate in one way to your businesses and in another way to individuals. Donations within a time frame. How to find cumulative giving within a time frame. So how to find people who gave a grand total of, say, a thousand dollars last year or something like that. A little bit different than just finding the the basic transactions from that date range. Then I will talk about your first time donor query, so finding the people who gave maybe for the very first time in 2024 or 2025 who hadn't given before. And then, also talk about a clean mailing list. Some people don't really want you to worry about sending them any kind of mail or anything like that. And, there should already be a field set up for mailing status for most of you. And if you utilize that, there are ways that you can create, a clean mailing list to make sure that anybody marked as do not mail, bad address, and so forth, get left out. Now one question that did come in is what is a query? Defining what a query is because you'll see the word query. You'll see and hear the word report. Well, what's the difference between the two of those? Well, queries take certain pieces of criteria, be it they gave within a a date range, they gave something at all, or they have a certain, attribute or user defined field that marks them a certain, particular way. And what it will do is a query then says, okay, you're basically asking the database to pull those transactions or those accounts based on those pieces of criteria. So if I wanted to say give me everybody who has blue marked as their favorite color in my user defined fields page of of accounts, it will then you know, I'm I'm querying the database to find that information and it will go out and pull those people into a list. Now from there, I can take that list and I can say, okay. Now I wanna build a report where I find extra information about those people or I wanna send a mailing to them. The query identifies the people and reports and communications where you use those queries will then display information, that goes beyond just simply finding the transaction or the account. So whenever you see terms like queries, reports, those are two separate concepts because the query is finding who or, which transactions match. So which accounts or which transactions match a certain criteria that you're asking the database to kinda dig through. And the report and the, communications is saying, okay. Now that we've got that, what do you wanna send to them in an email or create a a document for? Or what do you wanna see in a report with specific columns that may tell you more about that person or more about the gifts that they gave? So that is the difference and the the definition of query is asking the database to find those specific, entries and those specific people that meet criteria to help segment out that database for that. K? Oh, and Nicole, yes. I'll talk a little bit more about that later when we get to the clean mailing list about, the about where that typically is marked within a database. In short, it should be a user defined field within the accounts defined fields page. So we'll I'll talk a little bit more about that, when we get there. Okay. So there are three steps here that are going to be the same for every single query that we're going to make. So I'm only going to talk about this this one time here because all of this sessions or all of the sessions queries begin here. And that is by clicking on queries and then either clicking manage queries, which is the blue button in the menu, or clicking directly on one of the categories that's already listed there. They list 10 of, you know, 10 categories. Usually, the first 10 categories that are listed. If you need to see more, I usually just say click on manage queries. You'll see all of your categories there. Then once you get to your list of categories, that's where you will select the category that you want to create your query within. In this case, I've got the arrow pointing to base. That's, just kind of a generic category that all databases typically have, when you start out. And then once you get inside the category under tasks, you'll find new queries. So all of the, queries that I that I talked about, the first three steps are here. You open up the queries menu. You either click on a category already listed or manage queries. Then when you're taken to the list of query categories, you'll click on the category that you wanna store the query in. And then under tasks within that query or within that category rather, you'll click new query. So that is going to be the the starting point for all of our queries that we'll talk about. So let's now talk about account type queries. K. So account type is typically a field that is going to be found in your defined fields page within the account. So if you go to an account, you'll click the find fields. Usually, under the base category, you will find a a field that's already listed there for you called account type. And within that, you might have options like individual, business, maybe organization, maybe government organization, or something like that. But this is a great way that you can mark accounts so that you can say, I only want to send a communication or I only want information about my individuals. So that's how you would, do that. So when you get into a new query screen, so this is after you click on queries, you click on, you know, your, manage, categories, then you click on the category and you click new query. You'll name the query. The starting query the best way to describe what a starting query is think of a query like a funnel. You are taking a broad group of people and you are using criteria to narrow it down into a finer point. And if you think of it like either a funnel or like a pyramid or something like that, the starting query is your common denominator. It's your widest group of people that you're going to be looking at. By default, this is usually going to be or by default, this is going to be set to base all constituents. Most of your queries will start with base all constituents because it's looking within the, it it's looking within the all constituents query and then saying, okay. This is our starting point. These are the people we are going to start this query with. And then when we get to the criteria here in just a moment, that's where it will whittle down to the specific people that you want to find. The next thing that you will find is the results section. Now the results section, generally speaking, there are going to be two options that you will choose more often than not. And that's going to be accounts, which is the default. So it's going to display account information in the query preview. And then it will allow for you to pull extra information within a report or a letter about the account on the on the account level. The other option is journal entries. Journal entries are and we'll get into the example here, in a few moments about what journal entries will, be the best option for. But journal entries basically says, okay. Now you don't necessarily need only the account information. What you really need is you need to get specifics about a, about gifts or about, the, the the journal entry that you're pulling the information from. PIPA has a great question here. All, constituents dash a is what you will see. Now all queries will have a dash a or dash something. And what that refers to is, and Emily's correct there, that refers to the account type of that specific query. So in the starting crate in the starting query, when you select your query, it's already telling you that you're starting with an account return type query. So all constituents is an account return type query. If it was, say, all journal entries, it would say dash j e because that's a journal entry return type query. So it's giving you an indication of what information that query in the starting query is pulling information from. Tammy, if yours does not have it, I don't really know what to tell you on that. I would have to actually see it from inside your database. All queries should have some sort of a dash letter at the end in that drop down menu for query. Now base wouldn't the, the names of the queries within the category wouldn't necessarily, but in the starting query in that drop down menu, it would have to. If you aren't seeing that, I'm not really sure what to what to tell you there because they should always have a dash and then an initial after that in the query drop down menu. If they don't, you might have to refer to to support on that. So my suggestion for your account return type or for your account type query that's going to find all of your individuals in your database is to pull the results to be accounts. If we were to switch this to journal entries, you would have to add some additional criteria. But on a basic level oh, that's a good point, Geoff. If you are set to, search if you click on search by category, it should then show the dash, point. By default, it will say search by name. But if you change that to search by category, it shouldn't. Yeah. That that's a good point, Geoff. I didn't think about that. Yeah. Okay. Good. Yeah. So that's one thing. My examples, I am always doing the search by category. So when you're in starting query, by default, it's search by name, and it will just have, like it will say just base all constituents. If you switch it to search by category, then you will get those two drop down menus. So I sorry for my, my lack of explanation there. But yeah. So I recommend accounts for this because we're just going to be pulling the account information about these accounts that are marked as individuals. Okay? So this is the top half of a query. These are the basics. These are your name, your starting the the starting point that you're going to be working from, the common denominator, that everybody's going to share that you will begin to whittle the results down to, and the results themselves and the data return type that is going to be returned. Now in the bottom section, this is where you get into your criteria options. Now the first thing that you'll see there is the criteria matching. Most of the time you will probably be leaving this alone and having us just left at the default of match all criteria. But I will define what this is. Match all define all criteria versus match at least one criteria means, basically, you are saying I want the criteria to be they are the account type of individual and they have given within a date range and they have some other attributes selected or whatever the case is. Match all criteria strings the criteria that you select at the bottom of the page together with and statements. So I want this, and I want this, and I want this. It must match all you know, for the results to to display, an account must match all of that criteria. Match at least one criteria, strings those together with or. So you could say, I want, the account type to be individual or I want them to be a board member or some other attribute. Usually speaking, the more criteria that you select while having match all criteria selected, the more restrictive the query becomes and the finer the point will be if we use that pyramid or that funnel, analogy once again. But match at least one criteria. The more criteria you select, the broader the results will be because you're saying give me this or give me that. And what it will do is it will say, okay. These are people marked as individuals as well as, other people who are maybe not marked with one of these, account types, but they are marked with some other attribute in this case. So, Helen, as far as if you're going to pick journal entries and results, do you need to select all constituents dash j? No. You would still be working from the accounts. It would just be saying we're looking into these all constituent accounts. So it should still be that all constituents dash a that that, that default. It's just saying these are the accounts that we're going to be looking into and the results may be accounts or journal entries depending on what you select there. Elena has, asked here, if you are searching all constituents, then it doesn't make sense to have match at least one. Right? Because then it would just be a list of all constituents. Well, that's where the next portion of it, the the the criteria selection comes into play after that. If you don't select any criteria, then it's just going to to give you a copy, basically, of all of those accounts in the, base all constituent starting criteria the starting query. When you are selecting, like, actual pieces of criteria, so that's where we come down here to the second part of the the very bottom part of a query screen, where in the drop down menu under browse fields, you'll select UDF's constituent because that's where you will find account type. You select account type and then you check whichever account type you wish to use as your criteria. If you were to select more than just this criteria, then that's when that criteria matching match all criteria or match at least one criteria will come into play. Okay? So if you end up selecting a bunch of different criteria, match all, we'll say, okay. Based on all of this criteria, the results have to have each one of these things selected or present within the account. If you were to say, okay. Here's a bunch of criteria, but I don't care if anybody has all of it. I just need to have somebody with I just need the results to have people that have at least one or two of these pieces of criteria, then it will, it will do that. Now Desiree, when you click on the UDF's constituent, if you don't see these different options, the reason for that is is that your database may not have those fields in them. Most databases should already come with some of these, like account type. But, like, other things, like gender, the mailing or the board membership, all of that stuff are fields that I created myself. So it will be dependent on the, it will be dependent upon what the, database has in it in order for you to be able to see what selections are here. If you don't have an account type, field, I would recommend that you do so that you do create a user defined field for that because, especially if you're receiving donations from businesses and individuals, it's a good idea to have something that will delineate that, so that you're able to communicate specifically to individuals versus, businesses. You'll create that under by going under management and then going into, user defined fields. And then you'll be able to to create a new defined field in there. I would also check with the, knowledge base, which you can get to inside of, etapstree by going up into the question mark icon and clicking on knowledge base there and then searching. I would just search, define fields, and you'll be able to find that. What a journal entry is, Lindsay, is a journal entry is an actual like, that's what would be like a gift or a note or a contact entry. Every account has a, journal section, and in that section, you'll be able to create journal entries, whether it's a contact or a gift or a note. That's what a journal entry would be in that case. Okay. So, so after you make your selection here, so, like, if you have a UDF's, so if you go into UDF's constituent, you have account type, you want to select that you are specifically pulling in individuals, This I mean, you could have any type of, defined field that you may want to select. You may have a defined field that's called something different but does the same thing. Finds individuals, finds businesses, finds board members, whatever the case may be. You can, select that field then select the exact value that you want. In this case, because I want to find all individuals, I would check individual in this case. And then you can save your query by selecting an option from the save and. So you can either view the queries which will take you back to your category. It will save the query and take you back to your category. Or you can preview the query's results and then you can click save and. So you just make that selection in the drop down menu and you click save and. And then at that point, if you preview it, you can look over the list to see, okay, does this look like the list of people that we wanna have? Or is there maybe something that's not working correctly, is not showing any results? If it's not showing any results, it might be, an issue with the field not being in use, or it might be an issue with how the query is built. But something usually with just a single piece of criteria like this, you should see some sort of results if the field has been in use. But if you do save and view queries, well, the query is still saved. That list will still be there. It will be dynamic. It will allow it will update itself as you add more and more accounts into the database and you make a selection based on the criteria here. It will know that so that if you then later go to create a letter or create a, report, you can do that. If you do save and preview, it's still saving. When you click save and it is still saving the the query regardless of what selection you use. So you don't have to worry about then saving it after the fact. Okay. So let's talk a little bit more about giving in a date range. Now this is a scenario where you're going to say, I wanna find everybody who made a donation last month, last year, whatever time frame you want to use. And this is going to be a situation where certainly everybody's going to have a need for a query like this. And of course, after you get into a new query screen, you have those same three fields, those basics. Right? Again, name the query. I named the just donated last year so that you have some idea of what, I'm putting in for the criteria later on the next slide here. I am still keeping the starting, starting query set to the all constituents. The reason why for this is that I'm still looking through all of my constituents to find who those people were that donated last year. The main key difference here, aside from naming it something different, is that I'm going to set this to journal entries. Because in the end, I might be saying, okay. Well, I wanna see those transactions later. And you would certainly be able to see that within a preview or be able to use that within a report. But, if I were to leave this set to accounts, then I would only see the accounts of the people who gave last year. I wouldn't see the the details of the date, the entry, and so forth. Now if, so, Shirley, you had mentioned that when you preview the query, you can't find the you can't see the journal entries you input. What could be the reason? Could be two reasons. One, the data return type is set to accounts. Two, it may depend on the criteria that you're using. If you are using something that is not a note or is not a, or, like, if your criteria is specifically looking for gift information, then it's not gonna show notes. So, that's one thing to take into consideration there. And Cindy has an excellent question here as well. If we only wanted individual donors who donated last year, would you use the account query as your starting query? Yes. You can absolutely do that. You could say, I have an account type query that shows all of my individual donors. I'll use that as my starting query. You can absolutely do that. You could also use so here's where I said that you can kinda get into, like, mix and matching when you get into criteria. When you get into criteria, the when you're looking at transactions within a date range, the key things that you would select are found under commonly used fields, and that would be individual transaction received and journal entry date. And when you select those, you can say, alright. The journal entry date, I'm going to put in whatever date range I wanna put in. I think I accidentally have a typo here where I meant to have the end date be, '24, 12/3124 instead of, '25. But still, same idea. You would put in a day range. There's also a range type where you could use things like, where you can use preset ideas like last month, this month, last year, this year, this week, last week, and so forth. Or you can put you can type in the start date and end date as well or select it from a calendar picker. But then for individual transaction received, there there's a drop down menu here that has things like, greater than or equal to, less than or equal to, greater than, less than, equals to. Typically, when you're just wanting to find people who have made a donation in a time frame, you would just say greater than or equal to one, which would be one of whatever your currency is, whether it's a pound, whether it's a dollar, whatever the case may be. So that you do not need to put in a currency symbol or anything like that. Now if you this is where I was saying, Cindy, where you could potentially mix the concepts of these first two queries that I've shown off. You can also include the, you can go in and change the drop down menu to UDF's constituent and select account type as well. You could do that or you could if you already have a list of your individual accounts, select that as your starting query. So, you know, this is the the basics though, if you're just saying, I just wanna see every single type of account that gave last year, this would be the basics. This would be the common thing, especially if you were doing, like, tax letters or you're doing something, like, a end of year report or something like that. This would be just the basics that you would probably at least want to have. So when you do this, you would say, okay. Within the date range, that's what those that's what that range type is doing. It's saying within the state range, there has to be a journal entry that has an individual or an individual journal entry that has a transaction received amount of at least $1, £1, one, whatever. So that's what you would be doing in order to be able to get this type of information. Now, again, like every one of these other queries, you would still have the save and options. If you selected in this previous slide here to do journal entries, when, that would be under the results section there, the data return type of journal entries, and then you did save and preview, you would see each individual transaction that meets this criteria. It was given in the date range that you selected, and it will show the person's name, what they gave, what fund they gave to, and so forth. And then that can be used in, like, a thank you letter, or it can be used in a, in a report for financial purposes. Now, in a few weeks, I'm going to do a similar session about reports. So I will talk a little bit more about, okay, if you have this type of report, how would you build a report that shows financial information in a couple of different ways and so forth? So, but, yes, this would be the, this would be, you know, finding all of your transactions within this date range, and these would be the steps that you would take to get that information. Now, kind of coming off of this on the back of this type of query, Let's now talk a little bit about cumulative giving queries. Because I admit, cumulative queries are very, very confusing. It's the way the system is built. It's been this way for a quarter of a century even before I got here. But if I were to say, okay. It's great that I have all of the individual transactions that are a thousand dollars or more. But I know there are people who give 250, 300, 400, $500 quarterly. They don't have an individual gift of a thousand dollars or more, but they have given a grand total of a thousand dollars. How do I get that? It may feel straightforward, but it actually isn't. And, it's something that I am going to do my best here to try to describe how we are going to do this. So, again, here's our scenario. We want to find all the donors who gave in 2024 who have at least given a grand total of a thousand dollars. Now for those folks in The UK, a thousand pounds. Just it it just any place where I say dollars, just just in your mind, replace that with pounds. It it works the exact same. Now for this, again, you will still start with building a query finding the donations from last year. So, again, you have the query just like we just did, donated last year. They, you have base all constituents set as your starting query. For the results, data return type must be set to journal entries to use in a cumulative, cumulative query so that you can find the total giving within this date range. Okay. So in order to do this, appropriately and to get the results that you're looking for, we need the data return type to be set to journal entries. And again, just like with the previous query, we would do journal entry date and individual transaction received. That's at the very least we would need these two pieces of criteria because what this is doing is it is finding the exact journal entry transactions, the transactions within the accounts journal that is pulling or that is from last year and at least gave something. So this isn't like a, soft credit or a $0 transaction in this case. This is hard credits, hard, received dollars that you've received. Once you save this query, you'll build a second query now. Okay? You will name the query, this time giving it something like gave a thousand dollars or more last year. But you will now set your starting criteria, your starting query to the query with the gifts from the date range that you wish to find that cumulative giving from. Okay? And then depending on how you intend to use the info that you queried, you can go either journal entries or accounts. But most of the time, you will use journal entries because you're likely going to wanna display how much they gave. Okay? Once you do that, then in the criteria, there is a category called cumulative amounts. Now the cumulative amounts has a field called cumulative received, and you can set that to greater than or equal to 1,000 in this case. And what this will do now is it will say, okay. From the starting pool, the starting query of all of these gifts from last year, From that, find the ones that when you cumulatively add them up, they they equal a thousand dollars or more from accounts. Okay? So John Adams gave 12 times a $100 each. He will get shown because he has, you know, he has $1,200. He all of his gifts will be pulled into the cumulative query. Jane Addams last year maybe only gave five times at a $100 each. She would not show up in this results because her entries only pile up to $500. Now for those people who are asking about, like, the, when for those people who are asking about, like, soft credits, for example. Soft credits could potentially be used here. The first query, instead of using individual transaction received, you can use, and yeah. Instead of this individual transaction received under the list of the commonly used fields, there's journal entry types. There, you can select things like accounts, or I'm sorry, gifts, recurring gifts, soft credits, and so forth. And then when you get to this step, there should be the cumulative received and soft credited that you can use as criteria. Okay? Now for those people asking about, yes. So Chelsea, this the the query preview will show every time that they gave. In the report, you will be able to collapse that and just show a grand total of what they gave. Again, in a couple of weeks, I'll talk a little bit more about the different ways that you can work with reporting to be able to show that kind of cumulative information. For those people who are asking about, like, how households play into this, I would still do this the same way. In a report or in communications, you can group things at that point by household, and it will only pull the head of households information, but still count all it will still add up all of the households giving into that grand total. So for households, I it will still list the people individually in this query preview, but in a report or in a communication, you can say, let's group this by household. PIPA, as far as what are soft credits? Soft credits are, credits that are handed out to people who might be involved with, directly responsible for, or potentially have some sort of influence on somebody else's transaction. So, like, let's say, for example, I make a donation to your organization. I give $50 and Blackbaud matches, say, $25 or $50 or whatever the match would be. You could, on the Blackbaud matching gift, soft credit me that amount too because without my gift, they wouldn't have given. So I had some sort of influence on that. It's a way to show without actually showing physical dollars given somebody's influence on a gift. So sometimes that does come into play in some reports. Now it wouldn't be necessarily useful in a end of year, like, exactly how much money did we raise or how much money did we physically receive, but it could show what my sphere of influence has been able to help bring in. I may be have only given a thousand dollars physically, but maybe my friends, after I had conversations with them, made a donation because of that. And you say, well, let's give Geoff some credit for those transactions as well. So hopefully that helps, describe that. It's called soft credit because hard credits are the physical money. Soft credits is like the influence. It's the we're giving you credit, but you didn't actually give this money. Will this poll, so yeah. As far as, Sherry, as far as, like, will this pull all the info paid for multiple accounts? Yes. So it will still pull everybody's it will still pull all of those accounts. And as far as, like, you know, the different account codes for the same company, yes. All of that, by the way that I have this, set up with the starting, you know, with the data return type being journal entries, the starting query being the, you know, all of the gifts from last year, and these steps as far as the criteria to pull, it will say, okay. Here's all of the gifts that will add up to a thousand dollars per account. So if it's John Smith or Mary Adams or the, ABC organization or whatever the case is, it will add all of it it will pull all of their transactions from those accounts that equal cumulatively a thousand dollars or more. And as far as, Tammy, as far as, like, being able to total them up, that would be done in a report or in the communications when you say, I either wanna group this by account and collapse groups that will total it up, or that would be in a report. And I'll talk about that in a few weeks. Or if it's a communication, you can say, I only wanna send one document per account. That will total up the received amount for those that gave multiple times. This will show anybody who gave anything and if their accounts end up totaling or if their gifts end up totaling a thousand dollars or more across all the different funds, across all the different ways that they can make a donate like a physical donation, it will add that up. DAF, donations as a soft credit to the donor. Yes. Because the DAF the donor advice fund is the one cutting the check. So if you have situations like that when you talk about soft credits, I would also maybe ask around how people are handling this in the community as well. The, as far as, like, the donor advised fund, they're the ones cutting the check. They would get the hard credit. The soft credit would go from, you know, to the individual that is behind that that donor or that donation. And, yes, this is being recorded. So, about twenty four hours after the session is over, you will receive a follow-up email and in that will be a link to the recording as well. You'll also be able to get the slides, in the recording under the doc section, as well. So, okay, so now let's talk about, first time donors. These are in our scenario here, you wanna find all of the people who gave for the very first time last year in 2024. K? So this is going to be where we're going to get into compound queries. Compound queries are where you take more than one query and you compare the two results and it will, based on what you tell it how to compare, it will provide you the results. But for the people who are the first time donors, we're going to start by finding the donors, from the year that we wanna find the new donors within. So here, this query is going to be the donors from 2024. Again, our starting query is going to be base all constituents. And then for the data return type, I do as a general rule of thumb. Now this doesn't necessarily this is not always the case, but a general rule of thumb is I would recommend that you set this query to accounts. The reason for that is is when you do compound queries and you do a subtraction or an intersection, it needs to find matching items to do the subtraction or the intersection. And if they're set to journal entries and the journal entries don't match, then it may not end up subtracting or intersecting anything. I'll get to a little bit more about that here in just a moment. But just like with our last couple of queries, when we talk about finding donors who gave within a certain time frame, we're using those commonly used fields of individual transaction received, journal entry date at the very least. So, again, these are going to be the accounts based on the data return type that gave at least $1 within the date range of 2024. K? Once you complete this query, you save it, and then we're going to build a second query. This time we're going to say, I want to find everybody who gave before 2024. So notice that I did change that in the title. Now it says donors before 2024 instead of donors in 2024. Again, we're going to use base all constituents as our starting query, and our results, again, are going to be accounts. Because ultimately what we're going to do here momentarily is going to be we are going to find we're going to find the differences between these two and we're going to do a subtraction. And that's, so again we're going to see we're going to be subtracting accounts from another list of accounts. For this query, again, individual transaction received, journal entry date, these are are these are going to be your two pieces of criteria that you're going to select. And you are going to put in individual transaction received greater than or equal to a dollar or a pound. For journal entry date, if you want to find everybody who gave up to a certain date, you can leave the start date blank. So it's basically stretching back to infinity at that point. And then you put in the end date as 12/31/2023. So what this is gonna find is everybody who gave before 2024 ultimately. Then you will save this query And now we're going to create a third query. This time we're going to select under tasks, new compound query. The top section of this, the query basics, are going to be familiar, but there's going to be one key thing that's not involved here. You're going to name the query, so this is gonna be new donors in 2024. And then you are going to, set the data return type to accounts in this case, but you're not going to there's not going to be a section there to select a starting query. The reason why is compound queries don't work with queries that the same way as regular queries do. So it doesn't ask for a starting query. What you're doing now is you're now going to say, how would you like the results of each query to interact? There's intersection that says, okay. These are the people who will appear in both of the queries that we'll select below. So find the people who are in common of the with the two lists. So these would be the people who gave in this example, these would be the people who gave before 2024 and gave again in 2024. That would be intersect. Adding would just take the two lists and smoosh them together. It won't duplicate people, but it will just take the two results and say, okay. Well, there were, you know, 50 people who gave in 2023 or before, and there were 25 people who gave in 2024. Take those two lists, push them all together. If there are people who gave in both years, they will only be listed once. But to find the people who gave for the first time in 2024, we're going to select subtract. Then when you select subtract, there are two queries that you have to select here. Now later this year, we are expanding this to be more than just two queries that will interact, but for now, you'll just see that there are two options here. Query number one, we have to set to the donors who gave in 2024. Query number two will be the people who gave before that. When you do the subtraction, what it's saying is it's looking at the accounts in query number one with the donors who gave in 2024, It's comparing that to the list of people who gave before 2024. And because this is set to subtract, it is removing any donor from before 2024 from the list of donors in 2024. Now I see a couple of people asking about lapsed donors. Lapsed donors would be you would take the two queries and swap them. If you're looking for people who gave before 2024 but did not give in 2024, query number one would be the donors who gave before 2024. The query number two would be the donors who gave in 2024, and it would subtract those people out from the other list, leaving you with the people who are now lapsed. But the same concept is at play here. You're taking two lists of accounts, you're comparing them, and then you're subtracting one group of people from another. And then what you it doesn't matter if the if query number two is larger than query number one, it still works the same. It's just saying these people match, toss them out. They're not going to be included here. Yes, Cindy. So if you were looking for lapsed, you take this query and you invert query number what's listed as query number two on screen here would be query number one, and query number one would be your query number two. So but the first time donors, is a key thing for a lot of, you know, like, you know, some people like to do, like, specialized communications to those folks or welcome packages or something like that. You could I mean, I do this based on years, but with the same ideas in mind, you can do it for, you know, new donors from this past month, this month, or whatever the case is. Same idea in mind. You would be taking two lists of people who gave in two different time frames and doing the subtraction at that point. And same concept in mind for lapses. It's just it depends on what query number one and query number two is. When it comes to compound queries, the key thing to remember is how do you want these or what what is it that you wanna see? I have a list of these people. Do I want them do I wanna also find the people who do I wanna find the people who are in both of these two lists that I have of these two distinct groups of people? That would be intersect. Do I want to just put them all together in one big list? That's add. Do I want to say, okay. Well, in this query, I have group a, but I don't want anybody who falls into group b. That would be subtraction. When you're doing subtractions, think of query number one and query number two like you would in a normal subtraction math problem if you were writing it out on a piece of paper. The what's being subtracted is on the bottom, in this case query number two. So just keep that in mind. Now another example of a compound query that is very handy for people to have and to be able to use is your clean mailing list. Now this is gonna be our last query that we're going to talk about here today. We are probably gonna go a couple minutes over, just let you know. But, this scenario you need to produce a list of accounts who are not marked with do not mail, deceased, bad address, etc. Now in these in this type of scenario I'm going to be using a field called mailing status. Many of you should have a field called Mailing Status in your Define Fields page within accounts. You may have it called something else, but the idea is this is where you are marking people as not to be mailed to. Okay? For whatever reason. In mailing status, we typically have fields that say, like, do not mail deceased bad address and so forth. And this would be, within the accounts. Yes. So if you went into somebody's account and you went to define fields and you opened up base, you should find some sort of a field. It may be the mailing status that I have that a lot of other people have. It may be called something else that your organization uses for the same purpose. So that's just the only caveat here. But here, again, we're going to create an account return type query of people who are marked as do not mail. So we call this do not mail, we set our starting query to base all constituents, the default, and your results will be accounts in this case because these are the accounts that are marked this way. We don't really care about their journal entries in this situation. Now here is where I might recommend that it'd be a good idea that you select the match at least one criteria under criteria options. The reason for that is is that in my database, this is a checkbox field, I could potentially mark somebody as both do not mail or do not solicit, or I could mark them both as deceased and bad address. K? So that would be a checkbox type of field. So in this case, I would want it to say match at least one criteria. So I can say, I want to find the checkboxes that are marked as do not mail, deceased, do not solicit, or bad address. Anybody who matches any one of those I want in this list. So to find this field, you would do the UDF's constituent in the drop down menu under browse fields, and then you would select, in my case, like I said, I have mailing status. You may not have mailing status. You may have a different type of field that selects that, but you would select that field and then make your selections. Then this will be then. We'll create another new compound query. We didn't have to select a second query. The reason why is after we named this query clean mailing list, we set our data return type to accounts and we select subtract as our, how would we like these to interact with one another. Query number one is going to be all constituents. So you would, select there to search by category then do base all constituents. And then for query number two, you're going to select your do not mail list. So this is going to then find all of those people who are marked with one of those mailing statuses. Do not mail, bad address, deceased, do not solicit, and then subtract them out from all constituents. Now you have a clean mailing list at this point. It would be all of the constituents that, as far as you're aware, are okay to be mailed to. This query, the the clean mailing list, could potentially be used as starting queries for other queries. So instead of base all constituents, you could start with the clean mailing list. It could be used just straight up as it is. When you create your mailing, you just say this is the list that we're sending to. We're sending to everybody that can be mailed to. But once you have this, you would have the ability to basically run from, you know, run with it from here however you want to then interact with these folks. So Kim asks, so anytime you want to, do a mailer, does this mean you would need to run this type of compound query? Well, once you set it up once, you will have it. So it would be the recommendation would be to either use this as your starting query for whatever list you do want to send to or use it as your mailing list. As you mark people with whatever status they're going to have, that will update on its own in the background. So you would really only have to create the query just once. Then let's see here. I also see, so no. Actually, I'm sorry. Cindy here so to to do the setup here, we just created this one query of saying, you know, like now conceivably, you could say, okay. You could take your cumulative, transactions and put that as as, you know, within this clean mailing list, you could conceivably do that. But to create a clean mailing list that you can always start from at least, you just create a query that that queries on however you mark those mailing statuses. And then once you save that, my example here is then you will do clean mailing list, data return type accounts, subtract, and query number one being your all constituents instead of, any I mean you could conceivably use any query that you want there, but if you just want to always have a list of people that automatically removes this group of people from the very get go, I would say use this setup where you have query number one as all constituents, query number two is do not mail. Do the subtraction and now you have a clean mailing list query that can use that can be used in place of all constituents in other queries or it can be used as your general mailing list. Yes. Yes. So yes. You're right, Cindy. Yes. So now, like, for example, if you were doing, like, a, cumulative query, the the first query where you find all of the individual transactions from whatever time frame you're looking for, instead of base all constituents, you could use clean mailing list as your starting query, find those, and then that query then becomes the starting query for the cumulative portion. So, yes. I mean, this can be used as the starting point going forward if you want to. Yes. Alright. So just a couple of things I do want. I know we're a couple of minutes over. I appreciate those who were able to stick around. If if you do have to leave, these last few minutes are still going to be part of the recording, so there shouldn't be any issues there. But, I do wanna talk about a couple of things here. All of these types of queries, there should be a, knowledge based solution with the steps written out for you for each one of these types of queries. You, you know, again, you can find the knowledge base by going into eTapestry. Click on the little question mark icon in the top right hand corner. Knowledge base will be listed there. Select that and you can search for, you know, what you want to to do as far as a query. Also, we have the Blackbaud University that does have a basics, talking about queries. There's also, if you have a learn subscription, there is more fundamental, instructor led courses about different types of queries, gets into a little bit more of the nitty gritty of the various, settings for queries. Of course, if you run into any issues or you need some troubleshooting help, you have the customer support team. You can reach out to them. Easiest way is to go up into that question mark, click on the chat now, and, you can ask the, the AI chatbot to, you know, connect you with a member of the support team, and it should be able to to help you get there. You also have the community. The community is a great place to engage with other folks, that are using the system just like you. I do sometimes put out some, blog posts. I don't have any planned quite right now, but I may later in the year. Once we get into August and into September, I may have some more, blog posts coming. But that's a great place just to interact with other folks who are using eTapestry. Ask them questions, kind of pick their brains as to what they are doing. Next Wednesday afternoon, we are going to have the Ask the expert, and I'm sure I'm going to, go through more of these types of scenarios, especially where I talked about where you can begin kind of mixing some of these concepts, having, donors who gave in a time frame, but also have another attribute selected. Those types of scenarios, you know, so I'll I'll probably be answering questions about that as well. One more thing here is we do have three, we have a couple of programs here that I do want to, make sure everybody is totally aware of. We have, the Blackbaud Champions. This is a great, place to really kind of build more knowledge around Blackbaud and, the specific Blackbaud products that you use, in this case, eTapestry or Blackbaud Merchant Services. I kind of think of champions as like community plus. You're kind of engaging with other folks within the organization as well as as well as members of the Blackbaud staff, as well. There are some professional development opportunities. There's kind of a, like a little reward system that that goes into it as well. If you're interested in, signing up for Blackbaud Champions, just let me know in the chat. I'll make sure to, get you over to the, the team that that handles that so that they can reach out to you and and help bring you in to the to the program. The reference program is where, you know, we might have somebody who's looking into purchasing Blackbaud, products. In this case, again, would be eTapestry. They may wanna talk to somebody who's been using the system for a little while and kind of ask them about some of the things that they've been able to do with the program, kind of before they make a commitment to it. If you're interested in that, let me know about that as well. You choose how often you are willing to take a call. So it's not a situation where you have to be sitting by your phone all the time. It is something that you can say, you know, I wanna be able to do this once a month or once a quarter or whatever the case is. And then finally, we have the spotlight your success. If you're ever interested in joining me on a webinar or, writing a blog for our community and talk about some of the things that you're doing with eTapestry, or, you know, some of the successes you've had with Blackbaud products. Please feel free also to let, let me know about that as well. But, for now, though, I think we are pretty much at the end of the road here. So next week, like I said, there is the, you know, we do have the ask the expert. That is found at the same place that you, signed up for this session. I usually get a lot of really great questions. I usually share my screen. I'm able to kind of show, kind of walk through those steps in a slightly more detailed way than I can in these types of sessions. So I definitely recommend that you, take advantage of that. And actually, I will get the link here for you in case you did not sign up for that. And we have a bunch of other stuff coming later in the year too. I just put the link to register for upcoming sessions in the chat. More sessions coming up in, like I said, in three weeks, we will have the other side of this, the the types of, ways to build reports that everybody should know about how to do. And we've got some, some additional things planned out for the rest of the year as well. So I look forward to seeing you there at those sessions, and I hope you have a great rest of your day and a great rest of your week.