Running Totals, within Groups (how to...?)

[ffarmer]

Posted

[RalphL]

The Troi Numbers plug-in may do what you want. http://www.troi.com

[ffarmer]

Does that mean FM7 itself can't do this? Really?

Surely, it must be a frequent requirement.

I am hesitant to muck around with 3rd-party plug-ins.

[ffarmer]

I've just been told my a FileMaker consultant that what I'm trying to do CAN'T BE DONE out of the box ...that it might work with some clever calculations and relationships, or a scripted loop.

I'm floored. I can't believe the seventh generation of FileMaker can't do such a commonly used summation.

My project depends on this capability, but I refuse to have to "hard-wire" some fancy code to achieve such a basic thing. I'm really pi**ed at FM7. This is one major shortcoming I've found in FM7, but there are several others that bug me (like it's inability to tell you the total number of pages in a report preview so you can show "Page n of xx" -- you need to create a ******* script for that and even then it's not always accurate)! The page number thing may not be a show-stopper, but the running totals thing is.

I bought FM7 so I could run my relational databases natively on my Mac. Looks like I'm reverting back to VirtualPC + Windows + Access (which I know DOES do what I want) for my relational database applications on Mac.

FM7 = BIG DISAPPOINTMENT !

[CobaltSky]

FileMaker provides out-of-the-box canned solutions for a selection of simple quick-fix requirements - and it provides a powerful and extensible scripting and calc interface for everything else.

I don't know where you get the 'hard-wire some fancy code' notion. Scripts and calcs are not some fancy add-on - they are part of the core application, designed to deliver the bulk of its flexibility and functionality. If, as it seems, you're trying to develop a solution but you regard scripts and calcs as beneath your dignity, then it's scarcely a wonder you're disgruntled.

[ffarmer]

When doing something as basic as a Running Total within control break group, I'd expect to be able to define a Total field, with a check box for "Running total" (just as FM provides now) PLUS another checkbox to indicate whether I want this running total to be "within control groups" OR "through to grand total". If there's mutlitple control breaks in the report, the running total would end according to which subsummary line it's placed in. Simple.

So simple, so basic, so common a need. I shouldn't have to develop script code to achieve this. It should be standard, built-in feature in FM7.

I feel the same way re getting "Page n of nn" capability on a report. Very useful thing. Access, Excel and Word handle this beautifully (&Page of &Pages). Why can't FileMaker?___ Why couldn't we say "Page ## of zz" (or some reserved word for total pages)?

Make life simple!!!

I spent over 30 years coding, programming (mostly mainframes). I know what it's all about. But with today's capabilities, I'm a strong advocate of point 'n' click for end users & consumers. When PCs first came out, and I saw CPM then DOS, I thought "where's the progress" in computing? No better than IBM's infamous JCL language. But when I saw Mac (in 1984), I was sold, and have been a Mac user since it first appeared. (Apple made some very bad business decisions years ago which kept them from becoming the dominant platform. Nevertheless, they survived, and are still a leader in computing advances).

FM7 has disappointed me. It's not as user friendly or as intuitive as it could be, and lacks a lot of work-saving features that should be included in any software that's reached its 7th version.

I must be (partly?!) masochist, because I'm still with FM7, and still have problems (even with many suggestions garnered from these discussion groups). Weeks of non-productive time, trying to solve simple needs. Life's too short for this. I wish there was a Mac version of Access.

End of rant!

[ffarmer]

Someone from this discussion group has kindly spent significant time helping me with getting running totals within control groups. He developed a script for me which works and the results are correct on my report.

HOWEVER, I want to EXPORT the rows and columns from my report to Excel for further processing BUT the Running Totals DO NOT export correctly (the export process tends to pick the first values in the running totals calculations). This means the exported figures are not the same as what's shown on the report! Crazy.

We both spent considerable time on this for the last few days. Does anyone have an answer???________

[CobaltSky]

ffarmer said:

Someone from this discussion group has kindly spent significant time

I take it that the 'someone' you are referring to is someone else and that the script you are having problems with is not the one that I posted for you five days ago on another thread on this forum at:

View Alternate thread here...

The script I wrote on that occasion can be readily adapted to return its results in the form of an export file, without exhibiting the kind of problems you are describing.

In fact I am attaching a modified copy which does just that.

ReportExport.zip

[ffarmer]

Ray, the "someone" was someone else! However, I did take a look at your previous attachment (thanks for the effort!) and see that your script is very similar to that someone else's (for setting up global holding fields, and testing for a change in group value, etc. Simple enough logic). The resulting report is good. Correct results.

HOWEVER!!!... exporting the data is something else. I tried exporting from your report. Couldn't access menus from your Preview mode, so after hitting Return, as requested, and returning to Browse mode, I exported the data. Export wouldn't let me select any grouping. I exported all three fields, and opened the resulting file in Excel. The "Running Total" came out looking just like it does on the Browse-mode report, showing "- - -". Hmmmmm. Back to the drawing board....

So, next, I duplicated your script, and deleted all the steps after Enter Preview Mode [], so the report would remain in Preview mode displaying the correct running totals, so they would be visible while I do File>Export.... (This may be irrelevant because it appears that, in FileMaker, what a report shows and what Export does are entirely unrelated -- unlike Access which exports precisely the report you see before you). In any case, I added a second button on your report to invoke the revised script (no other changes to the script or report). It produced the report with correct values (like yours did) and remained in Preview mode. I was then able to go to File>Export, and all the fields were shown in the Export panel. Exported. Opened resulting file with Excel..... drum roll......

Oops. This time Running Totals had values. But not correct ones! They were the first value in each group, not the running total -- which is the same problem I'm having with the other case I'm working on.

How can you say your Export works?! What am I doing wrong?_____

Thanks for spending time on this, mate

Frank

===========

[CobaltSky]

Hi,

It seems you haven't yet gotten around to looking at the file I atteched to my previous post on this thread. Yet you are responding to my message and telling me it doesn't work?!!

When you do look at it, you will find that..... drum roll......

It works.

*That* is how I can say that it works.

[ffarmer]

Thanks Ray!

I downloaded your db again, and it DID work correctly this time! -- both report & export. (Honestly!!!...it didn't export correctly before!! -- for me, at least.) This time, perfect.

Thanks very much for your effort on this. I will document your technique for future reference.... Good on ya!

My "real" application is a little more complicated than this one. It involves transactions which have ItemName, XtnDate (yyyy.mm.dd), Amount, and Units for each transaction.

I create a summary report showing:

ItemName Year (of Xtn) TotAmt Units

The report shows one line per Year per ItemName.

TotAmt is the total transaction Amounts per Year for that ItemName.

Units is a running total (like your example), year by year, for each Item Name (i.e. the running total crosses the Year control breaks, but starts fresh with each ItemName control break).

I sense this should be possible (to report and export), using scripts similar to your example. Would you agree?___

Frank

===== P.S. I've been visiting Australia frequently since 1974 -- love the place! ======

[CobaltSky]

ffarmer said:

I sense this should be possible (to report and export), using scripts similar to your example. Would you agree?___

Not necessarily, Frank.

The elements are different - and there are more of them, but that in itself is not a problem. However from your description, the lowest 'level' of data you want in your report is years and the year data is itself to be aggregated from constituent transaction records (ie there are multiple records being combined to produce each year entry in the report).

That being the case, you will need a script which deals with two layers, first aggregating the data by years and then by items, and you will need to use a final sort which reverses the order of records in the lowest sort group (ie year) in order to bring the final count total to the top, and then use the 'group' option when exporting to bring the data output up to second order (year rather than transaction) - otherwise the report wil default to the first (record) order.

All very do-able, but a layer or two of additional logic required beyond the simplified example we've been discussing.

[ffarmer]

Ray,

You've given me something to build on. Looks like I need to create a logic flowchart to map out my logic, and develop a script, or two, for my application.

My report is actually even more complicated, because on each line, I also want to show...

UnitPrice

AsOf (yyyy.mm.dd)

MarketValue (= Units * UnitPrice)

where UnitPrice and AsOf date come from another table (MarketPrice Table) containing:

ItemName

QuoteDate (yyyy.mm.dd)

UnitPrice

which has several records per year, for each Item, showing the UnitPrice for each QuoteDate. For the report, I want the LATEST UnitPrice quoted in each year (that matches Year in the report). That latest QuoteDate in each year would then become the "AsOf" date on the report.

I will also want to export the UnitPrice, AsOf, and MarketValue with the fields discussed earlier (ItemName, Year, TotAmt, and Units). Units, being a running total within each ItemName control group.

The report shows annual 'cashflow" for various investments (Items). By exporting this data, with the latest MarketValues, I can then calculate IRR (internal rate of return) for each investment. That's my goal. It worked with Access & Excel, It's gotta work with FM & Excel!

Linking the Transactions table to the MarketPrice table by ItemName is easy. Picking up the latest annual price quotes from MarketPrice table poses a challenge (for me).

Thoughts on how to do all this?.....

Frank

(P.S. A reminder.... I'm totally new to FM, and don't know much about its nuances! And I don't, yet, have a decent manual. Flying blind).

Thanks--

==================

[CobaltSky]

Hi Frank,

ffarmer said:

Thoughts on how to do all this?.....

Yes.

The relationship you are creating will draw back the market value for each transaction and you simply need the last to display on the year-grouped lines of the report.

Depending on how you have the report layout set up, either the last or the first value will appear in the relevant sub-summary part, if you simply place the field from the other table (sourced via the appropriate relationship) directly into the sub-summary.

If you find that 'straight off' you are getting the wrong value (the first rather than the last), reverse the sort of the final group. This will mean adding a further sort key at the end of the sort for your report, which sorts descending on record chronology (probably transaction date, but RecordID would likely do as well). This principle will apply to the running totals and also to the 'As of' date, so aiim to take out three birds with one stone.

The same will apply to the export process *except* that depending on how your sub-summaries are structured, the report may require that the internal sort of the lowest strata of the report groups be sorted the other way around from the preview and print version, in ordewr to pick up the appropriate (last rather than first) runTotal, MarketPrice and AsOf values. If so, you may need an extra sort step in your export script to flip the sub-stratum record order before export.

[ffarmer]

Ray,

I'm happy to report I got the running totals working by using "nested IFs" in the Calc Running Totals script. One total "breaks" at each change of year, the other breaks at change in ItemName. Report looks perfect. I based the script on your example.

The Export also works fine, thanks to your tip re sorting the "low hierarchy" records in descending sequence, which I did just before the export command. Works fine. (beforehand it was giving me the first running value in the break set rather than the final value in each set.

Thanks for your help! Now, I'll study your latest suggestion re picking up the final unit prices for each year...

Frank

=====