Custom Form drop down options driven from Excel doc or external DB?

[ScottHi]

Hello All, I have a scenario where I need to classify Business Unit, Division, Sector, Segment There is a file I have in excel with every instance of the companies structure but its massive 521 scenarios of those four groups. So manually entering this into custom form drop down is out and I also don't want that many options in one list, it would need to be smart so when one Business Unit is selected only the Divisions within that BU are shown in the next field, and so on for Sector and Segment. Is there a way to do this in custom forms or is this an API or something beyond WF custom forms? Thanks Scott Scott Hinchman Automatic Data Processing, Inc.

[NRYNR]

Hi Scott, We have a similar scenario where the first menu selection drives the sub-selections. Using display logic, we add the appropriate sub-menu so that the user only sees the appropriate sub-selections. It makes for a good user experience, but then you have the challenge of reporting since there are so many sub-menus. To solve for that, we added a calculated field to the form that had a CONCATENATE function that concatenates all of the sub-selection drop menus. This way, the one that was chosen gets populated. That gives us one field to present in reports or use in filters. Here's how the form looks, design mode and preview mode:

[ScottHi]

Excellent this explains how to have them work together. Do you feed the fields from an excel doc or API or do you manually create and maintain all the categories and sub categories? Scott Hinchman Automatic Data Processing, Inc.

[Doug_Den_Hoed__AtAppStore]

Hi Scott, What you are describing is commonly known as Cascading Parameters, where the selection in one dropdown then restricts a dependant dropdown to only a valid subset. It's a powerful concept that has been requested many times over the years, but has not yet been incorporated into Workfront's custom forms. To date, the Form Logic and optional Calculated Parameter as Narayan explained is the best workaround. However... Earlier this year, one of our clients had us adapt our "http://store.atappstore.com/product/ubercalc/">UberCalc solution to address requirements that were eerily similar to yours, so that Their SysAdmin obtains an Excel file with tens of thousands of valid three part codes from their financial system each morning They then upload the file into Workfront as a code table, effectively extending Workfront's data model to include that knowledge Users go to a special "UberForm" custom tab (which we created for them for this purpose) on each of their Workfront Requests On that UberForm, they can then enter the three part code into three separate text boxes, then click Validate The Validate routine then checks the Excel Data, and reports whether the combination is valid If it is not valid, the users can use a search window to partial-match the Excel Data (from within Workfront) and find the right combo Once a valid combination is entered, our UberForm presents a dropdown of Workfront Templates Users may then select the Workfront Template and click the Create Project button Our UberForm then creates the Project from the Request using the Template, preserving the Request (our client's preference) It also preserves the Request's Custom Data, Documents, and Notes under the Project, for transparency and efficiency This approach saves both the SysAdmin and the Users a tremendous amount of time, efficiently ensures the quality of the data, and increases the speed at which they can convert Requests to Projects, for those interested in such matters. If you would like more information, please drop me a line at doug.denhoed@atappstore.com. Regards, Doug Doug Den Hoed - AtAppStore Got Skills? Lend a hand! https://community.workfront.com/participate/unanswered-threads