InfoPath Dev

Hello, I've been searching these forums for the answer to my question - and maybe I'm not using the right Infopath terminology, but I can't find exactly what I'm looking for. I hope this is a really easy answer, but it's been confounding me for several days. 

I have a Scorecard based off of a table in SQL that has 4 columns. The KPI ID, the Month ID, the Team ID, and the Value. All of the detailed information (The names of the KPIs, the different hierarchy levels for the Teams, the actual names of the months) are all in 3 separate tables that are related to each ID column - it's a standard relational database. 

We have 10 KPIs that are not able to be automatically pulled and we want to create a form that can be used to enter these KPIs directly into the database. There are around 4-6 people involved in the process, and so we're creating a Infopath Filler form they can each open once a month to enter the data. It's a very simple need. 

I am NOT well-versed in Infopath, and I've actually gotten everything functionally working. I've created the main connection to the Main metric table and then 3 other data connections to each of the other tables with details (The KPI Table, the Team Table, and the Month Table). On the Infopath form, I was able to pull the Team names into a drop down menu, and the Month into a drop down menu, (both displaying the name, but providing the Primary Key ID for the value) and then when the query is run it will show a list of the KPIs and KPI Values for that team and month in a repeating table at the bottom of the form. I can change and add values to the form, hit Submit and it updates in the database.

The problem I'm running into is that, for the life of me, I can't seem to get the KPI Names to display in the repeating table. The KPI Names are in the KPI Table, not the main Metric Table.

I found this website explaining exactly what I want to do: http://sharepointsolutions.com/sharepoint-help/blog/2011/11/get-infopath-to-display-lookup-column-value-not-id/

But when I follow these steps I get an error that too many calculations are being done and the values are invalid. From what I can tell, the maximum number of calculations in Infopath are 16, but I only have 10 fields that I need to process. I've tried doing the same process with a Rule that sets the value of the field, but I get the same result.

I'm left with a form that works exactly as planned, but is unusable because no one knows which KPI is associated with KPI ID #20. When I ran the Inspector, an error popped up on the right that says "Repeating Section cannot store bound data type". Does this mean there's no way to combine the data from the Main connection with data from Secondary connections in the same Repeating Section?

I don't know what I don't know about Infopath, so I'm hoping someone here can help shed some light on what's going on! Thanks! :)

I can help you with this. :)

Is the KPI Name a field? or are you just wanting to display the name in the place of the ID in an expression box (calculated value)?

 I can make it a field, but when I try pulling data from a Secondary source, it creates a separate repeating section than the repeating section created when I put the data in from the Main connection source. Since I have the drop down menus working with the Query function, and that works with the KPI ID, it may make more sense to keep it working as-is and then change the KPI ID that is currently being displayed in the KPI ID field into the KPI Name from the Secondary table.

Unless there's a better way to do it! :)

I'd put a calculated value in the spot in the repeating table where you want to show the name. Then, for the formula, you want to select first the KPI name field from that secondary data source. Click on the Filter Data button, and select the KPI ID from the same data source, then is equal to, then select the KPI ID from your main data source. You are saying in the formula:

I want the KPI Name that has an ID that is equal to the ID in this row in my main data source.

Now, InfoPath sometimes messes these things up, and doesn't use the "current" function. So once you've set up the filter and you are back in the formula editor, click on the Edit XPath button and look and see if you see the word current anyplace. If you don't, paste the whole formula back here and I'll help you with it. :)

 EDITED TO ADD: http://blogs.msdn.com/b/infopath/archive/2004/09/13/228881.aspx has a good description of current().

I do not see Current anywhere! That may have been the problem I was having!

xdXDocument:GetDOM("KPIDescription")/dfs:myFields/dfs:dataFields/d:KPIDescription/@KPIDescriptionName[../@KPIDescriptionID = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:AccessTestTable/@KPIDescriptionID]

So - assuming the calculated value is in a repeating table bound to AccessTestTable, here's what your formula should be:

xdXDocument:GetDOM("KPIDescription")/dfs:myFields/dfs:dataFields/d:KPIDescription/@KPIDescriptionName[../@KPIDescriptionID = current()/@KPIDescriptionID]

Which translates to:

Silly InfoPath, please give me the KPIDescriptionName from the KPIDescription table where the KPIDescriptionID is the same as this row this calculation is currently sitting in.

I'm getting an error in the formula when I use that. The Verify Formula button says: "@KPIDescriptionID" does not point to a valid location path of a field or group.

So, to confirm I'm doing everything right, let me restate how I'm pulling the data. In my Main Connection to the table AccessTestTable, I have a set of query fields and a set of data fields. From the datafields, I've pulled in the KPIDescriptionID field into a repeating section. When the query fields are run, this field repeats and shows me a list of the numbers of the KPIDescriptionID. 

I changed the value of the text box of the KPIDescriptionID to a calculated field. I chose to show the name of the KPIDescriptionName from the Secondary Connection table, KPIDescription. I added a filter and put the KPIDescriptionID from the KPIDescription table on the left side of the filter. "Is Equal To" was already selected. On the right side of the filter, I went back to my Main Connection and from from the datafields section of the AccessTestTable, I chose the KPIDescriptionID.

 Do all the steps sound correct?

EDIT: It's also the holiday weekend, so this is in NO WAY critical to answer right now! :)

 Based on the answers you helped with at the end of last week, I tried doing some additional research this morning.

 I found this site: http://blogs.msdn.com/b/timpash/archive/2005/10/26/using-current-function.aspx

 in the XML equation above, I changed it from:

= current()/@KPIDescriptionID] 

to 

= current()/../@KPIDescriptionID] 

Because the blog post above says it needs the /../ in a repeating table. The verify formula button works, but all of the values are now blank instead of showing the KPIDescriptionID. So, it's doing something, but I haven't quite figured out what it's doing yet! :)

Any chance you can attach your form in a reply? Under the Options tab you can attach a file.

We communicated over a PM, and in the interest of posting a solution for anyone who finds this thread in the future, my problem was that I was trying to change the values of the KPIDescriptionID to the KPI Name whenever that field was equal to the KPIDescriptionID field of my secondary table source.

However, when I tried to change the value of the field, it no longer had any value. The solution was to leave the KPIDescriptionID field alone and add a separate text box with a calculated field that would USE the KPIDescriptionID field in the calculation to determine the name.

It makes total sense now, thank you SO much! :)

And just to be super duper clear - I actually added a calculated value to the form (so, we didn't add a text box, which has to be bound to a field - if we'd done that, you'd have had to change the table the form is based off of.)

Glad you are unstuck and thanks for updating the thread.