Typeahead field on form, populated from lookup of external data source; selected value executes query | Community
Skip to main content
M
Mylah_D
Level 4
October 19, 2020
Solved

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

  • October 19, 2020
  • 4 replies
  • 4630 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by gagiksukiasyan

    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

    4 replies

    Doug_Den_Hoed__AtAppStore
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    October 19, 2020

    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

    M
    Mylah_DAuthor
    Level 4
    October 19, 2020

    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
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    October 19, 2020
    My apologies, Mylah: Freudian typo; I’ve corrected it above to be doug.denhoed@atappstore.com. Regards, Doug
    G
    Adobe Employee
    gagiksukiasyanAdobe EmployeeAccepted solution
    Adobe Employee
    September 27, 2023

    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
      Community Advisor
      Doug_Den_Hoed__AtAppStoreCommunity Advisor
      Community Advisor
      September 28, 2023

       

      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_d 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

      G
      Adobe Employee
      gagiksukiasyanAdobe Employee
      Adobe Employee
      October 2, 2023

      Hi Doug,

       

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

       

      Thanks,

      Gagik

      William--
      Community Advisor
      William--Community Advisor
      Community Advisor
      September 29, 2023

      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

      If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
        ewanh
        Adobe Employee
        ewanhAdobe Employee
        Adobe Employee
        November 2, 2023

        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--
          Community Advisor
          William--Community Advisor
          Community Advisor
          November 6, 2023

          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.

          If you like my content, please take a moment to view and vote on my Idea Requests: https://tinyurl.com/4rbpr7hf
            SarahWilkersonCA
            SarahWilkersonCA
            Level 7
            December 6, 2023

            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?

            G
            Adobe Employee
            gagiksukiasyanAdobe Employee
            Adobe Employee
            December 14, 2023

            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.

            AutumnE
            Adobe Champion
            AutumnEAdobe Champion
            Adobe Champion
            July 25, 2024

            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. 

            Terms & ConditionsAccessibility statement

            Loading footer...