InfoPath Dev

How do I Sum monthly expenses,monthly Budgeted, and monthly Variance of these to into a Year to Date Running Total and out put it to a InfoPath Form; each time a user opens it up. Example


Monthly Expense Monthly Budgeted Monthly Variance
$12,000 $14,000 $2,000

YTD Expenses YTD Budgeted YTD Variance
$90,000 $140,000 $50,000


The YTD has to be retrieved from the SQL database from previous months. Also, when the user finish entering the current month and submits it to the databse, it will pull that information from the database and Sum it up right that and there so that the user can see their current total after their submission.

I have already figure out how to submit to the SQL database.
Thanks for all the help ahead of time.

Michael

Hi Michael,
U can accomplish this by writing a web service and creating a "Receive" data connection to this web service.

When the user enters the current month and clicks a "Submit" button,pass the current month(or date) as the input parameter to the web service.

Write a query in the web service to fetch all the Monthly expense.
Sum it up and then return it back to the infoPath form.

Regards,
Abhishek

Abhishek

Where can I find a example of doing this or do I pick up a book in InfoPath. I have a book called Microsoft Office InfoPath 2003 by Roger Jennings. Do you know a book or online documentation that explains how to do this.

Michael71

Michael,
I have read this book.But this book helps u to get a basic understanding of InfoPath rather than the core concepts.
I think u should try out "Developing Solutions with Microsoft InfoPath" by Vani Mandava and Patrick Halstead.

And Michael,I recomend u always read a book before starting working on any tool becoz it really helps u learn things much faster and much better.

Check the following site..
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ip2003_tr/html/odc_INF_Lab_08.asp
Regards,
Abhishek

Is this rather easy Abhishek. Is it any other way to display the Totals on the InfoPath Form since I am already connected to the SQL server and submitting data to it now.

Michael71

Hi Michael,
The first time u do it,it might take some time.But once once u get the hang of it,its pretty easy.
But I think u should know it and hence you should try it. Just go thru MSDN link and try it out.

Regards,
Abhishek

I don't need a web service....I'm already connected to the database. I just need to Sum up the monthlyExpenses.

Hi Michael,

Well, in that case...
Assuming that u have all the Monthly Expenses in a field(MonthlyExpense)in a repeating group, created when u create a dataconnection to the database...
Add another field to ur main data source by selecting the root node(myfields by default) in the datasource taskpane and clicking "ADD" in the dropdown.Let the field be called "YTDExpenses".

Double click the "YTDExpenses" field to open the properties dialog.

There is a Default Value section and u can find a fx button.Click the fx button.

In the "Insert formula" dialog that opens,Click "Insert Function" button.Select "Sum" and click ok.

U see something like this ...sum(double click to insert field)

Double click and select ur "MonthlyExpense" field in the repeating section.

Now u can find that whatever the Monthly Expense field contains in the Repeating section, the sum total can be found in the YTDExpenses field.

Regards,
Abhishek.

the monthlyExpenses are pulling from a SQl server database and so is the ytdExpenses. I know you asking about the repeating table, but will that cause my from to have a lot of rows on the form? I was thinking I could just write a trigger on the SQL database and whenever someone submits data to the database it updates that one text box with the ytdExpenses = SUM(monthlyExpenses). However this do not work, because as soon as I submit to the database it should add that data or amount and update the total right than and there. Is it a better and easy way of doing this, and not have rows all down my form. Thanks Michael

Hi Michael,
From what I understand, as soon as u submit to the database,u need the new total to be updated in ur form.

U can accomplish this by doing the following.
On clicking the submit button, u need to do 2 things.

1)Submit the new data to the database
2)Retrieve the new total from the database.For this u need to requery the retrieve dataconnection that u already use when the form opens.

Now regarding the following statement made by u..
"I know you asking about the repeating table, but will that cause my form to have a lot of rows on the form?"

U dont have to display the repeating table in ur form.Any way the repeating data is nothing but ur secondary data connection. So dont display the repeating rows.
Just display the ytdExpenses.

regards,
Abhishek

I'm a little slow at this....I have a ytdExpenses text box displaying data, but when I submit to the database it do not update right then and there on the form. Could you pleae give me a few steps to get me started.....