InfoPath Dev

Hi all, I'm in REAL need of some help!
I've been banging my head against this issue for a day now, and I'm getting nowhere fast, so I'm afraid I have to bug you guys!

What I want to achieve is this:

A list of software.
A list of software versions.
A list of servers.

A table that relates a server to a software version for lookup in the form.

And I want to be able to pull this in to an InfoPath form using an SQL connection. But more importantly, I need to be able to write this information back to the database once completed. I have had minor success in this, but I always hit one or more snags, and it is REALLY starting to drive me mad.

The form is a form-filler only form (no web, I have set this in compatibility)

My SQL Tables look like this:

Table: Servers || ServerID, ServerName
Table: Software || SoftwareID - SoftewareName
Table: SoftwareVersions || SoftwareVersionID, VersionName, SoftwareName
Table: ServerSoftwareVersions, ServerID(Related to Servers:ServerID), SoftwareVersionID(Related to SoftwareVersions:SoftwareVersionID)

The InfoPath form itself will firstly have a Repeating Table (which loads from the DB when the form opens). From here, the user can add new software or software versions if they wish. The next section of the form will be a repeating section, with a table inside it containing information for each server. Within that, there will need to be a repeating TABLE, listing all software versions associated with that server (this might sound complicated, I can post a screenshot if needed). Now, I have kind of got this working, but not in the way it really should, or I really want it to. The major issue I have is, if I add the ServerSoftwareVersions table as the Primary Connection, then add any of my other tables as a child, they do NOT show as repeating sections. Meaning I can't add the SoftwareVersions section as a repeating table within my Servers repeating section.

Confusingly, if I add the Servers table (or any of the others) as the primary, and then the ServerSoftwareVersions as a child, then they both show as repeating, but I can't add my other tables as they do not directly relate to the Servers table...

If I do try to add relationships for all the tables and add them in correctly (at least, I think I'm adding them in correctly, with the linking table as the parent and the others as children), I get an error telling me about many to one relationships :/

I'm clearly doing something insane here, could anyone help me? I've run around in circles trying to find a guide on how to do this correctly, but all I can find is information about reading from SQL servers, and they only show one (or two, but never more) table at a time.

Any help is much appreciated! I can send screenshots to help clarify anything I have described if needed!

Thanks in advance,

Rich

 Edit: It seems that my entire issue is the inability to submit to databases that have a one-to-many relationship. Could anyone advise as to how I can achieve this in InfoPath? Thanks!

So, it seems to me that you only need to submit to Software and SoftwareVerions out of the 4 tables listed, is that correct? And then the Servers & Software tables are to be used as lookups? Or can the user also submit new Servers / new ServerSoftwareVersions? That is, do you need to submit to the Server tables or are those just displayed for informational purposes?

 I'd really prefer the ability to also add new servers if required, as well as software and software versions... So, all three really, with the ServerSoftwareVersions table as a kind of "Master" table to tie the whole thing together, It seems as though my entire issue is the inability to submit to the database if you have one-to-many relationships in the table structure (which I clearly do). I have found a reference by someone called S.Y.M. Wong-A-Ton on Youtube who has referenced a way to do this, but unfortunately I can't actually find a guide on how this can be achieved :/

 The Software and versions tables could technically be flattened in to one table, I'd just have one column with the same data repeated a few times, which isn't the end of the world if it is required!

Edit: I actually meant many-to-one relationships here. If I reverse the binding (to be one-to-many) it lets me add two tables, but obviously I can't add more as they don't relate to the first one I added :/

Many to many is the problem. You could create a dummy table to connect them - something that is just an auto incrementing ID field. Add that field to your other tables so you can relate them to your new "parent" table. Does that make sense?

 OK! I have kind of got around this now, but I have hit a new issue! I'll try and explain:

• I am creating a monthly check list form.
• Some controls in the check list need to be pervasive (these are being saved to SQL) and need to be populated when a fresh form is opened.
• Some controls are only relevant to the month in which the form is being filled, and need to be empty the next time a fresh form is opened.

The fields that are being pulled from SQL are fine, and these are working well. My issue is that I now want to add the non-pervasive controls (created within the form) to a repeating section created from one of the pervasive groups.

So, my design looks a little like this:

• d:Servers (SQL Master Table)
• - d:SoftwareVersionsOnServers (SQL Child Table)
• my:PerServerCheck (created from the form)

So currently, if I check one of these boxes, it checks it in every server of the repeating servers section, and I only want it to relate to that one instance of the repetition. I know I can achieve this by adding fields to the root Servers table, but I want these controls to be NON-pervasive, so they need to be fresh every month and for ease of use it would be better if I could add checkboxes in future without having to edit my DB tables.

Does anyone have any suggestions? Would be much appreciated!

Richard

So... I'm a little confused. Are you also submitting the completed XML someplace? Otherwise your "non-pervasive" control data will just be lost at which point it wouldn't make any sense to fill it out, right?