I am using SharePoint 2013 (on-premises) and InfoPath 2013 to customise a list form.
First I need to explain the configuration of my various SharePoint lists that I'm using for lookups.
On my parent site I have a list called "Teams" (a custom list with 1 column entitled "Title" which is single text). I also have a site column which is a lookup to this list.
On my subsite I have 3 lists that come into play for this query.
- My "Main" list for which I'm customising the form.
- A Team Categories list which contains two fields - a Title field and the site column called "Team".
- A Staff Info list which contains a persons username (single line of text) and the site column that looks up a person's Team.
What I'm trying to achieve is that when a user opens the form on the main list, a field called Team ID will store as its default value a person's team (ID) by looking up the person's username against the current user's username() function. This bit works - for me it populates the Team ID field with the value 7 - which is correct - that is the ID of my team from the Teams list
The Team Categories list has several "categories" that are applicable to my team - I want a dropdown on my main list to show the categories that are available to a person's teams. So anybody in my team will see values A,B,C in the dropdown list, somebody in another team will see values X,Y,Z. Please note a person can only belong to one team in the Staff Info list.
This was previously working fine when the Teams list was on the same subsite as the other lists, but a requirement arose for that list to be moved to the parent site so it could be used in sibling sites of the current subsite. Now the problem I'm experiencing is that when opening the form to the main list in SharePoint there are no entries available in the dropdown list that is supposed to show the available team categories. However when I preview the form the correct categories are visible.
Here's details of my form:
I have a field called team category which I want to display a filtered lookup from another SharePoint list. This field is a lookup field - as a result a data connection was created to the list to which it is looking up data. But on the field's properties I changed the data source from the automatically created data source to a new data connection to the same lookup source. The entries in this list are set as follows (which I believe to be correct)
There is a filter as follows:
The "Team" column refers to the Team column that is in the first screenshot. The Team ID column is from the main data source.
The Team ID column has the following default value:
To explain the default value - the formula above looks up a separate SharePoint list which contains user information - retrieves the team column (which is a lookup therefore the ID is retrieved) but the result returned is filtered as per below where the username column in the Staff Info list matches the username() function in the form:
I believe my formula to be correct given it works in preview. I believe the problem to be the fact that the form's two data sources are to lists that lookup a site column from a parent site - previously the data sources had a lookup to a list on the same subsite.
Can anybody please advise if this is a known problem and suggest a workaround / resolution?
Thanks,
Ben