Need to save sorted totals outside subsummary part

[andypb]

Somebody tell me if this is even possible.

I have an invoice layout that subtotals the rate and quantity for several categories on each line.

example:

Color Prints 20 1.50 ea $30.00

Zip Disk 1 20.00 ea $20.00

Now, below the sub-summary part, I need to show the line totals again, all on one line.

example: Color Prints: $30.00 Zip Disks: $20.00

How can I save the line totals from the sub-summary and use them in a field elsewhere? I've tried using calculation fields below, with an If(category="Zip",line_total,0) calculation, but this always zeros out the field when the category doesn't match during a sort.

Help!

[Kurt Knippel]

Assuming that you are using Summary fields to get your "line totals" you can use the GetSummary ( fieldname, break field ) function to do this. It is a normal calculation field and can be shown outside of a sub-summary part.

[andypb]

The line total is a calculated field, not a summary field, since it shows quantity * cost. The subsummary is sorted by category, and each category (line on invoice) has a total calculated. It's this line total calculation that I want to save for display use outside of the subsummary part.

Help!

[Kurt Knippel]

Whoa, you are really mixing terms and possibly screwing yourself up for no reason. Calculation do NOTHING special in a Sub-Summary part and Summary fields do NOTHING at all in a non Sub-Summary part, so you either have a calculation which is simply showing what it could show anywhere or you have a summary field which is summarizing something, perhaps you have both.

Here is how I would setup the problem:

"Category", text field (or whatever)

"Quantity", number field

"Cost", number field

"LineItem_total", calculation field = Quantity * Cost

"SubTotal_sum", summary field = Total of LineItem_total

"CategorySubTotal_calc", calculation field = GetSummary (SubTotal_sum, Category)

You would use category, Quantity, Cost and LineItem_total on your data entry layouts. SubTotal_sum would be used in a Sub-Summary part to summarize the LineItem_total for that part. CategorySubTotal_calc could be used in any layout on any part, but would show the summarization of LineItem_total for the Category of the particular record you are on.

If you still do not follow, then lay out your fields in a similiar format so we can see what you are actually working with.

[andypb]

Yes, I am a FileMaker newbie! Apologies for mixed-up terms. But you've managed to grasp most of the problem. Your setup is basically the same as mine, so let's go with your fields and let me add this comment: CategorySubTotal_calc is not useful outside of the sub-summary part, since it needs a category for the break field. I need a field that holds the calculated (summarized, whatever) value of a certain category, and can be displayed outside the sub-summary part.

What I am looking for is something like this: "Total_CatA", calculation field = GetSummary (SubTotal_sum, Category="A")

And similarly for Categories B, C, D, E and F.

Is there a way to specify a *specific value* for a Break Field like this?

[andypb]

In the interest of Science and the Common Good, I will explain how I solved this problem.

I ended up writing a series of scripts. Basically, they each set up a Find for the specific category code, then perform the Find. Then I use the GetSummary calc field on the found records to get the total for that line-item, (which results in the same total as in the sub-summary part on the layout) and store it in a display field for that category's total. Then I do this for each category. The brute-force method.

In all honesty, I don't know about FileMaker... I mean, it's kind of cool, but it's kind of kludgey too. I guess that's what you get for being a Claris product, I mean Apple product, I mean Claris, no Apple.....huh?

[harryk]

It's not quite the subject anymore, but I like to react on your last sentences. I am a newby with Filemaker (v 5.0), I am in it for two months now but I am the kind of persisting animal; I won't stop searching until the solution is there. I am transfering programs I wrote for CP/M machines and Atari performing administrative functions and because I do not want to end up with too many computers (still use the old ones..) I started writing them to Filemaker. My experience till so far, is that EVERYTHING I had realised for that old machines, is possible in Filemaker too. There are things kludgey, clumsy, especially in the work-environment. Why that little screen to change the lay-out order, why not remember the last window size for the script-editor, why not start the script editor with the last script selected, and there are more of that little things. Why the need to perform an ENTER when changing object sizes (Quark doesn't, Adobe doesn't). There are those little things. But eventually, it gets the job done. It just takes a while to grasp the meaning of things. I found the subsummary thing very confusing in the beginning.

The nice thing about Filemaker is that in it's apparent clumsyness (not the right word, sorry I'm a dutchie), in the end, everything is possible. It's sometimes like a shop with all kinds of stuff you think what do I need it for, to conclude after a while that it comes handy, and that it makes things work. That's what counts for me. I've no experience with other databases, but till sofar I am far from disappointed.

Harry

[Vaughan]

Many of these issues -- window sizes being forgotten for instance -- are addressed in FMP 5.5.

[harryk]

Sorry, last off topic topic..

Do you happen to know whether Filemaker 5.5 is downward compatible to 5.0?

Harry

[Vaughan]

Yes -- though new features (like the Allow Toolbars [] function) are excepted of course. Note that AFAIK FMP 5.0 won't choke on the new 5.5 features, they just don't work.