1

i have two lists, list A creates events, list B creates reports based on events on list A.

I currently have a lookup field (in list B) that pulls the event name from List A, that way they can select the event they are reporting to. However, Since they are going to be doing hundreds of events in a 2017, they want to be able to filter the drop down list in list B to exclude those events that are 30 days and older from the event start date.

List A has Event Name, Event Start Date columns.

I feel like this is the answer (see link below), but i can't reference to a different list when using calculated columns.

How to make a filtered lookup field

Any ideas? Thanks!

BTW I'm using Sharepoint 2013 (Online)

Community
  • 1
nando
  • 149
  • 1
  • 7

2 Answers2

2

List A columns: Event Name, Event Start Date, Latest Event

Latest Event is a Calculated column whose formula is like below

IF([Event Start Date]>=[Today]-30),[Event Name],"")

List B columns: Event (Lookup field to List A's Latest Event). In this 'Event' column, non-blank values are returned.

Please refer this link, it might be helpful.

Divya Sharma
  • 1,461
  • 11
  • 26
  • Sorry, something went wrong Calculated columns cannot contain volatile functions like Today and Me. Also, Shouldn't it be IF([Event Start Date]>=[Today]-30,[Event Name],"") ? – nando Jan 27 '17 at 14:55
  • Use this link to get Today. This might be helpful. – Divya Sharma Jan 27 '17 at 15:03
1

As it is said in the link you provided, you have to create your calculated column in the list A that records the events. The calculated will be empty if the start date of the event is 30 days old, if not it will display the name of the event.

As it is said in the other post, you will then have to create your lookup in your B list and target the calculated column instead if the name field. This way if a field in empty as the condition is not met it won't be displayed in the lookup dropdown.

Samy M
  • 31
  • 5