Newbie Questions

Re: How do I use one table to find records in another table when there is only one common column?

Hilary Stoupa — Mon, 18 Jun 2012 16:13:04 GMT

You should be able to add another table when you create your form off your SQL DB - Select the main table first, then you can add the other table and relationship - as long as all the columns are allowable for submit (IP has some restrictions there) it shouldn't be a problem. You'd mentioned:

I'm kind of lost on this one after trying a lot of things (adding tables to my main data connection had very strange results that I wasn't expecting at all).

What were the strange results? From your original post, it sounds like you build the form off the child table and not the parent?

Re: How do I use one table to find records in another table when there is only one common column?

SeanPT — Sun, 17 Jun 2012 06:04:46 GMT

Those look like some interesting tools and I will definitely be checking those out but this isn't quite what I'm looking for.

Basically I'm looking to do a JOIN on two tables and be able to submit to and query from those two tables in one single form.

Re: How do I use one table to find records in another table when there is only one common column?

Jimmy — Fri, 15 Jun 2012 12:02:54 GMT

Server-side filtering of DB data is a common issue that comes up in InfoPath form development and we have developed some approaches to allow people to do this without writing their own code.

qRules provides a command called ExecuteSqlQuery that allows you to modify the SQL query for a secondary data source and perform filtering by doing that. This command requires a good deal of caution, as it can be an SQL injection risk if used improperly.

A safer, but somewhat more involved approach is to pull the data through a web service, and this is precisely what the QueryDB service in DBXL provides.

Re: How do I use one table to find records in another table when there is only one common column?

Gennady Vanin (Геннадий Ванин, Новосибирск) — Fri, 15 Jun 2012 11:38:12 GMT

SeanPT:
What I need to do is have other dropdown lists, like say Store Name, that I can use to populate that form

This sounds like cascading drop-down lists easily created in Infopath OOTB (out-of-the-box, without coding).

See, for example,
Browser Based InfoPath 2010 - Creating Cascading Dropdown Fields with no Code
https://www.nothingbutsharepoint.com/sites/eusp/Pages/Browser-Based-InfoPath-2010---Creating-Cascading-Dropdown-Fields-with-no-Code.aspx

Oops, "Inser/Edit Link" doesn't work for me

Re: How do I use one table to find records in another table when there is only one common column?

SeanPT — Fri, 15 Jun 2012 06:25:51 GMT

I was afraid the answer would be in coding. That is outside my abilities. I was able to find a bit of a hack -- I duplicated the status query field a few times to cover each field that I need to use. On each dropdown I changed the external lookup to display what I wanted but kept the value as the store number. Then I used a rule to cause each change in the dropdown to re-query and thus update the form. Ideally some of these dropboxes would just be read-only text boxes but I can live with what I have.

Re: How do I use one table to find records in another table when there is only one common column?

KAbhishek — Fri, 15 Jun 2012 05:50:28 GMT

Are you able to fill your first dropdown list ? You mean you need to fill droplist from first table and based on selection in droplist you need to fill second table ? In this requirement, I advise you go for code solution by using VSTA and ado.net. If you aware of C#, Ado.net and Xpath you can get this done accurately as you need. DC in your requirement will never be a good option.

How do I use one table to find records in another table when there is only one common column?

SeanPT — Fri, 15 Jun 2012 05:04:40 GMT

The backend for this form is SQL 2008R2, the form is designed in InfoPath 2010 and will be used with the InfoPath form filler.

In my SQL2008R2 table I have store information (Store number, Store Name, City, State, Zip) and in my second table I have more information about the store (Store Number, Opening Date, Owner, Manager). Store Number is the primary key on both tables. This form that I need help with is for data entry into this second table.

Currently my main data connection is to the second table. I have a receive data connection setup for the first table. I can create a Dropdown List query field and setup the options to come from the receive data connection to give me a dropdown list of store numbers. I can use that dropdown list and run a query to populate the form.

What I need to do is have other dropdown lists, like say Store Name, that I can use to populate that form.

I'm kind of lost on this one after trying a lot of things (adding tables to my main data connection had very strange results that I wasn't expecting at all). Hopefully I've explained this well enough that someone might have some ideas on how I can accomplish this.