InfoPath Dev

Good morning.  I have been tasked with creating a simple InfoPath form that is turning out to be very complicated. After many hours of Google searching, reading tutorials, and trying everything that I can think of, I need help.  What I must accomplish is to make a form where supporters can fill in the utilization for the week. Supporters open the form and enter their userid into the query field. The results for their id are displayed. This works fine.

ApplicationID and UtilizationPercentage are the fields. I would like but can't figure out the way for the form to display the application name instead of ID. Application names are in another table called Application with two fields, ApplicationID and ApplicationName..

I tried adding the Application table the the main datasource and setting the relationship to ApplicationID = ApplicationID which works for display purposes but then it tells me that I will not be able to submit data through the form. Next I tried setting up the Application table as a secondary data source but then I can't figure out how to create the relationship between tables in the primary and secondary datasources. When I add a control from the secondary source and then run the query, every entry in the result displays the first application name in the Application table instead of the application that relates to the ApplicationID in the primary datasource.

In the database world, I could accomplish this with joins but InfoPath seems to work differently and does not appllow joins with form submitting even if the join is only for display purposes.

I really hope that someone can help me because I just can't seem to grasp the concept.

 Thanks very much,

 Howard

Hi:

 You were on a track that was close to right, so don't despair!

You need to add a secondary data connection to your Application table. Then you can add an expression box where you'd like to see the Application Name.

Here's the tricky bit -- you have to use an XPath filter to perform what you'd think of as your join.

For the formula for the expression box, select the application name in your secondary data source (2ds) after selecting "Insert Field or Group". There will be a little button on the left of the window where you select the field that says something about filtering. Click that.

For your filter, you want the first drop down to be the app Id from the 2ds. The middle drop down should be "is equal to". The third drop down would be the app Id from your main data source.

Let me know if you need more info....

Hilary, that worked perfectly.  It is a great feeling to have this figured out. The form is now complete. Thank you so much.

Here is a more complex one if I may. Supporter utilization form connecting to a database where a supporter is assigned to a database which has an associated application.

Three tables:
SupporterList (SupporterID, DatabaseID,UtilizationPercentage)
DatabaseList (DatabaseID, DatabaseName, ApplicationID)
ApplicationList(ApplicationID, ApplicationName)

Form should look like:

Query field: SupporterID

Data: DatabaseName(expression box), ApplicationName(expression box), UtilizationPercentage(updatable text box)

 -------

My ideas are:

SupporterList (main data source) <------- DatabaseList (2ds) <------ ApplicationList (2ds)

Join the main data source to a 2ds and then join another 2ds to the first 2ds through a filter like ApplicationList.ApplicationID = DatabaseList.ApplicationID AND DatabaseList.DatabaseID = Supporter.DatabaseID?

or alternately,

SupporterList (main data source) <------- DatabaseList (2ds) 
                                                                   | 
                                                                  ApplicationList

One 2ds that has DatabaseList and ApplicationList both in it, setting the ApplicationID = ApplicationID relationship in the secondary data source. 

 Neither approach allowed me to see on the form the application that was associated with the database that the supporter supports.

 Not sure that I explained that well but thanks for any ideas.

 Howard

So, SupporterList is the only table you need your form to submit to?

In that case, yes, Supporter List = Main Data Source (mds). Have two secondary data connections -- one to Database List, one to Application List. You could have a repeating table bound to your Database List that shows only the items where the DB Id = the DB Id for the Supporter. Then, in the repeating table, use an expression box to show the app name rather than the Id. The expression box would use the App name field from the App List, with a filter where App Id = App Id from the DB List 2ds. Does that make sense?

Keep in mind -- if you are working with great big tables, you may take a performance hit here -- your secondary data sources can't be filtered, so you are pulling back all the table data....

Another option may be to use all the table in the main data source (defining the relationships in the data connection wizard) but not allowing the user to edit anything you don't want them to....

Really appreciate your guidance.

Howard