InfoPath Dev

Hi there, Imagine you have a "student admin" form that lets you load students out of your student DB and edit their basic info (e.g. age, DOB - all fields in 1 row in DB) plus edit a repeating table of their courses along with teacher name, room number, etc.. The form has a search field at the top that you type a student ID into which will load the rest of the forms contents into view on an OnChanged event. What is the best design to your form to accomplish this as far as fields in main data source and helper datasources? Still not quite able to conceptualize things... if I was to have a field you can type a student ID into at the top to populate the rest of the form... should the ID be associated to a field in my main data source and I then query the DB using this as a param? Or is it better to have the field be a secondary data source and have it copy into the main datasource upon load? And would I need to do anything tricky for the nested group for my courses associated to the student? Not sure if this means 2 queries to fill out form completely, or it can be done with 1 query (assuming I have proper relational model in DB with PK-FK between student and courses)? Thx, Robin

 You know - here's how I decide whether something should go in a main or secondary data source - do I need to maintain this data? In your case, if student ID is a piece of data you need in the form's data - then use a field in your main DS. If it is not - use a helper 2DS (an XML file as a resource in your form).

I make some exceptions - sometimes, in a repeating group, for simplicity's sake, I end up adding a few fields or attributes I can use to take actions on other fields within the same group. If there is secondary data I'm consuming that is likely to change and I need to know what it was at the point it was loaded into the form, I put that in a main DS.

So - in this case, I'd probably have a view that relates students to courses in my DB, my student ID field in my main DS, and execute a query off the student ID field to get student and course info, then populate my form's main DS accordingly. If you are using DBXL & qRules, you won't need custom code for any of this.

"then populate my form's main DS accordingly..."

 Not sure what you mean by this?   What I have done is created a field in my main data source "StudentNum".  In theory I type a student num into this field then I will hit the search button and the data is entered into my form.  Various fields are editable and I have a save button at the bottom.  When I create a DB query, and nest my tables accordingly I see all fields show up in my form but they are in a secondary data source.  I am drawing a mental blank on this.. should I copy the data into duplicate fields in my main data source... or just load the 2nd data source fields into my form?  Also how do I load the 2nd data source manually, but for just '1' student number?   In code-behind I would edit my SQL and insert varibles into my SQL with string concat... not sure using the non-code behind approach what I should be doing?

My form in theory looks like:

Search for Student: [___________________]     [Search]

 -----------------------------------------------------------------------------------------------

Student Name: _________________________  (editable)

Student DOB:   _________________________ (editable)

Courses: (repeating table, all fields editable)

Course Name | Course Teacher |   Student's Average

rows 1-n, all editable.

[SAVE} [CANCEL]

I started trying to accomplish this with code-behind. I can get my main Student info loading properly by querying the DB and then setting value by parsing out the query results, but this is because I am just setting the value on elements. As soon as I start dealing with my "Courses" repeating table I keep getting XPath schema validation errors. If I delete all elements in my repeating table, should I not just be able to execute a command similar to below to append a child element? myNavigator.AppendChildElement("my", "test", myNavigator.NamespaceURI, "testval"); Thanks, Robin

Managed to get this working.  I was trying to appendchild and appendelement using a string and it wasn't working.  I switched over to XmlDocument approach and had a few issues, but managed to get it sorted out.  My approach is as follows:

 0. Navigate to my repeating table and remove all existing row items via XPath.

1. Get DataConnection of my DB, and manually execute query against it (recursive based upon nested RT).

2. Get result set in DataSource and use XPathNodeIterator to move over each node.  My SQL columns are attributes of the node (table).

3. For each iteration I create an XMLDocument and add nodes to this document.  Taking great care to check if a value is null and it is one of the key types (int, date, etc.) and apply xsi:nil="true" attribute.

4. I then SelectSingleNode on the group that contains my RepeatingTable and: 

repeatingTblOuterGroup.AppendChild(doc.DocumentElement.CreateNavigator());

 

About to test this with nested Repeating tables (e.g. my courses table, for each row, would have multiple "Marks" nested inside a repeating table).

 

R.

No sooner do I try to use nested repeating tables than the error message "Schema validation found non-data type errors." comes back!

Is there a way I can see what validation is failing on this error?  Even if there is a way I can extract the schema of my main DS and then manualy validate the InnerXML of my objects I am adding in an external tool..   it's not a very useful message in and of itself. :(

My only theory it is because i am blowing away the existing items in the child level and then re-adding all new group elements that it fails. 

For example: 

Main: 

 Fields: StudentName, StudentNum

Courses -> CourseEntry -> has fields CourseName, CourseID, CourseTeacher (e.g.)

This works.. I delete every CourseEntry and then re-add new CourseEntries to the Courses group as described above. But now I am trying to add in a marks group, which has Mark's Items in it. Each of these has fields for mark, datereceived, etc.

Now the error message is back.  Anyone have thoughts on this?

Update: 

I dumped my entire sub-table from my form and just created a "Group" element (TestGroup).  I manually add this TestGroup into the form as an empty node:

XmlNode group2 = doc.CreateElement("my:TestGroup", rtc.NamespaceManager.LookupNamespace("my"));

repeatingNode = repeatingNode.AppendChild(group2); //repeatingNode is my Parent repeating row item.

 

Event when it is 1 group, nothing in it, it fails.  What would it be about having a group element that cause it to fail?  Also, if i don't add it, the schema is fine with it missing the group.

Update 2: 

I have even gone as far as to save my form, then debug it and see my "TestGroup" entry and compare.

In form:  <my:TestGroup></my:TestGroup>

From Debugging: <my:TestGroup xmlns:my=\"http://schemas.microsoft.com/office/infopath/2003/myXSD/2013-04-13T21:13:22\" /> 

Opening up the form in wordpad, and swapping out the form's version with the version from debugging and then saving it, and the form still opens.  It only ever seems to be when I am programatically setting the value.

Rob

After some further debugging this seems to get even more odd of an issue:

1. I add a group and a field to my repeating table group.  "TestGroup", "TestField" with a value of blank initially.  If I try to add this group/field into my XmlDocument, it will cause theschema validation error to occur.  If I wait for that method to complete and my XmlDocument to be attached, then manually browse to each individual row item with an XPath query, I can add the element just fine.  Q. Is there some restriction on how many levels of XmlDocument you can add as a child?  I seem to see this error if I have more than 1 level.

2. If I try to "DeleteSelf" on my containing group, I am unable to re-add the group.  In theory I would "DeleteSelf" on my root group and just re-create the entire structure but it constantly fails.  Is there some restriction on adding a group back but it being empty?  (I am only adding the empty parent group to start vs. adding in fully structured XML).

Code:

 mainNav = mainNav.SelectSingleNode("/my:myFields/my:MyOuterGroup/my:ParentGroupName", this.NamespaceManager);
                mainNav.DeleteSelf();
                //re-add Prent Group node (now empty).
                doc = new XmlDocument();
                doc.PreserveWhitespace = true;
                group2 = doc.CreateElement("my:ParentGroupName", rtc.NamespaceManager.LookupNamespace("my"));
                doc.AppendChild(group2);
                mainNav.AppendChild(doc.DocumentElement.CreateNavigator());