InfoPath Dev

I need to be able to generate a query that uses conditional criteria from two different tables (one a subsidiary of the other).

I have two tables :

Project Details 

            Project Id

            Project Description etc

            Area Id

Contact Details

            Contact Id

            Project Id

            Contact Name etc

I need to be able to be able to query on any of the combined fields .

Example Queries :

1) List all Projects (plus the Contact Details for the Project) where the Contact Id is 5.

2) List all projects that have an area Id of 5 and where the Contact Id is 2.

And so on .......................

I am using Infopath 2003 and I can not achieve this simple requirement.

Nor can I find any examples in books or on the net.

I have tried to do it through the form handling, through code behind buttons etc ............

Can someone please provide some expertise in this area?

I have created a main connection that has the two tables related together.

If I create the query fields from the main connection, then the query will successfully create the query on the main table (Project Details) and the Contact Details Query conditions will apply to the subsiduary table Contact Details.

I want to return back project details for all entries that have contact id 5 (and maybe some additional conditions on the main table).

If I just enter contactId 5 as the criteria, I get the entire main table returned and then just the contact ids that have 5.

I want the query to find the Project Details where there is a related contact id of 5.

SQL :

Select * from ProjectDetails where ProjectId in (select ProjectID from ContactTable where ContactId = 5)

Naturally the 5 comes from the form control value.

Thanks,

 Mike C,

Hi Mike.

From the look of your tables Contacts would actually be the main table since Project looks like a child of contacts and should have a key relationship via the Project ID.

Is this a database type form? Where it creates the query and data fields for you?  Is this using Access or SQL?  Have you looked at the Database Accelerator? Qdabra Database Accelerator Suite Version 2.1 - Developer It allows for more complex interaction with your table data than the standard InfoPath connection.

Typically if you query on a parent record you would see the related child records as well.  The other thing is making sure you are displaying the nested data properly.  I think the first thing to check is that your contacts should be the primary table.

Clay,

Thanks for your prompt reply.

I only provided a cut down version of the application. In reality, the Projects table has relations to many other tables and as such is the main parent.

Even so, I stopped and thought about making the Contacts the Parent .... but the problems are the same.

Trying to clarify the problem further .........

I have two tables related to each other   (Project Table and Contact Table).

This is a one to many relationship.

 A project can have any number of contacts.

Hence the table structure is:

Project Details 

            Project Id

            Project Description etc

            Area Id       

Contact Details

            Contact Id

            Project Id

            Contact Name etc

I need to be able to be able to return data from the Project Table and the related entries from the Contact Table.

There are basically four possible scenarios :

1) No criteria specified at all.

2) Criteria Specified for elements in the Project Table, but no criteria for the Contact table.

3) Criteria Specified for elements in the Contact table, but no criteria for the Project table.

4) Criteria specified for elements in the Contact Table and criteria specified for elements in the project table.

Lets concentrate on 4 ..........

I can set up the form to use Access, SQL Server, XML document published to sharepoint.

I could even (although I am not sure how to do this) generate the XML document and store the XML document in SQL server.

I can not (unfortunately) use web services. 

Unfortunately, I do not have full text searching available on Sharepoint (I am also told that this will not scale).

I am currently limited to using Infopath 2003 (although if I can show that I can use 2007 to achieve this then I can put up a case for that).

To try and get this going I am currently using an Access database and the form is connected to the database.

I have tried to use code behind the button to modify the queries but can not get this to work the way I want.

What am I really trying to achive :

I want to be able to optionally provide criteria for either table (or both) and that criteria then determines which Project table entries I return.

In point of fact, for my project,  I don't need to display the contact table entries.

I have set up the main connection with the Project table as the Parent and the Contact Table as the child.

I can successfully get the Project Criteria to qualify the Project Table.

What I can not achive is to get the Contact Table entries to affect which Project table entries are returned.

eg Return all Project table entries that have Area Id of 5 and have a related Contact Id of 5.

 I do not want to return Project Table entries that do not have an entry in the Contact table with a contact Id of 5. 

Any help would be appreciated.

 I am about to look at the Accelerator.

Thanks once again.