How To: Replace Typeahead with External Lookup Fields

[KatherineLa]

In my instance, we've added a number of Typeahead fields over the years that we've come to regret, mainly because they appear as top-level objects in Reporting tools and confuse everyone trying to build reporting. I can only explain why Project reports show two Portfolio and Program options with completely different options under them so many times. (Yes, I could re-name the fields but I wanted a better solution.)

Finally I decided to experiment with replacing the Typeaheads with External Reference fields that looked the information up from Workfront's API instead. I've included a set of examples below for the objects I replaced, along with some gotchas I found along the way. 

1. Project Templates. This was the easiest one, since we have less than 100 active templates in our instance.  Depending on what I wanted the drop-down to display, I could potentially filter further by the Group of the template, or any other template field. This returns a list of all Active Template Names, in ascending alphabetical order.
   1. $$HOST/attask/api/v17.0/tmpl/search?
   2. isActive=true&
   3. name_Sort=asc   
   4. Note: This does respect Template sharing settings, so the User interacting with the field will still not see any Templates they don't already have access to view.
2. User Dropdown: This one has a few layers to it, because I needed Active users, with Plan licenses, NOT in a certain Group. Similarly to the Template example, then I wanted the User Name field in ascending order. #5 was the final addition, when I realized my list of filtered users was still greater than the default 100 results returned by the API.
   1. $$HOST/attask/api/v17.0/user/search?
   2. isActive=true&
   3. licenseType=F&
   4. homeGroupID=<removed systemID from Group here, insert yours as needed>&homeGroupID_Mod=ne&
   5. $$LIMIT=200&
   6. name_Sort=asc
3. Portfolio: This was my next challenge, since we have thousands of active Portfolios. The form I wanted to put this field on happened to contain a Typeahead field to the Company object though, and what I really wanted in the drop-down was a list of Portfolios corresponding to that Company. The same Typeahead also appears on every Portfolio in our instance, so what I needed was a match on a custom field. Because our Portfolio names are auto-assigned and contain the entire Company name in them, I could use 'contains' as below. 
   1. $$HOST/attask/api/v17.0/port/search?
   2. isActive=true&
   3. name={DE:Company Typeahead}&name_Mod=contains&
   4. name_Sort=asc
   5. Note: What I really wanted here was to reference the ID returned from the Company Typeahead rather than match on the names, but never figured out that trick. If anyone else has, please comment below?!
4. Program: Now I want to display a list of Active Programs that correspond to the Portfolio the user just selected which is almost exactly what worked for the Portfolio with one more layer. In this example, 'Portfolio Test' is the name of the field I created in step #3 above. 
   1. $$HOST/attask/api/v17.0/prgm/search?
   2. isActive=true&
   3. portfolio:name={DE:Portfolio Test}&
   4. name_Sort=asc

Notes.

1. The use of $$HOST instead of my full domain 'https://sampledomain.my.workfront.com' is so that when our Preview/Sandbox environments are reset, the links continue to work there normally for testing instead of my sandbox still trying to call data out of Prod. 
2. Form field logic is not supported with External Ref fields (yet, maybe someday?)
3. Data returned from an External Ref field is ordinary text data, rather than a reference to the object. Consider implications for Fusion if you have Typeaheads being referenced in any Fusion scenarios. It's still workable, but will require changes to handle. 
4. By default, the API will only return the first 100 results without a $$LIMIT=$$$$ edit in the call. You can increase the limit up to 2,000 but consider form responsiveness in your testing. If it takes awhile to load, users may perceive the form is broken instead.

Reference Pages:

1. JSON Path Finder: JSON Path Finder  This is what I dump my initial output into in order to find the right path for the JSON Path inputs. 
2. Workfront API Explorer: API Explorer  This is where I reference the field names/abbreviations to put together the Base API URL path.
3. Workfront API Basics: https://experienceleague.adobe.com/en/docs/workfront/using/adobe-workfront-api/api-general-informati...

[skyeh]

our only gotchas that are not on your list is that external lookup fields are not working (do not display or are not functional) on dashboards and in the new Home Workspace. 

[KatherineLa]

Ahh, good add! For Dashboards, I've had luck with creating a text-mode column that references the field with "valueexpression=" syntax to at least make it visible but still not able to be interacted with. I haven't experimented to see if that would make it visible in the new workspace too.

[skyehansen]

we've discussed doing similar, but it did spur a conversation about users who like to create their own reports, and does underline that some of our dropdown fields are high maintenance (always getting inline edited). We're continuing to pursue enhancement requests that bring functional parity, but appreciate that textmode continues to be a workaround in many cases.

[D____D]

very cool .. I'm running into this issue where the "portfolio:name" lookup is not working for the program field - it looks like with what little I had went over in the docs, led me here as I started running into my issue .. am I missing something else? 

[D____D]

Fixed - typo LOL 

[skyehansen]

Adding to the list of gotchas: the external lookup field on a request custom form will not display in the Outlook plugin (e.g. if you want to convert an email to a request via queue), and in the Teams app (e.g. if you want to submit a request from there).

It does display in the mobile app, but I cannot toggle it (so it's non functional).

[D____D]

I'm wondering if any of you have tried leveraging this with the JIRA API? 

[tibormolnar]

Hi @KatherineLa,

thank you for the summary. I've been recently experimenting with similar things and got pretty much to the same conclusions.

The most annoying limtation is the lack of the object ID in the stored value. When processing a submitted request in Fusion, I need to run an extra search to obtain the ID, which is less then ideal, as the name is not necessarily unique and the search may bring multiple results.

I have found an (admittedly not very user friendly) workaround though, to obtain the ID straight on the form.

I create 2 external lookup fields: The first one allows searching for the desired record. The second one returns the ID.

In the below example we search for a user:

Here's the first field - this can include more filters, if needed:

Then the second one returns the ID of the user selected in the first field:

The inconvenience is that the user has to make the selection in the 2nd field - even though that (ideally) shows only 1 option.

The issue of multiple matching records remains here, though. So if we have 2 users with identical name, the second field (too) shows 2 IDs. For a user selection, I think I'll solve this by using the email addess (instead of the name) in the 1st field - that is unique. I have no solution idea for other objects.

I was playing with the idea of manipulating the "JSON Path", trying to display a concatonation of Name + ID, but didn't manage to get it work.

Furthermore, I found that I can only display a field (as an option to choose) which is included in the API response. And the response for users includes only a very limited number of fields:

And if a field value can't be displayed as selectable option, the comparison with the entered value doesn't work either. For example: We have have a custom field on the user object (.Workday ID) that holds a (unique) external ID. I can use that as a filter, but I can't use it as a typeahead filter. In other words, this is working:

but this doesn't:

I wonder if there is any way to get the User search API to return more fields (including custom fields) in the response.

Regards,

Tibor

[tibormolnar]

With regards to my own question raised in the previous post, I managed to figure out the answer:

1. The API can can be instructed to include further fields (incl. custom fields) in the API response by appending this to the BASE API URL:

&fields=field1,field2,...,fieldN

For example, if it's a custom field that we need:

&fields=DE:.Workday ID

Note that the dot is part of the field name in my case.

2. This allows us to display a custom field value in the dropdown, by editing the JSON Path.
Because of the semicolon (:) after DE and/or the dot (.) in the custom form field name, the JSON Path needs to be formatted as follows:

$.data[*].['DE:.Workday ID']

This doesn't work:

$.data[*].DE:.Workday ID

3. This then allows us to use the custom form field as the typeahead search field. So all of this comes together as follows:

[RandyRoberts]

Thanks, Kat. This was really helpful. I didn't realise it was just a search that doesn't have to rely on another field, but it can if you want it to. We can now call them External lookups and Internal lookups.