Expand my Community achievements bar.

A general where-used query of full database

Avatar

Level 10

5/3/17

Provide for System Admins a general, where-used, Google-like query that allows interrogation of all the data in the instance Workfront database.

Example uses:

When a commonly-used custom prompt needs to be changed, which reports use it?

Which views need to be updated with a revised commonly-used text mode column?

Which layout templates have not had the My Work customizations applied?

Which filters include the UserTeam IDs field?

8 Comments

Avatar

Level 7

7/12/17

I created a report that lists out all of my custom fields and the forms they are on. Report "type" = Parameter. No filters. No Groupings. One Prompt for "Name" so I can look up just one field if that's all I need. Columns (none are Text Mode; all are under parent of "Parameter": Name, Display Type, Description, Forms, ID (GUID). It will list multiple forms in the "Forms" column if the field is on multiple forms, separated by a comma. I export to Excel, delete the ID column if I don't need it, then do "text to column" and separate the Forms. Hope that helps!

Avatar

Level 7

7/12/17

I created a second report that lists out all of my custom forms and the reports they are on. Report "type" = Report. No filters. Two Prompts: Filter - ID and Grouping - ID. For the columns: Report - Filter ID, Report - Grouping ID, Filters (in text mode), Groupings (in text mode). ID yields the GUID, but I know what the GUIDs are from the Parameter report I created above. Text Mode yields the actual "code", and you can identify your custom field within that column. There is no API object for "prompt", so I couldn't interrogate for that. But to me this is minor. If the prompt fails, is doesn't cause the report to fail.

Avatar

Level 10

7/12/17

Yes, you have well explored these reports. The Parameter report (and Category report) is very useful for learning about custom fields and forms. I have some myself.

I would also love to know exactly where custom fields are used in reports, report prompts, views, filters, and groupings, in case I want to modify or remove them.

Avatar

Level 10

7/12/17

Kathy, can you share with me the text code you use in your Report type report for "Filters (in text mode)" and "Groupings (in text mode)"? "Filters" and "Groupings" fields do not appear in the report builder Columns interface for me. Thanks! Bob G

Avatar

Level 10

7/12/17

We had someone write a macro that takes the custom data kick-start export and parse it out into multiple tabs in excel.

1) Field list tab with a column showing each form the field appears on with hyperlinks and other attributes.

2) Form list tab with hyperlinks and attributes.

3) Individual tabs for each form on the system, containing the sections, and field layout along with attributes.

This has been a good way to help us find / see how custom fields are used on forms (it doesn't contain the new field logic, etc. as that didn't appear to be available in the kick-start export. We were wanting to do something similar with a kick-start export for reports, etc. however you are limited to the number of reports that can be exported at one time.

* Just wish there were a better way do these things in the tool.

Avatar

Level 7

7/12/17

I am so sorry, I was in a hurry and used "old" notes, while in fact I had made changes to the report. I don't have any Filters or Groupings on the report. I did have to use Text Mode for a couple of the Columns. To use this report to find a field used as a "prompt", run it without any prompts, then use your browser's "find" feature. The Text Mode columns I have on the report are as follows, and again, I apologize for any confusion!! (I think it's the nature of the content that can get tangled up!) :-)

I worked with the tech team on better options, but this is the best we could come up with within the tool.

Groupings column:

displayname=Groupings

linkedname=direct

namekey=groupByID

querysort=groupByID

textmode=true

valuefield=groupBy:preference:value

valueformat=HTML

Filters column:

displayname=Filters

linkedname=direct

namekey=filterID

querysort=filterID

textmode=true

valuefield=filter:preference:value

valueformat=HTML