Expand my Community achievements bar.

Calculated Date Field Referencing 2 Custom Form Dropdowns

Avatar

Level 2
Hi All- I'm still a novice at text mode, but trying to solve for a current need in our organization...I'd like to be able to create a custom field that displays when we need to kick off creative development for a given type of project based on standard lead times. We're trying to get this information earlier in the process than the actual project timeline is created, so our project timeline templates don't really help here. I'd like to reference the following custom form fields that we already have on our projects: Media Type - this is a single-select dropdown where users can specify the type of project (TV, Radio, Direct Mail, etc.) I n-Market Start Date - date field collected as part of initial project submission Ideally, the expression would be something like "If Media Type = TV, In-Market Start Date minus 90 days" (using 90 days as an example lead time for TV creative development)...we would include variations for the different media types, but I think if I could get the initial expression right I could modify for all other permutations. Is this even possible? If so, any recommendations on the text mode I'd use to create this field? Thanks in advance! Harry Thompson Marketing Admin Lowe's Companies, Inc.
Topics

Topics help categorize Community content and increase your ability to discover relevant content.

5 Replies

Avatar

Level 10
Classic, excellent question, Harry; and dead center of one of my favorite areas of interest! In short: totally doable, with several approaches and options. I have many thoughts...but have to scoot to choir. I'll elaborare on the latter later, and am happy to address the former too (although there are many others well qualified to assist who might beat me to it). Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

Avatar

Level 2
That's encouraging, Doug...I'd love to hear more about your thoughts, and feel free to throw in a recap of the choir practice as well. :) * Also to clarify my post subject, only one of the custom form fields is a dropdown...unfortunately I can only seem to edit the post content and not the subject. Harry Thompson Lowe's Companies, Inc.

Avatar

Level 10
Thanks for your patience, Harry -- choir practice was good (and called for). Alright! Back to your calculated date question, and my as-promised Many Thoughts: The "https://community.workfront.com/discussions/community-home/digestviewer/viewthread?MessageKey=2098c762-160f-4a56-a185-d43ab59cecf7&CommunityKey=ccf3381b-a473-4ef2-9f81-4dd3244cf522&tab=digestviewer&memberkey=5n1H2oNYJSCtYvFfR1xT4UEFt7nqrC17yB0zotcNSvrbyKEPM8zot9rvNuJwAvOy23KP3d2HROo%3D#bm2098c762-160f-4a56-a185-d43ab59cecf7">Tip Jar Topic: "Guess" or "Know" the Right Project Start Date? thread discusses a similar topic, whereby -- given a known, desired end date -- a formula can be used to reverse engineer the optimal (e.g. giddy up) start date by deducting a known interval of time. Given your use of "In Market Date", you could adapt the same concept but make the formula a Little Smarter so it takes into account the appropriate lead time depending on the Media Type selected. For example, you could create a Custom Date Parameter called Ideal Start Date, with a formula such as this: IF(Media Type = 'TV',"https://support.workfront.com/hc/en-us/articles/217196767-Understanding-Calculated-Data-Expressions">ADDDAYS (In Market Date,-90),IF(Media Type = 'Radio',"https://support.workfront.com/hc/en-us/articles/217196767-Understanding-Calculated-Data-Expressions">ADDDAYS (In Market Date,-60),...)) Once you have these (very important) dates defined at the Project Level, you can leverage them in Workfront Reports for Filtering, Sorting, Grouping, and comparing (e.g. color any Task that should be done 30 days after the Ideal Start Date but isn't yet in red) If you're interested in other kinds of powerful date based visualizations, I also invite you to consider our "http://store.atappstore.com/product/ubergantt/">UberGantt and "http://store.atappstore.com/product/pdfcalendar/">PDF Calendar solutions (among "http://store.atappstore.com/products/all/">many others ) Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads

Avatar

Level 2
Hi Doug...sorry to just be getting back to you, I got pulled away for some other reporting and hadn't had a chance to go back in and experiment until today. I played around with your second bullet point option and finally got things to work...although not without a couple of roadblocks (1 - had to change the name of the "In Market Start Date" field because the hyphen in 'In-Market' was throwing things off, and 2 - had to get the parentheses in the right place as I kept adding multiple IF statements). Thanks SO MUCH for your help, this is exactly what I needed! You rock! Harry Thompson Lowe's Companies, Inc.

Avatar

Level 10
Well done, Harry, and my pleasure! There is no substitute for wrangling through a Calculated Expression like this on your own, and the satisfaction of then seeing it return what you were after. Congratulations. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads