Calculating Sum and than save the value to database

[Stanley]

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é]

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é

 

[Stanley]

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.text);
}

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

Thnx,
Stanley

 

[Franck Dauché]

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.

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.text);
}

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

Thnx,
Stanley

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é

 

[Stanley]

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,

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.

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.text);
}

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

Thnx,
Stanley

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,

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é]

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,

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,

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.

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.text);
}

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

Thnx,
Stanley

:

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,

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