List Lookup from Multiple Lists and Multiple Filters (Cannot Cascade)

kelliganp
Userlevel 4
Badge +10

Hi Folks,

I have an issue and I need a workaround. Normally, I would use a cascading dropdown for this but that is not working in my environment (see background posts here: https://community.nintex.com/thread/20079-having-cascading-dropdown-issues) for some reason and I will be working with Nintex Support to overcome that issue. I need to accomplish this before I will be able to get approval for a Nintex Update as I have a new contract kicking off that needs this feature ASAP.

Thanks for the help in the previous thread to get me this far Marian Hatala‌  Philip Warrak‌. Feel free to add here Cassy Freeman‌ as I have seen you contribute to similar issues.

Does anyone know of an alternative method to filter by multiple values? I must populate my main form with the contents of a list that has several records for each employee. It is a Time Bank so each employee has a record for the first contract period (base year or "BASE") and each subsequent option period (Option Period 1 or OP01, OP02, etc.). An employee will go into the leave form and either auto populate the current user or if they are submitting as a proxy, select the employee from the first lookup. Then, they will select the Contract period (CP). Each employee is provisioned with an allotment of leave and sick time for each contract period. When the CP is selected, I want the Available leave data to auto populate. Auto populate is figured out and working with a single CP but I need it to discriminate as to the CP.

I am not a great coder but with some guidance, I can muddle my way through some JavaScript if need be.

Nintex Forms On Prem.

Any thoughts? let me know if I need to clarify or load up with screenshots.

Regards,

Patrick

21 replies

MegaJerk
Userlevel 5
Badge +14

Screenshots, diagrams, all things visual would help immensely in trying to untangle what it is you're trying to accomplish. 


E
Userlevel 5
Badge +14

it's as well not very clear to me how exactly your filters and cascade should work...

but I wonder whether cascaded filters still couldn't be achieved with Web Request control.

it however requires you are on enterprise licence

Nintex Forms Enterprise - SQL Request and Web Service Request enhancement 

with web request control you could call GetListItems method of Lists.asmx web service. it would allow you to build a filter on your own out of other form controls.

Lists.GetListItems method (WebSvcLists) 

kelliganp
Userlevel 4
Badge +10

For more context, what I have is a leave form with a List Lookup Ccontrol that goes out to another site on the site collection and pulls an employye's information to populate the form (LLU 1in the first screenshot).

Next, I have a LLU control that will go to a TimeBank list and should offer a selection of TimeBank records for the chosen employee, one for each of the contract periods (BASE, OP01, OP02, etc.) so that we can populate the leave request with the employee's available leave time from the chosen time bank record. This control is LLU 2 in the screenshot.

The full name as seen in LLU 1 is also a field in the TimeBank records. The screenshot show what is SHOULD look like when the control is filtering correctly. I have discovered an issue with my original attempt at this and that was the fact that I was not filtering on a Lookup field in the TimeBank reaching back to the "tbl_Weserners" list where the employee is found. It seems that this was the real reason I could not use cascading drop-downs because the Lookup control cannot see lists on other sites.

Clear as mud? happy.png

Please let me know if anyone has any alternative options to accomplish the filtering for populating the leave data.

kelliganp
Userlevel 4
Badge +10

Thanks Marian Hatala! I will investigate this option and any others that get posted here. I tried to add some clarity on what I am trying to accomplish below. Hopefully that helps.

MegaJerk
Userlevel 5
Badge +14

You say in your second section that:

"I could not use cascading drop-downs because the Lookup control cannot see lists on other sites." 

But you can see lists on other sites so long as you specify them in the path as shown: 

216151_pastedImage_1.png


Did you actually mean Column? As in you cannot create a Lookup Column on your TimeBank List because the Employee Information List is on a different site? 


kelliganp
Userlevel 4
Badge +10

I mis-stated... I meant to day that the lookup field cannot reach into other sites. It appears that my second lookup control must filter on a field in the target list that is a lookup field pointing to the list used in the first list lookup control. Is that an accurate understanding?

The video tutorial (https://youtu.be/Xx5q31FZF9g?t=27) that I used to create this did not explicitly say that but when a colleague (Eudoro Espinoza) was helping me troubleshoot this, he notices the characteristics on the screen clearly showed that the field was a lookup field.

Sorry for the confusion.

MegaJerk
Userlevel 5
Badge +14

Ah. When people say 'Control' rather than 'Column' I tend to think that they are talking about the Forms Control and not the actual SharePoint Column. 

My bad. 

You might be able to create a List Lookup column even if the sites are different as long as they are descendants and not siblings. 

 

Root Site => Employee Info Site  => Time Bank Site = You can make a Site Column that is a Lookup and will work

Root Site => Employee Info Site

+ 
Root Site => Time Bank Site = Out of Luck sad.png 

To learn more about creating a Site Column that is Lookup, see: Create a SharePoint Lookup Column to another Site | Benjamin Niaulin's Blog  




kelliganp
Userlevel 4
Badge +10

Hi nmarples,

You wrote...

Root Site => Employee Info Site

+ 
Root Site => Time Bank Site = Out of Luck  

 That is me... our of luck. It is a sibling site.

Any alternative ideas on how to get this thing populated given my circumstances?

kelliganp
Userlevel 4
Badge +10

Hi Marian Hatala,

I looked at the web request control option and I think that it may be useful but I often see the weather example used but is there and example of how to set it up to go to a site within the site collection?

Thanks and regards,

Patrick

MegaJerk
Userlevel 5
Badge +14

Well... I mean, I don't know how you are generating the Time Bank list, but so long as you have a column that is populated with *some* data, you should be able to generate a Lookup Control on a Nintex Form that not only points to that Time Bank List, but also filters on whatever column you'd like, using whatever data you'd like no matter where you're getting it from on the Form. 

If you have a list @ Root Site/Sub Site 1/Employee List 

with the contents of: 

Employee Name
John Doe
Jane Doe

and a second list @ Root Site/Sub Site 2/Time Bank List

with the contents of: 

Data TypeEmployee Name
Data A

John Doe

Data BJohn Doe
Data CJohn Doe
Data DJane Doe
Data EJane Doe

Even though the two lists are not linked in any particular way, you should be able to set up two List Lookup Controls on a form (pointing to either list respectively), and use the value from the first control (pointing to the Employee List) to filter the second one (pointing to the Time Bank List)... 

If I were to make Two List Lookup Controls on a Form (no matter where it is on SharePoint), if I set the controls up accordingly: 

LLU EmployeeList:

216164_pastedImage_3.png

LLU DataList:

216166_pastedImage_6.png

It will filter regardless of whether or not there are actual Lookup Columns on the sites:

216167_pastedImage_8.png

216168_pastedImage_9.png

Does that help? 

kelliganp
Userlevel 4
Badge +10

Hmm... If that works o your site, it does not work on mine. I created a dummy site and made the a 3 list setup with list 1 (L1) populating the first llu control, L2 populating the second llu control and L3 hosing the form with the lookup controls.

Here in L2, I had to make the llu_FullName column a lookup column in order to get it working. I fought with this thing a while until a colleague noticed the difference between mine and the YouTube video creator's setup.

L3 has the following form...

This works but only with the lookup column. If yours works without, I am not sure why mine is different.

I have a 6PM call with the support team and will run them through the setup an perhaps they can see where I have gone off the rails.

I will add one update to this. I created a TimeBank version on the site with the tbl_Werterners list and I made the control work. It is not an optimal solution but it does work.

MegaJerk
Userlevel 5
Badge +14

What are you actually filtering by on your second control though...? 

Can you show us your options / properties for both of the List Lookup Controls on your form? 

kelliganp
Userlevel 4
Badge +10

Here are two test scenarios. The first one does not work and the second one does.

Scenario One:

L1 layout...

L2 layout...

L3 form layout...

LLU control 1 configuration...

LLU control 2 configuration...

LLU control 2B configuration...

Outcome is LLU control 2 shows all items unfiltered with multiple repeating values and LLU control 2B is blank...

Here is scenario 2... I added a lookup field to L2 pointing to L1 and pointed LLU control 2 to that field.

and...

Now it works and not only does LLU control 2 work but LLU control 2B works as well when pointed at the text field. It seems that as long as one control is pointed to a lookup field/column in the form, any LLU control point to any field on the same list will work.

Sorry for so many images but it is most clear that way.

MegaJerk
Userlevel 5
Badge +14

Don't be sorry for including information. The more the better, always and forever. 

So my last idea would be instead of using the value of the control in question, use a formula to produce a lookup value that returns the EmpID number which seems to be a unique identifier which you could use to filter on your second list. 

I'm not sitting at a place with access to nintex, but on your Second Control where it says "Filter available selection" and you have chosen "By Control's Value", you should be able to select "By Value" or something along those lines, and you will have the option to use a formula. 

You should be able to use the Runtime Functions of parseLookup and lookup to grab the value of that EmpID from your first list. 

Again... I'm not at my desk so I can't tell you if this is exactly right but it should look something along the lines of:

parseLookup(lookup(sites/MainCollection/SubSite|L1, FullName, parseLookup({llu_L1}), EmpID)‍‍‍)‍‍

the inner lookup function is pointing to a List called L1 that is at the path sites/MainCollection/SubSite

It's the Column it's filtering ON is called FullName

The value that it's filtering BY is the result of whatever you have selected for the control called llu_L1

The value it will RETURN will be the EmpID. 

That returned value (of EmpID) is then parsed by the outer function and used as the filtering value in the secondary List Lookup Control. 

Again, this is from the top of my head. I'll try to give an example tomorrow, but my biggest guess for why something isn't working comes down to the nature of using Strings as values. If there is an extra space somewhere or a return character that is hidden (because not all characters even show up to you as a human), filtering off of a string can be incredibly difficult and deceiving. 

Using something like Numbers, unique or otherwise, typically leads to better results (unless you're using Lookup Columns in SharePoint which you can't in this case unless you feel like moving your lists to exist on the same site). 


E
Userlevel 5
Badge +14

maybe this one could help https://community.nintex.com/message/64506-re-how-a-copy-list-item-attachments-to-a-folder?commentID=64506#comment-45400 

it's example for O365, but on on-prem the configuration will be just the same.

kelliganp
Userlevel 4
Badge +10

Hi nmarples‌,

I will try to dig into this. Just a quick check shows that the ParseLookup nor the Lookup functions are not options in the InlineFunction tab on of the Filter By Value box. Should it still work? Also, there is no option to dynamically insert named controls into that location.

Regards,

Patrick

kelliganp
Userlevel 4
Badge +10

I will check it out. Thanks!

kelliganp
Userlevel 4
Badge +10

Hi nmarples‌ and Marian Hatala,

I spent some time with Nintex Support and they concluded that my issue was in fact hierarchical. I needed to have TimeBank in the same site "branch" as the employee info (tbl_Westerners). I had moved the TimeBank over to that site so it was a direct sibling with tbl_Westerners and the list lookup worked. It is not optimal but for now, I will keep it there so I can move forward on some other issues I need to resolve.

Thanks for struggling with this!

C
Badge +16

Are you sorted now Patrick Kelligan‌?  Sorry I have been on holiday!

kelliganp
Userlevel 4
Badge +10

Yes! Good to go now Cassy Freeman‌. Thanks for the follow-up!

kelliganp
Userlevel 4
Badge +10

All set now Cassy Freeman‌! Hope you had a great holiday!

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings