My form has a calculated value control doing a lookup to a SharePoint List within an If statement as follows:
This is working however, when a value is returned true the result comes back like this
I've used the ParseLookup to remove the lookup "ID" prefixed to the result, but I can't understand why it's returned in Square Brackets.
Solved! Go to Solution.
Ok, so I have solved my own issue, but I'm absolutely certain that I've gone down a rabbit hole that was entirely unnecessary and that there's a simple solution out there. I also had to change the Manager column in the target list so that it returned the Account name rather than display name
This is my formula
Blue text is the first part of the IF argument checking to see if the lookup I want to do has a value in it (if not it will default to the lookup in green)
Orange text does a does a lookup to a list looking for the Manager of the store based on the parsed "StoreLookup" field and then trims the trailing ] off the manager login that is returned which appears to stop the userprofilelookup from working.
Green text does the same but from a different list.
Please, someone tell me there's an easier way to do this. The problem all stemming from my lookup returning  around the login name of the user it returns.
square brackets denote you got returned array of values.
I would say, either you have configured manager field to accept multiple persons, or lookup might match several rows in remote list.
Thanks Marian, this was the bit I was missing.
Unfortunately I do need the multiple users in the field as there can be more than one manager, so my convoluted formula to strip out the bits and pieces I need is required.
hm, I affraid your formula is not going to work if you simply strip off brackets,
if you get more then 1 values in the array, they will be separated by a comma. so if you strip off brackets it will break syntax of your formula.
at second, userProfileLookup() accepts just single user account.
I have same problem. I have two lists where one list has the dept column. and other list with two column 1. dept (lookup column from first list ) and 2. email id.
Now in the third list i have the Nintex form with dept field to get the dept and std apporvers which will look from the 2nd list.
I used the calculated value with lookup formula. and it is returning [ blah , blah, blah] .
Now how cal i split or remove the  and , (comma) and put ;(semicolon) instead of , (comma).
Thanks for your help
Nintex workflow designer