InfoPath Dev

My repeating table, linked to a database,  has conditional formatting to only return rows meeting my criteria and it displays correctly. I have added a "total" field in the footer to display the sum of values in the records. However, it displays a sum for all records in the database, not just the ones that are returned with the conditional formatting for the table. How can I make it sum only the values that are displayed? Being new to Infopath, I'm not sure if I should use rules on this "total" field or conditional formatting (which didn't work) or how to set it up. Thanks.

Rico

Hi Rico, welcome to the forums.

It sounds like the conditional formatting you are using is not actually filtering this date on the server side but just filtering the data from showing in the form using conditional formatting.  if you are using the sum function this will sum everything including the data not showing in the UI.

I think you have a couple of options here:

1. Make the query so that it returns only the results from the DB.  Then you don't even need to do conditional formatting in the form

2. Add boolean for each row that gets set if the conditional formatting is tirggered, then don't sum these rows in your total at the bottom

hope that helps

 Ted

 Yes, you are correct, the conditional formatting is just hiding the data showing in the form. That was the only way I could get only the rows to display that I wanted.

 So my questions in relation  to your suggested options are:

1.  I'm not sure how to modify the query for the results directly from the Db. Are you talking about going into "Manage Data Connections" and then modifying (in this case a secondary data source) connection and choosing the "Edit SQL" option? I tried adding a WHERE condition, but then it didn't return any results. Also will it be possible for the SQL statement to query from a control on the form? I'm not at all sure of the syntax for that.

2. How would I set a boolean for each row? It sounds like a good idea, but I have no idea how to implement it.

here is a thread that can help you a bit of the SQL question:

http://www.infopathdev.com/forums/p/5747/21514.aspx#21514

To do the boolean you would add a field for each row and make it a boolean data type.  Then use your rules for how you want to conditionally hide the data to set the value of the boolean to true.  Then you filter the sum in your totals row to not count the rows where the boolean = true

I created a simple test form that you can see how I did this, take a look at my comments I put in this and also look at each field to see what I did to make this work

Good luck!

I have looked over your suggestions. The simple test form looks good and may give me useful ideas for the future, however neither seemed to meet my current needs and I am even starting to rethink some of my overall design philosophy because I'm starting to think it may have performance problems in the future.

Basically my form has two parts. There is one part that only allows users to input new records into the db through the main data data source in a repeating table. These records include their username, from the username() function and the current date.

The second part was intended to be a view only table, populated when the form was opened, that would display records from the db that had the current username and the current date (so users could see what they entered that day, before entering new records). The only way I could get this to work with my beginning Infopath knowledge was to create a secondary data source pointing to the same table as the main one and then using the conditional formatting to limit what information was displayed.

One problem that comes to mind now after reading some other posts is that it would appear that my form will read the entire contents of that table every time it is opened, so will get slower over time as more records are entered. Apparently a limitation of a secondary data source is the inability to use a WHERE clause. Also, my initial problem with the sum function was that it just summed all the records including the hidden ones. The example form with the boolean controls seems to work because the user inputs data which then causes rules to apply. However, in my example, the table is just a static, view only control that rules don't seem to work on. Are there any easy ideas out there or should I just delete this view only section of my form for performance?