3

I know this has been discussed here and other sites, and I understand how to get a calculated column to display info. But my situation is a little more frustrating.

I have two lists. List A is for Sessions. It has two views: Awaiting approval, and the default All Items. Everything goes into the Awaiting Approval view right away. Only after an admin assigns Date and Time does it move into All Items.

The relevant columns for List A:
-Presentation Title
-Date and Time
-Calculated column that displays the Presentation Title and Date and Time together.

List B is the form where users can sign up for available sessions. It has a lookup column that displays the calculated col from List A.

This all works fine, except the lookup col on List B displays every session. Views do not matter. Is there any way I can get a lookup that only displays 'All Items' from List A?

1

There is a way to trick SharePoint into doing what you want (if I understand what you want correctly).

In List A, create another calculated column that only has a value when "Date and Time" has a value. Otherwise, it should return blank. Something like:

=IF(ISBLANK([Date and Time]),"",CONCATENATE([Title]," - ",TEXT([Date and Time],"mm/dd/yyyy h:mm AM/PM"))

In other words, if the field Date and Time is blank, return an empty string, otherwise return your title and date/time (I think that last formula will work - use whatever you are currently using).

When you use this new calculated column in a lookup, SharePoint will filter out the blank values and only show those that have values as options in the lookup dropdown list.

Is this what you are trying to accomplish?

  • I about had kittens over here this worked (well, mostly)! It filters out the items without a Date and Time beautifully. Now I need to figure out how to filter the ones that have already expired. :) – Raquel Jul 11 '17 at 14:21
  • That will be more difficult because formulas are only evaluated when the list item changes. Some options might be a Powershell script that evaluates the date and fills in the lookup field, and run it on the scheduler OR use SPServices.js and it's SPFilterDropdown function to do it in your forms with JavaScript. – mannaggia Jul 11 '17 at 17:56
0

Try SharePoint Filtered Lookup Field. Description:

This project creates a custom SharePoint lookup field that offers new functionalities to default SharePoint lookup field by allowing filters to be applied to retrieved data. Applied filters can be either dynamic CAML queries or pre-defined list views residing in source lists

Below is a few of the features offered by Filtered Lookup field over standard SharePoint Lookup field:

  • Cross-site lookup (all sites within same site collection)
  • Filter retrieved data using list views
  • Filter retrieved data using dynamic/ad-hoc CAML queries. This means you don't need to create a list view each time you want to apply a lookup filter to source data
  • Supports retrieving data recursively using either list views or dynamic queries Supports MultiLookup with filtered data
  • Same look and feel as default SharePoint Lookup and MultiLookup (i.e. in list forms)

Check the second feature: Filter retrieved data using list views.

You have the component from different SharePoint versiones:

http://sp2010filteredlookup.codeplex.com/

http://sp2013filteredlookup.codeplex.com/

http://filteredlookup.codeplex.com/ (2007)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.