Sumarizing data

[Mini Mouse]

Hi folks,

New to infopath so I am wondering if it's possible to do the
following.

Using MOSS 2007 and InfoPath 2007.

The idea behind the form is to collect information for mileage
reimbursement. Each line item has an account number associated with a
given data group.

For example if I insert 10 entries, I'll have 10 account numbers. The
account numbers can be reused for each line item. At the bottom of the
form, or in a different view, I would like to sumarize which account
numbers were used; grouping by account number with totals for the
account numbers.

Any ideas as to go about it?

 

[Clay Fox]

You can use the sum function on your mileage field with a filter -
mileage[account = 1]
That will sum all of the mileage rows where account = 1
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Mini Mouse]

You can use the sum function on your mileage field with a filter -
mileage[account = 1]
That will sum all of the mileage rows where account = 1
--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

Hi folks,

New to infopath so I am wondering if it's possible to do the
following.

Using MOSS 2007 and InfoPath 2007.

The idea behind the form is to collect information for mileage
reimbursement. Each line item has an account number associated with a
given data group.

For example if I insert 10 entries, I'll have 10 account numbers. The
account numbers can be reused for each line item. At the bottom of the
form, or in a different view, I would like to sumarize which account
numbers were used; grouping by account number with totals for the
account numbers.

Any ideas as to go about it?- Hide quoted text -

- Show quoted text -

Hmm, that's a start. The down side is I'm not going to know what
account numbers are chosen, or how many there are going to be.

Think of it as if the form contained a cell phone bill that had all
the phone numbers called and the minutes used for each call. To some
up each phone number would require some sort of grouping function.
Let's say the form had this information:
555-1212 15
888-2334 23
10-10220 16
555-1212 16
123-4567 2
765-3214 18

The sumarized data would be like this...
555-1212 31, 888-2334 23, 10-10220 16, 123-4567 2, 765-3214
18...etcetera...

Might have to look in to various InfoPath events and use C# (ugh!). :|

 

[Clay Fox]

Ok gotcha. yes I have done this before.
Infopath is not the ideal reporting tool.
I now use the Database Accelerator, downloadable from http://www.qdabra.com,
to put all of my data in a SQL table and then can use SQL web reports, and
other office tools to generate far superior reports.

You need to look at the preceding:: function.

Typically I have a repeating section and then use this to hide all duplicate
rows.
PhoneNum = preceding::d:UsageData/@PhoneNum
Then add an expression control which sums minutes using the current function.
Sum(Mins[PhoneNum=current()/@PhoneNum])
This then sums for the current number in the row and displays your sum.
You can use the count function too, similar to the sum, to get a record count.

You can find more information on preceding and current at
http://www.infopathdev.com


--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

You can use the sum function on your mileage field with a filter -
mileage[account = 1]
That will sum all of the mileage rows where account = 1
--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

Hi folks,

New to infopath so I am wondering if it's possible to do the
following.

Using MOSS 2007 and InfoPath 2007.

The idea behind the form is to collect information for mileage
reimbursement. Each line item has an account number associated with a
given data group.

For example if I insert 10 entries, I'll have 10 account numbers. The
account numbers can be reused for each line item. At the bottom of the
form, or in a different view, I would like to sumarize which account
numbers were used; grouping by account number with totals for the
account numbers.

Any ideas as to go about it?- Hide quoted text -

- Show quoted text -

Hmm, that's a start. The down side is I'm not going to know what
account numbers are chosen, or how many there are going to be.

Think of it as if the form contained a cell phone bill that had all
the phone numbers called and the minutes used for each call. To some
up each phone number would require some sort of grouping function.
Let's say the form had this information:
555-1212 15
888-2334 23
10-10220 16
555-1212 16
123-4567 2
765-3214 18

The sumarized data would be like this...
555-1212 31, 888-2334 23, 10-10220 16, 123-4567 2, 765-3214
18...etcetera...

Might have to look in to various InfoPath events and use C# (ugh!). :|

 

[Mini Mouse]

Ok gotcha. yes I have done this before.
Infopath is not the ideal reporting tool.
I now use the Database Accelerator, downloadable fromhttp://www.qdabra.com,
to put all of my data in a SQL table and then can use SQL web reports, and
other office tools to generate far superior reports.

You need to look at the preceding:: function.

Typically I have a repeating section and then use this to hide all duplicate
rows.
PhoneNum = preceding::d:UsageData/@PhoneNum
Then add an expression control which sums minutes using the current function.
Sum(Mins[PhoneNum=current()/@PhoneNum])
This then sums for the current number in the row and displays your sum.
You can use the count function too, similar to the sum, to get a record count.

You can find more information on preceding and current athttp://www.infopathdev.com

--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

You can use the sum function on your mileage field with a filter -
mileage[account = 1]
That will sum all of the mileage rows where account = 1
--
Thanks
Clay Fox
Qdabra Softwarehttp://www.qdabra.com
InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com
:
Hi folks,
New to infopath so I am wondering if it's possible to do the
following.
Using MOSS 2007 and InfoPath 2007.
The idea behind the form is to collect information for mileage
reimbursement. Each line item has an account number associated with a
given data group.
For example if I insert 10 entries, I'll have 10 account numbers. The
account numbers can be reused for each line item. At the bottom of the
form, or in a different view, I would like to sumarize which account
numbers were used; grouping by account number with totals for the
account numbers.
Any ideas as to go about it?- Hide quoted text -
- Show quoted text -

Hmm, that's a start.  The down side is I'm not going to know what
account numbers are chosen, or how many there are going to be.

Think of it as if the form contained a cell phone bill that had all
the phone numbers called and the minutes used for each call. To some
up each phone number would require some sort of grouping function.
Let's say the form had this information:
555-1212 15
888-2334 23
10-10220 16
555-1212 16
123-4567 2
765-3214 18

The sumarized data would be like this...
555-1212 31, 888-2334 23, 10-10220 16, 123-4567 2, 765-3214
18...etcetera...

Might have to look in to various InfoPath events and use C# (ugh!). :|- Hide quoted text -

- Show quoted text -

*poof*
My brain just exploded.
I'll give this a go, but I'm missing something here. Since I'm using
browser based forms with MOSS 2007, I don't think the controls you're
mentioning are available.

I was hoping to keep this all in a form based document library for
individual user's mysite so that they can see their own stuff. The
potential down side is, browser based forms don't seem to be able to
submit data to a database (well they can, but it seems to be one or
the other for submit or receive).

But I'll see what I can do with the information you have given me.
Thanks!

 

[Clay Fox]

For a browser based form you need web services, which the Database
Accelerator delivers.

I believe the formulas I listed should not be a problem in a browser form
but trying to create summaries is a brain strain and complex.

That is the great thing about dumping it in SQL, it makes it nice and easy
to manipulate the data. Also you can set up Reports which show individuals
just their information.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

Ok gotcha. yes I have done this before.
Infopath is not the ideal reporting tool.
I now use the Database Accelerator, downloadable fromhttp://www.qdabra.com,
to put all of my data in a SQL table and then can use SQL web reports, and
other office tools to generate far superior reports.

You need to look at the preceding:: function.

Typically I have a repeating section and then use this to hide all duplicate
rows.
PhoneNum = preceding::d:UsageData/@PhoneNum
Then add an expression control which sums minutes using the current function.
Sum(Mins[PhoneNum=current()/@PhoneNum])
This then sums for the current number in the row and displays your sum.
You can use the count function too, similar to the sum, to get a record count.

You can find more information on preceding and current athttp://www.infopathdev.com

--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

On Dec 28, 9:11 am, Clay Fox <[email protected]>
wrote:
You can use the sum function on your mileage field with a filter -
mileage[account = 1]
That will sum all of the mileage rows where account = 1

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

:
Hi folks,

New to infopath so I am wondering if it's possible to do the
following.

Using MOSS 2007 and InfoPath 2007.

The idea behind the form is to collect information for mileage
reimbursement. Each line item has an account number associated with a
given data group.

For example if I insert 10 entries, I'll have 10 account numbers. The
account numbers can be reused for each line item. At the bottom of the
form, or in a different view, I would like to sumarize which account
numbers were used; grouping by account number with totals for the
account numbers.

Any ideas as to go about it?- Hide quoted text -

- Show quoted text -

Hmm, that's a start. The down side is I'm not going to know what
account numbers are chosen, or how many there are going to be.

Think of it as if the form contained a cell phone bill that had all
the phone numbers called and the minutes used for each call. To some
up each phone number would require some sort of grouping function.
Let's say the form had this information:
555-1212 15
888-2334 23
10-10220 16
555-1212 16
123-4567 2
765-3214 18

The sumarized data would be like this...
555-1212 31, 888-2334 23, 10-10220 16, 123-4567 2, 765-3214
18...etcetera...

Might have to look in to various InfoPath events and use C# (ugh!). :|- Hide quoted text -

- Show quoted text -

*poof*
My brain just exploded.
I'll give this a go, but I'm missing something here. Since I'm using
browser based forms with MOSS 2007, I don't think the controls you're
mentioning are available.

I was hoping to keep this all in a form based document library for
individual user's mysite so that they can see their own stuff. The
potential down side is, browser based forms don't seem to be able to
submit data to a database (well they can, but it seems to be one or
the other for submit or receive).

But I'll see what I can do with the information you have given me.
Thanks!

 

[Mini Mouse]

For a browser based form you need web services, which the Database
Accelerator delivers.

I believe the formulas I listed should not be a problem in a browser form
but trying to create summaries is a brain strain and complex.

That is the great thing about dumping it in SQL, it makes it nice and easy
to manipulate the data.  Also you can set up Reports which show individuals
just their information.
--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

Hmm, I'll take a look at the product but in the mean time...

I created a new form to match the fields of your suggested solution.

Using the following elements:
A table from Layout, within that a repeating section that I named
calls.
In that repeating section there are two text boxes; PhoneNum and Mins.

Below that in a separate area another table for layout.
Next a repeating table two columns wide.
Deleted the two text boxes it supplied.
Inserted an Expression Box with your suggested xpath statement of
Sum(Mins[PhoneNum=current()/@PhoneNum]) (this is before pressing the
fx button) and get an error of "Mins/PhoneNum" does not point to a
valid location path of a field or group.

Under Data source: Main
myFields
*group11
**calls
***PhoneNum
***Mins



No lovin' from InfoPath.
Then I changed the forumal for the Expression Box to:
sum(/my:myFields/my:group11/my:calls/my:Mins[/my:myFields/my:group11/
my:calls/myhoneNum = current()]//my:myFields/my:group11/my:calls/
myhoneNum)

Not it's not suming correctly. The result is always 0.

 

[Clay Fox]

Sorry for the lack of love....

I would use the formula builder so that you can make sure your syntax is
correct.

You need to have your repeating summary table bound to your repeating group
calls.

For your expression box use the FX button and then select the field Mins and
use the filter key in the lower left of the window.
Add a filter and intially just to test try PhoneNum = "a phone number in the
data set" That way you can test it and make sure you have the rest correct.
Once that is working then try PhoneNum = and use a formula and select
PhoneNum again. Then edit xpath and add the current (). My guess is it
should be Mins=current()/Mins. What ever the field syntax add the current
()/ ahead of that.
This is often tricky as you do not get error messages, it just doesn't work
so often there is some playing with the syntax.
That should then give you a total for each row.

You can then add conditional formatting to the table and use the preceding::
function.
I would guess PhoneNum = preceding::calls/PhoneNum

This will give you one row per unique Phone Num but the expression will
total all minutes. you probably will want one other expression box that
displays the phone number field.

--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

For a browser based form you need web services, which the Database
Accelerator delivers.

I believe the formulas I listed should not be a problem in a browser form
but trying to create summaries is a brain strain and complex.

That is the great thing about dumping it in SQL, it makes it nice and easy
to manipulate the data. Also you can set up Reports which show individuals
just their information.
--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

Hmm, I'll take a look at the product but in the mean time...

I created a new form to match the fields of your suggested solution.

Using the following elements:
A table from Layout, within that a repeating section that I named
calls.
In that repeating section there are two text boxes; PhoneNum and Mins.

Below that in a separate area another table for layout.
Next a repeating table two columns wide.
Deleted the two text boxes it supplied.
Inserted an Expression Box with your suggested xpath statement of
Sum(Mins[PhoneNum=current()/@PhoneNum]) (this is before pressing the
fx button) and get an error of "Mins/PhoneNum" does not point to a
valid location path of a field or group.

Under Data source: Main
myFields
*group11
**calls
***PhoneNum
***Mins



No lovin' from InfoPath.
Then I changed the forumal for the Expression Box to:
sum(/my:myFields/my:group11/my:calls/my:Mins[/my:myFields/my:group11/
my:calls/myhoneNum = current()]//my:myFields/my:group11/my:calls/
myhoneNum)

Not it's not suming correctly. The result is always 0.

 

[Mini Mouse]

Sorry for the lack of love....

I would use the formula builder so that you can make sure your syntax is
correct.

You need to have your repeating summary table bound to your repeating group
calls.

For your expression box use the FX button and then select the field Mins and
use the filter key in the lower left of the window.
Add a filter and intially just to test try PhoneNum = "a phone number inthe
data set" That way you can test it and make sure you have the rest correct..
Once that is working then try PhoneNum = and use a formula and select
PhoneNum again.  Then edit xpath and add the current (). My guess is it
should be Mins=current()/Mins.  What ever the field syntax add the current
()/ ahead of that.
This is often tricky as you do not get error messages, it just doesn't work
so often there is some playing with the syntax.
That should then give you a total for each row.

You can then add conditional formatting to the table and use the preceding::
function.
I would guess PhoneNum = preceding::calls/PhoneNum

This will give you one row per unique Phone Num but the expression will
total all minutes.  you probably will want one other expression box that
displays the phone number field.

--
Thanks

Clay Fox

Qdabra Softwarehttp://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the Worldhttp://www.infopathdev.com

Hmm, I'll take a look at the product but in the mean time...

I created a new form to match the fields of your suggested solution.

Using the following elements:
A table from Layout, within that a repeating section that I named
calls.
In that repeating section there are two text boxes; PhoneNum and Mins.

Below that in a separate area another table for layout.
Next a repeating table two columns wide.
Deleted the two text boxes it supplied.
Inserted an Expression Box with your suggested xpath statement of
Sum(Mins[PhoneNum=current()/@PhoneNum]) (this is before pressing the
fx button) and get an error of "Mins/PhoneNum" does not point to a
valid location path of a field or group.

Under Data source: Main
myFields
*group11
**calls
***PhoneNum
***Mins

No lovin' from InfoPath.
Then I changed the forumal for the Expression Box to:
sum(/my:myFields/my:group11/my:calls/my:Mins[/my:myFields/my:group11/
my:calls/myhoneNum = current()]//my:myFields/my:group11/my:calls/
myhoneNum)

Not it's not suming correctly. The result is always 0.- Hide quoted text-

- Show quoted text -

Clay,
Thanks for all your help. I think I'm going to throw in the towel.