Browser Forms

Re: Dropdown list options working in form preview but not in SharePoint list

Hilary Stoupa — Thu, 19 Jan 2017 16:16:41 GMT

I'm sorry you ended up talking to yourself here - but glad you posted & included your solution! Glad you got it figured out. :)

Re: Dropdown list options working in form preview but not in SharePoint list

bkwhodgson — Thu, 19 Jan 2017 16:05:37 GMT

So I eventually realised the error was that my conditions in the substring-after formula were the incorrect way around - I of course needed to use substring-after(username(),"\") - and now everything works.

Re: Dropdown list options working in form preview but not in SharePoint list

bkwhodgson — Thu, 19 Jan 2017 15:57:20 GMT

On further investigation the root cause of the problem is that function username() in the preview just contains a person's username whereas the published form contains i:0#.w|domain\username.

Unfortunately using a substring-after function also doesn't solve the problem.

I have added a new text box called current user and I need to make the value of this box the current user's username (without claims/domain prepended) - but I get the following strange results:

substring-after("\",username()) returns:

- In preview mode: nothing (since there never was a backslash)

- In published mode: nothing

substring-after("i:0#.w|domain\",username()) returns:

- In preview mode: nothing (since there never was a backslash)

- In published mode: i:0#.w|domain\username

Why wouldn't substring-after work correctly with the username() function????

Re: Dropdown list options working in form preview but not in SharePoint list

bkwhodgson — Thu, 19 Jan 2017 10:03:12 GMT

OK so it looks like my screen shots haven't worked - they were mainly used as proof of what I'm describing

Dropdown list options working in form preview but not in SharePoint list

bkwhodgson — Thu, 19 Jan 2017 10:01:08 GMT

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