Summary based on value list

[djeans]

Is it possible to get a summary based on another field?

For instance, I have a database of liquors and wines, their cost per bottle, and how many i have of each bottle. I also have a value list field where I have selected either wine, or liquor.

I would like to create summary fields that tell me how much total $ I have in wine and liquor separately based on the count field, cost field, and wine/liquor field.

Is this possible?

Thanks

[Søren Dyhr]

Do you mean something like the shown above??

--sd

[djeans]

Sort of, but not exactly.

There is actually 3 tables to the file. The first table has a catalog of liquor, beer and wine with the fields "Barcode" "Name" "FullWeight" "EmptyWeight" "Cost" "Volume" and "Category" with category being a value list with "Liquor Beer Wine" being the values. The full weight and empty weight fields are not being used right now, but in the future will be used with a scale to calculate how much liquor is left in a bottle by weighing the bottle.

2nd table is for counting full bottles and has the fields "Barcode" "Count" "Name" "Volume" "Value" and "Category"

The tables are related by the barcode field. When I scan a bottle, all of the other fields are lookup fields based on the relationship, except the value field, which is a calculation of "Count" X "Cost".

3rd table is going to be used in the same way, but will incorporate weighing bottles that are not full and calculating remaining volume.

Right now, the idea is to have a table format database where I scan a bottle (wine or liquor at this point, beer later), and then enter a count. The bottles would be scanned as they are encountered, meaning that it would not be all liquor, then wines. They would be mixed in together.

I would like to have a running total at the bottom for dollar value of Liquor, Wine, and Beer, all separated if that is possible.

I appreciate your help.

Thanks

Darron

[Søren Dyhr]

Since you're on FM6 is going to be a little tough, since the straight forward answer to use selfjoins on the 3 pop cathegories as constants ...have the serious problem that it scales badly, because aggregate functions are designed to smaller chunks of data than summary functions.

But as you say will the data be entered in mixed order, so getting your data by breaker values requires sorting by each new entry, eventhough you can use your valuelist items as breaker values in the GetSummary( function.

This means you need a more transaction'ish kind of system, where you should interpolate these two template's ideas 1) using this for each of the summaries:

http://www.filemakerpros.com/LULAST.sit

and 2) this for keeping each cathegory apart:

http://www.filemakerpros.com/SerialCat.sit

These methods uses, stored near redundant data, so this transaction files is going to get hefty in size very soon, perhaps could this idea be utilized (I havn't tied it out yet!!!):/

http://www.nightwing.com.au/FileMaker/demos/AdjacentRecord.sit

....with dynamic valuelist, but it's then unstored values calculating on other unstored values, which I guess is almost as slow as aggregate functions if not much much slower??

If you have considered upgrading to fm8.5 should you investigate the uploaded template of mine:

http://fmforums.com/forum/attachment.php?attid/8288/

The topic as such is called crosstab reports, and it's important to recognize that filemaker always have been a little underpowered for such matters, but also recognize that the borderline between functions and script are very different to a spreadsheets cut to macros. Your wish to have live updated summaries for each instance without making a dedicated subsummary report, is kind of pulling a database out of it's realm.

There are other ways to get the figures pretty fast although not instant, and it's either:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

or this which have similarities in the approach:

http://www.kevinfrank.com/download/incidents.sit

--sd

[djeans]

Thank you for your very thorough reply. I will look at each solution. I also have a demo of 8.5, so I will try the last one to see if it works well.

Darron

[Søren Dyhr]

Only mine is requirering fm8+ the rest are of some age!

--sd