Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / General MS InfoPath Questions / November 2005

Tip: Looking for answers? Try searching our database.

Calculating Sum and than save the value to database

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Stanley - 15 Nov 2005 14:55 GMT
Hi,

I have a repeating table on a form. It is connected to a datasource, so it
shows all records of a table. I want to calculate a sum out of for example 3
fields.
             Personnel Cost     Other Cost      Total Cost
Bike             10                       11                   21
Car              8                         19                   27
Airplane        9                         10                  19

The last column would be calculated with an expression box. But now i want
to save this total cost to the database in code. How can i access this
expression box? Is it even possible to access it? I see that it is also not
possible to bind a textbox to the expression box.

Thnx,

Stanley
Franck Dauché - 15 Nov 2005 15:10 GMT
Hi Stanley,

Why don't you add a field in your schema to store your total and bound it to
a textbox to display the sum?  
In your repeating group, you would have:
field1: Personnel cost
field2: Other Cost
fied3: Total Cost

Then, in your form code, you can easily access your field3 nodes and push
data to the database as you are planning on doing.

Regards,

Franck Dauché

> Hi,
>
[quoted text clipped - 14 lines]
>
> Stanley
Stanley - 15 Nov 2005 15:51 GMT
Hello Franck, thnx for your response.

What i actually want to achieve is, when a user opens the form, he/she sees
those 3 records with their "personnel cost" and "Other Cost" (it is displayed
in a repeating table which is bound to a datasource). They can than change
the these values. When doing so, the total must than be recalculated. Than
the user can store the new values in the database. I do have a "Total Cost"
field in my schema, but did not use it because i don't know how to do a sum
per record and write it to that field.
I wanted to add an on_afterchange event behind the "Personnel Cost" and
"Other Cost", so that when the value changes it should recalculate the total.
But that would bring me to my next question, how do i loop thgrough the
table, and is it possible to retrieve the current selected record out of the
table so i only have to update that one record and not the whole table.
I have tried the code you posted in one of the other postings:

IXMLDOMNodeList oNodeList = thisXDocument.DOM.selectNodes("<what goes in
here?>" );
           
for (int i = 0;i<oNodeList.length;i++)
{
  msg.ShowMessage(oNodeList[i].text);
}

I have problem with the "<what goes in here?>" part. I keep getting a length
of 0.

Thnx,
Stanley

> Hi Stanley,
>
[quoted text clipped - 30 lines]
> >
> > Stanley
Franck Dauché - 15 Nov 2005 17:19 GMT
Hi Stanley,

Select your 3rd textbox (linked to field3).
Right-click
Select the Function button to the right of the 'Value' field.
Click "Insert Field or Group..."
pick field1 / OK
type " + "
Click "Insert Field or Group..."
pick field2 / OK
Click OK twice.
Now, your total will be calculated automatically for each row.

If you need to loop through your nodes by code, in case your schema looks
like:
myFields
 group1
    group2  <- Repeating
        field1
        field2
        field3
("<what goes in here?>) is:
//my:group1/my:group2 or /my:myFields/my:group1/my:group2

Hope that it helps.

Regards,

Franck Dauché

> Hello Franck, thnx for your response.
>
[quoted text clipped - 60 lines]
> > >
> > > Stanley
Stanley - 16 Nov 2005 10:26 GMT
Franck,

I have already tried using the value field, but the sum that is being showed
for each row is the sum of the first record. So in case of my example each
row will show a Total Cost of 21.

But i managed to solve it in another way. I have created a function to
calculate the sum:

IXMLDOMDocument3 oSecondDoc =
(IXMLDOMDocument3)thisXDocument.DataObjects["Costs"].DOM;
oSecondDoc.setProperty("SelectionNamespaces","xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\"
xmlns:d=\"http://schemas.microsoft.com/office/infopath/2003/ado/dataFields\"");
IXMLDOMNode oSecDocNode =
oSecondDoc.selectSingleNode("/dfs:myFields/dfs:dataFields");
           
int intSum = 0;

foreach (IXMLDOMNode oN in oSecDocNode.childNodes)
{
    if (oN.nodeType == DOMNodeType.NODE_ELEMENT)
    {   
      intSum =
int.Parse(oN.selectSingleNode("@PersonnelCost").text)+int.Parse(oN.selectSingleNode("@OtherCost").text);
    oN.selectSingleNode("@TotalCOst").text= intSum.ToString();
    }
}

Than in the on_afterchange event of the PersonnelCost and OtherCost i call
this function. I now have a function to calculate the sum per record/row and
i can loop through the records/rows for writing data to the database or doing
something else.

If this is NOT a good solution because of whatsoever i would like to hear
your opinion.

Thnx,

Stanley

> Hi Stanley,
>
[quoted text clipped - 90 lines]
> > > >
> > > > Stanley
Franck Dauché - 16 Nov 2005 14:42 GMT
Hi Stanley,

Can you create a new blank template.  Drop a repeating table (default of 3
columns is OK).  Following the instructions of my last post.  Each row total
(field3) is working as expected, right?

Franck

> Franck,
>
[quoted text clipped - 130 lines]
> > > > >
> > > > > Stanley
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.