Custom Sorting

[Mantic Cow]

Hi

I'm working on a FM9 DB (Mac and PC) that generates a number of different reports using multiple sub-summaries. My client wants to be able to turn parts of the report off (disabling sub-summaries from the sort) without using the built in FM sort dialogue (ie they want a layout with checkboxes for enabling / disabling different parts of the report). Is this possible?

Secondly, they want empty sub-summaries to be removed from the report automatically (ie if a report runs and a sub-summary part is empty, then it should be left off all together, so excluded from the sort).

Any help would be appreciated )

[bcooney]

You'll have to script it. Perhaps there are only a few valid sub-summary combinations. You could have a script that stores the sort order for each combination.

I don't understand the empty sub-sum part of your question. Perhaps you could elaborate.

Have you seen this demo from Ray Cologon? One Layout, Many Reports

[Mantic Cow]

Hi Bcooney

Thanks for your suggestion - unfortunately they are many possible sub-summary combinations and I'm really trying to avoid a massive nested IF / CASE situation.

Thanks for the heads'up on the Dynamic Layout Technique - I'll see if it can help.

As for the empty sub-summary situation, what I meant was that if a report results with a sub-summary containing no data, I would want to turn that sub-summary off automatically. Each sub-summary has a title and I'm trying to avoid spurious titles / blank space when a sub-summary contains nothing.

It is for this reason that I haven't been able to go down the other route, that of generating many report, exporting each as a PDF and then appending each PDF, thus creating a single PDF. Using this system results in lots of blank space, which my client is less than happy about.

Any other suggestions from anyone?

[bcooney]

The "title" in the subsummary could be a merge field of a calc., and with sliding on, it should disappear.

[Mantic Cow]

Yes, I came up with something similar but there are more than just titles, which is why I wanted to disable the sub-summary if it was blank (I felt that if I - or someone else - could come up with a neat way of disabling parts of the sort, then it wouldn't be difficult to use the same technique to remove the blank sub-summaries.

[comment]

If there are no records in a category, Filemaker will not create a sub-summary part for it.

[Mantic Cow]

Quite - however as explained, there is a title (not to mention boxes and lines) in the layout within the sub-summary. These are being printed regardless of whether the sub-summary contains records or not.

If I can put the "automatic disabling of a sub-summary from a sort when it is empty" to one side, my primary concern is creating a report which allows a user to enable / disable fields within the sort without using the 'sort dialogue' built into FM. If I can do that, then disabling blank sections can be accomplished as well. Thanks )

[comment]

there is a title (not to mention boxes and lines) in the layout within the sub-summary. These are being printed regardless of whether the sub-summary contains records or not.

I don't think so. However, if there is a sub-summary part, and the records are sorted by its breakfield, there will be at least one instance of the sub-summary in the final report (provided there is at least one record in the found set).

To answer your real question: I don't know of a good way to let users sort their records without giving them access to the sort dialog. Perhaps they could use table view for this, before they move to the print layout. Another workaround is to use unsorted calculation fields for the sort - but this requires handling different data types and is too slow to be practical with more than a handful of records.

IMHO, it's best to provide a fixed number of scripted options.

[Mantic Cow]

That's as I feared, and the only viable option I could think of. Not being able to script custom sorts easily is irritating and, considering what else is available in FM, feels like a missing feature.

Many thanks for all your help - now I'll have to browbeat my client into accepting the solution : )

[David Jondreau]

Sort by calculation would be an excellent feature.

[comment]

The feature already exists - it's just difficult to implement and slow in performance, for reasons stated earlier.

[David Jondreau]

Then it's not really a feature, is it?

Sort by Calculation[] as a native feature would by an excellent addition to the FM script steps.

[comment]

How would that be different, in any significant way? The calculation formula needs to be the same. The results cannot be stored, so again there is no index.

[David Jondreau]

To be useful the script step would need the same features as the current sort box...namely the ability to set each field ascending, descending, or by value list; override default language; and sort by summary field.

[comment]

I must be missing something. Doesn't the current Sort Records[] step does exactly that? Where would the calculation part come into this?

[bcooney]

The need for a context-free sort script step is what I imagine the goal is.

[Josh Ormond]

The idea of sort step where you could dynamically set the fields and sort options sounds appealling...but man, difficult to implement.

I could see the Specify dialog being one complicated mess of If/Case statements. Fascinating, but it makes me wonder (at least initially) if it is just easier to use a calc field based on a field's value or a global value. Then sort based on the calc field.

[comment]

If you mean that the sort fields could be specified by a calculation (á la Set Field By Name) - OK, I can see how that would be useful. But I don't think FMI could implement that easily, esp. since the number of fields in the sort order is not constant.

I would be happy with just restricting the fields users can select in the sort order. Being able to relabel the fields (e.g. 'Show field comments instead of field names') would be a special bonus.

[mr_vodka]

I would be happy with just restricting the fields users can select in the sort order. Being able to relabel the fields (e.g. 'Show field comments instead of field names') would be a special bonus.

Yes most helpful as they should allow in exports as well. If one could export or sort by either the field label or comment as you had stated then it would help so much for end users. Many times I am reluctant to give this directly to end users since they have no clue what my field names mean.

Sometimes I will have to use a "translation file" with calculations with the names that they want to see, referencing the data file; only because they want an export to Excel the way they want to see it.

Now give me that ability in conjunction with what Michael said earlier regarding a list of fields to select from that I pre-designate and I would be more than happy to open up FULLY exporting to my end users.

[Mantic Cow]

Well you can tell where I stand on the issue : )

A number of the sub-summaries in my report are based on fields within the layout table itself - not on related tables. Consequently, even if the field itself is empty, the record isn't - so the SS is going to display.

I have to use sliding / shrinking to hide those blank fields but of course, you still get the white space between the fields showing up on the report. Thus, when running a report on a record with a number of empty fields that are associated with the sub-summaries, you get blocks of large, combined white space

I'm thinking that the only way to rid myself of this is to use calculated fields instead of the white space between the sub-summaries / fields / titles and shrink them as well.

Crazy.

[comment]

Well, at least exports do not really benefit from indexing - so that's something I would consider tackling on my own, if necessary.