Can I add a custom date field to a form?

[JillAc]

Hi I need some help as I've not done this before and need some advice. I hope you'll stay with me through my explanation - I have a Task report that shows a certain set of tasks and that works nicely. - I would like to add a column to refer to the Planned Completion Date of A DIFFERENT Task within the SAME project. This would be by a query that says -- If the Task Name in this project contains "Mail Date" then display the "Planned Completion Date" of that task -- there is only one task in any project that meets the "if" criteria but theoretically it's a one to many relationship and I suspect that's my problem - I *think* I need to create a custom form with a custom field that I put into the Mail Date tasks, and that links to the date that I'm looking to retrieve (so that the field I need is contained within the task, and so the task report can find it.) 1. Is that logical? 2. If so, what would be the code for the custom field to retrieve the date? 3. If no, any other way to do get this? I will continue to research custom fields but it does make me a bit dizzy. Thank you! Jill Ackerman Jill Ackerman

[EricMa]

Hi,Jill. If you're asking what I THINK you're asking, then there are 2 (maybe 3?) approaches that I'm aware of that you can try: 1. "Widen" the filter of your task report so that it includes BOTH your original set of tasks AND your new "Mail Date" tasks. (Use the "OR" operator in the Filter builder for this.) Then group the results by project. This will display the information about your original task set and the Planned Completion Date for the Mail Date, one row on top of the other, within the project grouping. (I don't think it's possible to have information about both tasks appearing in the same row of a Task report.) 2. If you really want information from both tasks to display in the same row, you'll need to build a project report that uses collections in the Filter and in the View. Then you can get something like: Project 1 name "Original Task" data "Mail Date" Planned Completion Date Project 2 name "Original Task" data "Mail Date" Planned Completion Date The drawback here is that collections are hard. :( 3. I'm not sure this is possible, but if you could add a Calculated custom field at the project level that captures the "Mail Date" Planned Completion Date, then you could include that in your original task report. Building that would require using a collection, as well, though, and I don't believe you can use those with Calculated fields. I'd be happy to be wrong, though. Hopefully that gives you enough to get started! ~Eric Eric Manning Language Line Translation Solutions

[JillAc]

Thank you for this. Collections keep coming up and I can't understand how to use them. I've read the Help articles but it seems like they have to be used with an API, and that is so far above my pay grade I'm stumped on it. I'll try your other suggestions, I understand what you're saying and I'll see if one of those things works. Thanks!

[SkyeHa]

Jill: stay with it, because the articles are going to get you pretty close, and the workfrontpro.com site will get you the rest of the way. Here's how I would have approached it: "https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report" title="https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report"> "https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report#referencing-collections-in-reports" title="https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report#referencing-collections-in-reports">https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report#referencing-collections-in-reports In this section here is your basic starting point. Step 13 shows you code for generating ALL the task names (or all the issue names). You can use this to learn the format for how to show all of a thing, and start to string things together (the third set of code in step 13). "https://workfrontpro.com/textmode/text-mode-views-collections/#milestone-tasks" title="https://workfrontpro.com/textmode/text-mode-views-collections/#milestone-tasks">https://workfrontpro.com/textmode/text-mode-views-collections/#milestone-tasks In this section here (thanks Narayan) you have really specific examples. I linked to the one on milestone tasks because I thought it was closest to being something you could use. Compare this bit of code to the above bits of code, and you notice that the line you're really needing to work on is the valueexpression line. This is set up for "if" something, then "concat" something. So what you need are the bits of code that says "if a project task name is mail date, then concat the task's planned completion date" and "if a project task name is your other task, then concat that date" -- one in each column of your view. You're so close! Keep going. It's not above your pay grade at all. You just need to remember or find the code for project task name and task planned completion date, and this is all done in camelCase. Keep going, girl, you got this! -skye

[JillAc]

Thank you for the encouragement. Can you also add 5 hours to my day so I can do this!! I am now stuck at one direction I found from someone here, that says "copy the API key" but I see nothing in the API Explorer/Object/Collections tab that says anything about an API key... stuck again.

[SkyeHa]

Don't go in too many directions! Just pick one. I can't help you with your API key other than to say if you had one it would be in your setup area under customer info. -skye

[JillAc]

Ok thanks. I'm going to drop the directions I was working with and switch to yours. Wish me luck diving into the dark web.

[JillAc]

Ok here is how far I got trying to get the planned completion date of a different task from the same project onto the same row as another task. But it's not working, nothing shows up. I am referencing the Task Collection because it is a Task Report I'm working on -- can't quite figure out if it should appear as task or Task There is not an example like this in Naryan's document, so I'm trying to do the IF statement, but it seems wrong too. Any advice? Should it be a project collection? I'm a little unclear when to do which. displayname=Mail Date listdelimiter= listmethod=nested(task).lists textmode=true type=iterate valueexpression=IF({task}.{name}="Mail Date",{plannedCompletionDate}, IF {task}.{name}="Deploy Date",{plannedCompletionDate}) valuefield=plannedCompletionDate valueformat=HTML Jill Ackerman

[JillAc]

And also, this is the part I keep getting stuck on, not sure if I need to do this but I cannot find API KEY - bolded below. This is from the workfrontpro document that was linked to earlier in this thread, and also referenced in other areas about collections that I have been reading. Collection Fundamentals (Filter) A collection is a list of objects that are linked to another object. View Workfront's help article about collections in a report for more about collections. The following procedure explains how collections are added to a filter or a report. View the API Explorer at https://developers.workfront.com/api-docs/api-explorer/ Find the object you want to get the list of items relating to (project, task, issue, user etc). Click on the Collections tab. Click on the data type you want. Copy the API Key into the OBJECT text below: Click on the URL to the collection object and click on the field you want and copy the API Key in the FIELD text below. Select a modifier and copy into the MODIFIER text below. See modifiers here. Add the appropriate VALUE to the text below. 1 2 OBJECT:FIELD=VALUE OBJECT:FIELD_Mod=MODIFIER Jill Ackerman

[SkyeHa]

1) I think you don't have to state "task.name". I think you just state "name". You told workfront you want to access the tasks list, and now you're telling workfront you want all the name fields. So just go "if name =" 2) I think what you need to do is create 2 columns. In one of them, put the mail date stuff. If you have a mail date task, put in the planned completion date. In the second column do the same thing for the deploy date. I find that as a beginner, I am just asking for trouble if I go too complex and try and add two IF actions together. See if the simple thing works first and then start combining if you get confident. 3) Pay closer attention to the workfrontpro.com example. I think you need to specifically say "if name = mail date, concat plannedcompletiondate". Leave out the valuefield line, as this isn't on the example code. So just basically follow the sample code a little more closely but plug in the things that you are looking for. Does that make sense? PS: I don't think you need an API key to make this work. I'm not 100% sure but it hasn't come up in community posts before this. -skye

[SkyeHa]

PPS: if you want to see if an API key truly is needed, I would look at the workfrontpro code samples and copy a simple one, verbatim, into one of your reports. If it works, then you don't need an API key. -skye

[EricMa]

Hi, Jill. To use the task collection, you'll need to set aside the Task report you've been using and build a new, Project-based report instead. You will have to go through some gymnastics to make it look like your old Task report. Your syntax looks correct--you will need to apply it to the appropriate column where you want the Mail Date's Planned Completion Date to display. You will need to create and apply an additional collection like this for each of the columns that need to display task-based data. ~Eric Eric Manning Language Line Translation Solutions

[JillAc]

Hi Eric Thanks for this advice. I will try it. Are you able to explain the logic that a report that lists tasks should be set at the Project object level? (I'm trying to wrap my head around the concept of collections; obviously struggling!) Thanks Jill Ackerman

[JillAc]

Hi I think I am close. My THINK my issue is the value expression IF statement. I can't find one on workfrontpro that is close enough to what I'm trying to do. I am very certain that my nested If statement is the problem and I have parenthesis in the wrong place, and just not following the format required What it should say is: if the task name equals (or contains would work too?) EITHER Mail Date or Deploy Date, then give me the Planned Completion Date for that task The logic I'm using is that if the task name=Mail Date then give me task completion date. If it is false, then look for Deploy Date and give me that one's task completion date. I'm using Excel's method of writing an IF statement, trying to plug in the field names for Workfront valueexpression=IF({name}="Mail Date", {plannedCompletionDate}, IF({name}="Deploy Date", {plannedCompletionDate}, "")) (Now I'm going to try making a project report instead to see if that works, but if my IF statement isn't right then all is lost) Thanks for sticking with me on this one. I have a very frustrated user that I'm trying hard to get her what she needs. Jill Ackerman

[SkyeHa]

Jill, I think this is great feedback to give back to Workfront so hopefully someone is listening. Your answer is right here: "https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report" title="https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report">https://support.workfront.com/hc/en-us/articles/115000586214-Referencing-Collections-in-a-Report In the Understanding Collections section: you're about to exploit the one-to-many relationship. Your project has multiple tasks and that list of tasks forms a collection for the project. So you're making a project report, asking for a "collection of tasks" and then saying "if one of the tasks is mail date, show me the planned completion date." I still suggest that if your "IF" statement doesn't give a result, you should ramp it down and go for one IF statement at a time. But that's just me! -skye

[EricMa]

Hi, Jill. I agree with Skye here. I think you'll definitely be better off setting up 2 columns in your report that use collections: one that will display the "Deploy Date"'s Planned Completion Date, and a separate one that will display the "Mail Date"'s Planned Completion Date. The first column would look something like this: displayname=Deploy Date listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({task}.{name}="Deploy Date",{plannedCompletionDate}) valuefield=plannedCompletionDate valueformat=HTML The second column would look something like this: displayname=Mail Date listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({task}.{name}="Mail Date",{plannedCompletionDate}, IF {task}.{name}="Deploy Date",{plannedCompletionDate}) valuefield=plannedCompletionDate valueformat=HTML I did find one typo in the code you posted earlier. In the "listmethod" line, it should be "nested(tasks)" instead of "nested(task)". Hope this helps! ~Eric Eric Manning Language Line Translation Solutions

[EricMa]

One other thing I noticed after I posted--if you're using a "valueexpression," you need to remove the "valuefield" line from your code. You can use only one or the other. I also realized that I didn't finish the "Mail Date" column properly; I had some cleanup to do before posting. So, corrected code below. This should work better for you. Sorry for any confusion! The first column would look something like this: displayname=Deploy Date listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({task}.{name}="Deploy Date",{plannedCompletionDate}) valueformat=HTML The second column would look something like this: displayname=Mail Date listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({task}.{name}="Mail Date",{plannedCompletionDate}) valueformat=HTML Eric Manning Language Line Translation Solutions

[JillAc]

IT WORKED!! But slight change in the code – you don't need the {tasks}.{name} as Skye had pointed out, it knows to look in the tasks collection. Also, the IF statement needs the final instruction as to what to ddisplay if the result is false – in this case "" meaning leave it blank. displayname=Mail Date listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF({name}="Mail Date",{plannedCompletionDate},"") valueformat=HTML Thank you! I will soldier on with this report and everyone will think it was nothing to get this, but it took me a lot of time. It's a thankless job. Jill Ackerman

[JillAc]

To close the loop on this, here is how to use "task name contains" to get the planned completion date. I haven't tried the nested IF statements, those I'll save for another day. displayname=Run Campaign Due listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF(contains("Run Campaign",{name}),{plannedCompletionDate},"") valueformat=HTML

[JillAc]

I'm so so so close. I'm trying to get the "assigned to" name into this report, which is built as a Project Object. I've tried a bunch of hobbled together codes from Workfrontpro but it's not quite right. This is the result, showing all these random commas. The name is correct, but it looks terrible. . (The blank lines are correct, there is no assignment on those tasks) Any ideas what I'm doing wrong? This is the code. I've tried different CONCAT formulas but they all turn out blank. At least this one is finding her name displayname=Assignment listdelimiter= listmethod=nested(tasks).lists textmode=true type=iterate valueexpression=IF(contains("Run Campaign",{name}),{assignedTo}.{name},"") valueformat=HTML

[SkyeHa]

what happens if you just leave off with the quote marks at the end? valueexpression=IF(contains("Run Campaign",{name}),{assignedTo}.{name}) -skye