locked
Filter Lookup Returning ID RRS feed

  • Question

  • SP 2010 Enterprise list forms modified in InfoPath2010, I have two relational lists, a Parent List and a Children list (literally), these are client records so they have an Active check box so inactive clients can be filtered out.  The Children list has a drop down lookup to select the parent that goes with the child, this works as expected.  I want to filter the lookup so it will only show active parents.  When I apply the filter Active = True, it filters as expected when creating or looking at active items.  The problem is inactive items, with the filter applied when you look at a children record who's parent is inactive the field returns the ID number not the parent name.  Also in the view page the with the filter applied the field still returns the name.
    Wednesday, March 9, 2011 3:11 AM

Answers

  • In Sharepoint form modified in InfoPath.  Here is a PDF http://www.bizserv.com/pics/Filter.pdf showing the forms and the List box properties from InfoPath Designer.

    Ok, then it makes perfect sense.  The form has no idea what the display name for 19 is, because you didn't retrieve that data.  You filtered out all inactive parents, so then the field is only able to show the ID.  InfoPath can only show data in dropdowns that it can "see."  Since you've applied a filter that strips out all inactive parents, the form then has no idea what name goes with 19 and thus only can show the raw, underlying data.  You are going to need to add a field to your form that stores the parent name separately (add an action rule to the dropdown that sets the parent name field to the name corresponding to the selected item), and then add this parent name field as a text box control adjacent to the dropdown.  Then, when they're side-by-side add mutually exclusive conditional formatting to the two fields so that one shows if Inactive, and the other shows only when Active.  That way, the user will always see the right thing and will never know the difference.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    • Marked as answer by JimBizServ Wednesday, March 9, 2011 4:43 AM
    Wednesday, March 9, 2011 4:32 AM

All replies

  • I can't understand what the problem is.  All lookups bind to the ID of the related list.  The display name is only for display purposes, and the real value is the ID, so it is expected that you see the ID as the raw value.  SharePoint itself is able to show the display name for user-friendly purposes, and InfoPath is also able to do this within the control itself, but the raw data is still the ID.  So, I understand the nature of your topic, but I don't understand WHERE you're seeing the ID.  This is what you wrote:

    "The problem is inactive items, with the filter applied when you look at a children record who's parent is inactive the field returns the ID number not the parent name."

    WHERE are you applying this filter and WHERE is the "field" that is returning the ID number?  What field and what interface are you talking about?


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Wednesday, March 9, 2011 3:58 AM
  • In Sharepoint form modified in InfoPath.  Here is a PDF http://www.bizserv.com/pics/Filter.pdf showing the forms and the List box properties from InfoPath Designer.
    Wednesday, March 9, 2011 4:21 AM
  • In Sharepoint form modified in InfoPath.  Here is a PDF http://www.bizserv.com/pics/Filter.pdf showing the forms and the List box properties from InfoPath Designer.

    Ok, then it makes perfect sense.  The form has no idea what the display name for 19 is, because you didn't retrieve that data.  You filtered out all inactive parents, so then the field is only able to show the ID.  InfoPath can only show data in dropdowns that it can "see."  Since you've applied a filter that strips out all inactive parents, the form then has no idea what name goes with 19 and thus only can show the raw, underlying data.  You are going to need to add a field to your form that stores the parent name separately (add an action rule to the dropdown that sets the parent name field to the name corresponding to the selected item), and then add this parent name field as a text box control adjacent to the dropdown.  Then, when they're side-by-side add mutually exclusive conditional formatting to the two fields so that one shows if Inactive, and the other shows only when Active.  That way, the user will always see the right thing and will never know the difference.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    • Marked as answer by JimBizServ Wednesday, March 9, 2011 4:43 AM
    Wednesday, March 9, 2011 4:32 AM
  • I was thinking that was the issue, just couldn't wrap my head around the answer. Thanks
    • Proposed as answer by havana59er Monday, March 21, 2011 7:25 PM
    • Unproposed as answer by havana59er Monday, March 21, 2011 7:25 PM
    Wednesday, March 9, 2011 4:42 AM
  • I had this same issue with a dropdown list and I found a great solution.

    1. Go to your Parent control's Properties -> Data Source -> Entries -> Filter Data
    2. Modify your filter condition.
    3. Add an OR condition for ID is equal to Parent.

    In XPath this OR condition would look similar to: d:ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Parent

    Essentially you are telling the filter, "Only retrieve Active entries, or the current entry."

    • Proposed as answer by havana59er Monday, March 21, 2011 7:38 PM
    Monday, March 21, 2011 7:38 PM