I have a series of SharePoint lists that have various lookup columns, which all feed one large list that I would like to use form choices to return a particular value, but I need help with setup! Here are the basic lists:
So if every company had every level of every job, we would have 4x4x3 = 48 different entries.
However, to make this more complicated, each job has a different rate based on if the work is hourly, remote or offshore.
How should I set this up to be able to filter it to find the value based on Company, Job, Level, and Rate?
Should I do it this way with the values (48 different entries):
Or split it into each rate value? (144 different options)
Where Company, Job, and Level are all lookups to lists containing additional information and descriptions. And then, when that is all populated, how do I create a Nintex form with the ability to see the rate when I choose Company A, Ninja, Senior, Offshore?
The amazing @SimonMuntz showed this is possible by creating different lookups with the same name (https://community.nintex.com/t5/Nintex-for-SharePoint/Form-filtering-on-many-to-many-lookups-cannot-...) but this doesn't allow for 1-1 referencing of any particular lookup value (if a company changes its phone number, it has to be updated in many places, not just one).
THANK YOU, NINTEX COMMUNITY!
Solved! Go to Solution.
Thank you so much for this - I ended up doing something very similar by creating and concatenating a series of short codes for the various lookup columns, and the result is the same - it works! Also, on the lookup column setup on the final list (Rate), I was able to get it to bring back the "Friendly" version (text field) for the dropdown selection instead of the item Title. In my "real" environment, I had 29 jobs for 13 companies, but not every company had every job or level, so the Rate list had some 700 entries, which the form handles elegantly.
For those trying to do something similar, @SimonMuntz has a very elegant solution here for those able to create a SQL database: https://community.nintex.com/t5/Community-Blogs/Appointment-System-Solution-for-SharePoint-On-Premis...
For my stickiing point of not using lookups correctly, and the possibility of having to update a company's phone number in too many places should it change, I was able to reference a "Standard" company list, which can tie into a column for each of the lookup company entries.