where best to store summary field(s)?

[mzimmers]

Hi -

Another question from a beginner: I'm trying to wrap my brain around reporting. I have a DB that I'd like to sort by RMA number. Within each RMA, there can be multiple records (and for each records, multiple related records).

So...how exactly does the summary field work, anyway? Do I need one for each record I'm reporting on? Or should I create it as a global? Does it generally go in the table that you're reporting on?

Thanks for any clarification...

mz

[IdealData]

What you'll need on your report is a sub-summary part (typically above the body part)

You will need the summary field (it cannot be a global) and there is a specific field type called "summary".

The summary field can have several different formats, but typical examples are "Total", which will totalise the specified field. The totalisation only happens when the field is presented in a sub-summary part and will totalise based on the sorting order that is active for the current data.

The related records pose a different problem, but for now just try summarising data in the main file.

[mzimmers]

OK...I created a layout for the report. I have a summary field in my record. I chose it to summarize on the repair cost. My hope was to have a report that looks something like this:

RMA

label / cost

label / cost

label / cost

total cost for RMA

(repeat for each RMA)

(finally, one grand total.)

The problem is, I'm getting a total for each label, not for all the labels with a single RMA. What did I do wrong?

Thanks...

[Genx]

... the summary evaluates according to two things... a) what you actually specify it to evaluate i.e. fraction etc... it will evaluate according to its location within a layout... meaning... if you put it in the body... it will evaluate only for that record... if you put it in a subsummary part, it will evaluate for all records that fit into that subsummary, if you put it into the grand total, it will evaluate according to all records within your report.... and you can use it multiple times in different parts throughout your report...

hope that helped

~genx

[mzimmers]

Thanks for the response. I changed the part definition that contained the summary...I made it to summarize when sorted by the RMA field instead of the label field.

Now, instead of getting a summary for every record, I'm only getting one summary at the end. I expected to get two, since I have two RMA numbers in the records I'm reporting on. What do you think I did wrong *this* time?

Thanks again...

[Genx]

... sort by rma and put it in the sub summary part... even if that is above the body... not the grand summary... the grand summary will summarize ALL records...

~genx

[mzimmers]

I *swear* that's what I'm doing...but I'm not getting sub-summaries when the RMA number changes. Do I have to define the field as a sub-summary or something?

I should probably give up for the night...

[IdealData]

FMP is very fickle about the placement of fields on a layout. Your RMA may "appear" to be in the sub-summary, but just to be sure why not add more depth to the sub-summary part and make sure you can see"daylight" above and below the field borders for RMA.

Whilst you're at it - check that you have specified the sub-summary part to summarise when sorted by RMA.

[mzimmers]

OK...here's what I'm doing:

1. Create new layout/report

2. Name it and select the table I want to use

3. Choose Columnar list/report

4. Choose Report with grouped data, and check Include subtotals

5. Specify the fields I want (I include my summary field here)

6. Organize by two fields

7. Specify my subtotal field

8. Select a theme

9. I don't choose any headers or footers

10. I don't create a script

11. View the report in preview mode

I get a grand total in each record, plus the repair cost (one of the fields) shows up THREE times per record.

I know I'm doing something very wrong, but...this doesn't seem to be one of the easier features in FM.

mz

[LaRetta]

Can you post your file or a sample of what you are doing, mz?

[mzimmers]

You bet!

Test DB

I didn't bother to zip it or anything; hopefully that's OK.

Thanks for looking...

mz

[Lee Smith]

FileMaker files have to be compressed. Zipped works best.

Lee

[LaRetta]

I have a DB that I'd like to sort by RMA number. Within each RMA, there can be multiple records”

But your FIRST leading sub-summary is based upon Label.

What do the Cards::Serial number have to do with your RMA report sort in your script? Your report must be sorted by RMA then by Label Number. And the parts must also match this sequence; RMA first, then Label. I’ve attached your file back. I added 15 or so test records. With only 4 records it is difficult to tell if the results are correct particularly when working with summarized data.

Fire your FMA Report script and see if we’re getting closer. If not, we can adjust.

LaRetta

DentalCardsREV.zip

[LaRetta]

Hi Lee

Either that has been fixed now or it was because this was a very small file.

L

[mzimmers]

LaRetta -

The serial number field is just another data field that has to appear on the report (sometime)...if it's getting in the way of trouble-shooting I'm happy to remove it.

I wanted the report to summarize by RMA number. The way the DB is supposed to be set up, many labels can be put under a single RMA. (I don't think there is a need for an RMA table.) So, I'd like to sort by RMA, and have all the labels (and their costs) to appear with each RMA.

It sounds like I did a few things wrong, doesn't it?

Thanks...

mz

[LaRetta]

Try this one then ...

:wink2:

DentalCardsREV2.zip

[mzimmers]

You, my dear, are magic.

OK, give it up...HOW did you make it work? Or, more to the point, what was I doing wrong? I notice in the layout that you have operands demarcated by << and >>; I didn't have those. What is the significance of those? They look like text, but I'm sure there's more to them than that.

Another question: report layouts seem to be easier to create than to modify. Do people generally just throw them out and start over if they don't like them?

Once I understand what you did to make this work, I'm going to tackle the issue of getting fields from another record into the report. But...first things first.

mz

[LaRetta]

I notice in the layout that you have operands demarcated by << and >>

I'm afraid I cheated because I wanted to incorporate text into the IDs so it was obvious which was which. Those are merge fields. You can select them by selecting from Menu (while in layout mode) Insert > Merge Field. Reports don't *need* to be made up of real fields.

Report layouts seem to be easier to create than to modify. Do people generally just throw them out and start over if they don't like them?

I don't see a way of rearranging the parts if you change your mind (drag them up or down). As it is, you must respecify them (I think) and, you can't switch order of the part until you rename the first because you can't have two parts based upon the same field. It feels clunky to me but then again, I've probably just missed a way to do it. Adding a part is really easy so I'll usually just delete the offending part and re-create it.

Here is the secret to the sequence. Largest group starts (in your case RMA). Then if you want another 'group' it would go as next leading sub-summary. If you had a field called Part Type for instance, you would put it next. Then RMA would split *its* records by the Type. So sequence of the sub-summary parts MUST MATCH the results you want.

And the last key ... sorting must then match the part order. You had a sub-summary for Labels. But each Label was unique so you didn't need to 'group and sum' them. I deleted the leading and the trailing Label parts. When you create a report, select the fields to place in the ORDER you want the parts and watch the display in the box (lower right) when you select the categories (RMA). FM will show you how the data will be grouped. Just be sure to sort on those same fields in the same order.

And if you want a report of only the TOTALS per RMA or whatever, you can delete the body totally after the report is created. It is unnecessary unless you need the detail to show.

LaRetta :wink2:

[mzimmers]

Heh...man, is this ever mystifying. I think the key is that, as you said, your sorts must match your summarizing.

I'm still a bit perplexed about how to get to all of the setup information for a layout. How, for example, would I go back and check/change what field(s) I specified to sort on?

Anyway, I seem to have a working report, though I wouldn't bet on my ability to regenerate it from scratch. I am ready, however, to begin including related records. Any pitfalls I should know about?

Thanks for all of your help.

mz

[Vaughan]

LaRetta wrote: "I don't see a way of rearranging the parts if you change your mind (drag them up or down)."

Go into the Part Setup dialog, you can drag the parts to re-arrange them (some, like header and footer, are locked in position).

[LaRetta]

:shocked:

Thank you!!

:smile2:

[LaRetta]

"... to begin including related records. Any pitfalls I should know about?"

Usually it works better to generate your reports in your child table (the 'many' side of the relationship). :wink2: