Troubleshoot Calculated Fields and Columns

Transcript

First and foremost, welcome to today’s workshop with customer support, really focused on common examples and troubleshooting for calculated fields and columns. So if this is your first session, welcome. This is going to be a little different than our traditional workshops where you’re not going to be able to come off camera or off mute. However, we do have the chat functionality available for you to connect with your peers, introduce yourself, share best practices tips, maybe some of your favourite calculated expressions, whatever that might be. And then if you do have a question for Quinn today, I’m going to have you put that in the Q&A and we’ll use that to launch our Q&A session at the end of today’s workshop. So another housekeeping item is I am going to record today’s session solely for the purpose of you guys getting a copy afterwards. So keep an eye out for a follow-up email later today with a copy of the recording, a full copy of the slide deck and any other resources that you guys may need. And then we will also be publishing this to Experience League in case you have any follow-up questions that you want to reach out and ask about. So with that, I am going to just jump right in and let’s get going. So Quinn, do you want to introduce yourself? Yeah, I’d love to. So kind of handy dandy little slide. So yeah, my name is Quinn Colson. I have been doing support for Workfront with Adobe for just a little bit over three years now. And I’ve pretty much covered a little bit of everything when it comes to Workfront. Every aspect of it, I’ve had a hand in it at some point or another, but I always come back to reporting and custom details really because I just love them. They make sense. They follow very specific rules and they’re just fun. About me personally, I’m a family man, married, two kids, and I’m a big nerd. So I love Dungeons& Dragons. I love video games. And I love waking up and cooking pies at, oh goodness, midnight or something, just because it sounds fun. So I like cooking. Yeah, that will pretty much tell you most of what you need to know about me immediately. I don’t know if there’s anything else you want me to talk about, Nicole, but all right. No, feel free to jump right in. Fantastic. Yep. So we’re going to have our fantastic Q&A experts here. We’ve got Nathan and Akiva joining us today. They’re amazing. So if you have any questions while we’re going through this, please put it in the chat. They’ll be able to help you out. And when we get to the end of all of this, if you have any other questions for me specifically, we’ll go ahead and go through that. All right. I’m going to go ahead and turn off my camera here so you can focus on the presentation and not my bright shining red face, I’m sure. So what we’re going to be doing today, we’re going to be going over a brief overview of calculated fields and columns, kind of what they are, functionality and really what they’re used for, when you would use one as opposed to the other. We’re going to take a little bit of time and go over some calculated expressions, the common usage of them, the ones that I see more often generally, and then some brief troubleshooting and finishing up with a live demo of kind of how to put together some of the information that we’re going to go over. For any of you that are a little bit more experienced already with calculated fields, a lot of this is going to be very familiar to you, but there are some fantastic details in here, some little nuances I think will be very helpful. So calculated fields and columns are used absolutely all the time in Workfront. I don’t know that I’ve ever been in an environment where I have not seen these being used at some point or another. If you haven’t used them before, you are going to want to or you’re going to end up using them eventually. So by the end of this presentation, hopefully you’ll have a better idea of what they’re used for, when you would want to use a calculated field as opposed to a calculated column, how to go through and build out some of the common calculations that I’ve seen in my experience, and then how to go through and fix them when they are not doing what you want them to do. So we’re going to go ahead and start, hopefully I don’t actually jump an extra slide, it’s going to be a little slow for me, there we go. So what are calculated fields and columns for? In a nutshell, if I were to take this and break it all down to a single kind of definition, they are used for manipulating information. Workfront has an absolute just metric ton of information. You can have dates, you can have currency, you can have text values of various kinds and name fields, you have numbers all up and down all the different projects and tasks and issues, there’s a lot of information. And so a lot of the time what you want to do is take that information and do something with it. Sometimes you need it in a different format or you need to know something specific about it. So calculated fields and columns are how you do this. They can let you manipulate that data in simple ways from mathematical operations like addition, subtraction, multiplication, or you can create more complicated cases. Things that let you take conditions to check if values are equal to each other, if they’re different, if they have certain values or within different ranges. They also let you take information and put it together in different combinations. So really the overall goal of a calculated field or column is just to give you different ways to get the information that you need in the way that you need it. And because of that, we want these to be as customizable as possible. So calculated fields, kind of where the difference between calculated fields and calculated columns. Calculated fields are going to be used specifically in custom forms. And the most important thing about them is that they are what is called static. When you create a calculated field on a custom form, that value, that field, is saved in memory. And that’s useful because it means that you can use it in reports of all different types. It means that you can use that information in other calculations if you want to kind of break things up and grab different pieces from different places. And it lets you have that data for the long term so that you can reference it wherever you might need it. The difference then is with calculated columns. So calculated columns are also called value expressions, and those two terms are really used interchangeably. Calculated columns as opposed to fields are going to be what is called dynamic. And so what that means is that every single time you load in a report or a view, that calculation is going to run and it’s going to give you an answer based on the calculation that you give it. However, that’s really the end of it. You have that answer, but you can’t use it anywhere else in the system. So you can’t move that into another column or compare it, and you can’t use that data in other reports in the same way. Really for a calculated column or value expression, that is meant to be used for more one-off situations where you need to get a particular piece of information, but it’s not necessarily so important that you need that to be available all the time or elsewhere in the system. So one-off situations and reports and views are generally where you’re going to see calculated columns. And we’ll go over a couple of the other differences between the two as we jump into some examples here in just a moment.

So going next here, common calculated expressions. With calculations in Workfront, there is absolutely no limit to the number of different ways that you can get to different information and get the results that you’re looking for. And so because of this, it’s kind of like math where if you want to get to number two, you can do that in a couple very, very simply, or you can find very complicated calculations to go all the way through and still end up with the same answer. So because of this, we want to go over some of the common calculated expressions here that are used in building out calculated fields. Like I mentioned previously, there’s a slight difference in how this is set up between a calculated field and a calculated column, but we’ll go over that more in just a moment. The ones that I have seen most commonly as I provide support for a lot of different environments are going to be these ones here at the bottom. It’s going to be the date div function, the concatenation function, and if statements using a couple of different conditions with equals and contains. I’m going to go ahead and jump over to my own environment here for just kind of a couple of examples of these, how they look, what they do, and we’ll go ahead and go from there. So I have put together a custom form here just called common calculated expressions examples as well as those fields that I have mentioned as I go through this here. So beginning with date diff, there’s a couple of things I want to point out here as we get into this. So date diff is a very simple function. In fact, most of these have a pretty simple format where it starts getting more complicated is when you want to combine these things into longer calculations. We’ll be doing that a little later, but I want to break down these a little more directly than when I usually see them used. So date diff is very simple. It takes in two date fields or date values that you type in, and it’s going to give you the number of days between those two dates and return it to you. It’s very simple, it’s very straightforward, but it is incredibly useful. So I’ve taken this custom form and I have attached it to a request or issue, in this case maybe, to kind of demonstrate this. You can see here my date diff is returning two and a half days. I have this taking the difference between my planned completion date and my planned start date on this. So if we look at my overview, you can see my planned start date is July 31st at 6 a.m., my planned completion date is August 2nd at 6 p.m. So that’s exactly two and a half days. I see this used most commonly when users want to track things like SLAs. They want to see the actual amount of time that has been worked on with an object. They want to see how long it’s supposed to take something to use in other calculations. There’s a few different uses I see it for, but this is very commonly used. And so one other very important thing to keep in mind when using calculated fields on this is going to be this format field right here. If you notice in my date diff I have this set as a number format. The reason for that is that date diff, as we can see, returns a number. But that matters because you can return it as any number of things, text, currency, number, date, or date time. But you need the format to match what you are planning to do with that field because this cannot be changed after you save a calculated field. You’ll notice that the format is actually grayed out currently so I can’t change this. I want this to be a number because if you want to do math with this value or use it in other calculations like that, it needs to return it in a number format. If I return it as a string and then I try to add that number to another one, it will throw an error and say you can’t add strings to numbers, it doesn’t work. So that’s just one thing to keep in mind when making any calculation. You need to keep in mind the end result that you want and how you’re going to use it. All right, so enough about that. We’re going to move on to the concatenation. So concatenation, again, very simple and straightforward. All it does is it takes in a number of strings or text and it puts it all together into a single line that shows all the information side by side. In this case, I have a simple example here that is pulling in the name of my calculated field, it’s putting in a dash between that, and then the reference number of my issue. So you can see if we go down here, it’s currently pulling in the name, calculate fields example, inserts a dash, and has the reference number of 14151, which we can find right up here. This I see used most commonly when there is a lot of information, usually little pieces of information that users want to see side by side and they kind of want to see it at a glance. So they can put all the different pieces of information together in this one field, pull that into a report, and then when they pull the report, they can just look at that to get kind of a brief idea of what is going on. Or it’s used often to create labels or other custom information. So again, we’ll get into some more involved usage of this because there can also be the use of other expressions within these concatenations. And I’ll show you an example in the live demo as well, which should be real fun. The last one, and this is the absolute most important and most used expression in all of Workfront. I have never come into an environment that has calculated fields or calculate columns that does not have if statements. And that’s because these are conditional statements. The format of it is, again, pretty straightforward. It’s made up of a condition, what we’re looking for, a true expression, what do we do if we find it, or a false expression, what do we do if we don’t find it or it’s not true. So in my case, I have a simple example here where I’m checking the name again of my issue to see if it is equal to the term example. Now one thing I want to draw your attention to is specifically the use of the equals symbol in here. There’s a very important difference between using the equals here and using the contains, which we’ll talk about in a moment. When you use an equals symbol in an if statement, it is going to be looking for an exact match for whatever you hand it here. So in my case, this is going to check if name is equal to exactly the word example. It’s not going to check for any partials, it’s not going to check for any pieces of information, it is going to look for that as a whole. So that is something to be very aware of when you use if statements like this. So in my case, that’s my condition. Does the name equal example? If it does, we’re going to return some text. Yes, this is called example. If it doesn’t equal that, we’re going to return no, this is not called example. One other thing to be aware of, we don’t have to return just text in these. You can return fields as values as well. So if I really wanted to, if this was true, I could return the value, the name field, rather than text that kind of tells me what the result was.

So you just be aware of that. There’s a lot of different ways we can go about this. So we take a look at my calculated field here. The if equals, you can see it says no, this is not called example because it’s not, it’s got a lot more. However, if we were to change this and change it to example, you can see this will automatically update itself to say yes, this is called example. You’ll also notice my concatenation here that pulled in the name also updated to just be example.

So it’s pretty cool what it can do with that. We’ll go ahead and reinsert that there and go down to the if contains statement on this. Contains is fantastic when you want to find pieces of information. So when you want to find partial matches or when you need to go through large batches of information that might have different naming conventions or just kind of small differences in data that you don’t want to necessarily have to individually account for every single possible version of something. So the contains is our condition statement and what it says is it looks for particular text within other text or another field. So in this case, I am looking for again the term example inside the name field. If it comes back as true, then I’m going to say yes, this contains example. Otherwise, I’m going to return no, this doesn’t contain example.

And we can see that same behavior right here that says yes, it contains example because it does. One other thing to be aware of with the contains statement is that it is case sensitive. So if I were to go into here with example and change this to a lowercase e, you notice that my calculation then says no, it doesn’t contain example because it is looking for that specific term that I have handed it in this format. Now in terms of how this works with calculated fields specifically, so more or less sorry, more or less calculated columns on this, if we step over to our columns, it’s going to have a slightly different format like I mentioned, but it’s going to be almost identical. It’s just the setup of it that is a little bit different. So I have a report here. This is going to be just pulling in that exact same calculated field or issue that I created.

And what we can do is really pull the exact same information that we have in here. Now, because these are calculated fields, if I really want to, I could just add in those fields. They exist right here in the report now because I’ve made them, which is what I was mentioning earlier about them being static and you can use them in any report. But let’s assume for a minute that we don’t have any information available and we want to create this as a one-off piece of information. Generally, I find it easiest to come into whatever report you’re doing and just grab the name field. And what we’re going to do is we’re going to modify this by going into text mode.

And then we’re going to remove some of these lines because we really don’t need most of this when we’re doing a custom calculated column. So we can remove this linked name and name key. We can also remove this query sort on this. These are really the only three lines we need. There’ll be one more that shows up, but it’ll add that automatically. So the display name is just going to be what the column is named. So we’re just going to call this Date Diff. And then value field, we’re actually going to change to a value expression. That’s where that kind of interchangeable nature of calculated columns and value expressions comes in.

Aside from this setup with the format at the beginning, the calculation is done the exact same way. So if I really wanted to, I could even come into my Date Diff here and just copy this exact same calculation and paste it here. What this will do is it will match up with anything that’s pulled into the report, grab these fields, and it’s going to go ahead and pull in that same result. So we can see we get two and a half days just like before. Once you have that format put together, if you want to put other fields in, you can really just copy and paste this and then make some slight adjustments for what you’re looking for. So I can change the name to Kincat, grab my same example here, and just copy paste this into my value expression. And it’ll do the exact same thing in this report that it did on the field. There it is. But again, I cannot use this anywhere else. So I don’t want to do this if this report is the only place I really care about seeing this information. All right, so that’s it for kind of the most common calculated expressions because I do see these used absolutely everywhere in Workfront, especially those if statements. Those will show up everywhere and sometimes they can get a little bit more complicated. So let’s go ahead and jump back to our slideshow here. And next we’re going to be covering, oh, I included a little slide here for anyone who has a copy of this afterwards that just kind of goes over these briefly. A little tip about them, what they do, format, all of that. This is all generally available in our documentation as well, along with about 40 other expressions that are available. So there is a lot of information I wish I could cover it all in this training, but these are going to be some of the most essential ones that you’ll see the most often. All right, so move on to troubleshooting. Because calculations can get really long and really complicated, there will be a time that you are going to have it not work correctly. It’s really not so much a matter of if, it’s a matter of when. Calculations I showed so far are very, very simple, just a one or two fields. I’ve seen calculations that have hundreds of lines and can just be an absolute beast to try and go through and figure out what’s going on. So because calculations can be so varied in the way that they pull information and the information that they’re working with, I figured it would be a little bit more useful to provide a few general examples of troubleshooting steps that will work for any calculation, regardless of what it’s doing, of how it’s set up. And those are going to be counting parentheses and checking the curly braces on fields, which has to do with the formatting, breaking the calculation into smaller pieces, and then moving value expressions into calculated fields. So we’re going to go ahead and step into my environment again. And I’m just going to show you a couple examples of this.

So I’m going to do this from a report, in this case, and value expressions. And the reason I show it here is these same rules do apply to calculated fields, but calculated fields generally have a lot more built-in troubleshooting as they go. And what I mean by that is there is a lot of information that’s displayed as you put things together. It shows text in red, it highlights fields in green. The parentheses are kind of highlighted and shown to what matches with what. So because this is all kind of self-contained in here and does a lot of that for you very nicely, it seemed like it’d be more useful to do it here because value expressions are a little different in that they don’t show you what went wrong. When it doesn’t work, it just doesn’t show an answer.

So I have the two examples here for a parentheses check and breaking it down further.

The parentheses check here is going to be just this exact same contains example. That we had here in this issue. So in this case, it’s not returning anything, meaning that something is wrong with my value expression. So counting the parentheses with calculations like this is a very simple way to see if you have your formatting correct. Generally, I like to start just from left to right and count up for every open parentheses I have and count down for every closed parentheses I have. If we end at zero at the end, we know we have them matched up, or at least we have the right number of them. So if we did that here, we have one, then two, tailing on, we go back down to one, go through it, we go to zero, and then we go to negative one. So what we’re seeing here is we have an extra parentheses at the end, which is one of the most common things I see happen is just an extra parentheses inserted by mistake on this. So if we go ahead and remove that, then these should now be the right amount of parentheses. And then we only have one field in here, which is name. So we can check we have curly braces or curly brackets around the name field. So that is correct. Go ahead and run this now. We can see it now correctly pulls that calculation in, and it’s working. Now for this one, I want to explain a little bit of kind of what this is supposed to do. So I have a multi-select field on this issue. What this does is it has a whole bunch of some different departments and 10 recipients here that I can select and change as I go through. And this is supposed to take whatever I have selected here, and it is supposed to return a series of four or three letter codes that show what my intended recipients are in a single string. But it’s obviously not working. So they can get a little bit more involved and a little bit longer. If we want to try and count the parentheses in this, it would be an absolute nightmare because there’s just too many. You have to count up, down. There’s no guarantee you’d keep your count correctly or accidentally miss one. It’s just a bit more of a beast. So this is an example I actually had on a case recently that I’ve gone through and modified a little bit to protect their information. But generally, when you want to do something this long, you want to break it down into a more readable format. Usually how I would do this is copy the whole expression and put it into either a Notepad or paste it down below further into this text mode. Then what we’re going to do is we’re going to separate it from the other lines and start breaking this down. Now a concatenation, if you recall, is going to have a pretty straightforward format where it takes in a number of fields or a number of strings that are separated by commas. So because we know that, we can go ahead and move this line down and then we can move one parentheses out, knowing that that is for the concatenation. Because really, we want to check all this stuff inside here. So because we know these are separated by commas on concatenation, we’re going to just look for the end of each of these if statements where we have a comma and we’re going to break it up by that. So I’m going to do a new line here after each comma where an if statement begins and just get all of my if statements in a nice line for me so we can go through each of them individually. So you can see this is already looking a lot easier to read as opposed to kind of this chunk together stuff. Now an important thing, and the reason you want to save the original on this, is because value expressions obviously do not like having all these return characters and new lines. So this won’t run even when we fix it. So what we do is as we fix the problems here, we’re going to fix them on the original as well. So let’s go ahead and go through this. Now that we have our individual lines, we can count parentheses, we can check curly braces, and we only need to do it a line at a time. So for the first one we have one, two, curly brackets look good, one, zero. That one’s perfect. Same with here, one, two, curly brackets are fine, one, zero, and just continuing on one, two, in turn, ah right here is our problem, or at least one of them that we can find. We have an equal sign that has been left behind in this calculation. That’s going to cause problems there. In this case that happened because this originally was not a if contains statement. This was originally set up as an equals statement and then this was changed but that accidentally got left behind.

So let’s go and continue on here again, one, two, brackets, one, two, close there, and we’ll just go through this. This one is looking fine, one, two, curly braces look good, two, one on parentheses, one, two for the open, curly braces look good, two, one, one, two for open, curly braces, two, one, ah, and negative one. We have another extra parentheses at the end of this calculation. That can happen very commonly especially with concatenation that is storing if statements like this. It’s hard when you have a big line like this to keep everything straight at times. So we’re going to go ahead and remove that and those two changes we’re going to make down here. We’re going to remove the equal sign and we know we have one extra parentheses.

Everything else in this looks just fine. So now that we know that that’s looking pretty good, what we can do is just copy this back out and paste it in with those updates that we have made. Go ahead and save this and we can see that now this is correctly checking those values and returning codes. So if we update this and make some changes, it’s rewriting, it’s reading correctly, and now this looks just fine. Now the last step on this one, the last of those three troubleshooting steps I mentioned, is moving the value expressions into a calculated field. For larger expressions like this, when you were making them in a report, it can get out of hand. You know when you have something this long, you might go through all these troubleshooting steps and count things as best as you can, break into smaller pieces, and you still just can’t figure out why it’s not working. In cases like that, what you can do is actually just copy this, come into a calculated field, you can use an existing one or a new one, as soon as this loads here for me, it happens to support too.

But if you create a new calculated field, I’m just going to call this temp for a moment, you can just paste the calculation that you wrote in there and it lets you break things down a little bit easier, since more easy, since it will highlight the different things, the expressions will be in blue, the parentheses, it’ll show you the match of this, which ones go to which, so you can see exactly what is being contained in different statements, it’ll show you the fields, and this can generally be very helpful in determining exactly what’s going on, especially because with things like parentheses or anything like that, if it’s not set up right, it’s going to tell you that this is invalid, and in some cases it’ll even underline the issue that it’s complaining about, saying, hey, you have a parentheses wrong here, and remove that, and then it’ll tell you, you did it, good job. So the reason I didn’t want to mention this one first, though, is because this can be very tedious, it’s not very time effective or convenient a lot of the times to grab every single individual calculation here and moving it into a calculated field just to troubleshoot it. So if you can get these fixed in here, it’ll generally save you time and effort. So that is it for kind of the immediate troubleshooting steps that I had in mind in this. Again, there’s other methods you can use, but these are usually the most effective that I’ve found for finding issues and calculations. So moving forward from here, I want to go ahead and step into our live demo of making use of these different fields and kind of building out something that’s just a little bit more complicated. So I’ve put together another custom form here called project details, and I have a few custom fields at the moment. I’ve got this calculated field, custom project label, I have a drop down field that has a couple of departments in it, finance, marketing, and sales, and then I have a checkbox that says, is this for internal use, and you can select yes.

What I’m going to show you how to do here is how to put together a custom project label using the project reference number, checking the drop down values for a certain, you know, three or four letter code, checking if this box has been selected to give a result, and then checking the date difference to see if this is a rush job or a long job. So we’ll go ahead and jump right into it. I’ve got this form already on a project that’s set up, and we’ll kind of go back and forth as we make each part to show how it’s displaying. So when creating a calculation in this, you can use maximize or you can use just the sidebar here. We’re going to begin with a concatenation because we have all the information we want to put together in a single string. Now if you remember, a concatenation is made up of a series of strings or fields that are put together.

If you are building out this calculation, the first thing we’re going to put in is a reference number. When you start typing in a field, you’ll get this autocomplete. You can select it from that autocomplete and it’ll show it up there. Now if you are familiar with creating calculations, you can just type it in and get it right where you want it to be. If you are not familiar with this, that’s okay because what you can do as well is come down here to the bottom of this, and there’s actually a little field library. If you know the name of your field, you can start typing it in and it’ll autocomplete it for you. If you click it, it’ll automatically insert it wherever your cursor was at. You can also go through the different fields that are available. My form is a project form here, which is why it shows specifically these project drop-downs. There are other ways to pull other fields throughout the system. That can get very complicated though, and that usually involves use of our API Explorer, which pretty much just shows the connections between different things in Workfront. I’m not going to jump into that for this because that is a whole other can of worms. So if you would like a more involved training on going through the API Explorer, we can probably arrange that in the future. So we have our reference number now. That’s just like we did previously. If we come in here to our project and do a recalculate, that will tell this calculation to check again what we’ve done. So we have our reference number. Step one done. Quick and easy. Now, a cool thing about the calculated fields here is that we can actually use new line characters. Well, the value expressions don’t like those. Calculations really don’t care in the calculated fields. So we can go ahead and put this reference number in there and do a new line and continue our work as we go to keep it well organized. So I’m going to put in a comma because we’re going for our next value. We’re going to go to a new line and we’re going to put in our next statement. So right here, we are now going to check our dropdown values for a letter code to insert. So we’re going to do an if statement, which we can insert, and then we need to check what this field is. So we’re going to say if department is equal to, and in this case, I’m going to use equal because we’re looking for an exact value. I’m going to copy my values because I don’t like to have any type of those, anything that will cause a slight error in this. We’re going to see if department is equal to finance. If it’s equal to finance, then I want to return fin.

And that’s all I’m going to do for the first part of that. Now this is where we’re going to get a little bit more involved in what’s called a nested if statement. We can have, if you recall, the if statement is condition, a true statement, and a false statement. We can have this false statement be another if statement, which is what we’re going to do here. So I’m going to put a comma here, and I’m just going to insert another if statement and check for the next value in my department, marketing here. So I’m going to say if department is equal to finance, return fin. Otherwise, if department is equal to, in quotes, marketing, then we’re just going to return mar. And we can do the same thing for the last one. If department is equal to, put into our parentheses, or sorry, our quotes, and we’ll grab sales here.

And insert it. It’s equal to sales, then we’ll just return sale. Now, if statements do not need to have a false statement. If you don’t have one, it’ll just stop. It won’t return anything else. So let’s go ahead and take a quick look at how this is looking.

We go ahead and run our calculation again. It doesn’t show anything yet because I need to add department. That’s on me. I add marketing here. It’ll update as soon as it saves.

You can see it’s inserted mar. Now this is a little bit blocky and stuck together. So what we’re going to go ahead and do is insert a separator here as well. So we’re going to go ahead, do our new line, and we’re just going to insert again a dash between our reference number and this. So currently we have our string one is equal to our reference number. Our string two is the separator. String three is this if statement, which you can see is all contained together in one. So moving on to our next portion, we’re going to do a comma. I’m going to insert another separator. And then we’re going to move on to checking is this for internal use if this has been selected. So this is going to be another if statement. Like I mentioned, these are what show up the absolute most in all of Workfront. We’re just going to check the is this for internal use. My if statement went away. There we go.

If this is equal to yes, we can do it this way or we can make use of an is blank statement, which is actually one of my favorites as well. Is blank is literally just checking if the field is blank. So we can put a field in there. Is this for internal use? And if it is blank, it will return true. So if this has not been selected, if it’s blank, we’ll return external. If it is not blank, so our false statement will return internal. Pretty straightforward on that. That’s all we need to do for this particular field. So if we go ahead and apply this, come back to our calculation here and refresh this again, you can see it now returns external. And if we select this checkbox saying yes, this is an internal job, it updates it to internal.

All right, we’re almost done. The last one we’re going to do, I’m going to insert another separator here. And then we’re going to do one final if statement where we’re going to check a date diff on this. So we’re going to see if the difference between our planned completion date and our planned start date. We’re going to see if that difference is greater than 10, because remember date diff returns a number value. So I want to know is it 10 days or more? If it’s more than 10 days, I’m going to return long. If it is shorter than 10 days, I want to return rush saying this is a rush job, we need to have it done quickly.

All right, so we’ll go ahead and apply this. Do one more recalculation here. You can see it shows rush, which makes sense because my planned start date and planned completion date on this are the exact same time. So yes, this is absolutely less than 10 days.

You can see we’ve gone ahead and we’ve put together a lot of different points of information. We have a concatenation holding just a plain field, holding an if statement that’s running other if statements. We have an if statement checking for empty values. We have an if statement checking for the difference between dates. So you can use this in a lot of different ways and all of these values can be really put together. And so it can get complicated, but you can do fantastic and incredibly powerful things with this. So going back to our slideshow here one final time, that is all that I have for you in terms of the live demo and everything. I’m going to go ahead, I have some helpful links that I’ve posted at the end of our slideshow here that go over the list of all the expressions, the general use of text mode, which is going to be calculated columns, date differences, how to do nested if statements, and a couple of other fun things if you want to experiment in your own time. So yeah, anybody have any questions for us? Let’s take a look.

What an awesome presentation. I’m going to open the Q&A here. I feel like almost all of these have been answered by Kiva and Nathan, but I did see one that came across the chat very recently, and although Kiva, I don’t know if you responded, what’s the difference between if, in, and just if? Oh great question. So an if, in statement is specifically going to, well it’s actually kind of self-explanatory on that. If, in is going to be looking for if there is a particular value or a list of values inside an individual field. So let me go ahead and share this again. I can show you here. So an if, in statement, we’ll actually put this on a new calculated field, go with temp again. If, in, if you actually mouse over this, it’s going to show some fun stuff. But basically you hand it a series. Now it’s going to be mad at me. You know what? We’re going to pull up that link really quick here because I want to show you exactly what this does.

You get to see my list of reporting goodies that I like to use here.

We’ll try not to get too far into the weeds on this, but okay. So as I mentioned, there’s a lot of different expressions you can do a lot of things with. But if, in, in particular is really neat because you can give it multiple values that you want to take a look for. The first one you give it is the field or the text that you’re wanting to search through. And then you give it a list of values separated by commas that you are looking for in there. So rather than having to do nested if statements like I showed to check out the values, you could use this if you’re wanting to return one of two answers. So in this case, check for the owner’s name on the object. If it’s any of these people, these two people, Jennifer or Rick, we can return marketing team. Otherwise it’s other team. So it’s when you have multiple values you need to compare for one or another on the output. Thanks Quinn. Let me open the Q&A here.

A question from Drew. Is there documentation on which functions support and or operations? Sometimes they work and sometimes they don’t, but haven’t found documentation. That might be something we can probably just share some links in to the Q&A pod. But I don’t know if Quinn, you have any other ideas or tips or recommendations for which functions support and or I’m trying to write this or say this out loud. Like the and, the ampersand, or the or, like the double parallel lines operators. Gotcha. So the question is that some of those are removing or not working? It sounds like functions support that. Is that true? Gotcha. In a lot of cases, yes. It’s most commonly in if statements. So you see that used where you’re checking if a value is equal to, you know, one term or another. Generally with that you want to have the entire expression where you say if name is equal to example and then you have your and or your or statement there, your two lines or your ampersand and then you have the if statement again. You don’t always get good results if you try to say if name is equal to Bob and Phil or Bob or Phil in that case. So having the actual statements in their entirety when you were doing an and or an or will usually give you better results in that sort of calculation. Got it. Okay, great. Another question that came through was how do you calculate fields refresh on a regular basis? So there’s two ways you can go about it. Okay, so there’s a few things that are going to trigger the refresh of custom data when it’s on an object. So anytime an update is made to the scope of an object, it’s going to rerun the calculations in this. So anytime I update a field, anytime I change the name, if I change the status, this is going to run again. Now the two other ways you can go about it because there isn’t really an immediate, you know, rerun this on a regular schedule. You can do the recalculate expressions to do it manually or and most useful is if you want to recalculate it across the system. In every calculated field there is this apply to existing calculations option. When you select this and then click apply, what it does is it’s going to go ahead and find, it’s getting mad at me because I have that if in, when you save this it’s going to actually go through the system and find this custom form everywhere that is used and it’s going to rerun or refresh this calculation. So if you ever make an update and you say I need this update across the whole system for everything that’s using this, that’s what you do. You check this, click apply, it’s going to clear itself because it’s a one-time trigger and then it’ll recalculate them all. But now there isn’t an immediate, you know, refresh this every 15 minutes or every 30 minutes sort of thing.

Great and just so everyone is aware, like if you are changing any of your calculated expressions and then you do recalculate, it might override. So just keep that in mind. If you’re like hey I don’t want to recalculate any of these because maybe you have sort of an old expression put in there, just know that that can happen. Yes it will override old ones. So if you need historic data you want to probably look at making a new calculated field rather than updating the old one.

All right let me try and run through and see if there’s any other questions.

Let’s see, I do see a question here from Peter. Will the calculated columns remain present when the data is exported in either excel or pdf format? The answer to that is yes. Yes when you create a calculated column it is going to return those results because that calculation does run as part of the report. So if I were to export this right now I would get these values in here.

So that does happen.

All right let’s keep going down here.

Thank you Patrick.

I always enjoy working with things. I know Patrick well, we’ve worked on a few things together. It’s always a pleasure. All right we have a few more minutes. If you have any other questions big or small I’m happy to do my best to answer them for you.

Yeah if not we can always wrap a bit early but I will also just say that if you got, you know, we talked about a lot about, you know, different troubleshooting steps and examples of common calculated expressions in fields. So if you guys are running into issues, you know, be sure to submit a support ticket. You know these guys are here to help and offer any guidance and suggestions to get it the way it’s working as expected. So yeah like I said if you’re running into any issues feel free to just submit a support ticket. Hold on I just got tagged in something. Maybe that’s a question. Thank you Madeline. Always fun working with you too.

Okay we have come across issues with date diff or weekday diff where the date fields includes a time which is close to 12 a.m midnight.

Is it any reasons this could happen? Is it linked with user schedules? Okay yeah so with I think what you’re talking about is where the value does not seem to come up correctly with the date diff when you are around the midnight or kind of the change of the day as it were and I have run into that a few times. Yes it can be connected to time zone. That is very accurate actually. So there’s a big difference between what date diff does in here which date diff gives particularly the number of days down to the decimal point between two date fields. Weekday diff this gives business days and ignores week weekends. Now the important thing about weekday diff is it only considers full days. So if you don’t have a full day in the dates or the including the times that are put in here it won’t come back with that whole day. So you might have a three day gap for your dates but the time might not quite be a full three days in which case is return two days. Now what you will see happen for users who are running this calculation on their side if you’re in a different time zone especially I see this most commonly at the end of the month usually with tasks and issues that are put with the end time as midnight of you know like tonight July 31st and then you have time zones that will put it an hour past that or more and things like that where it will it’ll say hey there’s actually one more than there is or one less than there is. In cases like that you need to account for those time zones being a factor because the um yeah I mean the actual amount of time between them is going to be the same in terms of date diff just because the start date will also move forward by that much but yeah you could see some oddities in that with it moving those dates forward. So if you do run into a weird one like that open up a support ticket so we can look at it it’s a pretty unique basis and pretty specific to each situation in which it happens.

Got it okay another question just came in I think this one is probably relevant to almost everyone. Are calculated uh custom fields specific to the form or the field I notice I have to paste in the expression on every form the field is on? Fantastic question and the answer is yes. So the reason for that is you can have a lot of different object types right you can have project forms, task forms, issues forms and you might need to pull data even if it’s the same piece of data you might need to pull it in different ways depending on what object it is on because of that a calculation is going to be unique to the custom form that it’s on. So if I were going to my original form here I’ve got my custom project label and I try to insert that from the field library you’ll notice the calculation is empty but the instructions are not and this is because this form has no way of knowing if this calculation is what I need for this field on this form.

So if you do need to use the exact same one all you do is you copy this and put it into the instructions because then as soon as you do attach it it’s actually going to be sitting here the instructions you can just copy and paste it into the new one.

Yeah and I can’t just reiterate paste that in the description very helpful.

All right another question that came in and I feel like I’ve also been on this side is you know how do you determine and maybe it’s just trying maybe it’s just taking two seconds to think about it but you know custom fields whenever you are creating them you know if text fields or calculated fields you have to select another a number a date a text like how do you know which one to select? Usually the biggest factor in that is what do you want to get out of the form or the field in this. So if you are going to do math with it or you’re going to you know compare it to other information generally you know like a date diff or if you’re wanting to do any sort of mathematical functions I would use number. Essentially if it has numbers and it is not a date use number format if it’s a date use a date format and if you just need to see the result and you aren’t going to do anything with it afterward and you know that you can use text. Text will work for pretty much everything. The most important point where that becomes a factor is going to be in reporting. So specifically in a lot of reports you’re going to want to see things like the total value of different groupings. All right if you have it as a number field you get this nice little option to summarize a column meaning you could take the average of the numbers you can take the sum of the numbers things like that. If you have it as a text field however this will never show up because it’s not going to take the average of text even if the text is technically numbers because text creates the option to return words and letters as a answer on that calculation so we can’t run those. So if you are going to need to see any totals of multiple values grouped up in a report or anything like that make sure that you are using a number format.

I feel like we’ve all been there before where you create a field you’re like oh I’ve been there and then you have to recreate.

That’s correct.

All right I see another one can I create a reference field on a calculated field of another custom form? Yes so calculated fields they can reference all over the place. So my common calculated expressions examples right I don’t have if I take this custom project label out and insert a calculated field I can absolutely reference that field even if it exists on another custom form.

Let me go find it here oh that’s because I’m only in the issue type right now. So it gets a little bit more varied here but this is kind of what I was mentioning with the API Explorer and everything you can step from one type of object to another to reference those fields. So this custom project table currently is only living on the project object so I can step over into the project I’m on an issue every issue lives on a project so I can step into a project and then I can absolutely reference this custom project label and because it’s a custom field I need to have DE in front of it but there is no problem with me pulling that value from the project level into my issue and using it for other things. It’s available across the whole system I just need to know the right path to get to it.

All right last question before we wrap um calculated fields cannot be used in report I sort of a workaround to get them as a prompt.

So you can check for some results I believe on report prompts but now you got me wanting to double check here so let’s see here let’s actually go into a project report really quick I know we’re going to be right at time this will be the last thing that we’ll take a quick look at here on this so yeah depending on the format of the calculated field you absolutely should be able to use them in report prompts so if I come to my project report here and do a report prompt I should be able to take a look at my custom project label right here that I’ve done and that is a text field in this case if I go ahead and run this I will get the prompt saying my custom project label does it is it blank does it contain something does it not contain anything if you have a number format it should also give you the prompts for greater than less than equal to greater than or equal and if it’s a date field you should see between greater than you know month any sort of thing so you should entirely be able to use calculated fields in prompts it’s still just data that you’re comparing. All right I think that really just hits the point home of if you’re running into an issue and you’re like I don’t think this is working or maybe I have a question on how this functionality you know works inside of Workfront submit a ticket to support um someone had asked if it’s a free resource it sure is um you can submit a case right through experience league or give them a call um but I know we’re one minute over so I just want to be conscious of everyone’s time here first and foremost thank you to Quinn um for the fantastic presentation the explanations everything you did today it was it was really wonderful and Kiva you guys were you know rock stars in the chat here um answering questions as they came in so um Quinn if you have any final thoughts otherwise we’ll just wrap up and um keep an eye out for a follow-up email later today with slide decks and recordings and resources.

Awesome yeah it was a pleasure thank you everyone for coming. All right thanks guys.