Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

textmode for filtering custom form in template report?

Avatar

Level 10

Is there any text mode for filtering for a custom form in a template report if your users are using multiple custom forms?

In a project report, it would be this:

objectCategoriesMM:ID=xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

objectCategoriesMM:ID_Mod=in

(i.e. filter on Categories ID)

In a template report there's nothing. I did try copying over the code in the valuefield on the view (I think it's customFormsName), as well as the textmode from the project report. But everything errors out. So can I really only filter off the primary custom form in a template report (i.e. filter on Category ID)?

Topics

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

0 Replies

Avatar

Level 8

Usually, when something (like a template) can have multiples of something else (like a form), the form is a collection of the template. Workfront has the somewhat mysterious objectCategory as the collection of the template, and the categories are a reference of the objectCategory. I suspect this is where you are having trouble.

Listing a collection, and even a reference of a collection, can be done in text mode by adding a column. To just list all the forms in a tempalte, add a column in text mode to say:

displayname=Custom Forms

listdelimiter=<br>

listmethod=nested(objectCategories).lists

textmode=true

type=iterate

valuefield=category.name

valueformat=HTML

Because it's a reference in a collection and not a direct property of the collection, we can't just filter on the collection. So instead we have to use the text mode EXISTS functionality to link between templates and categories, via objectCategories as the linking object.

So the text mode for the filter will be:

EXISTS:A:$$OBJCODE=CTGY

EXISTS:A:category:ID=[replace with ID of the form (category) you are looking for]

EXISTS:A:ID=FIELD:categoryID

Avatar

Level 10
thanks Barry. I was fortunate that there was already an existing reference for the view part of the report--however because there was that, I didn't even think about using an exists filter. :slightly_smiling_face: I should have known. Thanks so much! -skye

Avatar

Level 10
I'm going to table this for now (haha). I couldn't get the exists statement to work, which kind of makes sense. (My understanding is that the OBJCAT listing would have to show in the references tab for me to be able to link to it--is that right? and it doesn't so I can't) I did flail around a little with different syntaxes, but no joy. -skye

Avatar

Level 10

To retrieve all templates that have a particular custom form's guid attached, simply switch your Template report's filter to text mode and use this one-liner:

  • objectCategories:categoryID=41b3dac47210ff4da13ea1c3748ab204

And yes, if you're wondering: it was much harder to figure out than it looks.

Regards,

Doug

Avatar

Level 9
Level 9

I'm so mad! I tried approximations of both Barry's and Doug's solutions over the weekend, but couldn't get my use case to work.

In my case, I was trying to do a similar thing as Skye, but list templates in which a specific form was *not* attached. Therefore, everything I tried had either a NOTEXISTS or _Mod=notin modifier.

So, any idea how to do this with a not equal modifier?

Avatar

Level 10

Hi Bill,

I invite you to stop beating your head against the wall, as this second line finds Templates that do NOT have a particular custom form:

objectCategories:categoryID=41b3dac47210ff4da13ea1c3748ab204

objectCategories:categoryID_Mod=notin

Sleep well.

Regards,

Doug

Avatar

Level 10
thank you Doug! That worked. My user assured me he'd always have a use for that formula, so I appreciate all the time you took in noodling that out :slightly_smiling_face: -skye

Avatar

Level 9
Level 9

That does work, Doug - kind of... It works in a report, but not where I was trying to do it - in the template landing page list.

I guess the real problem is the manner in which I create filters and views. It may be a bad habit of mine, but when I'm creating a report, I start by creating the filter and view in a landing page list. Then I create a new report and apply the existing filter and view. I fell into this habit because there were user requests to look at a single report, but have the ability to switch back and forth between the report's default view/filter and a the view/filter that exists on other reports. And, I found it an easy way to quickly update multiple reports with a new iteration of a view, or, apply a report filter in other reports/landing page lists.

I'm still a fan of this practice, until things like this pop up where a filter works in a report but not a list view, or vice-versa. It's the second time in as many weeks!

Avatar

Level 10

Gotcha Bill,

I applaud your keep yer views (and groupings and filters) as separate objects convention, and aspire to it myself, as well.

It's unfortunate (and unusual) that it does not work as you'd expect, in this case.

Regards,

Doug

Avatar

Level 10
William: This flipped case doesn't work for me either way (in a report or on a template tab filter), and the way it fails indicates a need for an Exists statement. In other words, the result I get when I use a "notin" Mod is basically ALL the templates that have ANY custom form (so anything that isn't blank). -skye

Avatar

Level 3
This code works for to find custom form(s) featured in templates. Thank you all for posting questions, feedback and many thanks to Doug for code! Kind regards, - Joe P.S. does this giphy format work in this forum Joe Salas Logitech International SA

Avatar

Level 8
Doug and Barry, I tried both of your filters in a task report to find tasks that had 2 specific task forms attached with no luck. It only shows if at least one of the categories are attached. Any ideas? Adina Pierce

Avatar

Level 8
I don't think either can be bent to work for finding Form A AND Form B. An alternative approach would be if both forms have a mandatory field, have the filter to say Field on Form a is not blank AND Field on Form B is not blank. If either (or both) don't have a mandatory field, add a calculated field to each that just calcs to 1 and use a similar approach - Form A calc >0 AND Form B calc >0 Barry Buchanan - WMA Work Management Australia

Avatar

Level 3

I'm having trouble applying this code to a Project report. I would like to filter out projects that DO NOT have a specific custom form. Any code that I found here or concocted has not worked. All of our projects have multiple custom forms attached to them and I am not sure if this is impacting this effort. This is what I've tried:

objectCategories:categoryID=5ecc319201c4e9a0200c66fa5bd12aae

objectCategories:categoryID_Mod=notin

I have also played around with several versions of EXISTS/NOTEXISTS code snippets to no avail.

Any help would be appreciated!

Avatar

Level 9
Level 9

If I remember correctly, filtering on objectCategories works as expected when viewing the project list (All Projects > Add New Filter), but doesn't yield the same results when applied to a report, which doesn't make sense at all but did seem to be the case at the time.

In the past few months, I have started applying a set of three calculated fields to every form in our instance. It looks like this:

Field1 name: SYS Work Type RCS

Field1 calc: CONCAT("TRUE")

Field2 name: SYS Work Form RCS 01

Field2 calc: CONCAT("TRUE")

Field3 name: SYS Work Type

Field3 calc: CONCAT(IF(ISBLANK(SYS Work Type RCS),"","Retail Creative Services, "),IF(ISBLANK(SYS Work Type CCS),"","Content Creative Services, "))

SYS is a solution code/field prefix that reserves this field for system admin use.

RCS is a solution code/field prefix that indicates a field or form is reserved for Retail Creative Services.

CCS is a solution code/field prefix that indicates a field or form is reserved for Content Creative Services.

We have a few dozen 3-letter solution codes.

Field1 exists on this form, and every past/future iteration of the same form. Our org is very finicky and asks to update and retire team forms all the time. Now I have a field I can filter against that returns all objects in which any version of the Retail Creative Services form is (or is not) attached.

Field2 exists on this form and only this form. This gives me a field to filter objects on which this version of this form is (or is not) attached. If when this version of the form gets retired, I append the name with RTR to indicate an outdated version of the form is attached.

Field3 exists on all forms. It's actually much longer, because the IF statement covers all our solution codes. This gives me a single field to filter against if I want to find (or exclude) objects that contain specific solution codes (and I don't feel like joining a series of AND / OR statements.) More importantly, it gives me a single field that I can use to group records based on the kind of form that is attached, regardless if it is a past or current version of the form.

It took a while to get this on all our forms, but the payoff has been worth it when it comes to filtering and grouping by form (or form version) that may or may not be attached, and works when multiple forms are attached (which is nearly every object in our instance.) It's also easier to just give users a specific field they can use to filter on their own, rather than try to teach them the complexities of something like EXISTS.

Hope that helps!