MikeCrawford
Nintex Newbie

Nintex Community - Is this form possible?! Need data structure help please!

Jump to solution

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:

lists3.PNG

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):

Prices.PNG

Or split it into each rate value? (144 different options)

price2.PNG

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!

 

 

0 Kudos
Reply
3 Replies
v-tmasenko
Nintex Newbie

Re: Nintex Community - Is this form possible?! Need data structure help please!

Jump to solution
The best data structure is the following because a List lookup can only contain one column (in this case Price)
Company, Job, Level, Rate, Price

So you're going to have a 4 level deep List Lookup hierarchy. The challenge is the When property in a List lookup can only refer to one column.

Consequently, you need to use concatenated calculated columns in the SharePoint list to maintain the hierarchical context across multiple columns.

1) Simple List lookup for Company with Show Only Unique values enabled

2) Filtered List lookup for Job with Show Only Unique values enabled and When Company equals the text from the previous List lookup

3) Create a hidden calculated field called CompanyJob with the definition:
=[Company]&"|"&[Job]

4) Create a form variable called CompanyJob with the value:
[Form].[Company]+"|"+[Form].[Job]

5) In the List lookup properties for Level:
- In the When property use the hidden calculated column CompanyJob (created 2 steps prior)
- In the Equals value property use the form variable CompanyJob (created in previous step)

6) Create a hidden calculated field called CompanyJobLevel with the definition:
=[Company]&"|"&[Job]&"|"&[Level]

7) Create a form variable called CompanyJobLevel with the value:
[Form].[Company]+"|"+[Form].[Job]+"|"+[Form].[Level]

😎 In the List lookup properties for Rate:
- In the When property use the hidden calculated column CompanyJobLevel (created two steps prior)
- In the Equals value property use the form variable CompanyJobLevel (created in the previous step)

9) Create a hidden calculated field called CompanyJobLevelRate with the definition:
=[Company]&"|"&[Job]&"|"&[Level]&"|"&[Rate]

10) Create a form variable called CompanyJobLevelRate with the value:
[Form].[Company]+"|"+[Form].[Job]+"|"+[Form].[Level]+"|"+[Form].[Rate]

11) In the List lookup properties for Price:
- In the When property use the hidden calculated column CompanyJobLevelRate (created 2 steps prior)
- In the Equals value property use the form variable CompanyJobLevelRate (created in the previous step)

The method for extracting the Price could made to look cleaner by setting the field to hidden and a rule to extract the text value into a simple read-only text field.

View solution in original post

0 Kudos
Reply
kunalpatel
Automation Master
Automation Master

Re: Nintex Community - Is this form possible?! Need data structure help please!

Jump to solution
0 Kudos
Reply
MikeCrawford
Nintex Newbie

Re: Nintex Community - Is this form possible?! Need data structure help please!

Jump 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.

Thanks all!

0 Kudos
Reply