grouping data and subtotal

[ekr]

Hello-

I am trying to group data points to get a subtotal.

I have item numbers with 'suffixes' whose quantities I want included in a subtotal, but not grouped separately by item. For example,

item qty
8471 3
8471-1B 8
8471W 4

all should be in group "8471" and show a subtotal of 15.

I would like everything that starts with "xxxx" number to be included in the "xxxx" group, but the syntax is eluding me. I can't list all the item numbers individually in the formula- there are several hundred.

I've looked at a similar thread here

http://www.tek-tips.com/viewthread.cfm?qid=1595385,

but I can't figure out how to get my data together with such a large group of items and suffix variations.

Thanks in advance for any assistance - E. Rice, CR 8.5, .dbf database

 

[lbass]

left({table.item},4)

Insert a group on the above formula.

-LB

 

[careymw]

A couple additional questions for you.

Are all the item suffixes limited or restricted to 4 numbers "example like 8471"?

Will there always be a space after the item suffix and the Qty?

You could make 2 formulas and use one for grouping(item) and one for your subtotaling.(qty)

create a formula called Item
left({xfield}, 4)

results = 8471

then create a formula called Qty
Right({xfield}, 2)

results = 2

 

[ekr]

thank you so much for the assistance! works perfectly