locked
Help with filtering a lookup field using a calculated field RRS feed

  • Question

  • I have two lists, one is "Courses" the other is "Inventory Items."  The Inventory Items list has a choice field labeled "Active/Inactive" with the two choices being "Active" or "Inactive." 

    In the Courses list, I have a field labeled "Related Inventory Item"s which is a lookup field that pulls the "Inventory Title" from the "Inventory Item"s list.  I am trying to have this lookup only display the active items for possible selection.

    To that end, I created a calculated field in the Inventory Items list.  The calculated field uses this calculation:

    =IF([Active/Inactive]="Active",[Inventory Title],"")

    However, it is not working.  It is showing both active and inactive items in the calculated column.  Can anyone tell me what I might be doing wrong? This seems like the correct way to do it.


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Thursday, March 21, 2019 6:29 PM

Answers

  • I was able to accomplish my goal by checking for the Active/Inactive status in the workflow. Basically,

    If Active/Inactive = "Active"

    update the field used as the lookup with workflow variable where the text string is held

    If Active/Inactive = "Inactive"

    update the field used as the lookup with empty string.

    This way the value can change from active to inactive.


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    • Marked as answer by run4it Friday, March 22, 2019 10:14 PM
    Friday, March 22, 2019 10:14 PM

All replies

  • Hi,

    Here are steps I have tried, have a check and it shows only Active Title values.

    1. Inventory List: Created below fields

    Calculated Field Formula: 

    =IF([Active/Inactive]="Active",[Inventory Title]," ")

    2. Course List: For field "Related Inventory Items" call in your calculated field from "Inventory List" as a lookup field with below settings marked in red.

    3. Add a new item from "Course List"

    Please mark it as answer if this solution helps you.

    Thanks

    • Proposed as answer by ME_77 Friday, March 22, 2019 7:02 AM
    Friday, March 22, 2019 7:00 AM
  • Hi, run4it,

    Just as ME_77 has showed, the if statement you use is correct. I directly use your statement with copy and paste. It works normally in my test list, only showing active items. For I am not able to reproduce you issue, please tell me how you set your columns.  Screenshots will be helpful. I will show my column setting below.

    Best Regards

    Jerry Xu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Friday, March 22, 2019 8:47 AM
  • I wish I could use this.  However, in my circumstance the "Enforce Unique Values" radio button cannot be selected.  Probably because I need to allow multiple values.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Friday, March 22, 2019 4:20 PM
  • I was able to accomplish my goal by checking for the Active/Inactive status in the workflow. Basically,

    If Active/Inactive = "Active"

    update the field used as the lookup with workflow variable where the text string is held

    If Active/Inactive = "Inactive"

    update the field used as the lookup with empty string.

    This way the value can change from active to inactive.


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    • Marked as answer by run4it Friday, March 22, 2019 10:14 PM
    Friday, March 22, 2019 10:14 PM
  • Hi, run4it,

    Thanks for your sharing. It will be a help solution for other community members having similar issue.

    Best Regards
    Jerry Xu


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Monday, March 25, 2019 1:23 AM