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?

Improve this question
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?
    – Dave Paylor
    Commented Jan 29, 2014 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
    Commented Jan 29, 2014 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
    Commented Jan 29, 2014 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
    Commented Jan 29, 2014 at 21:01

1 Answer 1

Reset to default
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)
Improve this answer
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
    Commented Jan 30, 2014 at 6:27
  • Testing again they don't show up in the lookup after all, so your fix works! Thank you very much!
    – user22307
    Commented Jan 30, 2014 at 6:36
  • Nice to hear that!
    – Nadeem Yousuf-AIS
    Commented Jan 30, 2014 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
    Commented Jan 30, 2014 at 8:59
  • I reproduced this error in my SharePoint 2013 VM.
    – Nadeem Yousuf-AIS
    Commented Jan 30, 2014 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.