I have several SharePoint lists: booking calendar, hot desks and location.
The calendar has hot desk as a lookup column (linked to the hot desks list). The hot desks list also has a lookup column (linking to the locations list).
When I add a calendar item I have set up a workflow task to retrieve the location from the hot desks list (based on which hot desk was chosen) and populate the location field in the calendar.
That works fine, but I want to limit the list to those in a particular location (which is the filter in the calendar), so hot desks in other locations don't show - this is due to the fact there will be a 'hot desk 1' in more than one location.
Alternatively, is it possible to display the location in the dropdown when adding an item (i.e. Hot Desk 1 (Location Name) rather than Hot Desk 1. How can I do that (if possible without having to write C# code (i.e. just using SharePoint Designer))?
Edit:
If code has to be written, how do you filter the list before the form is shown to the user (i.e. is there an event fired, e.g. 'OnFormShow', 'OnListShow' or similar)?
Edit 2:
Cascading fields, while useful, are not quite what I am looking for as the add/edit form is opened from a calendar which is already filtered, and I only want to show the hot desks appropriate for that calendar view.