External Lookup does not return value if query has special characters

[simplykevinm]

I am trying to fetch the Program associated to a Project. The Project is selected using a typeahead and the name of the Project is used in the URL to fetch the associated Program. The issue is that the External Lookup field is not returning any value when the Project name has special characters. This is what I could deduce from what I could see. Any suggestions or leads are appreciated.

Project Name: Supplier for SAV+ OTV

Thanks in advance.

[Kurt_Jones · Accepted solution]

Hi simplykevinm,

I had similar problem using external lookup fields.  I had to create a calculated with url encoding to get around the issue.  You can use a website like urlencoder.org to get the appropriate symbols.  Mine involved Products and Product Lines so watch the flow below:

Created initial Product field I needed users to select (notice special characters in titles)

Next, had to create a calculated field to look at the field above to basically do url encoding so the next lookup field will be able to read it.  I used website urlencoder.org to input my title and it will spit out the formatting for you.

Finally, I created the Product Line field which took the selection from the first field (but was manipulated by the calc field) to show only Product Line items related to the Core product that was selected.  Note the API call and dependency is using the calc field I created above so it can be understood and pull the right info.  Hope this helps, it was a challenge for our side as well.

[Kurt_Jones]

One more thing....this won't test properly in the Preview Editor (it doesn't calculate), so I made test project with test custom form and my fields above to test and make sure all the right options popped up when core product was selected...but it worked.

[simplykevinm]

Thanks @Kurt_Jones for the detailed explanation. This does shed some light onto my problem but not fully. The field I am querying is Project Name and not a Dropdown Field Option Value and so it is not possible for me to write calculated logic for individual items. But with your answer I will try to replace the special characters (those that I can account for) with the corresponding encoded value.

I will post an update if I get it to work. Thanks Again!

On another note, do you suppose this can be internally handled by Workfront in the future? Because as far as I can see, using calculated field is a workaround rather than a permanent fix. Hopefully this will be addressed in upcoming versions of Workfront.