Our Senior Consultant, Chris Hanson, is up for this week’s Tech Tuesday on SmartConnect:
One thing I run across a lot is what happens when using calculated fields to manipulate strings within SmartConnect. Most of the time everything works as expected, but occasionally it’ll appear that the calculated field isn’t doing what it is supposed to when trying to run compare, concatenate, index, etc. commands.
What I am referring to is the situation where the string has padded characters on the end of it, so the data doesn’t stop where you expect it to. The biggest culprit is actually GP, almost any field you pull out of GP that is a string will have white spaces padded to the end due to the data types GP uses to store the data.
The easiest example is pulling out a first name and a last name from GP and trying to concatenate them together to send to another system or out to a file. If your source fields are named FRSTNAME and LASTNAME this is what your calculated field might look like if you are not aware of the additional spaces:
Return _FRSTNAME & ” ” & _LASTNAME
That calculated field would return something close to this result:
What should be done is a Trim statement should be applied to each field:
Return _FRSTNAME.Trim() & ” ” & _LASTNAME.Trim()
That would give us the expected space between the names without all the additional characters. The Trim statement should be used anytime you are not sure if there are trailing white spaces on the fields you are going to be using within a map or calculated field to ensure you are working with just the data you want.
Have something you’d like to see featured on our next Tech Tuesday? Email us at email@example.com!