Hello Workfront friends! I could use a little assistance on trying to run a report. In our instance, we have what I'll call a "parent" project in each Program. This project has a custom form tied to it where we collect certain metadata for reporting. There are "child projects" in each program related to that parent project - however the metadata is all really collected in that parent project.
I'd like to run a report, and filter based on one of the fields in the "parent project" custom form, to pull into the report only certain parent projects. However, I'd also like the report to pull in all of the "child projects" that are in the same program as the filtered parent projects.
Any suggestions on how to go about this? Many thanks in advance!
Solved! Go to Solution.
Views
Replies
Total Likes
The syntax for that kind of thing would be to add the following to your current exists filter.
line 4: EXISTS:a:DE:projects:YourCustomField=_____
line 5: EXISTS:a:DE:projects:YourCustomField_Mod=<your modifier>
The reason is below.
Your original filter is this:
1) EXISTS:a:$$OBJCODE=PRGM
2) EXISTS:a:ID=FIELD:programID
3) EXISTS:a:projects:categoryID=<your custom form ID>
The first two lines are the traditional lines that start off an exists statement.
1) You identify the object you are linking to. In this case, program
2) This is my linking line, and says that the program's ID is linked to the project's programID
The third line is the filter.
3) This is actually a collections filter. Now that you have linked to your program, you're searching all the projects within that program to see if there's a project with a specific custom form.
The fourth and fifth lines will also filter on the same collection. A way to think about it is, now that you have your project with the custom form, you'll also be picking out a particular custom field and additionally filtering on this.
I can't really give you any advice on the modifiers and such, other than to say that whatever you would normally filter for in a project report, use that filter, convert it to text mode, and the bring it into your exists statement that way, so that there's nothing to guess where the modifiers are concerned.
Sounds like you need a project report filtered to show certain parent projects based on a project typeahead field? If that's the case you might need to create another calculated field to pull in additional information from the project selected in the typeahead. Then use that calculated field to filter out your report.
Views
Replies
Total Likes
Hi @RowvillBh1
Can you explain the Typeahead field part? It's not something I'm currently using but I can potentially see how this could help when I'm trying to pull projects into a report based on a shared Program. I sent the following of summary of what I'm attempting to accomplish below. Let me know if you think the Typeahead could be useful. Thanks so much!
My goal is to create a project report based on a field in the custom data of a project. Let's say we have Program A.
In Program A, sits Project 1. Project 1 has a custom field titled Field 1 (its a Yes / No question) - Answered Yes.
In Program A, I add two additional projects. Project 2 and Project 3. These projects don't have any custom form fields in common with Project 1. The only thing they have in common is they're in the same program as Project 1.
Several other programs have a single project, with Field 1 answered "yes", and additional projects in the same program with no custom form data.
How would I run a report based on - I want to see all of the projects that have "Yes" to Field 1, and if the answer is "Yes" to Field 1, I also wanted all the other projects in that program listed on my report?
Views
Replies
Total Likes
Hi @BrianHa3,
This might be another use case for my How to "Echo" Custom Data between Object Peers post, where I've just posted something for you (to keep the ideas together, there).
Regards,
Doug
Views
Replies
Total Likes
Hi Doug. Thank you very much for the reply! This is in the right ballpark of what I'm thinking - however, I think your report is pulling all of the tasks you want already. You're then applying custom data from one task to other tasks on the report.
My goal is to create a project report based on a field in the custom data of a project. Let's say we have Program A.
In Program A, sits Project 1. Project 1 has a custom field titled Field 1 (its a Yes / No question) - Answered Yes.
In Program A, I add two additional projects. Project 2 and Project 3. These projects don't have any custom form fields in common with Project 1. The only thing they have in common is they're in the same program as Project 1.
Several other programs have a single project, with Field 1 answered "yes", and additional projects in the same program with no custom form data.
How would I run a report based on - I want to see all of the projects that have "Yes" to Field 1, and if the answer is "Yes" to Field 1, I also wanted all the other projects in that program listed on my report?
Does this help clarify my question? Thanks again for trying to help me with this!
Brian
Hi @BrianHa3,
Looks like you're on your way from the comment you left on my other post -- good luck, and I look forward to hearing where you end up.
Regards,
Doug
Views
Replies
Total Likes
You could try experimenting with the following sample code -- you would have to test it thoroughly. I think it would work as long as that custom form on the first project is at the top of the list (the category ID for that project). I don't have a lot of examples in my instance that I can test it against, so it's really up to you to do the extra legwork.
It goes like this:
EXISTS:a:$$OBJCODE=PRGM
EXISTS:a:ID=FIELD:programID
EXISTS:a:projects:categoryID=<your custom form ID>
Views
Replies
Total Likes
Hi Skye. This is great! So with this - I can pull in any projects that have the Custom Form (Category ID), and any projects that are also in that program with those projects. This part is a great start.
Is there a way that you know of that I can also filter based on one of the fields in that Custom Form? I'm at a loss for expanding on that Exists syntax.
Thanks so much!
Brian
Views
Replies
Total Likes
The syntax for that kind of thing would be to add the following to your current exists filter.
line 4: EXISTS:a:DE:projects:YourCustomField=_____
line 5: EXISTS:a:DE:projects:YourCustomField_Mod=<your modifier>
The reason is below.
Your original filter is this:
1) EXISTS:a:$$OBJCODE=PRGM
2) EXISTS:a:ID=FIELD:programID
3) EXISTS:a:projects:categoryID=<your custom form ID>
The first two lines are the traditional lines that start off an exists statement.
1) You identify the object you are linking to. In this case, program
2) This is my linking line, and says that the program's ID is linked to the project's programID
The third line is the filter.
3) This is actually a collections filter. Now that you have linked to your program, you're searching all the projects within that program to see if there's a project with a specific custom form.
The fourth and fifth lines will also filter on the same collection. A way to think about it is, now that you have your project with the custom form, you'll also be picking out a particular custom field and additionally filtering on this.
I can't really give you any advice on the modifiers and such, other than to say that whatever you would normally filter for in a project report, use that filter, convert it to text mode, and the bring it into your exists statement that way, so that there's nothing to guess where the modifiers are concerned.
Thank you so much!! This works! One other question for you. Is there a way to reference more than one custom form on this line? In my case it can be one of 3 custom forms.
EXISTS:a:projects:categoryID=<your custom form ID>
Views
Replies
Total Likes
I think just the same way as always -- you add all three IDs on the same line, separated by tab spaces.
This did the trick. I can't thank you enough! This has been so so helpful.
Views
Replies
Total Likes
Views
Likes
Replies