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,
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!