InfoPath Dev

 I have a situation that has me stumped. It is probably easy but I am a novice at InfoPath (2013). A form library will not work for this situation due to a number business requirements I am locked into a SP list. (Building the list form an XML file is also not an option)

Any help would be appreciated. 

NOTE: When I use "External" the external lists are in the same site just external to my main list 

GOAL: Create multiple cascading dropdown lists with same info 

Situation:

• I have a large SharePoint list that has a lot of information needed from "External"  lists
• There is a section that requires the ability to put in a series of data (business benefits" several times.
• Each of these has 3 lookups, Benefit area, Level 1 Org, L2 Org. Just these 3 would require 21 lookup fields in my list (20 is our max)
• I have other look ups so using lookups in my SP list is not really an option.
• So I created text fields and used an external connection to populate them. (Both display and value in these = "title" this gives me the title not the value in the SP list when submitted which is what I want

The ISSUE:

• the L1 Org and the L2 org need to be cascading lists
• Everything works fine for cascading if I use "ID" as the "value" as you would expect
• However this then writes the list ID to the SP list not the title
• If I change the value to = the "title" of course the cascading breaks.

Question:

Any ideas on how I can address this? 

Here are a couple pictures: 

This is the form 

 

This is the SP List

These are my connections:

 

 

 

 

Hi - your pictures aren't showing up for me - you can attach an image under Options in a reply. You can use the title for the value, but that will change your filters - maybe just attach your form and I'll give you a hand?

Thanks for the reply and offer to help! I can get the cascading to work but then I get ID numbers in my SP list. Of I can get no cascading and get the text in the text boxes. 

I can't get pictures to show but here are links to Dropbox 

https://www.dropbox.com/s/53ljiagld2abbti/List-sections.JPG?dl=0 this is the SP list

 https://www.dropbox.com/s/c5u6muqpasscoi7/Form%201.JPG?dl=0 this is the form

https://www.dropbox.com/s/wz9bvfkr3gbghj5/DataConnections.JPG?dl=0 this is the data connection

https://www.dropbox.com/s/z4fwu9n8yk6nwaf/BVPReplacement.xsn?dl=0 This is the actual form 

 

Okay, I get it now. So, your filter is just going to be more complex on the L2-Benefits-1 dropdown. The first two dropdowns stay the same in the filter condition - but for the third one, use a formula: xdXDocument:GetDOM("Business L1 Organization External")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ID[../d:Title = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:L1_x002d_Benefits_x002d_1] That formula is the ID from the L1 list, with a filter that Title = the selected L1 Title from the form. I've attached your form with this in place - give it a try and let me know if it works. Save the attached file locally, right click and select design to open in design mode. :)

 This is great! I never would have gotten hat. thank you so much. It is working for the most part and I suspect I will have to make multiple connections to the L1 list at least but thought I would ask.

As you say I have  seven L1's, L2's etc.  When I choose an L1 (say CEO) in the first list it filters the second list correctly. However when I go to the 2nd L1 in the list and choose a different L1 (Say Printing) the L2 list is still filtered for the CEO L1.

 Am I right that this is expected and the only way around it is to create multiple connections to the L1 external list? Or is there a "reset" that would still retain the data for the first L1 and let me change the second L1 - I suspect not but thought I would ask.

 

You have been way more helpful that I could imagine. 

 I figured it out - brain dead on my part.

xdXDocument:GetDOM("Business L1 Organization External")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ID[../d:Title = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:L1_x002d_Benefits_x002d_1] 

The 1, 2, 3 at the end need to be changed to 2 , 3 etc.....

Sorry and thank you so much again! 

Awesome, glad you got it!