2

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?

4
  • 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? 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. 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
2

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)
5
  • 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! 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? Jan 30 '14 at 8:59
  • I reproduced this error in my SharePoint 2013 VM. 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