Data Connections

Re: How to display a value from one table based upon a value in another table

altamiranm — Tue, 19 Jan 2010 22:51:37 GMT

Hilary,

Thank you for the prompt response! I decided to go ahead and test the theory I had and determined that it was the GenderID which I had as an "AutoNumber" in my database list table. Once I set this field on the list table from "AutoNumber" to "text" and relinked the table as a secondary source to my infopath 2003 form, I was able to write the argument as you suggested and it now works. I suppose what this means is that I cannot use the "AutoNumber" for my list's IDs. Unfortunately, I have about 20 list tables set-up this way all linked as secondary sources in my form. I hope by changing them from "AutoNumber" to "text" that I will not run into some other issue down the road while writing queries and such. Thank you so much for your help again!

Re: How to display a value from one table based upon a value in another table

Hilary Stoupa — Tue, 19 Jan 2010 22:33:45 GMT

Mind posting your template? Under the Options tab there is a place you can attach a file. Also, are you using IP 2003 or 2007?

Re: How to display a value from one table based upon a value in another table

altamiranm — Tue, 19 Jan 2010 22:31:26 GMT

Hilary,

Thank you very, very much! Your example is simple and straight forward, however, it is not working for me. When I write my string to match yours, I am only getting the first entry on my list table as a result for every line. It does not matter what I choose, it is always the same. The best I can determine when I compare my project to your example is that a number of the fields I am using, like the GenderID and the GenderType are hardcoded as integers in my database as opposed to simply being strings. Could this be causing me issues? I am never given an option to write text for "current()/Gender" I have to enter that as another expression I am guessing because it is not text? I don't know and I hate to go back and alter the fields in my database to be text strings if that is not the issue. Thank you again!

Re: How to display a value from one table based upon a value in another table

Hilary Stoupa — Sun, 17 Jan 2010 23:18:46 GMT

Ah, for repeating, you'll need to use the current() function in your XPath. Please see the modified attached sample....

Re: How to display a value from one table based upon a value in another table

altamiranm — Sat, 16 Jan 2010 21:36:41 GMT

Hi Hilary!

Thank you so much for your response. I had tried that before posting and while it does work, I found that it does not work when the data is in a repeating table. Each expression box only shows the first entry. Is there a way to get around this issue? Thanks!

Re: How to display a value from one table based upon a value in another table

Hilary Stoupa — Sat, 16 Jan 2010 14:18:36 GMT

Hi --

Add an expression box. For the value, use the secondary data source that provides the values to your Gender drop down list. Add a filter, so that Gender ID in your secondary drop down list is equal to the field in your main data source that is bound to the drop down fed by your secondary data source.

Samples speak louder than words, so see attached (right click link in header, select Save Target As to save locally. Right click file and select Design to open in design mode).

How to display a value from one table based upon a value in another table

altamiranm — Fri, 15 Jan 2010 22:03:15 GMT

I am creating an Infopath 2003 form using a Microsoft Access 2003 database 
(will be converted to SQL 2005 when completed.)  I am currently using some 
list tables in this database as secondary data sources that allow for simple numeric values to be 
stored (submitted) back to the main data tables, but allow long display names in the form.  
For example:

List table lstGender
GenderID        Gender
1                    Male
2                    Female

A selection of "Male" allows "Male" to be displayed on the form, but a 1 is 
stored back in the databases data table.  The problem I am experiencing is that I need that 
field to appear on a second view as read only.  With a text box linked to the 
dataset, it is displaying the GenderID "1" instead of the Gender "Male".  Is 
there a way, either via XPath or some other code that will allow me to change 
the numeric value back to the text value on the form for display purposes 
only?  I want to keep the data numeric, but to allow it to display in other places in the 
form as read-only text.  I know I can do it by using another drop down box containing 
duplicate data, however, all other instances need to be read-only.  Thank you 
in advance!