ÃÛ¶¹ÊÓƵ

Transcript

Thank you. I’m very excited to be here. My name is Anthony Imgren, and I’m a Senior Professional Services Consultant here at ÃÛ¶¹ÊÓƵ Workfront. I work with our clients across various maturities to make sure that they get the most out of their Workfront instance. And today, I want to talk to you all about calculated fields. I have some specific examples that you can use, copy and paste right away, but I also want to talk about calculated fields. But I also want to talk to you a little bit about the philosophies behind them so you can use some of your own calculations to optimize them for better reporting in your instance. Today, we are going to talk about three key things. The first one is really about how do you get information from a calculation on your request onto your project. The second one is how can you build upon existing fields that you have to end up with easier and more efficient results. You end up with easier reporting. And then the third one is just a little tip and trick that I give quite a bit to clients on how to do complex if statements. All right? So let’s get started with our first one. So the specific example I’m giving today is how to get the queue topic onto your project. So we have three steps here. We’re going to talk about getting the calculation itself onto the request or the issue custom form. Then I’m going to talk to you about what we do on the project form so we can easily capture that data during conversion, and then we convert the issue. And I have an example of how that looks. So let’s talk about our calculation for our request form. My specific example here I picked because I want to talk about referencing. Sometimes on calculated fields, people feel you can’t reference other objects like you can in text mode. We’ve all done it where we’ve done the colon portfolio colon name on our reporting. But I did want to point out that in calculated fields, you can reference as well. It’s just instead of using the tech name, you kind of use the more formal name of the field. So I have three examples here for you, which we will drop in the chat so you can easily copy and paste them. I’ve picked the queue topic, the parent topic group, and the parent of the parent topic group for you because I know these are commonly ones where people want to capture the information and don’t know how to reference it. I also wanted to add a little trick into our presentation here where I’ve decided that I want to do a concatenate for my particular original request type. And so I’m taking the parent topic group name and then putting a dash and then putting in the topic group so that my result ends up being corporate website dash new. I’ve also put, if you noticed in the instructions over there, what I would have done for emails, digital ads, printed videos, and that’s just capturing the queue topic name. And I want to point out that you can have the same calculated field on different forms and have different calculations. So for my emails, all I need to know is that it’s an email request. But for my corporate website or my intranet, I need to know if it’s a new page or if it’s a revision to an existing one. So I have a different calculation there even though I’m using the same fields. So step two, let’s talk about the project. So we’ve now created our calculation on our issue, and now we want to make sure that we have a place for it on our project when we convert it. So what you’ll want to do is first go to the field library and grab the existing field. But before you put a calculation in it, you’re going to want to hit the apply button. And what this does is this brings this field onto the object type because we’re going to be referencing it in our calculation itself. Once you’ve hit applied and it goes back to let you edit, you will then just copy the name of the field into the calculation itself. So you just take everything that’s a name and you’re going to pop that into the calculation. The one exception is if you’re using the plus sign, dash, slash, or the asterisk, you’re going to want to put curly brackets around it just because sometimes that confuses work front into thinking you’re doing a math problem when you’re not. So if you put the curly brackets around it, then it knows that it is actually just text there. So we have our calculation on our request. We now have our calculation on our project. Now let’s talk about converting it. When you convert an issue to a project, any fields that are the same on the project custom form, the value from the issue will go into the project. So in that little edit screen or new screen, you will see under original request type, corporate website dash new. So that will automatically populate over no matter what the calculation is in the project. When you hit save, that’s when the calculation is going to go through and do its thing. So our calculation on the project is just, hey, whatever’s already in this field, keep in this field. And so that’s going to lock the corporate website dash new into that field. So even though the project doesn’t have two topics, and even though there isn’t a way to reference the original request through a calculation, that information now is captured on our project, and we can easily report on that going forward. If you have any questions about this, we do have a Q&A afterwards. So please feel free to pop those questions into the chat, and we can talk about that during the Q&A section. All right. My next one that I want to talk about, my example is SLA calculations, right? So we’re going to cover a few things. First, we’re going to cover how do you add time to an existing timestamp. Then we’re going to talk about how do you calculate timestamps based off certain items. And then an if statement that shows, did you even meet your SLA? So again, feel free to use other examples that you have of your calculations. This is just the main philosophy behind this specific example. So first, let’s talk about adding time. If you want to add full days to something, so in this case, the entry date, you can use add days or add weekdays. The main difference is, do you count weekends or not? So if I were to submit this request on Friday at noon, if I did add days and added two days, my time would be Sunday at noon. But if I did add weekdays and added two days, it would be Tuesday at noon. So for full days, you can use either one. It just depends if you want to count the weekends. At the moment, if you want to add hours or not full days, fractions of days, let’s say, to your time, unfortunately, you can only use add days at the moment. Add weekdays doesn’t work when you want to do that. So in my example here, the second part, I said, well, what if you only want to add three hours? Like you have a really fast SLA. You have three hours to do something with the request. What you would do is three divided by 24. And so I have my add days, I have my entry date, and then I have my 0.125. So that’s where that comes from, which is three divided by 24. And so if I were to submit at noon on Friday, my SLA deadline would be 3 p.m. on Friday. So that’s what we do when we are trying to add time to a specific field. The second part is how do we calculate our time step to see if we’ve met our SLA. If you’re using fields like actual start date or the actual completion date or any of the date fields that we have in Workfront, those are already time stamps, so you don’t need to worry about that. Those are things you can already reference. So you can move on straight to step three. But I do know that sometimes people will have unique things that they want to go up against for the SLA. Like maybe there’s a special status, for instance, under review, that doesn’t trigger the actual start date, but they want to capture the date and time that we moved into that status. Or maybe it’s, hey, we need to capture the time somebody is assigned to the request, a real human being versus a team. So I have both calculations here, which again will appear in the chat, so you can easily grab and paste those. The biggest thing, though, is for the status, you want to grab the status key. So you’ll just want to replace the U-R-V that I have in my code with whatever status key you are looking for. So let me show you what you do for that. So with these steps, these are the three, with those calculations, these are the three steps you’ll do. The first one is you’re going to create the calculated field with a name and a label, but don’t put in the calculation yet. Just have the name and the label. Then you’ll hit save and close and then reopen, or you can hit the apply. That should work, but I have run into instances, sometimes that doesn’t. So if you hit the reply, but then the calculation still doesn’t work, I recommend just hitting save and close and reopening. Then go back in and paste the calculation with the field name that you’re referencing. Again, the reason why we do this is because we’re referencing the field in its own calculation, right? So in the example I have here, we’re saying if the assigned to ID is blank, just keep whatever value is already in here. If it’s not blank, then look to see if this field is blank. And if this field is blank, populate it with the timestamp now that we have the date and time of now. But if there’s already a value in here, just leave it as it is. So because we’re self-referencing, that’s why you want to put it in first without the calculation. Save it and then put it in. All right, so now we have, what is our deadline? We have the time step that we want to reference to see if we’ve met it. So did we meet it? To compare the two fields, I just did a simple if statement here, where if the deadline is less than the user first assigned time, then no. I want the word no to populate, and then I want yes to populate if it is greater than that. I did add for this particular one, is blank. So if this field is blank, I put a not sure. And that’s just so that you don’t have the word no value appear or n a, because these are requests where they have been submitted, but a user hasn’t been assigned yet. So I don’t know if they met the SLA or not. And then I do have a little example of a report for you to show you what it looks like. So I have my four request queues, and I can see which ones, the percentage didn’t make the SLA, didn’t make the SLA, or have things that are pending. And sadly, my help queue, I have not met my SLA once for all of my issues here. But this will show you that information very easily. So my last one here is more of a tips and tricks kind of thing. I get quite a bit where people are like, what do I do if I have a lot of if statements that I want to do? I have like a lot of filters that I want to go through. A prime example is I have a bunch of countries listed, and I want the region to auto-populate. That’s a good example there, where you want to go through each country and what region they belong to. Well, the first thing you want to do is you want to write out all your if statements and potentially validate them if you have any questions on if they are set up correctly. Then starting from the bottom, we’re going to copy those if statements and move those into either the true or false value. And then once we have that, we’ll have our long complex if statement, and we can paste that into our field. So my example that I did here, I gave you the one about countries and regions. That’s like the one that I get the most. But I wanted to just change it up a little bit and show you one where I’m looking at two different fields. So this particular group wants to calculate their team priority based off of a point system. So first, my three criteria that I’m looking at is, first, is it a 911 request? If it is, then it automatically gets urgent no matter what. So that’s my first one. Then I have, if the priority calculation field is greater than 30, it’s going to be high. If it’s less than 10, it’s low. And if it’s between 10 and 30, then it’s medium. So I have my three if statements here. And like I mentioned, we’re going to start at the bottom now, and we’re going to replace the last one that we have with the false statement above. And that is going to give us here what we have in our first step. So the first line, that stays the same because we didn’t touch that. We only touched the second and the third one. And so that will look like this. So you can see where my if statement now has the, my false area has the new if statement. And then I’m going to take this whole bottom line here, and I’m going to bring that up to the false statement that is in the first line. And so for my second step, this shows you now what it would look like. If I had, for instance, 30 countries, I would just keep going 30 times up until I had just one long strain of if statements to do. And this is great, like I mentioned, to do this because instead of thinking offhand, okay, what are my different priorities or different filters here? Do I have enough closed parentheses at the end instead of counting to make sure I have everything? I already know I do because I had singular if statements, and I’m slowly combining them together, so I have all the parentheses that I need to make sure that those are being counted for. So this is what it looks like. I did grab in my screenshot the new calculation editor. It came out in the last release, or previous release. And so I wanted to showcase that to you because I find it very helpful. Not only does it give me more space, but it does color code some of the things as well, so it’s very easy to see what is a field versus what is a value. And if you notice, I’ve clicked my last parentheses, and you’ll see that it highlighted the first parentheses here. So I can now click through all my parentheses to make sure that they have a match. And if I’m missing one, then I know I either need to add one, which I know for some of you who do a lot with calculations or text mode know that missing parentheses is always the biggest thing that is the issue. And then to show you what that looks like in the report or in an object, here you can see in my test one, I have it’s not a 911 request. The priority calculation that the team had was 25, and so that makes my team priority a medium. So that pops up right there on the form. And if something were to change, then this calculation picked that up. All right? So I hope these three examples were very helpful to you. Like I said, I wanted to give you specific examples so you can easily just take them today and start moving forward. But I also want to talk a little bit about the philosophy behind them. So if you have calculations that are somewhat different, you can still at least use the philosophy behind them to help you out in your reporting needs. Well, that’s it for today. Thank you so much for joining me. I hope that you found this helpful. As I mentioned, I wanted to give you both specific examples so that you could easily copy and paste into your instance and start using. But I did want to talk a little bit about the philosophy that’s behind them. That way you can use your own calculations to optimize them. For instance, in our first one, you can use any calculation that’s on your issue form, but how do you now move that over onto a project? Or like in our second example there, we have multiple different calculations that you’ve been doing. How do you easily make them a yes, no at the end for ease of reporting? And then of course my tip and trippy at the end for if statements because we all love complex if statements. Again, thank you for joining me today. And I look forward to answering your questions in the Q&A afterwards. Thank you, Anthony. My brain is buzzing with ideas. I love the recommendation about crafting the complex if statements in the new calculation editor. I feel like the color coding is going to be a game changer. Yes, very helpful. We have a ton of questions from the audience. So if you still have questions, you guys can still type them in. But let’s jump right in. Our first question comes from Austin. And it is, can you explain the top use cases for Q topics? And a bit of a follow up is Thomas asked, also kind of can you share the use cases for Q topics, but is it for reporting or why do we use those? Yeah, so Q topics are used if you want to in the same request queue have either different forms, different approval paths, or different routing rules. So for instance, you might have where you have a request queue for, you know, print studio and there’s where is it something that is a mechanical or is it something that needs retouching? So that can be your Q topic. And then you can, based on what they select, have different routing rules or a different form that’s attached to just make the user experience easier for your users. So that’s the use case for Q topics. For reporting on them, sometimes it’s just to help reduce work. So like in my example I had in the slides there with my request, I had a corporate website, a new page for the corporate website. So instead of me having to now on my project say what type of project is this, oh, it’s a new page for the corporate website, I can just bring that over from the request. And so now I can do project reporting and I can have that information in that report. Awesome. I feel like all of those inputs become useful as you think about what can you do with the data. So I love thinking about all of those different inputs. It’s just one more piece that you can pull into other areas. A question from Amy is on portfolio. So is there a way to populate the portfolio field in a project based on the selection made from the dropdown in an issue? Okay. So the one thing to remember with calculated fields is they don’t replace other fields. So you can’t, okay, if somebody selected portfolio in the issue and you bring that to the project, it populates the actual real portfolio field. It can be a custom form, and I’ve seen some groups that do that where they call it like client slash brand or something, and then they just use that custom data as opposed to using real work front portfolios. But you can’t actually like put the project in that portfolio without something like Fusion or something that can help you out grabbing the ID number and moving it. So it helps you with custom form and custom fields, but doesn’t actually populate the native ones for you, unfortunately. Got it. But it sounds like Fusion is an option, as it often is. As it often is. Okay. Question about SLAs. So a question from Carrie that says, Anthony, just amazing science in terms of the SLAs and these calculations. Is most of this reliant on humans and what best practices do you recommend on implementing or the follow through? And as a subset of that, can you just remind us what SLA stands for? Yeah. So SLA is a service license agreement. It’s that between you and the other teams you work with, how fast you will either pick up an issue or complete a project from the time that it was requested, things like that. So the calculated field helps you because it kind of determines that deadline for you without human interaction. Like it says, hey, it was submitted at noon on Friday. We have a three hour SLA before somebody is assigned to it. Boom, your SLA deadline is 3 p.m. on Friday. So it kind of helps you so you don’t need a human to calculate that. Now, for the time stamps or the first assignment, that does rely on the human interaction. So the one thing I always recommend when people first start this is don’t use it so much for bad, like, policing. Use it more for like, hey, we’re noticing that we said three days, but it’s really being five days. Is there an issue? Do we need to extend our SLA or is there something, a report or something that we can do to help? So kind of that initial phase B, we’re just using it to monitor to make sure that our SLA is really effective or do we need to make some changes? That’s a good point. With SLA’s service level agreements, you want them to be more, you know, is it a carrot or a stick? It’s really the it’s just the knowledge. It’s the awareness of what’s happening. So you want to be mindful of not using those SLA’s, like you said, as a punishment of being able to say this is what we agreed upon and we’ll hold up our end of the bargain. You hold up your end of the bargain. And honestly, you can they’re mutually agreed upon. And so as long as they are agreed upon and if you find that over time that SLA isn’t working, you can always go back and say, hey, it might be time to readjust that. Maybe maybe this wasn’t the right time frame. So. Exactly. Or add an if statement to it and it depends on the type of request. Maybe that’s all they changes. There you go. Oh, somebody had commented in chat really early on and said, I do love a good if statement. Y’all are in the right place. You are our people. A question here from Victoria. Is there a way to account for business days, maybe holidays with the calculation instead of just add weekdays? Yes. So with add weekdays, you can assign a single schedule. So unfortunately, it only works for one schedule. But what you do is you grab the work front ID for that. And so you would have, you know, add weekdays, parentheses, entry date, comma, two days, comma. And then you put the ID number of the schedule you want and then close the parentheses. And that being the third condition there, it will look at that schedule. So if there’s a day in office closure or something, it will then skip that day as a work day. So it doesn’t look at one schedule. So you might, again, need a complex if statement if you have multiple schedules in there. But there’s a way to account for that if you’d like. Really helpful. There was actually a follow up question, I think, on SLAs that came in from Jose that said, What’s the best way to represent average turnaround time for issues, for example, a month or even a year turnaround time on a report? Well, so there’s a few, a few durations you can use. Some people like to go from when it was requested versus when it was first worked on. So that’s the entry date to the actual start date. Sometimes it’s how long do we work on it, which is the actual start to the actual completion. And then sometimes it’s the whole lifecycle. So then that’d be entry date to the actual completion date. So you actually have kind of three different things you can use. How long did it take somebody to start work on it? Or one of those custom timestamps we had. How long did we work on it? And then what was the total lifecycle? And so all those you just do the, you know, weekday diff or day diff calculations there to calculate those things if you’d like. I think we’re hearing that too. It almost could be an input into your SLAs to say, before we implement an SLA or these agreements, let’s just look at what the average time was. Let’s look at that report and say, this typically takes us a week, a month, whatever it might be. So if we’re going to take a first pass at SLAs, we’ll use that as our baseline and starts a tweak from there. Exactly. Yep. A question from Nicole. She says, I’m new to using calculated fields. Can you provide an example use case for calculations covered? So I’m interested to understand if and how these could be leveraged in my instance. Well, there’s a lot of different use cases you can do. A lot of it is to help out with either concatenating, putting information together, doing math is the biggest one that we have. My recommendation is, so in the chat, you will see that we have a Workfront 1 thread that will be for this presentation and the community site and kind of going there. And if anybody on the call wants to hear this event, wants to put what they use calculated fields for, because there’s a lot of different things you can use them for. It’s just ways of getting data that’s already, as long as the data is already on your object or a referencing object, just being able to collect that. Like again, concatenate tends to be the big one, as well as math equations or complex if statements. Those always come up. It’s a good call out though. I would actually say, those of you guys that are attending right now, if you already use calculated fields, drop into chat. I won’t call them all out, but just to give other folks an idea of how you’re using them, drop those ideas in. And Anthony mentioned he’s going to have a follow up thread on Workfront 1 that has more detail into some of the screenshots that he shared. You can certainly drop them in there. But I’m curious to see you guys drop into chat what your use cases are today. Question from Jamie. It says, how do you get a calculated field on multiple forms? I have a field. When I add to the project form or other issue forms, it clears out the calculation. Yes, so that’s one. It’s actually a nice feature to have, that you can have the same fields, have different formulas in it. Prime example I think I used in the presentation is, for my websites, my corporate or my internal websites, I wanted both the Q topic as well as the parent topic group. But for my emails and prints, I just need the Q topic name. It allows you to do that. A little trick I use is, I actually put in the description field of the calculated what the formula was. So, you know, oh, for projects, the calculation is this. But for issues, it’s this. Or for this type of issue, I use this one. That way anybody who uses the field can see what type of calculations I’ve been using with it to help them potentially with their formula that they need. Especially if you’re using the same formula multiple times. That might be one of my favorite tips. That description field can be valuable in a number of different ways. That’s actually a really good one. A question from Dave. This is coming back to the user error or kind of human error. How do you fix the Q topic or topic path on a request when the user selects the wrong one? Oh, I wish, I wish, I wish. So, innovation. First of all, if you don’t know about the Innovation Lab, that’s a great place to put in these wonderful ideas for product. What I have done in the past when I worked with a group that had this issue quite a bit is I actually had an override field. So, I would use a calculated field to capture the path or the Q topic name or whatever, however much information I wanted. And then I kind of had an override field where I could go in and be like, okay, it was really supposed to be this. And then it kind of overrides that in that. So, when I do my reporting, I will look at that calculated field and that calculated field would combine either the original path or my override field. But as of right now, once they pick that path, that’s, or at least to my knowledge, that is the path that is there until we have some tool with product in a future release to fix that. Almost a little bit of maybe possible feature requests. So, do think about the Innovation Lab. But also, if you’re seeing it happen a lot, it could be maybe a training issue or a governance issue where you say, I think people are getting confused what these might be. We might need to go back and do some training. Sometimes it’s with a certain team maybe that just came on and they’re newer to it. So, they don’t have that historical knowledge. So, think about it. Is it really a technical problem or could it be fixed with some training and governance? And you can also use the description field on Q topics as well. And with the new Workfront experience, I mean, it makes a very big like boom when they select it or hover over it. So, maybe we can, you can use something in there as well if you notice it quite a bit to say, only use this for these types of things to kind of help them out. A great tip. Description field again for the win. A question from Albert. Can you create a calculated field when converting a request into a project that will allow you to give the project a specific project number, for example, 001-23-2022? So, yeah. So, the calculated fields, you can do that to do job number as long as it’s information that’s already on either the request or the project. So, like a lot of groups will do like fiscal year. It will be a question on their form or something. And so, they’ll include that. They might include in the reference number that Workfront generates for the issue and then maybe like the client name or some other field there. The one thing it can’t do is do a counter for you, right? So, I’ve had some requests where it’s like, oh, I want this to be job number one. I want this to be job number two. So, it doesn’t do that. It’s not a counter. But it can take like the reference ID that’s on an issue since that’s a unique identifier and then populate that to give you the string that you would like for your job number. So, we have done that before with calculated fields and that’s great. Super helpful. And kind of a follow up to this, it’s a little bit different, but also still in that very technical vein. So, if we’re getting too in the weeds, just tell me. But Michelle asked, is there a way to create a calculated field that includes the month a request was submitted as two digits instead of one? For example, we want March to come in as 03 and not three. So, yes. So, you can do that. There’s the month one that gives you the month. And then, well, okay, so this would be a great one to go to the thread in Community to show you there is in the, when you switch then, there’s a way in the value format when you go to that field in a report that you can actually say how many spaces. And one of them can be not, you know, you have the two decimal places. You can actually go to the other way as well. So, I always want it to be two digits or three digit number. So, you can do that inside the report or the view that you’re looking at. You can capture that. But there is a way, I have it in my OneNote, that you can also do through a calculation and I can pop that in. So, yeah, if just, I think what you said, Michelle, just put that in the Community site thread and I can get you the real calculation you can do as well to kind of get that number for you. It’s a good reminder, we have a hive mind of the Community. Monique and I were talking about this too, as she said, I usually give myself about 20 minutes to troubleshoot and then I just go post on the Community because somebody will have encountered this before. So, I’m going to, we’ve got, I don’t actually know that we have time for any more questions. I have, well, I have one question about resources. Somebody, as we wrap up, there are a number of folks that say, I really want to learn more about calculated fields. Where do you recommend they go? A Community, again. I mean, there are a lot more learning paths and documentations now that are in guides in the resource area on one.workfriend.com. But pretty much everything I learned was, you know, off of somebody, that hive mind thing, somebody posted a comment like, oh, I never thought about that. Another tip that kind of works, not in every situation, is also if you Google Excel formulas, how do you do this in Excel? Almost all of the functions in Excel, not all of them, but almost all, are in Workfront. So, you can kind of cheat a little bit off of that, be like, oh, okay. That’s how they did it in Excel and kind of work your way around in Workfront that way. But like you said, 15, 20 minutes, if you can’t figure it out or Google it, put it in the Community site because somebody somewhere will have the answer or at least get you close enough to it. And it’s amazing. For sure. Anthony, we have come to the end of our time. I again, I could talk to you for a lot longer. You are a wizard and I appreciate you so much. Thank you for being here. No problem at all.