I'm trying to set up this solution for creating a filtered lookup: How to make a filtered lookup field

I have set up an ItemAvailable column like this:

The column "Availability" is a choice between "Out of stock", "Available for order" and "Ready for pickup". Now if I apply the changes initially the field ItemAvailable is empty in the correct places. But when I change the status of an item to "Available for order" and then back to "Out of stock" the strange numbers show up as you can see here:

I have no explanation what this number could mean, any ideas? When I change my formula to for example: =IF(Availability="Out of stock","123",Item) then everything works fine. How is this possible?

  • I cannot see anything wrong with your formula and cannot reproduce this issue in 2010 nor O365. Do you have any workflows or scripts operating on this list? – Dave Paylor Jan 29 '14 at 20:12
  • There was a workflow running that would set Availability to "Out of stock" when the column "Stock" went <= 0 and to "Available for order" when >=1. But I just disabled that and the error still occured. – user22307 Jan 29 '14 at 20:24
  • Try creating a new list with just those fields you need for the formula and see if you get the same problem. My guess is that it is something specific to this list. – Dave Paylor Jan 29 '14 at 20:53
  • I just created a new list and it shows exactly the same error. The number is always the same, but I have no idea where it comes from. i.imgur.com/xdmrO4U.png – user22307 Jan 29 '14 at 21:01

I was able to recreate this issue. The fix is to have an empty space in the formula between the double quotes i.e. the formula should look like this:

IF(Availability="Out of stock"," ",Item)
| improve this answer | |
  • Thank you for your answer, sadly I need the column to be truly empty so that when I use it as source for a lookup the empty columns are ignored. If there is a space in the column the lookup counts it in. – user22307 Jan 30 '14 at 6:27
  • Testing again they don't show up in the lookup after all, so your fix works! Thank you very much! – user22307 Jan 30 '14 at 6:36
  • Nice to hear that! – Nadeem Yousuf-AIS Jan 30 '14 at 6:37
  • Glad you got it sorted and nice workaround Nadeem. Odd error though - I wonder if it is related to the latest updates in O365? – Dave Paylor Jan 30 '14 at 8:59
  • I reproduced this error in my SharePoint 2013 VM. – Nadeem Yousuf-AIS Jan 30 '14 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy