Question

Expression to join two fields seperated by a .


Badge +1

This might be super simple, but cant seem to find a way to make it work, I have this expression:

 

"{{ticket.ri_5_cf_employee_first_name}}.{{ticket.ri_5_cf_employee_last_name}}"

 

when testing, this produces the desired output:

Result: tim.dodd

 

testing the expression does not produce a syntax error, but when trying to save the node, we get the below error of:

 

Invalid Syntax - Invalid operator found.

 

If anyone has any pointers of how to get this to work, it would be greatly appreciated.

 

 

 


13 replies

Userlevel 7
Badge +13

Try this 

if(1==1,"{{ticket.ri_5_cf_employee_first_name}}.{{ticket.ri_5_cf_employee_last_name}}","")

 

 

Userlevel 6
Badge +10

Hi Josh

you can always use concat too.

concat('{{ticket.ri_5_cf_employee_first_name}}', '.{{ticket.ri_5_cf_employee_last_name}}')

Badge +1

Thanks both of you for the speedy responses, unfortunately both of these are erroring for me on save but both do work on testing giving the correct results:

 

This code:

concat('{{ticket.ri_5_cf_employee_first_name}}', '.{{ticket.ri_5_cf_employee_last_name}}')

give the error:

Invalid Syntax - Missing ).
 
Although wouldn't it need an additional open bracket to require another close bracket?
 
This code:
if(1==1,"{{ticket.ri_5_cf_employee_first_name}}.{{ticket.ri_5_cf_employee_last_name}}","")
 
gives this error:
Invalid Syntax - Operands are mismatched.

 

Userlevel 6
Badge +10

Might be wrong but check the top of your expression is a string and not a number?

 

 

 

If that does not work maybe deleting that expression workflow and re-adding in a new expression box. 

if that does not work can you send a screenshot of the Whole expression builder workflow with the error code?

Badge +1

So the output is set as string, and I've created a new test workflow to try to add the same expression again, it's also getting the same errors:

 

 

With both of these the test does not produce the syntax error only when trying to commit the node clicking done do we get the error.

Userlevel 6
Badge +10

Hi Josh, 

 

i see what you mean now, i never saved the code as i did not need it, but i got the same error message… Odd.

However, i added a second concat to seperate the Full stop ‘.’ and that seems to have fixed the issue.

so i think it might be due to the full stop working as an operand. 


Here is the code that worked for me.

 

concat(concat('{{ticket.ri_5_cf_employee_first_name}}','.'),'{{ticket.ri_5_cf_employee_last_name}}')

Badge +1

 

 

Amazing thank you for this its working now!

 

Just to add a wrinkle here as we have had it with other expressions, this all works for 99% of names, but we have some names' mine included that will break the expression as we have users with ‘ in their names.

For instance, your above code works but only when the name is formatted without the ‘ in O’Brien

once its included we get:

Invalid Syntax - Missing ).

 

The fields are from our new starter form that is a free text field for the managers to type the new starter's name, is there a way of sanitizing the field to remove these sorts of symbols?

No worries, if not we will just have to do some coms on how format names when submitting the form.

 

Userlevel 6
Badge +10

I’ve just been working on this myself!

I’ve used the PowerShell app before any expression to remove the Apostrophe’s and spaces (as the “Van Persie” of this world would also not work in the Sam Account Name.) Things like Hyphens should be fine.

The below code should put it in the format of Uppercase first letter and lowercase rest of name. 

so someone typing o’brien should return


OBrien

 

 

$name = "{{ticket.ri_70_cf_first_name}}"

$name = $name -replace '\s',''
if ($name.Contains("'")){

$pos = $name.IndexOf("'")
$leftPart = $name.Substring(0, $pos)
$rightPart = $name.Substring($pos+1)
$rightPart = $rightPart.Substring(0,1).ToUpper()+$rightPart.Substring(1).ToLower()
$leftPart = $leftPart.Substring(0,1).ToUpper()+$leftPart.Substring(1).ToLower()
$result= $leftPart+$rightPart
$result
}
else {
$name
}

Badge +1

Ah this is great i will give this a go and presumably we can with a little adaption use this to sanatize the JSON parser output fields as they always come through with ‘[josh.obrien]’ which ends up causing fails on a bunch of the azure workflows

 

Userlevel 6
Badge +11

Hi @afautley  

As you all know FS workflow Expression nodes are very particular about formatting and they will only accept single quotes  ‘   in their expressions.

The reason your nested CONCAT worked (nice solve btw!) was bc CONCAT will only accept 2 values and that period (‘ . ‘ full stop) is considered as the 2nd value so the CONCAT errors when it sees the parameter (unacceptable 3rd value).

 

Hi Josh, 

 

i see what you mean now, i never saved the code as i did not need it, but i got the same error message… Odd.

However, i added a second concat to seperate the Full stop ‘.’ and that seems to have fixed the issue.

so i think it might be due to the full stop working as an operand. 


Here is the code that worked for me.

 

concat(concat('{{ticket.ri_5_cf_employee_first_name}}','.'),'{{ticket.ri_5_cf_employee_last_name}}')

 

Interesting puzzle!

Bryn :-)

Userlevel 6
Badge +10

Hi @BrynCYDEF 

yeah had to think about it to solve it but got there in the end!

confusing thing was the in my first try it only had 2 parts with the full stop at the very beginning of the second part.(highlighted below)

 

concat('{{ticket.ri_5_cf_employee_first_name}}', '.{{ticket.ri_5_cf_employee_last_name}}')

It worked when clicking the test button, but failed when clicking save??? not sure why it worked on the test but failed on the save button, surely the test is suppose to check for this sort of thing??

Anyway code works now, i just put it down to the full stop causing an issue…………...

 

 

Userlevel 6
Badge +11

Yeah seems like the test should fail if it won’t ultimately let you save your work!!!

I found that moving the full stop into the 1 segment produced the same error and I RTFM about CONCAT and realized that, unlike most CONCAT, you cannot have more than 2 values.

 

concat('{{ticket.ri_5_cf_employee_first_name}}.', '{{ticket.ri_5_cf_employee_last_name}}')
 

And if you remove the full stop, this does work and will save so…………...

Badge +1

I’ve just been working on this myself!

I’ve used the PowerShell app before any expression to remove the Apostrophe’s and spaces (as the “Van Persie” of this world would also not work in the Sam Account Name.) Things like Hyphens should be fine.

The below code should put it in the format of Uppercase first letter and lowercase rest of name. 

so someone typing o’brien should return


OBrien

 

 

$name = "{{ticket.ri_70_cf_first_name}}"

$name = $name -replace '\s',''
if ($name.Contains("'")){

$pos = $name.IndexOf("'")
$leftPart = $name.Substring(0, $pos)
$rightPart = $name.Substring($pos+1)
$rightPart = $rightPart.Substring(0,1).ToUpper()+$rightPart.Substring(1).ToLower()
$leftPart = $leftPart.Substring(0,1).ToUpper()+$leftPart.Substring(1).ToLower()
$result= $leftPart+$rightPart
$result
}
else {
$name
}

 

Thanks for this was playing around with it this evening, when running via PowerShell it looks like the output name was formatted correctly but when done via the PowerShell node in the workflow it seems to be introducing a trailing space to the field.

 

I'm not well versed enough on PowerShell scripting to see if this was introduced via the script, but I've resolved via a trim expression on the result of the script execution, but just wasn't sure why it was getting introduced in the first placed.

 

Thanks again, this has all been very helpful.

Reply