InfoPath Dev

Hi there,

I was wondering if anybody could help a relative newcomer to InfoPath. I have been playing with it for several weeks now, but i'm am having troubles creating 'sums' and 'counts'...

I submit my forms to an Access 2003 database. Essentially, there is only the one 'Main Data Connection' - which the data is submitted to and queried from.

When the database is queried, each record which I submitted to the database, is displayed in a new Repeating Section. So, after querying the database for all records of a specified Date, several results are displayed.

The objective: e.g. each record has a field 'Parts_Cost'.
In a seperate view, I wish to display the Sum of the 'Parts_Cost' fields (for each record displayed from the resutls of the query).

When I insert a Text Box Control, InfoPath requires me to Bind the Text Box to a field from the Form's Data Source. To do this I created an extra field in the Access Database called 'Sum_Parts_Cost'. The formula I then enter into this control is: 'sum(@Parts_Cost)'. When I preview the form, the value in this field stays 0, regardless of the values in 'Parts_Cost' for each record. I have no idea what to try next.

I hope that I have provided sufficient information - but if more is required I will certainly get back to you. I would appreciate absolutely any help possible. I may even be trying to do something, which just cant be done...

many thanks,
Adam

Hello Adam,
One question that I have for you – By saying “The formula I then enter into this control is: 'sum(@Parts_Cost)'” what do you mean? Do you mean that you have entered the formula for text box in InfoPath form? If you want to sum all the ‘Parts_Cost#%92 and display in InfoPath form, follow these steps:
1. Double click the text box.
2. Click on fx button in Default Value section.
3. Click on “Insert Function” button.
4. Select Math in Categories, Sum in Functions and click OK.
5. Double click on “double click to insert field” and select the field of which you want to have sum.
6. Click OK 3 times.

Hope this helps!

Yes, the formula I referred to came from Text Box Properties by clicking Value: 'fx'. It inserted the function as you described. Hmm...

I think the problem I am encountering is because i'm trying to add values from different repeating sections.

Hello,
You can add values form different repeating tables also for that you should insert this formula “sum (Parts_Cost) +sum (Parts_Cost)” for the text box.

Hope this helps!

Thank you, there is hope at least!

Am I doing the right thing regarding creating the text box for which this formula goes into? ...

In order to insert a text box - it seems like it has to come from a data source. So I added the field 'Sum_Parts_Cost' to the database. I then created your suggested formula:

sum(@Parts_Cost) +sum(@Parts_Cost)

into the 'fx' value of that cell. The @ symbols appear when i go back into the 'fx' formula box. When I click Verify Formula it agrees that the formula does not contain any errors.

Thank you again

Just to clarify a bit more...

When I try to add the text box to the form (in which i wish to display the result of the sum), I click > Insert > Text Box - then a window pops up called 'Text Box Binding'. The message displayed is 'To set the binding, select a field or group in which to store the control's data:'

So, in the same window, the data source I chose is my Main source (from the Access Database), then MyFields > dataFields > d:Daily_Aftersales_Record, and within here I chose the 'Sum_Parts_Cost' field (rather than the Parts_Cost field - which is the one I wish to sum).

I then receive the message:

You have chosen to bind a non-repeating control to a reapeating field or group. The control will be bound to the first item in the repeating field or group. Do you want to continue?

.. to which I say Yes.

Then formula 'sum(@Additional_Work_Done_Parts_Cost) + sum(@Additional_Work_Done_Parts_Cost)' and is entered into the fx box.

Yet the value in 'Sum_Parts_Cost' remains '0'.

:( adam

The issue is that you have put the sum_parts_cost inside the record table. I thought you wanted to sum all the parts/costs across records.

Option 1) move that outside each record.
Option 2) if you don't actually need to store the data then use the "Expresion Box" control which doesn't need to be bound to a field. You can create the same formula to sum(parts_cost) across all the records.

Hi, thank you for your reply - I have been away for the past week, but using an Expression Box control works perfectly as I dont need to save the results.

Many Thanks, I really appreciate the help.

Adam