Calculated Fields

Good afternoon, everyone. My name is Janet Carroll, and I’m happy to be your trainer today to learn all about calculated fields in your DonorPerfect database. We’re going to go through an introduction of what calculated fields are all about, and we’re going to look about how Read More

Good afternoon, everyone. My name is Janet Carroll, and I’m happy to be your trainer today to learn all about calculated fields in your DonorPerfect database. We’re going to go through an introduction of what calculated fields are all about, and we’re going to look about how to build calculated fields, and we’re going to do some examples. So there’s nothing better than really demonstrating what it’s all about firsthand. So let’s get started. And I’m part of the DonorPerfect training team, happy to be joining you this afternoon. So thank you for joining us today. We’re going to talk about the purpose of calculated fields. We’re going to give a few examples and go through them step by step. We’re going to go through the steps of building a calculated field, and we’re going to look at ways to use Calculated Fields, because there’s nothing better than putting something into play and then using it right? We’re going to create something custom, and then we’re going to see how indeed it can be used within your database. Let’s take a look and thinking about the custom way to do the work that you need. So imagine that we are looking at a calculator like this is showing and imagine that these are really behind the scenes formulas. So some of you might be thinking, Well, what’s a formula here? What are we talking about? Well, sometimes I’m thinking about wanting to add up the number of gifts in a date range. So I want to get a count of gifts in a certain date range. I might want to add up the dollars and cents that’s, let’s say, money that was given towards the building fund. Maybe I want an average of the last two completed calendar or fiscal years, all of these different metrics, and the list goes on and on. It’s really a matter of your data your way. So lots that we can do. It’s a matter of saying, What do each of us need in our database that we might, right now might be literally pulling out and working in Excel, and we’re saying, Can the database do this for us? So we’re going to see a lot of the different ways that this can work for us to help us be more efficient at our work. Right? We can also, besides all the numbers, part of it, we can also join together calculate character fields. One of the first things that comes to my mind is either, let’s say title, first name, last name, or title, first name, middle name, last name and suffix. Another popular one is city, comma, state space, zip code, all sorts of different opportunities. I even remember helping somebody a while ago when they wanted the code for the general ledger with a dash and then the description of that general ledger. So again, your data your way. Whenever we do whatever we set up here we set up our rules. Remember, it’s your way. It’s your calculations. Once we set up the instructions and we we say, go ahead, do this for the very first time, and as long as that was successful, where we did all the setup correctly, then the database is going to keep that up to date for us automatically. I find that is one of the biggest carrots that we have here to setting up these custom calculations. Where can we use this? Well, remember, when we’re setting up something custom, it’s there to use, just like all the other data that we’ll be entering directly, or let’s say, downloading from an online form. So it’s a field like any other field, except that we’re not going in there and manually creating this, adding up or putting the data together. So yes, we can ask for the data within a filter. We can include it in reports. We can whether it’s in a easy report or use it in an export format or an export template. We can also display it on our data entry screens. Typically, when we create a custom calculation and we want the data to be, take the database to be, controlling it, we typically make that field for display only, for read only access, so we can look at it but not judge it, depending on how you set it up, if you need to have an override, such as a quote, unquote full name, like title, first, middle, last and suffix, if there’s a custom way that you need to have it override. Maybe we need to have the database created for you only when it’s not yet filled in, and then it’s there for you to do an override and edit. So we have different ways that we can go about that approach. So here we are creating data your way and then using it and different ways within the database. So let’s take a look at this gift grid here, this entry screen grid for Roger stands. As we’re looking at it here, we notice certainly, a few things, right? I’m looking here at the gift amounts. I’m looking at the campaign fields. I see dollar amounts, I see different campaign codes. And the idea is, what is it that we are looking to do? Well, we’re all we’re part of us is thinking, what’s here? What can the database do for us. So with that data highlighted, we’re actually taking the next step and saying, You know what, I only want those that are part of the capital campaign. So in this case, I have the dollars highlighted for the capital campaign only, and then we come in say, You know what we’re going to do, a letter, and we are adding up all those campaign capital, campaign dollars that we just saw, and putting a total of that dollar amount right in the letter. So I find that that is fabulous. So if anybody has any questions about that, please, let me know. I find that this is one of the big, bigger advantages of setting something up and then using it in multiple ways. So I see that there is a comment and yes, we can format it. Somebody Mary was just asking about formatting it. We see it has $1 sign in that currency format? The answer is yes. We set when we set up these calculations, we want to define it as we know we need to store it. So in this case, it’s a currency field. We’re adding up yes, we’re adding up numbers. We’re adding up the currency fields in that amount of all of those capital campaign transactions. So we created this total field for all those capital campaigns, and we made that a currency field as well. So this is fantastic. So we see it right away. We see it in use. What about this? Looking at gifts on the top of the screen, and we are also looking at pledges. So I’m looking here, and I see we’re on the gift we’re in the gift area here, and down here, we are looking at our pledges. So now we’re going to say, well, what are we trying to find this time around? And now we’re looking at the dollars and cents. We’re also looking at it by the gift type, right? And we want to make sure that we understand what’s going on, right? We see in kind we see credit cards, we see soft credits, and we also see a pledge. So the question is, how might this be helpful to you? So if you have any ideas or thoughts, please send them my way. How? What do you see in this report? Right? How could this be helpful to you? And it looks like we have some comments about the straight gifts. Maybe their pledge payments. We have a pledge on the books. Is somebody putting money towards the pledges? That’s a great question. Yes, we would need to take a look further right now the top portion on the gift area is not displaying whether or not it’s a pledge payment to know whether any of those are literally going against that $2,500 but a great point, nonetheless, fantastic, right? So we need to decide, what are we doing here? How can this information be helpful? Well, I’m now looking at this top donor listing, and what am I doing here?

Well, I’m actually looking at this column that is added to the top donor listing. If you’ve ever used the top donor listing report, we get reminded that this top the right hand section, these right five right hand columns, are standard parts of this report. So whenever we have an extra column in here, like we do right now, it means it was one of these custom calculations, or a you. It could be a calculation that’s simply not one of those last five columns. So when I think back to the screen we were just on, we get a lot of displays from 2024 and we clearly added up the dollars and cents, right? That’s what I see here, campaign, fiscal year, 24 underscore, total. So somebody created a custom calculation and then did a ranking on it. I find that to be fabulous. So we are able to do rankings within the top donor listing right as long as it’s that numeric or and currency is one of the numeric options, right? So as long as it’s numeric in one way, shape or form, we get to do a ranking. The rankings are always top down. So we see that Smith, company was a was had the most donation so far, overall, with $18,000 and we also see that we see exactly who it was right from the top donor listing report.

We’re now looking at, look at this. Excuse me, donor, full name, Iris Stark, and I’m looking at this, and I see that we’ve got our first name of ARIA and the last name of Stark, and there we have this donor full name. Let’s see what we’re going to do with this. Well, we’re going to come back to that, because we’re looking at all these different options of where names can be coming from. So now I’m looking at the Cross Tabulation report. The cross tabulation, as a quick reminder, allows us to choose two fields, because this is a financial report, at least one of these two fields has to be financial. So in this particular case, I somebody chose the last name field and the solicitation Code field, right? So we have the financial field, the solicitation code. And in this case, it was saying, let’s put the last name and compare that to the solicitation code in a date range. And I have the date range right here, all right. Well, this last name column does not look for me. It does not look very useful at all times when I see ABC foundation, that looks great. Adams, McKinley Corporation, it looks great. But look at some of these others. Adams, Agarwal Allen, look at this. Arnold Atkins Anderson, these are just last names, and there could conceivably be multiple donors with that last name. And to be honest, I don’t want them mixed up with others that happen to have the last name, whether related or not, because the idea here is to have each unique donor. What do you think about that? Does any Did anybody see that right when you’re looking at this report? Yes, it’s an alphabetical order. Good observation. Absolutely. Richard, so we’re looking at this report and we’re saying, how could we make this better? Well, I’m thinking back to that other screen where I had a first name and a last name and I had that donor full name that was highlighted, and I’m thinking that would make this report much better if I could combine that information on individual name fields that would make this report much more useful. And that’s what we did right here. So take a look at this. We’ve got a full name. This is a custom calculation, a calculated field. And look at this. We now have names, full names, right? We don’t have just last names. And here is that one that was demonstrated just a few slides ago. Are you Stark? So it’s much easier to see that who somebody is with their full name to, of course, identify them. But at the same time, if we had two different people with similar names, right last name being in the same now, they’re all going to be separated. In this particular case, I have two Orleans. I would not want this left hand column to be first name, just like I wouldn’t want it to be just last name. So here we pull it all together, and we have a much better analysis on the donations by solicitation code, using that field called Full Name. Same that we set up with a custom calculation. So I don’t know back to you, but I find this is pretty darn cool. And if you have any questions, please, please feel free and ask. So I would love to ask everybody, with everything that you’ve seen, I would love to ask you if there’s anything in particular that you have been thinking about that you would like to see set up in your own database that would save you the time and effort in doing something manually right now, because I am going to be demonstrating this in a little bit, and I would love to be able to take some of your examples and do that along with some of what I have prepared to do, because if you have a direct interest in it, that’s the beauty of having a live webinar. Okay, so let’s continue on, and let’s go through the process of what it takes to add a brand new calculation, a custom calculation, or as we like to call it, a calculated field, always custom, right? So we are doing this from the settings menu. I’m just going to take a quick sidestep here and just point out that where we are going to be working is right here on the settings and calculated field option right here, the first option under Settings. Yes, I have many in my test system right here. We’re going to be here creating but right now we’re going to take that side step back and look at some of the options that we’re going to be filling in in our examples. So when we add a brand new calculation, the first thing we are asked to do is provide a name and a description, because on the grid, on the outside of looking in at all of your calculated fields, we get to see what’s there, and we do want to have logical, good names that we could refer to at any time, and that is going to be described and accessible to everybody that’s maybe new and not knowing what’s what’s there, what somebody set up to be calculated. We want it to be very user friendly. So Provide a name and a description, and this part of it, you can always come back in really anything. You can come back in here and and then edit it. In case you have one of those aha moments that we all get every so often and find that we have a better description to for what we’re actually setting up, then we need to decide where we are going to be storing, holding on to this data that we’re about to create. So you might have a feel that you created beforehand, or somebody created for you, okay? And you also might say, You know what? I don’t have one right now, but I need to create it brand new. So as this says here, the screen of the field is going to determine the type of calculation that is available. And that really means that for on the gift screen, we are limited to adding up or multiplying or doing some kind of a calculation with data on the gift screen. If we were on the main screen, we can literally take data from other screens and add them up, or count them and find only certain values of it, depending on where we are, is going to determine what, literally what we have available to us to perform. Whoops. Go. So we have a lot of different types of calculations that we have possible. I mentioned earlier that we could count. Some people like to count gifts. I know of others that like to count touch points. You know, you might have personal ones, as opposed to those that are auto generated, let’s say, from Constant Contact, right? So you might have a mailing groups of different kinds of mailings, and you might say, You know what? Give me only those contact transactions that are phone calls and personal emails or handwritten notes, etc. Find those and add them up. Just simply count them. You may also say that I want the first membership gift ever, you know, or maybe the land the date, okay, maybe the date of the last membership payments, all these different references you could refer to, again, like was shown before, whatever you’re doing, that’s this calculations you could use that later on, the examples I just gave, I might be using internally, like my touch points. Uh, but the date of my first or last membership gift, right might be used in some of my correspondence, in a merge document, email or letter that’s going out to my member. So there’s lots and lots of opportunities you set the rules and you decide how it’s going to be set up, right? So you need to know what’s being used in the calculation. If I wanted the date of the last gift, it’s going to be my date field, actually my date of gift field, which is gift underscore date. If I’m asking for a count. I don’t need to know a field. If I’m asking for something to be added up, I need to put in the names of the fields that will be added together. Yes, you’re absolutely right. Bill with a plus sign, okay? If I have a an average of two different years, I need to know the names of those fields. And yes, we have resources to show you how to get to those fields, right, so that you can pull down the list, and you’re going to see, when I get to that point, how we can go about doing exactly that.

We sometimes need to put in restrictions on what data that we’re looking for. My example before about membership gifts, I likely have to have a a filter that’s asking only for my membership transactions. So it might be general ledger equals membership, okay? Or depending on what your codes are, what is specifically is going to be your selection filter? Sometimes there’s a date range. Every so often, there is no date range. If something is lifetime without any other restrictions. As far as a filter is concerned, if we’re working with gift data, we then have additional options. And I’m going to use the word opportunities as well. Do we want to include soft credits? Are we including money in the door, which includes pledge payments and straight gifts, or are we going to use pledges and straight gifts only? So again, different options and opportunities. You have to think about the rules that you are setting up to make sure that it is going to be done your way. Okay, the way that somebody needs the information. Last but not least, of course, we need to save the work that we’re doing. When we save the work we’re doing, we’re literally saving instructions. And when we save instructions, we need to then make sure that it’s going to work for us, that we set up our instructions correctly. So what do we do? We have this calculator on the front facing screen, and we click on it, and then it’s really for purposes of saying, let’s run this now. Let’s make sure that everything was set up correctly and successfully, and once you do that, it’s and it is successful, it’s now going to be running in the background automatically. So I happen to love that part of it. If there’s any questions, please, please let me know what your thoughts are, and I’m happy to take you again down that path, because there’s nothing better than just making it all happen. So I’m going to come back in the database here, and again I am at settings and calculated fields. And what I’d like to do now is do a combination of what I have prepared plus what you have asked for. So the first thing I like to do is do a sum. I’d like to just show you how we can easily add up money in one particular General Ledger code. So let’s do that again. I’m at settings calculated fields. I see all of these set up. Their order does matter, by the way, when we whenever we add something new, it’s always at the top of the list. But you know what? You can always click and drag. Sometimes we have calculations, and one is dependent upon the other, and since every time we add something new, it goes to the top of the list, if we actually have to have it run, then in that reverse order, in the order that we created it, we just have to change the order once we’re done setting it all up. So as the old as the saying goes, if we need to go in order of A and then B and then C, we need, we might put set them up in that order. But because we set them up in that order, and it will then look like it’s C, B and then a, we just need to, again, click and drag, and we can put them in the order that they need to be in, because if one value is a total of the others, we need to take those. Create those components first and then the automation would because it goes in order, it will basically, let’s say, calculation a or number one the second calculation and the third one could be the sum of the first two. So we sometimes do need to click and drag to make sure that everything is in the order that it was designed and expected to be in. I see the very top. It says that the default is showing the active we see that here in the against the green background. And if we want to see all some of them might not be, we might have some inactive calculations. And right here there is a total of 74 so there could be some inactive ones in this list. But what I’m going to do is I’m just going to come right down to the bottom, and I’m going to say, add a new calculation. So remember at the beginning the first step was to add a calculation, name and a description. So that’s what I’m going to do right now. And my first example is going to be adding up the total dollars in my building fund for this year, 2024 so I’m going to put in a description total building fund, 2024 and my description, I need to decide, am I just doing money in the door? Am I going to use pledges or soft credits? I need to decide that. And if I change my mind later on, I need to make sure that I change whatever is applicable towards that. So I’m going to call this right, this one, this example here, money in the door. Okay, right. So that means straight gifts, whoops, straight gifts and pledge payments, no soft credits, just simply money in the door. I’m going to click Find field, because right now I have to identify where I’m going to store this information. So I’m going to click Find field. I don’t have anything right now, so I’m going to click at the very bottom of the list and say, add new fields. If you’ve ever added a field to dinner, perfect before you might find that this particular screen is familiar. My goal right now is to add up dollars and cents from the gift screen and store them as a total and because that’s not about one gift, it’s about potentially many gifts, that means I need to store it elsewhere. So I’m going to get stored on the main screen. So I’m going to keep the screen as main and I need to think about a naming convention. So what I’m going to do is, I happen to like to start out my total fields with total underscore. I like underscores. You can use underscores to help make the field a little bit more easily readable, so it doesn’t look like one big compound phrase. You’re allowed 20 characters. I’m going to ask for total underscore. BF, for building fund, underscore 2024 so what I’m doing here is I’m setting up a naming convention for myself so that the next time around I might do total underscore, BF, underscore 2025 right? And I could then keep built adding on to that. And remember, this is calculating this or setting this up on a donor by donor basis. So I find that pretty nice. So if I want to be adding to all of this and having year after year, I can have extra metrics all about the building fund, if that’s my goal. Right now, I want to create this total for 2024, so now I’m just going to create the screen prompt which is user friendly, which means I am allowed to have spaces. So I’m going to say total building fund for 2024 and this is money, so it’s currency. So my display type is correct at text box, my data type is going to be currency, and the currency right here is better than number, because currency, it knows what to do in the behind the scenes about decimals and for to use the potentially the currency field if I’m asking for that in any of my work. And now the question becomes, where do I want it displayed? Okay, so what am I looking for here? I am going to put this field in my giving and engagement profile, where I have a lot of other fields being displayed. I’m just going to let it go to the end of that section. So I’m going to leave it blank to go to the first open field. And I’m also going to say, not only display it, but make this read only. And reading read only means that it’s going to have that gray background so nobody could try to click and change anything. It’s going to be set up by the database and stored by the database, the calculation itself. All of these instructions, all right, I’m going to click Save field. And exit, I now have a brand new field that always shows up in green. So whether it’s a brand new field, like I just created, or if I selected an existing field, it’s always going to show here in green. Yes, I’m going to be using data from my gifts and pledge screens, but now I have to decide again. How am I doing this, I am going to use, as I said before, money in the door. So I’m going to update this with the sum. Look at all my choices here. So some I’m going to add up the dollars and cents money in the door. So that means I need my amount of gift fields. If I don’t know what to type in here, I come over here to find field, and I’m just going to do a search here to make it easy for me, and I’m typing in amount, and now I just need to make sure I get the right field here. And I’m choosing gift amount, and I’m going to select so if you knew to type in amount or DP gift dot, amount, you can type that in yourself. But a lot of people choose the magnifying glass as the best way there. We now need to set up a rule, what records, what gift transactions are we going to be finding in order to add up the money? So I’m going to set a selection filter.

I’m going to click Apply. Oh, and I apologize. I think I just got bumped out. Let me just do a quick save here. Let’s try again. Ah, I apologize. I think I have to come back in here. Let me do that very quickly. And I’m going to edit what I have already set up. This should be here, total building funds. Let me click the blue pencil here, and let’s do this again. Let’s click Apply. Here we go. I’m going to add a brand new filter, and remember, I’m looking for the building fund, which is a general ledger for me. So I’m going to go onto the gift screen in step number one, and I’m going to ask for general ledger. I only want one code my building fund, so I’m going to ask for equal to and then in Step number four, I’m going to click look up codes. Best practice when I see that button to click on it, and I’m going to ask for my building fund right here. So I’ve found my building fund, and I have my code of BF in blue. So I clicked on that. And remember, I’m looking for the date range. I want only 2024, so I’m going to click add more criteria. I’m going to start again in step number one, I still need my gift pledge file. This time. I want the date range, so I’m going to come into gift date, and I want to between, and I want it from January 1 to December 31 so I have my between, and you can use your calendar picker or just type it in. And here I have my date range, and I’m going to click continue. So I have my general ledger building fund, and I have my gift date begin between January 1 and December 31 so now I’m going to save this, and I’m going to save building fund, and I’ll just remind you here that is GL during 2024 gifts. Let’s add the word gifts for the building fund during 2024 because I have a calculated field, I want a reminder that this is for a calculated field, and I’m going to do that within my filter folder. I don’t want anybody changing this. If they go to change this using add more criteria or other editing it means it’s going to, indeed, to affect my calculated field, my custom calculation. I want a reminder, and that’s what this folder is about. And I am going to, in this particular case, I’m going to exclude pledges and link gifts, and I’m going to share across all users so my colleagues can see it and use it if they need to. Let’s click Done. Now I have that other area about what do I want to include? My pledge payments selected means it’s all money in the door. I’ll do another example that will include pledges or soft credits later. So I see some people have put some suggestions in there and the box in the chat, so I’m going to take advantage of that. But for this one, I want to just do include pledge payments, and I’m gonna leave all the boxes checked by default. I’m gonna leave that alone and click Save and Close. I now have my instructions ready to go. On this top line, I’m going to click my calculator. It says, run this calculation. I have chosen to run the following calculation. This looks good. I’m going to click Run calculation. It is successful. I’m going to X out of here. And I just want to take a quick diversion before going to my next example. And let’s see whether. Or George Judson has any Nope. It says total building fund. 2024 George has not given any money, not yet. So you know what I’m going to do, I’m going to actually run a quick report. Let’s do the top donor listing. Here we have the top donor listing, and I’m going to ask this to be run on the total building fund. Here it is, and let’s just look at the top 10, because I will make a quick look and show it to you on the on the giving and engagement profile section. So I’m just going to run this report now I’m and look at this. We have met so many donors here. I only asked for the top 10, so I’m just going to click on John Howard, and let’s look at that giving and engagement profile section. And here it is, total building fund of 2024 up. I see some people saying, very cool. This is really nice. And remember, gray background, it’s going to be there to look at. We know it’s controlled by the database. Pretty nice. All right, let’s do a couple of more custom calculations. Some more calculated fields. I’m back up here at settings, calculated fields. I see what I just did on the top, and people were indeed asking. I see that note. So thank you all. I see some people that want to know about, about and have that experience of including other than something, other than just money in the door. And I know that this is something that a lot of people tend to do, so I’m going to come down to the bottom of here, and I’m going to add a new calculation. And let’s do a lifetime amount, plus pledges, plus soft credits. All right, so all dates. Let’s do this. I’ll just as part of my descriptions, we’ll just say all dates, money, plus promises, plus self credits. And then self credits can come from different areas, so we’ll leave that as something that’s general. I’m going to click Find field. I remember I need a place to put this total. So again, you could be searching through in case to find something that you or somebody else created. I’m going to click add new field, knowing that I want to show you how to do this the setup. I’m going to come into field name, and because this is a lifetime total, you might want to use the word lifetime. I tend to use total, unless you’ve got a series of lifetime values in different other categories. Again, have a naming convention. Stick to it. It makes it your life and your colleagues much easier. Mine happens to be with total underscore, and I’m just going to do a little bit of abbreviating, just because I find that to be a little bit easier. And I need to also stick to the 20 character limit for my field names. So I’m going to use amount, and I’m going to use P for Pledge, and I’m going to use SC for soft credit. So if I ever go past 20 characters, it’s going to say something to me. It’s going to give it’s going to remind me that I can only use letters, numbers and underscores in my field names, and it has to be within 20 characters. So I’m fine, nothing. There was no message that would be displayed to the right. So now I’m going to put my user friendly prompt. So I’m going to say total for the amount, plus pledges, plus soft credits. But you know what my amount is really straight guess, right? So we’re going to maybe add something here, so we’ll say straight gifts. Oops, there we go. So we’ve got total amount, which is straight gifts, plus the pledges, plus soft credits. So when somebody has those donor advised funds or matching gifts, and you want to know the influence and the effect of wherever it’s coming from, but not double counting the Young Money within the same record. When we have Pam pledges and pledge payments for not including pledge payments. Again, this is still currency like the other one was. So I’m going to come in to leave, we’ll leave the display type as text box, and in this case, I’m going to still make the data type of currency, because this is a calculation. Again, I like want to keep all my calculations together. You may have different sections for different types of calculations, whatever works for you, for your particular screen, we want you to follow that you. Like before, I’m going to let it go to the bottom of the section, and not only will this be displayed, but it’s going to be read only, so it’s going to have that gray background on it, and I’m going to save field and exit. And yes, again, I’m going to be taking the data from the gift and pledge screens, and this time, literally, both of those screens. And yes, right now it’s going to be adding up. It’s going to still stay as some like I did last time. However, I now need to have a few fields here. I need to not just do that amount of gift and again, if you don’t remember what to type in, I’m going to come over here to click Find fields, and I’m going to come over here and type amount, and again, the gift amount, and select but now I need to add a plus sign, right. And now I need to find the total dollars of the promise, the pledge. If you’re not sure at any point in time, if you ever go back to the data entry screen, you’ll see your prompts. So when I click Find field, the searching here looks at the prompts as well as behind the scenes. So I’m just going to type total, knowing that the total field on the pledge screen is that total dollars promised. I’m going to click select it and click select here.

Apply. Oh, and I apologize. I think I just got bumped out. Let me just do a quick save here. Let’s try again. Ah, I apologize. I think I have to come back in here. Let me do that very quickly. And I’m going to edit what I have already set up. This should be here, total building funds. Let me click the blue pencil here, and let’s do this again. Let’s click Apply. Here we go. I’m going to add a brand new filter, and remember, I’m looking for the building fund, which is a general ledger for me. So I’m going to go onto the gift screen in step number one, and I’m going to ask for general ledger. I only want one code my building fund, so I’m going to ask for equal to and then in Step number four, I’m going to click look up codes. Best practice when I see that button to click on it, and I’m going to ask for my building fund right here. So I’ve found my building fund, and I have my code of BF in blue. So I clicked on that. And remember, I’m looking for the date range. I want only 2024, so I’m going to click add more criteria. I’m going to start again in step number one, I still need my gift pledge file. This time. I want the date range, so I’m going to come into gift date, and I want to between, and I want it from January 1 to December 31 so I have my between, and you can use your calendar picker or just type it in. And here I have my date range, and I’m going to click continue. So I have my general ledger building fund, and I have my gift date begin between January 1 and December 31 so now I’m going to save this, and I’m going to save building fund, and I’ll just remind you here that is GL during 2024 gifts. Let’s add the word gifts for the building fund during 2024 because I have a calculated field, I want a reminder that this is for a calculated field, and I’m going to do that within my filter folder. I don’t want anybody changing this. If they go to change this using add more criteria or other editing it means it’s going to, indeed, to affect my calculated field, my custom calculation. I want a reminder, and that’s what this folder is about. And I am going to, in this particular case, I’m going to exclude pledges and link gifts, and I’m going to share across all users so my colleagues can see it and use it if they need to. Let’s click Done. Now I have that other area about what do I want to include? My pledge payments selected means it’s all money in the door. I’ll do another example that will include pledges or soft credits later. So I see some people have put some suggestions in there and the box in the chat, so I’m going to take advantage of that. But for this one, I want to just do include pledge payments, and I’m gonna leave all the boxes checked by default. I’m gonna leave that alone and click Save and Close. I now have my instructions ready to go. On this top line, I’m going to click my calculator. It says, run this calculation. I have chosen to run the following calculation. This looks good. I’m going to click Run calculation. It is successful. I’m going to X out of here. And I just want to take a quick diversion before going to my next example. And let’s see whether. Or George Judson has any Nope. It says total building fund. 2024 George has not given any money, not yet. So you know what I’m going to do, I’m going to actually run a quick report. Let’s do the top donor listing. Here we have the top donor listing, and I’m going to ask this to be run on the total building fund. Here it is, and let’s just look at the top 10, because I will make a quick look and show it to you on the on the giving and engagement profile section. So I’m just going to run this report now I’m and look at this. We have met so many donors here. I only asked for the top 10, so I’m just going to click on John Howard, and let’s look at that giving and engagement profile section. And here it is, total building fund of 2024 up. I see some people saying, very cool. This is really nice. And remember, gray background, it’s going to be there to look at. We know it’s controlled by the database. Pretty nice. All right, let’s do a couple of more custom calculations. Some more calculated fields. I’m back up here at settings, calculated fields. I see what I just did on the top, and people were indeed asking. I see that note. So thank you all. I see some people that want to know about, about and have that experience of including other than something, other than just money in the door. And I know that this is something that a lot of people tend to do, so I’m going to come down to the bottom of here, and I’m going to add a new calculation. And let’s do a lifetime amount, plus pledges, plus soft credits. All right, so all dates. Let’s do this. I’ll just as part of my descriptions, we’ll just say all dates, money, plus promises, plus self credits. And then self credits can come from different areas, so we’ll leave that as something that’s general. I’m going to click Find field. I remember I need a place to put this total. So again, you could be searching through in case to find something that you or somebody else created. I’m going to click add new field, knowing that I want to show you how to do this the setup. I’m going to come into field name, and because this is a lifetime total, you might want to use the word lifetime. I tend to use total, unless you’ve got a series of lifetime values in different other categories. Again, have a naming convention. Stick to it. It makes it your life and your colleagues much easier. Mine happens to be with total underscore, and I’m just going to do a little bit of abbreviating, just because I find that to be a little bit easier. And I need to also stick to the 20 character limit for my field names. So I’m going to use amount, and I’m going to use P for Pledge, and I’m going to use SC for soft credit. So if I ever go past 20 characters, it’s going to say something to me. It’s going to give it’s going to remind me that I can only use letters, numbers and underscores in my field names, and it has to be within 20 characters. So I’m fine, nothing. There was no message that would be displayed to the right. So now I’m going to put my user friendly prompt. So I’m going to say total for the amount, plus pledges, plus soft credits. But you know what my amount is really straight guess, right? So we’re going to maybe add something here, so we’ll say straight gifts. Oops, there we go. So we’ve got total amount, which is straight gifts, plus the pledges, plus soft credits. So when somebody has those donor advised funds or matching gifts, and you want to know the influence and the effect of wherever it’s coming from, but not double counting the Young Money within the same record. When we have Pam pledges and pledge payments for not including pledge payments. Again, this is still currency like the other one was. So I’m going to come in to leave, we’ll leave the display type as text box, and in this case, I’m going to still make the data type of currency, because this is a calculation. Again, I like want to keep all my calculations together. You may have different sections for different types of calculations, whatever works for you, for your particular screen, we want you to follow that you. Like before, I’m going to let it go to the bottom of the section, and not only will this be displayed, but it’s going to be read only, so it’s going to have that gray background on it, and I’m going to save field and exit. And yes, again, I’m going to be taking the data from the gift and pledge screens, and this time, literally, both of those screens. And yes, right now it’s going to be adding up. It’s going to still stay as some like I did last time. However, I now need to have a few fields here. I need to not just do that amount of gift and again, if you don’t remember what to type in, I’m going to come over here to click Find fields, and I’m going to come over here and type amount, and again, the gift amount, and select but now I need to add a plus sign, right. And now I need to find the total dollars of the promise, the pledge. If you’re not sure at any point in time, if you ever go back to the data entry screen, you’ll see your prompts. So when I click Find field, the searching here looks at the prompts as well as behind the scenes. So I’m just going to type total, knowing that the total field on the pledge screen is that total dollars promised. I’m going to click select it and click select here,

the other place, by the way, if you’re not familiar and you want to know what is called behind the scenes, you can always come into settings and screen designer to look at what it says on the screen prompt and what is called behind the scenes. And that’s also going to help you out. So if you wanted to type that in yourself, you can or even just to look to see what to search for. That’s also very good. This is lifetime. I do not need to have a date range, but what I do need to make sure is, if I’m going that I’m going to be including pledges and gifts, straight gifts, that is remember, as well as soft credits. Soft credits use the Amount field. So I don’t need to have a separate field for that. I don’t need a filter, so I’m going to leave this as no filter selected, but I’m going to click show more options, and now I’m going to say include pledges. So the pledges refers to, of course, the total field. And when I’m including pledges, I’m including straight gifts, which includes amount. And I’m also going to check the box that says soft credits. And as I said before, the soft credits uses the Amount field, so I am golden. I’m going to click Save and Close, and here we are. I have all my instructions set up, and I’m going to click my calculator. And again, it was successful. So I have all my money, all in the door. I’m going to just pull up John Howard again, and let’s see what this next calculation was set up for. So we see the total building fund was 1300 but look at this. The total everything that I typed here for the for this user friendly screen prompt is here, and now I have the total dollars, straight gifts, pledges and soft credits. So very nice. All right, I’m going to close that up, and let’s come back to this settings and calculated fields screen. And somebody had put in there that they would like to see what I mentioned earlier in one of those other demonstrations. They want to see how that the names can come together right a change from these first two. So that’s a great option. And I’m going to just take a moment and open up a knowledge base article that I’m going to send to everybody. And it is called Creating a full name calculated field. So I just put it into the chat. So take advantage of that one. And what I want to do is show you, so if you go back later, you can always open up the knowledge base, help and knowledge base and search the term full name, and you’re going to get the same set of instructions that I just put into the chat for you. But let’s take a look at creating this from scratch. So right, I’m on my Calculated Field screen as before, and I’m going to come down to adding a new calculation, and we’re going to do a calculation name, and I’m going to call this full name, but my description now is going to be a bit more of including, what is it that I want to have be part of this quote, unquote, full name. So I’m going to put in here what we set up the basis for and you could always, of course, use what parts of it that you would like if you don’t want to use all of it. So I’m going to say title plus first name plus middle name plus last name plus suffix some of you might not track. Of this. Some of you might say, well, if it’s filled in, I want to use it. Others might have other rules about what you want to include. So I’m going to use the basis that works in the framework of what our knowledge base has so so it’s going to support the materials that you have as references. You can always reach out to any of us, refer to somebody in support, or reach out to a trainer if you’re working with one of us, and we’ll help you customize if that’s what you need to do. So now it’s time to create or find that field to update. We need to store this full name. I’m going to click Find field, and if I were to search, I am looking for full name, and I don’t what do I have here? It looks like I have something that doesn’t quite fit here. It looks like it’s not finished because I don’t see the right parent here. And this, this one does not just say full name. So I’m going to come in here and I’m going to say, add new fields, because I don’t find what I’m looking for. So I’m going to call this full name, and I like the underscores to help me read it more easily and remember that the field names are all later converted to all capital letters, so I might type it in proper case or lower case, as I just did happen to do it, the field names will later be all capital letters, but my prompt is the old WYSIWYG what you see is what you get. So I’m going to just type in full name here in proper case. And this indeed is a plain, old fashioned character field, right, title, first, middle, last and suffix. So I’m going to leave it as text box and character. I am going to leave it here in that top section. I know that I’m going to put it in the top section, and I will come back later and put it in a quote, unquote, better position, better place. Okay, when I look at it and decide where is optimal for me, and I’m going to do that, and I’m going to leave this displayed, but you know what? I am not going to do a read only. The last two I did because the calculations is all about the money. This time I am going to not, whoops, I am not going to have this read only. The reason is that I only want this to be filled in when the full name is blank. So if I only have it being done when the full name is blank, it means that once it’s filled in, I, the DonorPerfect user, can come in and edit and customize. If somebody has a nickname or they don’t want that title in there that happens to be filled in. They might want it more informal. Okay, so that’s what I’m going to do, a little bit different than I did last time. So we’re going to, we have it as a character field, and it’s not going to be read only, and I’m going to say field and exit, all right, so again, I have my field name is in green. That’s where my data is going. But in this case, the data is not coming from gift pledge is coming from main bio. It’s all about the name fields. So now I’m going to say, well, now I look at this big white space. I have to say, update with what? So if you happen to open up that knowledge base article, I’m actually in step number two, and I’m going to do a copy, and I’m going to copy a line in there where it says creating the calculated field to update full name and what this is all about. And you can see here we have title, we’ve got first name, and on and on. It adds some really neat syntax that says, Is this blank? Is it null? If it’s not null, take the title and add a space, and then hey, is the first name field? Is that blank or null? If not, then use the first name and add a space, and it goes on and on again. Same thing with the middle name. Is it blank? That is null is not part of the last name, because last name is a required field and done or perfect, so the last name is always going to be there. And then it asks, Is the is? Says, is null again. And it’s all a matter of the suffix, but for the suffix, if it’s not null, right? If it does exist, in this case, it’s going to add a space before the suffix. In the other cases, it added a space after the value, like in title, first name and null name. So I’m going to do this. I have my text here, and I have my nice syntax, but what is my filter here? Somebody might say, I just want the computer to take over and let it do its thing. In that case, there’s no filter. But I suggested that maybe you want to have a an override for informal names, or if something has. Changed, I would that you want to have control over that kind of information, so I’m going to click Apply, and I’m going to add a new filter, and I’m going to come into main bio, where my field is stored. And now I’m going to need to find my full name, because I want to say, Only do this if the full name is null or blank, so my full name is not one of my favorite fields here, so I’m going to click all fields, and now I’m just going to scroll and I’m going to find my full name field. And here’s my full name exactly as I asked for it and created it just a few minutes ago, full underscore name, and I’m going to ask where that is not blank. Oh, sorry, whoops. Ask where my full name is blank. Is blank is when I want it to be calculated. So I’m going to click continue. Okay, right? The full name is null. In that circumstance, I want the database to fill it in for me.

So I’m going to say full name is blank or null, oops, and again, like before, I’m going to set this up for calculated fields as a reminder of where that’s stored, and I’m going to click Done and my show more options here. There’s nothing about those gifts and the pledges and the, you know, the the pledge payments and the pledges and the soft credits, because this is nothing to do with the gift and the pledge screen. So that’s not here. It’s not applicable. It’s not here. And yes, I want to calculate all of this when I’m saving a record, when I’m importing, when I’m posting a batch, and yes, is, of course, it’s going to be active like the others. I’m going to do is save and close. I have my instructions again on the top line, and I’m going to click my calculator, run calculation now, and here it is filled in. Let’s come in here. Let’s choose somebody else. And here My full name is. Look at this. It says Mr. George Jetson, because we’ve got the title filled in, we’ve got the first name filled in, we’ve got the last name filled in. Suffix is here, but it’s not filled in, and middle name is not displayed here. So it’s presumably, it’s not filled in, even though it’s it’s not displayed because it did not affect it’s not part of my full name. So everybody has all of these fields, the title, first, middle, last, and suffix it. Everything may or may not be displayed depending on your organization’s choices. So very nice and again, that full name can easily be used in reports. So if there’s any questions, please just let me know. Because I find this, I’m going to say, totally fascinating. I love to create something from nothing, and hold on to that link and remember that you could easily access that again from within help the knowledge base, and just type the term full name, and you’re going to get it. You’ll get that article back again. So let’s remind ourselves of ways that you can use these calculated fields. So selection filters, right? You want to make sure that you want to ask the database. Let’s go back to my building fund, one you could ask for that total building fund 2024. Is greater than zero. Okay, now I have those that have given to my building fund, right? If I want those who have not given in 2024 or never, right? If they have not given, I can ask for that total building fund equals zero, so I get that, as it says here, a precise list of records, right? What if I’m looking for that second calculation that included a lifetime with everything you know, straight gifts, pledges and soft credits. I might have $1 range that I’m looking for. Maybe it’s a low dollar, maybe it’s a high dollar. We’re looking to assign maybe some of your donors to different solicitors within your organization. That’s a great way to start. What about export templates? You can always add any of these three fields and anything custom that you do there on that settings Calculated Field screen, you can easily add any of these fields to your list of fields to being be included in that export and put that field in the order that you wanted, all the usual rules about setting up export templates, Mail Merge templates we saw early on where our example was used in a letter. So imagine that you. Had, you know, the total building fund for the year, and you were making a particular building fund letter out to your donors. You can take that field that we created, total underscore, BF, underscore 2024, and put it into the middle of your letter. I find that to be really nice, convenient. And then as long as it says what you needed, what you’re looking for, what you need it to say, then you’re spot on for presenting something and not having to do any manual work in order to get that. Let the database do the work for you within easy reports. Really no different than setting up a field in let’s say you’re an export, an export template. You’re choosing a field to use. In this case, you are putting a different kind of a format right. You’re still showcasing the the field that you have created, that custom calculation. If you have any entry screens, any of those grids when you come let’s say onto the gift screen. If you’ve got a gift calculation, you could display it right there. So lots of ways that you could do that search results as well. When you get to display certain calculations on my search main search results of the database, I could display my full name, if I wanted, I can display, actually, any of the three that I did today. I could display on the main search results. Two are based on the dollars and cents, but all three of those fields are main fields, so it literally can be part of my my main search results, which I find to be pretty darn nice, very convenient as well. So if anybody has any thoughts, I’d love to hang around and hear what your thoughts are, and even give you some pointers if you’d like. But first, I want to do a general recap, because I want to be respectful of everybody’s time that you have set aside today to learn about calculated fields. So remember that these calculated fields are formulas inside of DonorPerfect fields, we created three unique types of behind the scenes formulas make it yours. What rules do you need to follow to get what you and your colleagues need. We’ll help you do that. If there’s any questions. We have lots of different types of calculations. We use the sum today to add up the building fund we added we used some to add up the straight gifts, pledges and soft credits. We did. We use the word concatenate, we pulled together, and we use the plus sign for the character fields where we put in title, first, middle, last and suffix, but only when they are not blank, when they are not null. But I love all of these calculations because some of what I mentioned earlier. You know, let’s say, if you’re a membership type of an organization, I love to get that last membership gift date, okay, and put that in the middle of a letter, okay? You might, and you might be putting in the number, the count of gifts in in some other, in some other correspondence. Count is also very popular, in addition to some when we’re talking about volunteer time, think about the instance by instance, right. Counts become very popular for how many times somebody volunteered or had behind the scenes, some touch points where we’re counting the touch points, but not necessarily all the automated ones, but some of you may be asked to do that again, give yourself really good names. Whatever your naming conventions are, be consistent with them, so that you can see a list of fields alphabetically and choose what you’re looking for and have it be easy to find indeed, what you’re looking for. Remember also that once we set it up, we set up all of these instructions like clicked on that calculator icon that you saw. Once that was successful, the database is doing the rest for us automatically. We’re still going to do our data entry, we’re still going to, you know, do our data entry, do our saves, do our downloads, whatever it might be. And because we were successful at setting the Calculated Fields up, it’s now the automation is now taking over, which is super nice.

Also remember that we have many places that we can use all of these calculated fields. We get reminded that we can use them in selection filters, in various kinds of reports, whether they are fields to rank. Buys, such as that top donor report, or we could use it in export templates or easy reports, if you have easy reports as part of your DonorPerfect package. And this entry screens right those search results, we get to pick the columns that display in search results or any of these entry grids, depending on what kind of fields you have. And yes, we they’re fields, these calculated fields or fields in your database, but they are controlled based on your instructions. So yes, you get to pick and choose and display them where you would like on those entry screens, right? So all super nice. I find this to be very efficient way of working. So if you have any questions as you’re building your database and building some of your automations and efficiencies, please reach out to any of us. I’m happy to hang around and answer questions for you. I want to thank everybody for joining me today, and I am going to stay right here in the database. In case anybody has any questions, I am happy to hang around and answer them and make sure that you have some guidance along the way. So best of luck with all creating all that you need, and reach out to any of us when you need some help. So thank you again, everybody for joining me today. Look forward to seeing you at the next webinar. Take care. Everybody. Bye, bye. You.