Expand my Community achievements bar.

SOLVED

External Lookup - Limiting Drop-down Options on Custom Field based on Selection on Previous Field

Avatar

Level 2

Has anyone had success using the External Lookup field to limit custom dropdown field options based on the response to a preceding custom drop-down field in the form?

 

Example:

We have Business Units, Product Lines, and Products. I want to limit the Product Lines to only show the options that fall under the business unit they have selected. Then, I want to limit the products to only show the products in the product line they have selected.

 

I understand External Lookup is supposed to make this possible, but I'm having a hard time figuring out how to set this up. Does anyone have an example they'd be willing to share?

 

Thank you!

Topics

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

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

We have business units and sub-business units. To represent these in Workfront I am using parent tasks for the business units and child tasks for the sub-BUs. This is all within one project to represent my collection of BUs and sub-BUs.

 

In the child (sub BU) tasks, I make every task description the name of the parent (business unit). So if I have a parent task/business unit called "Fruit", and child/sub-BUs of "Apple", "Orange", and "Lemon", all these tasks have "Fruit" in their description. Not the only answer -- just what I decided was easiest for me to do.

 

So a BU external lookup field might look like:

 

$$HOST/attask/api/v17.0/task/search?projectID=____________&numberOfChildren=0&numberOfChildren_Mod=gt&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

0. Search for tasks in a project with ID ____

1. the "number of children" phrases are what identifies my parent tasks -- you would have to do something different since you have three levels. So this is just an example with two levels, basically

2. I'm including only "new" tasks. If I have to retire a BU, I just mark it as "cancelled" and it drops off the list

3. I'm sorting it by task number (so the order I have it in the project is how I want it to show up

 

And then a sub BU external lookup field might look like:

 

$$HOST/attask/api/v17.0/task/search?projectID=_______________&description={DE:My Business Unit Fieldname}&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

0. Search for tasks in project ID___

1. You see here I'm searching on the task descriptions now. So no need to call out "number of children"

2. The description is equal to whatever you selected in the BU field. 

3. Again, ordered in a particular way

 

As long as you only pick one of each BU and sub BU, this would work (the last field can be multi select). I haven't gotten it working to a point where you would be able to pick multiple BUs or sub BUs.

View solution in original post

5 Replies

Avatar

Correct answer by
Community Advisor

We have business units and sub-business units. To represent these in Workfront I am using parent tasks for the business units and child tasks for the sub-BUs. This is all within one project to represent my collection of BUs and sub-BUs.

 

In the child (sub BU) tasks, I make every task description the name of the parent (business unit). So if I have a parent task/business unit called "Fruit", and child/sub-BUs of "Apple", "Orange", and "Lemon", all these tasks have "Fruit" in their description. Not the only answer -- just what I decided was easiest for me to do.

 

So a BU external lookup field might look like:

 

$$HOST/attask/api/v17.0/task/search?projectID=____________&numberOfChildren=0&numberOfChildren_Mod=gt&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

0. Search for tasks in a project with ID ____

1. the "number of children" phrases are what identifies my parent tasks -- you would have to do something different since you have three levels. So this is just an example with two levels, basically

2. I'm including only "new" tasks. If I have to retire a BU, I just mark it as "cancelled" and it drops off the list

3. I'm sorting it by task number (so the order I have it in the project is how I want it to show up

 

And then a sub BU external lookup field might look like:

 

$$HOST/attask/api/v17.0/task/search?projectID=_______________&description={DE:My Business Unit Fieldname}&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

0. Search for tasks in project ID___

1. You see here I'm searching on the task descriptions now. So no need to call out "number of children"

2. The description is equal to whatever you selected in the BU field. 

3. Again, ordered in a particular way

 

As long as you only pick one of each BU and sub BU, this would work (the last field can be multi select). I haven't gotten it working to a point where you would be able to pick multiple BUs or sub BUs.

Avatar

Level 2

Thank you so much for this! I haven't had time to go in and try this yet, but wanted you to know I saw your response. I will come back and report once I've had the chance to set this up. 

Avatar

Level 2

Skye! Thank you - this was SO incredibly helpful! Since mine was 3 levels, this is what I came up with using your guidance.

 

BU:

$$HOST/attask/api/v17.0/task/search?projectID=<My Project ID>&parentID_Mod=isblank&status=NEW&status_Mod=in

 

Product Family:

$$HOST/attask/api/v17.0/task/search?projectID=<My Project ID>&description={DE:My Business Unit Field Name}&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

Product:

$$HOST/attask/api/v17.0/task/search?projectID=<My Project ID>&description={DE:My Product Family Field Name}&status=NEW&status_Mod=in&taskNumber_Sort=asc

 

I'm so stoked to have this new tool in my pocket! Thanks again.

Avatar

Level 1

This indeed looks extremely promising!
I do have a question though. For an external lookup field, we need to provide the Base API URL and the JSON Path.
Hence, in the context of the example that you have provided, may I please know what information needs to be added in these two fields?

 

Thank you!

Avatar

Level 2

Hello! Below is what I have in those fields. Replace "ENTER NUMERICAL PROJECT ID HERE" with the project ID. Then after the project ID, it's up to you which modifiers you want to include. 

 

Base API URL:

$$HOST/attask/api/v17.0/task/search?projectID=ENTER NUMERICAL PROJECT ID HERE&parentID_Mod=isblank&status=NEW&status_Mod=in

 

HTTP Method: Get

 

JSON Path:

data[*].name