File Browse Input has Column Name Trailing Space Trimmed on Server Only, Not Designer

[CharltonFranklin]

I have a workflow where the user can input their own file then select which fields they'd like to append. The analytic app asks which field in their file is the account number. The option they select is what joins the data together and dynamically queries the database. This was working perfectly until someone chose a field from their file that unknowingly had a trailing space in the column name "Column ". When they choose that field on the server, that field is trimmed or at least appears to be based on the following message:

I'm pretty certain this is the root cause because when I remove the trailing space from the column it works. Also, when I use debug in designer, this issue doesn't occur. Although I would describe this as a bug, I'm wondering if anyone has an idea for a workaround. This tool is intended for non-data users, and I don't want them to have to think too much about the details.

I'm attaching a simplified version of the workflow. Since it uses a dynamic SQL query, you won't be able to run it, but you can see the construct.

[gabrielvilella]

I recommend having a dynamic rename tool as early as possible in the data stream to get rid of any leading/trailing white spaces from field names.

[CharltonFranklin]

Thanks for your suggestion @gabrielvilella . That could be a good idea, but do you have any idea of the implications since that dynamic rename would need to be connected to other analytic app tools? I've had situations in the past where certain tools wouldn't work as inputs on the server.

[gabrielvilella]

It all depends on what you are looking for in the workflow. If you could please share the workflow or at least a screenshot of the area you need to adjust the field names I will be able to help you. 

[CharltonFranklin]

@gabrielvilella I tried the Dynamic rename, but I can't use the file browse as an input to a magnifying glass input for an dynamic rename tool.

By the way, my workflow is attached to the original post. Here's a screenshot:

The person selects their file in #1 and then has to select a column from their file in #2, but by the time it gets to #3, the value is trimmed and the formula in #3 is supposed to be based on a field in the file.

[gabrielvilella]

I see what is the issue now, thank you for the explanation. Have you tried just inserting a Trim() in the formula within the action tool? Like this:

if [#3] then
Replace([Destination],'Account_Number',Trim([#1]))
elseif [#4]
then Replace([Destination],'Account_Number',Trim([#2]))
else [Destination]
endif