InfoPath Dev

Hoping someone can help me out. 

I have an infopath form that needs to pull data from 2 sharepoint lists.  The 1st list contains high level project information: title, description, start end dates, manager, etc.  The 2nd list contains statuses of those projects (these can be weekly, bi-weekly, monthly, etc).  I would like an infopath form that has ONE repeating table that can display information from both lists.  For example I'd want row one in the repeating to show project title and description and then a bi-weekly status for that project title. 

Thanks

So - is the bi-weekly status repeating?

That is, your table would look like:

I will give you more details.  Here are the two lists in SharePoint.

List 1 -  Projects  <-- this information shouldn't change that much.

• Project Name
• Description
• Manager
• Start Date
• End Date
• Project #
• Benefits
• Company

 List 2 - Project Status

• Project Name
• Status From Date
• Status To Date
• Overall Health
• Issues
• Accomplishments
• Report Frequency

The idea of having two lists is because the information in List 1 will not change that much.  However list 2 information will change a lot and will have multiple status reports for each project.  Therefore Project XYZ would have 1 entry on List 1 but may have 10-15 entries (status reports) on List 2.  My problem is how can I join these lists together in a repeating table AND use queryFields to allow the user to select what they'd like to see on the report.  Seems like there should be a way for me to link them with Project Name since it is the "key" for both lists.

Currently I have a infopath form and on View 1 I am allowing the user to select the projects they which to report on.  I'm doing this by using the queryFields from list 1.  This filters & populates a repeating table on a View 2 (my print view) that has all data from list 1.  I then have another section on View 1 that allows the users to select which project status they want to see on View 2.  I am using queryFields from List 2 to do this.  This filters a repeating table on View 2.  Right now they are two separate repeating tables but I'd like to join them.

bump

I would bind the repeating table to List 2 and use some expression boxes to also show data from list 1.

As to using your query fields for data - some of them could be bound to list one and some to list two. It sounds as if currently you are only querying on project name - and since both lists have project name, you could return data for both based on that.

I don't know of a way you could do this with a single data connection - it will have to be based on the form design - that is, you'll need data connections to both lists, and you'll need to query both lists. I suppose you could add lookup columns to List 2 in SharePoint - then you'd have all the info in a single list. But of course, lookup data is always fun in InfoPath because it has an id, a semicolon and a pound sign before the display value - that can again be handled via expression boxes.

How do i use expression boxes to show data from list 1?

You add columns to the table and then add expression boxes - when you create the formula to populate the information, you'd add a filter that the project name is equal to the current row's project name. I'm attaching a simple sample that uses XML files for secondary data sources. Save the file locally, right click and select Design to open in design mode. The first two columns are from the Projects data source and use XPath and the current() function to get the correct data.

Hilary first off thank you so much for your helpful posts, every piece of information you've provided gets me closer to my solution!!!

I still not there yet and was wondering if you could help me out with calculated fields (I believe they are the best way for me to link up 2 repeating tables).

I am going to attach 5 files, they are screenshots of my infopath form (some fields blurred out).  Hopefully these screenshots will help me better communicate my issues.  There are 5 jpg files, help1, help2, help3, etc.  Since I can only attach one file per post I am going to reply to this thread several times until all help jpg's are posted.

Help jpg #1 - this shows the first View of my form.  In the "show / hide information" section I use it to allow the user to select which project's information will display on "project information" repeating table in the "print" view.  I did this by adding query fields to the view.  In the "report selection criteria" area I am using query fields to allow users to select what they want to see on the "print" view for repeating table "project status". 

Help jpg #2 - this shows "print" view of my form.  This is formatted for printing.  "Project Information" is coming from one list and is a repeating table.  "Project Status" is comign from another list and is a repating table.  I want to join these tables together but can't figure out how to do it.  I believe you said calculated fields is the answer.  Example: I would like have information like "overall health" field display in the first repeating table.  What strange is I cannot add a new row to the first repeating table only new columns.  How do I fix this?

Help jpg #3 - this shows the data view for the "project status" list.

Help jpg #4 - this shows the data view for the "project information" list.

Help jpg #5 - this shows my attempt to create a calculated field to display "overall health" in the "project information" repeating table.  It didn't work.

My 2 big questions:

• How do I add new rows to the first repeating table.
• How do I create a calculated value field that will link a project status field to a project information row in repeating table 1.

Thanks

Image 2

help image 2

help image 3

More helpful would be a screenshot of your data source, since creating the calculated value is going to have to do with getting the XPath right. What's the formula you used that didn't work? Check the Edit XPath box before pasting it in so we can see the full XPath....

Here it is.

Basically i tried to say project name from data source 1 is equal to data source 2 AND pick the ID that has the max value, since I wanted to return the latest status report from data source 2 and max ID should be the latest.

Data source 1