Many to Many Relationships

[Dave Newington]

I have a small database with many to many relationships where the
intersection entities have no data other than the relevant foreign keys. I
would like to know if it's possible to query records in the "secondary" table
using an InfoPath form. I.e. An Application runs in many Sites, and a Site
has many Applications running there. I will be initiating the queries using
either of the two tables as primary. I don't mind having two forms, one for
each direction of search.

If possible, I would like to query, for example, which sites are running a
selected Application, and if a Site is missing from the list, select the
relevant Site from a drop-down list to link it to the Application.

 

[S.Y.M. Wong-A-Ton]

Taking your last example: You can create a form that is bound to the
database. Then when setting up the tables for your Main data connection, add
the applications table first and then the "join table" to the sites as a
child of the application table. Set up the relationship between the two via
the application id in both tables. Once you've done that, you can drag
controls to your form. For the sites side: Add a secondary data connection to
the sites table. Then you can convert any text field showing the id for a
site on your form into a drop-down list box and bind the drop-down list box
to the secondary data source, so that you can select the sites from a list.
Note: The sites must exist beforehand (pre-fill the sites table), because you
won't be able to add them using this form for the application.

It's quite difficult to explain, so if you did not understand something, ask
away.