Calculated Drop Down field with values from another List based on a condition

Regular Visitor

Hi All!

 

I have a question regarding calculated fields. I have a created a calculated field that populates drop down options based on items from another sharepoint list if they meet certain conditions. The use case is the following:

- List 1: It's a list with candidates for potential hiring, it has multiple columns with differente data and one specific calculated column that populates a drop down with the positions that are currently "Open" for hiring in List 2.

- List 2: Contains all positions that have been opened for hiring, some are "Open" and some others are "Closed" becuase the positions have already been covered.

 

The problem is that if a candidate from List 1 had an Open position selected from the drop down but later on the position was Closed in List 2, the candidate form in List 1 gets bugged and cannot be opened. 

 

I hope I have explained this well enough. I would really appreciatte some help here on how to work around this.

 

Thanks!

0 Replies