cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Automation Master
Automation Master

Square Brackets around Display Name in Lookup

Jump to solution

Hi,

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. 

Thoughts? Solutions?

Labels: (2)
Tags (1)
0 Kudos
Reply
9 Replies
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump 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 

If(not(isNullOrEmpty(lookup('StoreLocations','Title',parseLookup(StoreLookup),'Manager'))),userProfileLookup(replace(parseLookup(lookup('StoreLocations','Title',parseLookup(StoreLookup),'Manager')),"[0-9]*]",""),"PreferredName"),userProfileLookup(replace(parseLookup(lookup('Approvers','Title','Procurement','Name')),"[0-9]*]",""),"PreferredName"))

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.

View solution in original post

0 Kudos
Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

Would you be able to just pass the [blah.blah] through a RegEx to remove the brackets and ID?

 

Just thinking out loud, I haven't sussed this out entirely.


PS: I like your colour coding. Easy to read!

Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

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.

Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

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. 

0 Kudos
Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

That's what's I've done in my solution, thanks.

0 Kudos
Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

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.

0 Kudos
Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

Good point, for the purposes of this particular task, I only need a single manager so I'll strip off anything after the comma. I guess if I needed both managers then I could have two controls, one looking for the first manager (before the comma), the other looking for the second (after the comma) and have them hidden on the form. I know there will not be more than 2 managers at this time. Otherwise we're into Javascript to put them into an array and make the call, but I'm trying to avoid code options here as there isn't anyone technical to take over ownership of the form when I move on.

0 Kudos
Reply
Automation Master
Automation Master

Re: Square Brackets around Display Name in Lookup

Jump to solution

I would say to maintain the formula will not be much easier

0 Kudos
Reply
hpyogesha
Nintex Newbie

Re: Square Brackets around Display Name in Lookup

Jump to solution

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

Yogesh

Nintex workflow designer

0 Kudos
Reply