I'm having a few issues using 3 cascading dropdowns
I'm currently working on a project where people fill out a form for an objective that they want to complete and it would be great if i could use dropdowns to link objectives between eachother.
First dropdown would be the type of objective to link to (Company wide, team, or personal)
Second would show departments filtered by first dropdown
Third would show the objective filtered by the first and second dropdowns.
I've had a look at Three-Tier Cascading Drop Downs: Do Go Chasing Waterfalls! and it seems to work great when all list items are static and can be created at the beginning. Keeping the first and second dropdowns static is easy, however all objectives will be unique so i won't be able to create a list beforehand.
Ideally I'd be able to set up a workflow that can populate a list similar to the link above with each unique objective, but I haven't been able to populate the lookup columns using workflow. This is the error message "Coercion Failed: Unable to transform the input lookup data into the requested type." when trying to populate a lookup column in another list. I can avoid the error message by setting column as single line text but that doesn't fix the dropdowns.
Solved! Go to Solution.
when creating items with lookup columns, you need to pass the lookup ID rather than the text.
So it might be
1 Company Wide
2 Team Wide
Then when you create a new item and need to add type of objective, you would pass 1/2/3 instead of Company Wide/Team Wide/Personal.
Does that make sense?
You can lookup the ID for the text you have in a query list action?
I've played around with lookup ID's so that the lookups in other lists get updated if the original list item was updated and that seems to be working fine. The issue I'm having is filtering between 3 dropdowns (objective type, department and actual objective)
Example: Someone wants to create a personal objective and they want to link it to their teams objective, their team is HR.
Ideally they could come in and select dropdown 1 (team), select dropdown 2 (HR) which has been filtered by dropdown 1 to only show team objectives, then the final dropdown is filtered by the above dropdowns and only shows current objectives that are (Team), and (HR) based.
After coming back to this project after a bit of a break I was able to solve it doing pretty much exactly what you said, so thank you very much for the input.
The only issues I ended up running into was some of the items in the pre defined lists weren't unique, ie, a sales team may be in multiple departments. This was solved by adding some extra filters into the query list workflows like you suggested.