Typeahead field on form, populated from lookup of external data source; selected value executes query

[Mylah]

Is it possible to create a Typeahead dropdown list that queries an external data source? When the user chooses a value from among typeahead options presented, we'd like the selected value to execute a query that looksup related values and returns them

For example ...

You have a Typeahead field on a form called "State". User types in string "CA" and the field looks up the external datasource column for State to populate typeahead suggestiongs

User Gets typeahead suggestions of "California", "N. Carolina", "S. Carolina"... & selects "California".

This executes a query to a "geo" database using California as search parameter to return the related values for Country and Continent.

The following 3 fields are populated on the form:

State: California

Country: USA

Continent: N. America

Has anyone accomplished this type behavior on a form? We do have Fusion FLOs that execute upon Request or Project update *after* the form is submitted and retun to populate other fields on the form, but nothing like a dynamic form that populates values prior to submitting/saving it.

Our target data source has a restful API, and may alternately allow for a live db connection (JDBC/ODBC etc) to be established.

Please help us brainstorm possible solutions to this type of use case. We'd love to hear from admins who've implemented a similar process.

Thank you,

Mylah

[Doug_Den_Hoed__AtAppStore]

Hi Mylah,

Depending on how much data there is and how often it changes, our UberForm solution might meet your requirements:

• pull the latest info from the source into Excel
• upload that Excel file into Workfront
• direct users to raise requests via a Workfront dashboard with our UberForm within it
• as they type (eg State = CA), UberForm would restrict a list drawn from the uploaded Excel File to the entries that match
• once they select the desired entry, UberForm would look up and automatically populate the related information (eg US and North America) so the user can visually inspect them before submitting the request
• when ready, the user clicks the Submit button on the UberForm, thereby creating the request

We’ve used UberForm in such cases to effectively extend Workfront’s data model, streamline workflows, and improve the user experience. If you’d like to explore it further, you’re welcome to email me at doug.denhoed@atappstore.com.

Regards,

Doug

[Mylah]

Thank you, Doug. I sent an e-mail to you at doug.denhoed@atappstore.ca and received a bounce-back. Did I use the correct address? (I will try to resend now a 2nd time).

Best,

Mylah

[Doug_Den_Hoed__AtAppStore]

My apologies, Mylah: Freudian typo; I’ve corrected it above to be doug.denhoed@atappstore.com. Regards, Doug

[gagiksukiasyan · Accepted solution]

Hi Mylah,

I'm happy to let you know that we have added the External Lookup field type to custom forms that might cover your needs. It is already in preview and will be release with the upcoming release.

Here you can find more details on that.

Thanks

Gagik

[Doug_Den_Hoed__AtAppStore]

Thanks @gagiksukiasyan,

The new External Lookup Field looks quite promising!

Do you happen to have an example of how it could be configured to handle the longstanding cascade dropdown challenge such as the one @Mylah posed, where a user would select Continent in Field 1, then Country (within that Continent) in Field 2, then City (within that Country) in Field 3, assuming the source for all three fields was (somehow) "data stored within Workfront"?

Regards,

Doug

[gagiksukiasyan]

Hi Doug,

William has a great response above detailing how the cascading works using this new field type.

Thanks,

Gagik

[William--]

Had a chance to talk with the product team about this after testing in Preview and happy to report that it looks really promising. The biggest drawback at this moment is the field can query Workfront records, but the header can't use a user's current sessionID to authenticate. It's not a safe practice to hard code static credentials in the header in order to enable authentication on the query. (The samples I'm sharing here do that - do as I say, not as I do!) 

The core pieces to understand are:
Base API URL: this is the url to query your API.
Dependencies: if you want to insert variables into your query, you can wrap it in curly brackets. E.g., if you want to filter your query results to a list of cities from a state field that appeared earlier in the form, you can add &state={DE:State} to your query, and Workfront will replace {DE:State} with the value that the user selected in the form.
$$query wildcard: This is a special dependency that can be put in the Base API URL. When used correctly, it will help to filter the result list to only those that contain the characters that the user has inputted into the external lookup field. (e.g. start typing "Sp" and the list filters to Spain, etc.
JSON Path: When the API returns a list to Workfront, it comes in the form of a JSON payload. Because every API is different, Workfront has no way to know how to parse the payload to display just the relevant piece (typically "name" value). JSON Path tells Workfront how to parse the response payload so it can display a user-friendly value. Google JSON Path for tutorials, or paste a sample payload into ChatGPT and ask it what is the JSON Path to get the list of name values. At this time, you can only pull one parameter from the payload. You can't yet combine values into a unique display, like, "Name, Title" in a user query - you can only pull name or title, not both. 

The video here shows a couple examples of the external lookup in action. All examples use the $$query wildcard in the Base API URL to filter the list of results based on characters inputted in the 

"All Countries": a list of all countries
"Region": a normal hard-coded Workfront custom field
"Country filtered by Region": same as "All Countries" but filtered to only countries that are part of the selected "Region." 
"Customer Category": a normal hard-coded Workfront custom field
"Customer filtered by Category": queries Workfront for task names in a project, where the description equals the value selected in "Customer Category" 

Hopefully this will help some folks understand how the field works and try it out for themselves. Good luck!

-WE

[ewanh]

Hi William, 

Great findings!  One thing...you can use the $$HOST to represent your domain now. Check it out in the documentation.  That was an add to the documentation just prior to the release so our early adopters didn't see that.  Myself included.  You don't need your api key/headers with that. 

Cheers!

Ewan

[William--]

That's a terrific enhancement and will come in handy for sure!

Definitely opens up the option to create "psuedo-typeaheads" for objects that can't currently be made into a typeahead (issues, tasks, etc.) 

Also allows us to enable more typeaheads in general. We're typically conservative in how many typeaheads we will create in the instance, since each new typeahead creates a new object type in the system. TA fields aren't nested under other object types and therefore make the report builder less user friendly.

[ewanh]

Love the feedback William about the Typeahead fields impact on the report builder.  Haven't thought about that much myself as usually there are only a handful used per instance.  I know in your org you use them often and that impact probably hasn't been registered before.  @gagiksukiasyan 

Also...I'm sure our Product team will love to hear your feedback about the use of the new wildcard.

[gagiksukiasyan]

Thanks for the feedback, William! May I ask you to provide more details on this feedback? What do you mean by a new object type? I'm happy to jump to a call to discuss this. 

[William--]

Hi Gagik, of course. 

By new object type, I mean to say "additional table in the report builder." When a user is creating or editing a report (let's say it's a Task report) they are expecting that every field in their task custom form will all be available to select under "Task" in the report builder. But, typeaheads don't appear in the report builder alongside all the other task-related custom fields. They're not nested under anything - they're their own top level table. 

This can be confusing for novice report builders, and can become very tedious for anyone who needs to scroll through the table list to find referenced fields. (e.g., I want to display Project Planned Completion Date in my task report, I have to do a lot of scrolling to get to the Project section in report builder.) 

We only approve 10% of the requests for new typeaheads in our instance, and the list of tables in report builder is already very long. Now that we can effectively create our own "psuedo typeaheads" using the new External Lookup, we can say 'yes' to 100% of requests for lookup fields without making the list of tables in report builder 1,000 kilometers long, and also making the fields easier to find in report builder.

[SarahWilkersonCA]

Although the "external lookup field" on the new form builder looks promising, you need someone who knows JSON and an environment that allows for it.  But unfortunately I can't get anyone's time on the dev or IT team...so I'm on my own.  I wish there was a way to just link to an Excel file or CSV to get an external list (also so it can be easily maintained by non-tech folks).  Why does this need to be so complicated with JSON?

[gagiksukiasyan]

Thanks for the feedback Sarah!
We are looking to find ways to simplify the selection of the JSON path. 

As this is an API integration field, we need to have some way to understand which value from the payload we should display as a dropdown option. The best way to provide a configurable and powerful way of selecting the options from the payload is the JSON Path. 

There is this https://jsonpath.com/ website which helps to find the right JSON Path for the JSON payload returned by an API, but it still may require some technical background. 

We will see if we can find ways to provide easier way to accomplish this functionality.

[AutumnEa1]

Hi, Gagik!

Any workaround users have found for the issue above with a CSV/excel file? I am trying to create a custom field that 

that uses a fusion scenario to update the field values to be dynamic from a CSV file instead of an API call for this data won't be available until end of the year. 

[gagiksukiasyan]

Here are some options that might help:

Option 1:

*Note: I don't know how reliable this platform is; I found it with a quick search.

Use this public web app https://jsonserve.com/ that allows to create a JSON and serve it with API. For example, you can open it and put this JSON:

[
  "A",
   "B",
  "C"
]

The save it will give you a URL, something like: https://api.jsonserve.com/qkrgHV

When you access this URL, it will return the JSON you saved.

You can create an External Lookup field with this JSON Path: $.[*]

Option 2

You could utilize native Workfront tasks as items in your csv file.
You need to create a configuration project—let's call it a control project—and add tasks to it. 
In this case, we can use such API call:
$$HOST/attask/api/task/search?projectID=<you control project's ID>

And use JSON Path: $.data[*].name

I hope this helps.