Skip to main content

Hi,

As I see the lookup function in the form only works if the column to filter on and/or(?) value to filter on has a letter in it. 

I need to use it to find duplicate numbers in a single line of text field. 
Is there a way to make it work with numbers as well?
Example list with all column single line of text field.

IDbreakfast
2001apple
2002coconut
2003peach

lookup("Example list","ID",ID,"breakfast") 

It wont throw error, simply not finding anything.

I've run a test on Forms for O365 and it seems to work.

Is it anything to do with your ID column? Are you actually using the ID column and is your reference pointing to a control or a property? I've created a SLOT column for my lookup list ID and then a Choice control that contains the same values and I'm able to return results as shown above.


I use this construct on SP 2013 on-prem regularly without any problem.

I would as well suggest to focus on "ID" reference an its value, maybe it holds a value you do not expect to.

it might as well happen that real problem is at some other place but browser's parser points you to this formula.

it quite usual in case of syntax errors, eg. if you miss or have an extra bracket, apostrophe, coma, semicolon, etc.


converted discussion to question.


Its not a drop down, but a single line of text field where I add the number. It can be any number so I cant make it selectable. Can you check if it works for you that way?


I use it regularly as well, but this is the first time I need to lookup numbers only.

If I add any letter it works. But in real life scenario user wont add letter, its just a "all number" ID field.

Its a single line of text field, it shouldn't  "add" anything else beside what was written in it. 


Ok, If the ID in your lookup list is a SLOT column then I can confirm I have the same issue. Adding a character at the end of the ID makes it work, but just numerics do not work. Changing the ID column to a number column works.


Great, will check it!


ok, I think I understood your setup now,

the reason of problems is unpleasant 'feature' of calculated value control that every input convertable to a number automatically considers and converts to number.

see similar case here  https://community.nintex.com/message/34514?commentID=34514#comment-34514 

in this particular case it causes that nintex sends wrong datatype in CAML built for lookup call (number instead of text).

you can enforce to take supplied value of ID reference as string/text eg. by a following formula.

lookup("Example list","ID",toLower(ID),"breakfast") 

Excellent response


thanks happy.png


For me, MRF

lookup("Part","PartNo",toLower(PartNo),"Description")
toLower() is not working. Since PartNo column contain number and string , I can not mark this column type to Number. 

I find my problem, toLower() is working, Since I remove the "000" in string


Reply