Newbie Questions

Re: Help needed creating a filtered dropdown

Larry W. Virden — Fri, 04 Apr 2014 12:21:01 GMT

I tried to reply to this message once before but the system appears to have "eaten it".

I wanted to post a reply that described my current approach to solving this problem. I really appreciate the help provided by so many of you. There is a "thinking in InfoPath" that has to occur which is difficult to achieve. I won't say that I am there now - but my brain is slightly more warped around that pattern than it was earlier.

The final solution includes at least these steps.

1. I removed the lookup columns from my custom list and replaced them with single lines of text.

2. I implemented the lookup/drop downs via controls on the InfoPath form.

3. I created data connections to the lists that were the source material for the dropdowns.

4. I created drop down boxes, and pointed them to the data connections. The initial drop downs load their data when the form starts. However, the cascaded drop downs do NOT do that

5. Within the initial drop downs, I point them to the appropriate data connection for their source. I set the display name to be the column the users want to see (which in my case was a calculated column that glues data from other columns together). The value that I have the drop down return is the column value - not the ID - of the item selected.

6. I created a rule with 2 actions on the initial drop downs. The condition is "when the value changes". The first action is to copy the value to the query field of the cascaded control's data connection that is the corresponding column. The second action is to invoke a query on the second data connection. This results in the second data connection's result set being those rows that match the value obtained from the first drop down.

7. In the second drop down, I made certain that the populated data connection was selected as well as the value that I wanted to be returned and the column containing the display name to use.

This works really slickly.

I think those are the relevant parts to the proces.

Thank you all so much.

Re: Help needed creating a filtered dropdown

Hilary Stoupa — Thu, 20 Mar 2014 17:39:53 GMT

Hi - you can walk through the data connection wizard for the data connections to change whether something runs on load - it is in the last dialog in the wizard.

I think for your filter you want this instead:

d:Department_x0020_Code = xdXDocument:GetDOM("CAS Department Numbers and Names and Divisions")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Department_x0020_Number[../d:ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Sponsoring_x0020_Department]

So, for the first drop down in the filter, you can select Department Code, then the second drop down is "is equal to" and in the third drop down, select Use a formula and paste in:

xdXDocument:GetDOM("CAS Department Numbers and Names and Divisions")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Department_x0020_Number[../d:ID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Sponsoring_x0020_Department]

Re: Help needed creating a filtered dropdown

Larry W. Virden — Wed, 19 Mar 2014 18:34:30 GMT

I don't understand how to "[p]opulate that [i.e. the department code to be obtained from the first list, using the ID from the first list] into the Cost Code query.

My first control is called the Sponsoring Department lookup. It is a drop down single selection control. It displays all rows from the Department Numbers and Names and Divisions list. I have that control configured to use for display name the title column (which is the department name), but to use the ID as the value (as was recommended previously in this thread). No filtering was done. The Entries path is: /dfs:myFields/dfs:dataFields/d:SharePointListItem_RW.

When I test this control, I see the department names as expected.

My second control is intended to show rows from the Departments Cost Codes and LAP Codes list, filtered into just those with the department selected from the first control. It is a single selection dropdown.

Here's what I tried but it is not working.

The correct data source, that points to the list I mention, is selected. The entries says this:

/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:Department_x0020_Code = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Sponsoring_x0020_Department]

To get that, what I did was select the d:SharePointListItem_RW from the dataFields portion of myFields.

Within the Filter Data dialog, I selected the Departments Cost Codes and LAP Codes data source, opened the d:SharePointListITem_RW dataField,

and selected the Department Code parameter.

I selected the is equal to condition.

I selected the Main data connection dataFields > my:SharePointListITem_RW > Sponsoring Department

When I try it, nothing appears in the drop down.

Since that is the case, my guess is that in my limited understanding, I selected something incorrectly here in the filter.

However, I have read various articles and still don't understand exactly what the correct thing to select is, if this is wrong.

I hope someone can help me understand this better.

Also, how do I control what datasources run when the form opens? There are 4 lookups in this form and 3 of them need to go at start up and one, which is filtered on the selected department code, would only go once.

Re: Help needed creating a filtered dropdown

bigbarryc — Tue, 18 Mar 2014 14:26:15 GMT

If the ID is returned then you can populate that in a query on your datasource. Get the Department code. Populate that into your Cost Code query then it will only return the values that exist with that Department code. I would only have the datasource for Sponsored department run when the form opens. Start the query for each of the other items after the Sponsored Department is selected. It's mor efficient than filtering on the entire datasource and gets you better results.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Tue, 18 Mar 2014 12:39:24 GMT

Okay, I am confused at this point. Let's use the Sponsored Department lookup as the first example. If I have it returning the ID, then I have no idea how I am going to get the value that I need in the Cost Code control - because it needs the Department Code value of the row that was selected in the Sponsored Department lookup. Obviously I am confused about how this works. I expected to have the return value of the Sponsored Department control be a department number, which I would use in the Cost Code lookup control. In fact, this morning several of the problems I was having are not exhibiting themselves (and I didn't work on things yesterday much other than creating the screenshots for you). There is an odd behavior though. I selected a department for sponsoring, and the cost code filtered out the cost codes used by that department. It did not display them sorted like I thought it would, but it did display them. I selected a department from the Other Department control as well. Everything looked good. I saved the item, and it saved rather than complain. I was really happy. Then, I looked at the list in which the newly entered item was placed. What I see is what I entered on the form. I have attached a screen shot of the two different views. I wonder how I fix this without breaking something else that is working.

Re: Help needed creating a filtered dropdown

Hilary Stoupa — Mon, 17 Mar 2014 22:59:38 GMT

Thanks for the screenshots. In general, a lookup column will store the ID of the item in the list we are looking up on. So, that is the first thing I'd make sure of in the form - that in the drop downs for the lookups, the ID field is the Value in the drop down properties.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 18:01:48 GMT

I really appreciate your help. I hate that this particular situation seems so complicated. Part of the issue is how to summarize the list and infopath info concisely. I apologize for not just uploading the stuff initially. I had hoped that it was a common issue that others had solved years ago and that I as a newbie was running into for the first time. I have gathered a series of screenshots from the list associated with the infopath that I shared earlier in this thread. The doc holding these screenshots shows all the columns and their types, as well as the edit column information for the ones labeled lookup. The zip file containing that document is attached to this message.

Re: Help needed creating a filtered dropdown

Hilary Stoupa — Mon, 17 Mar 2014 15:28:04 GMT

I should have been more clear.

The list this form is bound two has these two lookup columns. What are the settings on this list for the lookup columns? If you go to edit the columns, can you take a screenshot of those pages and attach under options?

If I recall correctly, a lookup will always store the ID of the item and then the list settings will determine what it will display.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 11:45:05 GMT

never mind that last post on the now(). Someone here just pointed out that the field name on the now rule was not the same as the rules on the text field I was working on. They are named too similar, unfortunately. It was a start date field and it indeed does have that out of the box rule.

Sigh - monday morning, and I don't drink coffee. Maybe I need to start.

I would delete that last posting if I could - i don't see an option for that on this forum.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 11:37:15 GMT

I am looking over all the settings this morning, and have a question about something odd.

As far as I know, I have one rule in all of this app so far.

The rule inspector shows this:

Validation

Proposed x0020 LAP x0020 Code: "Rule 1"

conditions

Proposed x0020 LAP x0020 Code != "" and

contains(Proposed x0020 LAP x0020 Code, string(" "))

Actions

ScreenTop: "This field cannot contain string(" "))

Calculated Default Values

LAP x0020 Code x0020 Start x0020: now()

Actions

None

Programming

None

The first part - the "cannot contain spaces" part - is something that I have been intentionally working on.

I don't understand that calculated default values. Is that saying that the control is going to put the current time in this field?

If so, how would I go about changing that? I open the properties for the field - a text box - and there is nothing in the default value field. There is a validation of Cannot be blank checked. There is a limit of 20 characters placed on the field and a left alignment. I fiddled with the size to make it look close to 20 characters.

I am not certain where the now() comes into play.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 11:26:50 GMT

Oh - you also asked about sponsering department's lookup.

Sorry - just noticed when your message was redisplayed.

Sponsoring department is a single selection dropdown on the department name column of my department numbers and names list. This lookup not only has department name column but also includes the department number column. In this way, it can use the number of the name selected in the lookup. It has the enfoce relationship behavior - cascade delete turned on.

The department numbers and names list has 3 columns right now (If I can figure out how to use the calculated field in the other dropdown, I'd like to do something similar for this list).

The columns are:

Department Number - a single line of text

DepartmentName - a single line of text

Division - a single line of text

The rest of the settings are similar to the Cost Code list, other than I use a standard view rather than a dataview because I didn't think of it when I created the list.

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 11:18:36 GMT

Per your question about the "source" that my filtered dropdown is using.

It is a custom list called Cost Codes. It has:

Department Number - single line of text

Cost Code - single line of text

LAP Code - single line of text

Department Number Cost Code and LAP Code - a calculated column that glues together the other 3 columns into a formated single field.

The default view is a datasheet view of the first 3 columns.

In a perfect world, in my single selection dropdown lookup column, I would like to display the calculated column and, when the user selected an entry, have as a value of the control/field the value of the Cost Code column of the list row.

The Cost Code list is pretty simple. No content approval for submitted items, no versioning on the list, no management of content types, users are allowed to read all entries, create and edit all entries, no attachments are permitted, no new folder command, results can appear in searches, items can be downloaded (so far - I haven't thought of the ramifications there), items are edited using the data sheet, forms launched in a dialog.

No validation settings, no rating settings, no audience targeting, automatically managing column indices, no view settings, form customized.

I don't plan on creating other views on the Cost Code list myself - the users who will be maintaining it can create additional views if they wish to have them.

Does that cover what you were asking about?

Re: Help needed creating a filtered dropdown

Larry W. Virden — Mon, 17 Mar 2014 11:09:35 GMT

You asked what the settings were on the two source lists. I am not certain to what settings you are interested.

Just to make certain that I have updated this thread appropriately - I have tried a variety of suggestions from various sources, trying to get a cascaded filtered dropdown to work from an infopathified custom sharepoint list.

From time to time the template seems to work, and then doesn't work as I try to resolve issues.

Today, it doesn't display the filtered options.

Last Friday, once I select something in the filtered dropdown, the selection was cleared immediately.

I am back to the drawing board on things this morning.

Thanks!

Re: Help needed creating a filtered dropdown

Hilary Stoupa — Fri, 14 Mar 2014 19:53:10 GMT

Okay, so it is a SharePoint list form, that is good info to have. I know on LinkedIn I was suggesting you drag things over onto the form from the secondary data sources - in an SP List form, you first need to click "Show advanced view" in the fields pane on the right.

Sponsoring Department and LAP Cost Code are both lookup types - what are their settings in the SharePoint list?

Re: Help needed creating a filtered dropdown

Larry W. Virden — Fri, 14 Mar 2014 19:41:43 GMT

So one helpful soul suggested that I attach the infopath form here so that they could see where things are at. So I just did. I don't know how much use it will be to anyone though. Here are the columns for the sharepoint list for which that infopath is intended. Column (click to edit) Type Required Activity Name Single line of text LAP Code Requestor Person or Group Checked Sponsoring Department Lookup Checked Sponsoring Department:Department Number Lookup Other Departments Lookup Other Departments:Department Number Lookup LAP Code Description Multiple lines of text Checked LAP Code Start Date Date and Time Checked LAP Code Finish Date Date and Time LAP Cost Code Lookup Checked Proposed LAP Code Single line of text Checked Existing LAP Codes Lookup Other Departments:Department Number Single line of text Created By Person or Group Modified By Person or Group