InfoPath Dev

Good day all,

I have spent the last 6 or so hours looking through forums, tutorials, and other examples and have not quite found a solution to this presumably simple situation.  Quick background: I am a software developer (but I get paid to be an HR Specialist) well versed in VB.NET, Java, C/C++, and know a little about C#.  I have very limited administrative privileges on this workstation, so I can only use the tools at my disposal.  As such, I have resorted to using Microsoft Access and SharePoint to provide solutions for various tasks.  I am used to designing forms and writing code behind using VBA to manage SP lists and having custom forms for users to interact with.  Unfortunately, the organization asking for help with a project does not want to use Access.  I tried to explore options, but without the ability to create custom web forms in SharePoint, I am turning to InfoPath (2007).

The current task that I am working on is creating a SharePoint connected solution for tracking workload for personnel in one of our directorates.  The directorate has many divisions, and each division has many sections.  There are certain tasks that belong to division.  An employee can only belong to one section.  The simplified database structure is (all lists on SharePoint):

Employee ( ID, Name, Section_ID )

Section ( ID, Section_Name, Division_ID )

Division ( ID, Division_Name )

Task ( ID, Description, Applicable_to_Division_ID )

Workload_Log ( ID, EmpID, TaskID, DatePerformed, TimeTaken )

As you can see, each table is related to another using its ID.  The reason it is setup like this is because there are many other facets to the project, but I am highlighting the tables needed for an employee to come to a central workstation (most are laborers who have very little experience with computers...most don't use one), pick their division -> section -> name (or someone else to be able to log for their specific section for all employees).  After going through a few designs with managment, they have settled on a form that has a drop down for Division.  When the user picks the division, it has a dropdown for Section, which was hidden by conditional formatting.  When the user picks the section, employee drop down shows up.  Then they pick the date performed from a calendar.  The Task drop down is also to be populated based on the division selected in the Division drop down. 

I have 4 DataConnections, one for Employee, Section, Division, and Task.  The Division drop-down is connected to the division data connection.  Section with section, etc

 I have tried to setup the section drop down by doing the following:
Setup the Division Dropdown connection by double clicking on the drop-down control
 - Change List box entries to "Look up values from an external data source"
 - Pick the "DIVISION" data source
 - Click on the XPath button and choose the "DIVISION" node (which might be wrong, it shows all of the fields beneath, but I cannot change value or display name without selecting this one)
 - Change Value to @ID
 - Change Display name to @DIVISION_NAME (yes, it is all caps in the list)

Do the same for Section, using Section, but go to filters while in XPath.  The filter looks like: "DIVISION_ID = cmbDivision"

cmbDivision is the drop down box for the DIVISION.  When I test this, I choose the division, the Section drop down appears, but it is blank.  If i take the filter out, the section drop down has all values, of course.  Could someone please help me understand how this works?  Thank you for your time.

[Edit]
More information:

Again, I am used to using Access to create forms.  What I would do to achieve this in access is create 4 combo boxes.  Call them cmbDivision, cmbSection, cmbEmployee, and cmbFunctionPerformed.  When the form loads, I would do:

Sub Form_Load()
  ' Other init code
  ' ...
  call checkDropDowns("")
End Sub

I would create a function called checkDropDowns that is set as the "After Update" event for cmbDivision, cmbSection, and cmbEmployee (passing its own name as a parameter) and set the Row Source accordingly.  In pseudo code:

Function checkDropDowns(whichBox as String)
  Select Case whichBox 
    Case "cmbDivision" ' Division was changed
      ' If nothing is selected in cmbDivision
      '   Hide the section and employee combo boxes
      '   Disable the other controls on the form
      ' Else  
      '   Make cmbSection visible
      '   Set cmbSection's row source to "SELECT ID, SECTION_NAME FROM SECTION WHERE DIVISION_ID=" & cmbDivision & ";"
      '   Set cmbFunctionPerformed's row source to "SELECT ID, Description FROM Task WHERE Applicable_to_Division_ID=" & cmbDivision & ";"
      '   call checkDropDowns("cmbSection") since the value may have changed
      ' End If
    Case "cmbSection"
      ' If nothing is selected in cmbSection
      '    Hide employee combo box
      '    Disable all other controls on the form
      ' Else
      '    Make employee combo box visible
      '    Set cmbEmployee's row source to "SELECT ID, LNAME & ', ' & FNAME & ' (' & SSN_LAST4 & ')' AS EmpName FROM Employee WHERE SECTION_ID=" & cmbSection & ";"
      '    call checkDropDowns("cmbEmployee") since the value may have changed
      ' End If
    Case "cmbEmployee"
      ' Similar logic, except all we do is enable all of the other controls on the form when cmbEmployee has a valid selection
    Case Else
      ' This should happen with the call in OnLoad and Reset, so initialize all of the row sources and hid section and employee
      cmbDivision.RowSource = "SELECT ID, DIVISION_NAME FROM DIVISION;"
      cmbSection.RowSource = ""
      cmbSection.Visible = False
      cmbEmployee.RowSource = ""
      cmbEmployee.Visible = False
      cmbFunctionPerformed.RowSource = ""
      ' disable the rest of the controls
  End Select
  ' Adjust control placements on form so there are no gaps
End Function

This is the functionality I am trying to mimic.  I am trying to get a screenshot uploaded somewhere, but it's having trouble.  I can provide more information if needed, but I am really struggling with this.  I will also continue to research today.

[/edit]

Screenshots:

The form in design view.  You can see that the dropdowns are bound to themselves, to store their value in itself

The properties for the Division drop down

The properties for the Section drop down

The filter for the Section drop down.  The employee filter is setup the same way, except SECTION_ID = cmbSection

The properties for the Employee drop down

The data connections. Each one is a UDCX in a SharePoint document library of data connections.  I created each one using InfoPath and converted it using InfoPath, then went to the document library and approved each of them.  (Again, I knew about none of these processes until reading about them yesterday, so I may be going about this the wrong way)

The preview.  When it comes up, Section and Employee are hidden since there is no value selected in Division.  The screenshot is what shows after I make a selection.  There is a section within the "Directorate Top Level" called "Top Level".  Each Division has at least a "Top Level" Section by default

PS Question:

Is there an underlying...code-behind that I could modify directly?  Something similar to VBA or VB.NET or C#. I read that I could modify the file generated by InfoPath directly in a text editor, but the only file I have is XSN, which appears to be byte code readable only by InfoPath.

I got closer, but am back to square one.  I started following this walkthrough: InfoPath form Services - implementing a Master-Detail on the web (which is geared at using C# to overcome cascading drop down issues with web-enabled forms.  My form does not have to be web-enabled).  I thought I was getting somewhere with adding the connection as XML to the lists that depend on the another list.  Unfortunatley, this walkthrough has you right click on a control and go to Programming -> Changed Event...  I thought I was just blind while I was looking for something similar to the VBA editor in the other Microsoft Office application, but it isn't there.  There is nothing in Tools -> Form Options... in the programming category for language.  So I googled that and came up with this explanation.  Aparently we have a group policy set that doesn't allow custom code.

As I have no administrative rights to this workstation, and our ... we'll call them "IT Department" is not going to change the policy.  In short, I work as a federal civilian on an Army installation and there are security concerns with enabling custom code in InfoPath, apparently. 

Sooo...still stuck.  I tried adding a rule to division to "Set a fields value" to set "DIVISION_ID" in the now XML connection to the SECTION sharepoint list (Set a field's value: @ows_DIVISION_ID0 = ."), followed by a "Query using a data connection: SECTION" and I get nothing in the SECTION box (no filter).  I also get an error when I change the value in cmbDivision: "Some rules were not applied.  The parameter is incorrect."  So, then I tried = ../my:cmbDivision.  Still nothing. 

I do realize that this is a common question, but I have yet to find an example or explanation of how to create a master w/ 2 children, one of which is in a second master-child type relationship.  To restate the situation: 4 SP lists.  Each of them populates a dropdown box.  I am requiring that the each box be populated in turn by showing/hiding them when a pre-requisite box is populated/not-populated.  When the 1st box is populated, it should filter the contents in the 2nd and 4th box.  When the 2nd box is populated, it should filter the contents of the 3rd box.  The rest of the form is disabled if there is at least 1 of the first 3 boxes that does not have a selection.  The point of this form is for users (employees with very little computer experience) to drill down to pick themselves (which I could do by filtering the employee list by current user in SharePoint), or for one employee who is tasked with entering the data for multiple employees to drill down and pick the current employee they are working on.  Then, when they submit for that employee, they should be able to use the already filtered employee list to quickly pick the next employee to enter data for.  The three tables are related like this:

1st (Division) table is a one to many relationship with the 2nd (Section) and 4th (Function_Task) tables.  The 1st table's key (simply called 'ID') is a foreign key (called 'DIVISION_ID') in tables 2 (Section) and 4 (Function_Task).  The 2nd table (Section) is a one to many relationship with the 3rd table (Employee).  The 2nd table's key (simply called 'ID') is a foreign key (called 'SECTION_ID') in table 3 (Employee).  When I pick a division, I would like to filter Sections and Function_Tasks that belong to that Division in their respective drop downs.  Similarly, when a Section is selected, filter the Employee list to only personnel in that Section.  This is purely for usability.  I have implemented a version of this that simply lists all of the employees alphabetically in the Employee drop down list without showing the other two lists, and all of the Function_Tasks available.  The user selects the Employee and the form controls are enabled (or, from the InfoPath perspective, the condition formatting rule to disable the control evaluates to false.)  They then enter the date that they are logging the workload performed for.  The function and time taken fields are part of a repeating table and the user selects the values for those fields, then adds a new row.  I am still trying to figure out how to calculate the total number of hours and minutes entered, but I am sure that will be done with a rule that updates the txtTotHrs and txtTotMins controls.  They have conditional formats to highlight yellow if the time goes over 8 hours in a day, and red if it goes over 10 hours (some employees work 4-10s, most work 5-8s, and there are some on 5-4-9.  When the user finally submits, the data in the repeating table will be (and I need to figure this part out as well, but have not even started researching if it is possible yet) pushed to one SharePoint list called "Workload_Log".  It will push the EmployeeID, Function_Task_ID, DatePerformed, TimeSpent_hrs, and TimeSpent_mins that have been entered. I'm thinking a Google search for "InfoPath submitting mutliple values to SharePoint list" will start me off in the right direction, unless anyone has a good resource for how to dump values from a repeating table to a single SharePoint list, but only when the submit button is pushed.  I have read something about submitting the InfoPath to the InfoPath form enabled document library and creating a custom workflow that consumes the data and pushes it to a table.  So, that might be the way that I go if I can figure it out.

Anyway, the resources that I have read through are:

http://blogs.msdn.com/b/bharatgupta/archive/2013/03/07/create-cascading-dropdown-in-browser-enabled-infopath-form-using-infopath-2010.aspx - Specifically geared towards accomplishing this using InfoPath 2010. 

http://www.sharepoint-tips.com/2007/01/infopath-form-services-implementing.html - Uses XML connection to the SP resource (which is a really nice approach), but requires the ability to write code-behind in InfoPath 2007.  I have found that if I create as much of the functionality as I can in InfoPath without the ability to write code, I can publish it to the SP site as a web-part and use it in a custom list form.  Then, I can use SharePoint Designer to modify the page markup.  Unfortunately, I know little about XSL, CAML, or how to use LINQ with this.  I am currently working through XSL tutorials and will be moving to CAML tutorials afterward.

http://www.bizsupportonline.net/browserforms/filter-cascade-drop-down-infopath-sharepoint-browser-form-owssvr-dll.htm - Same issue here. 

http://blueinfopath.blogspot.com/2008/11/cascading-drop-downs-in-infopath-web.html - The PDF location does not exist, but the comments have snippets of informatoin that I don't understand yet, but am sure will help when I uderstand XLST more. 

There are many other tutorials on cascading drop-downs, or master-child type boxes, but they do not involve relating two SP lists.  And the ones that do only show me how to code events in InfoPath.  I guess the next specific thing that I would ask about is how to modify the web-part in SharePoint 2010 to mimic the functionality I am trying to implement.  Again, I publish the InfoPath form as a web-part to the InfoPath form enabled library.  I have access to SharePoint Designer to manage the site that I have Full Control of, but I cannot make any changes to the SP server, such as adding qRules.  I can attach the (scrubbed) page source if needed.  I would greatly appreciate any assistance with this.  If I am being unclear, then please let me know what I can clarify.  If you have other approaches for accomplishing the same task, please let me know.  If you have resources that solve any parts of this that directly address my specific situation, please let me know.  Thank you for your time.

 Hi - I'm sorry, I tried to read all this and just got overwhelmed.

I take it from your screenshots that you are using InfoPath 2007 and SharePoint 2007 and your form is a browser form (which is why you are having issues filtering your dropdowns). Is this correct?

So - you are probably going to have to use an owssvr.dll data connection to your Sections and Employees lists and use code. I do not think there is a way around this without using some code or third party tools.

Good day,

 I do have the habbit of putting too much detail.  Hopefully this will be short:  I am using InfoPath 2007 as you have surmised.  The SharePoint server is SharePoint 2010.  I have Full Control of a SharePoint site, but I am not a SharePoint admin, nor do I have access to the SharePoint server. And, being a government employee...I cannot use third party tools that are not already added to our SharePoint server.

That being said, I have done as much as I can in the InfoPath template design: I created connections to Section and Employees using owssvr.dll data connection.  The Section connection url looks like: [URL_To_SP_Site]/_vti_bin/owssvr.dll?Cmd=Display&List={234F4EBE-DBAE-4D52-BB5F-F3F3CE043249}&XMLDATA=TRUE&noredirect=true.  I've tried it with the query string parameters in a different order.  I've also tried converting it to a SharePoint Data Connection.  In both cases, it worked to populate the lists, but I still could not filter the drop boxes since I cannot write code-behind.  When I use the owssvr.dll data connection, however, I get XML errors when trying to fill the form out using a browser. 

I am at the point where the form can be filled out, but the drop down lists do not filter, so instead, I just have the employee list, unfiltered.  The value is set initially as the currently authenticated employee, but they can pick other employees if they are the one who is to enter workload data for others.  I have the form set to disallow filling in using a browser so that I could get the Leave and Function task drop downs (connections to two differently filtered views of the same list) to populate correctly.  It is then submitted to a document library.  I now need to learn how to create a workflow that takes that data and dumps it into another SP list, called Workload_Log.  One record consists of EmployeeID - the ID for the employee the completed tasks are being entered for; Function_ID - the ID for which function is performed (a function_task is either a pre-defined line item, or a type of Leave, to include being furloughed, for example); the DatePerformed, and amount of time taken (TimeTaken_Hrs and TimeTaken_Mins).  the Function_ID, and amount of time taken are all fields in a repeating table, while EmployeeID and DatePerformed are from fields that are not in a repeating section.  If you have any insight as to where I start with this, I would gladly appreciate it, but I believe this is more of a SharePoint Designer question and wouldn't belong in this forum.  And I will certainly start my research on how to accomplish this. 

Actual InfoPath question, though.  When I connect to an SP list using the connection settings to connect to the SP List directly, I cannot use a calculated field as the display for a drop down list.  When I connect using owssvr.dll, I can select it as the display, but if the calculated field is a string concatenation, say: {last_name}, {first_name} ({SSN_last4}), it displays as "string;#{last_name}, {first_name} ({SSN_Last4})".  Is there a way to do string concatenation as the display name for a drop down list?  I know that there is a function concat(str1, str2) that does concatenation, but I can't manually type something like this in the properties for the drop down box.  I'll....stop typing now.

 Just to be completely clear:

There is no way, to my knowledge, to filter a drop down in an InfoPath 2007 browser form without using code.

HOWEVER - is there any chance you can personally move to using InfoPath 2010? Since you are publishing the form to SharePoint 2010, in InfoPath 2010 browser forms filtered drop downs are supported.

Yeah, calc'd columns look dreadful in IP. You can manually modify the view file if you feel adventurous and then you could use a concat formula. You have to:

1. Back up your form
2. Save your form as source files
3. Modify the view file in a text editor to add the formula for the display

I do greatly appreciate your response.  I would love to upgrade to 2010, and do have access to it on my personal computer.  Unfortunately this workstation is government issued.  They control the vertical and horizontal, so to speak:-).  I did find this: http://social.msdn.microsoft.com/Forums/sharepoint/en-US/d610bebb-1552-4a67-b52a-0b23ad7a1e8b/show-a-sharepoint-calculated-column-in-infopath2007 and was able to create the column and custom workflow.  That was my first workflow creation experience and it went rather well.  I do, however, feel quite adventurous and would love to modify the source files via text editor.  I found that if I publish the form and use it as a webpart in a list's "New Item", I could then modify the .aspx file.  Unfotunately, I do not recognize the format and syntax of the aspx file.  I read something about there being XSL and CAML.  I can certainly make "pretty" effects using javascript and writing .NET code that I'm used to, but I am quite used to using code-behind instead of inline.  Anyway...

Then, I decided to move forward with trying to create the workflow for when a form is submitted to the Document Library (user clicks "Submit" on the InfoPath template and it uploads to a Document library called "Workload_Input").  I knew this was going to happen but was hoping that quick searches would help me find the answer.  I have a top section (Employee drop down to get the Employee ID and Date Performed) and a bottom section (list of functions performed by that employee for the date specified.)  What I was hoping to do is write a workflow that grabbed the top data and for each item in the bottom area, add an item to the Workload_Log table with EmployeeID, DatePerformed, Function_ID, and Time Taken.  I've been trying to figure it out first by using SP Designer to see if it is intuitive.  Unfortunately, I do not see a way to loop through the repeating table.  My algorithm would be as follows:

• store the employee ID and date performed as variables in the workflow.
• for each item in the repeating table, get the function ID, time taken (hrs) and time taken (mins)
• insert a new item with the current fields
• continue until there are no more items

Again, I will do the research, but if you have any suggestions off the bat, I'd be happy to receive them.

[edit]Oh! I also didn't see an option for saving as source.[/edit]