Omit subsummary result?

[spb]

I'm not sure if the Scriptmaker area is right for this, but I'm scripting my way into to it, so why not?

I have a customer db related to a backorder db. The latter contains many line items. I can isolate "shippable" backorders (stuff that's come in) and generate a list view report by customer of all their shippable backorders. I am using a subsummary part that contains a calculated value summation for these backorders. The problem is, I would like to omit from this report all customers (and their line items) for which this calculated value is less than an arbitrary amount, say $50. In general, two-thirds of customers & their line items will be omitted if I can do this.

I've tried various ways, but I can't figure out how to run a Find that omits this summed value.

Steve Brown

[Kurt Knippel]

Make a constant and a calculated flag field:

fShowTotal = IF ( GetSummary ( sBackOrderTotal, CustomerNumber ) > 50, 1, 0)

cConstant = 1

Now setup a relationship between fShowTotal and cConstant.

Place your total fields in the sub-summary part and put a portal over it based upon this relationship. This should only be valid if the customer's back orcer total is over $50 and thus will show this. Otherwise a blank line will be displayed.

This is only one of the many ways to accomplish this and may or may not work for you, since I do not know how your report is formatted.

[spb]

I'm fascinated by the GetSummary function, which I haven't used before. However, setting up the flag field as you defined doesn't seem to work. Neither 1 nor 0 shows up, whether I sort on the break field or not.

My report doesn't have to look pretty, it's an internal tool. But your idea of setting up a self-join and superimposing a portal on the subsummary doesn't seem to be valuable to me. If the result is to make a blank line(s) over less-than-$50 results, the printed report will be 200 pages of mostly blank lines, instead of four pages of useful info. Also, some customers backorders can be fifty or more lines long, causing the superimposed portal to break across pages. Here's what the report looks like. A leading "S" indicates a subsummary line, and "B" indicates the body part which displays the line items:

S CustNbr Custname Value (a summary field totalling value of line items)

B QtyOrdered Item# ValueCalc (qty * individual value * cust discount)

So the report could look like:

S

B

B

S

B

B

B

B

B

etc.

The full list is 200 pages long, and I'd like to print a report that contains both subsummary and line item body parts for only those backorders where the value in the subsummary sum field is > 50.

If I can successfuly use the GetSummary function in a flag field, I realize this field will change depending on the sort. So my script should be something like:

sort by break field

find summary > 50

omit unshippable criteria

go to layout, preview, print

Steve Brown

[The Bridge]

Just a quick note: always Find before Sort.

[spb]

Normally finding before sorting is routine. But in this case I need to run a Find on a summary field that won't have anything in it unless the data is sorted on the break field first. This seems to be the root of my problem. I have to sort first, to get data in the field. Then I should be able to find & omit the less than fifty dollar summary records and resort on the break field. But it doesn't work.

Steve Brown

[LiveOak]

I think you need to attack this problem in the customer file. You can create a relationship from Customers to Backorders that shows only shippable backorders. This would use a compound key with both the customer number and a calculated flag for orders greater than $50:

In Customer:

Key (calculation, text, indexed) = "X" & Customer#

In Backorder:

Key (calculation, text, indexed) = If(BackorderAmt > 50, "X", "") & Customer#

You can sum the shippable backorders for a customer using a calculated field in Customer:

BOSum (calculation, number) = Sum(relationship::BackorderAmt)

A find can now be performed upon this field. A customer list of backorder totals can be printed out of the Customer file (even sorted by backorder amount, a hard thing to do with just Subsummaries). If you need to list each backorder, there are methods to isolate the corresponding Backorder records.

-bd

[spb]

>f you need to list each backorder, there are methods to isolate the

>corresponding Backorder records.

I had originally done this job in the customer file, using a keyed relationship similar to your suggestion (in fact, all the calc fields are still there). I'd placed a portal in which the shippable backorders can be viewed. But I need to print the line items. Printing a list that includes a portal that can vary from one line to 200 is problematic out of the customer file.

This is why I started fiddling with a subsummary layout in the line items db. I found that if I Find a group of customers with >$50 shippable backorders in the customer file, when I make a go to related records/run external script set, to end up with a printable report in the line items file, then I get only the set corresponding to the single customer record displayed in the customer file, not the whole found set.

Is there some way to get these line items into the customer file without using portals?

Steve Brown

[LiveOak]

You can use the "Go to Related Record" script step to isolate the desired set of Backorder records. Create a new global text field in Customer (call it gMultiKey). Create a new relationship between this field and the key field you just added to Backorder. Now all you need is to capture all the values of "Key" from before and paste them into the new global field as a multi-key (key separated by carriage returns).

Create a list view layout with only the field "Key", no labels or other objects...period (call it Key List). I'll assume you have a current "All Field" layout. To jump to the set of records in Backorder use the script:

Enter Browse Mode

Go to Layout (Key List)

Copy All Records

Go to Layout (All Fields)

Paste (select all, gMultiKey)

Go to Related Record(show, NewRelWithgMultiKey)

That's it!

-bd

[spb]

It works! Many thanks. Cap'n Kurt and LiveOak, you da men! (I presume you're men, anyway).

I've never used a global multikey in just that way before, and it looks like a powerful tool I'll be reusing in other situations.

I had one extra wrinkle, in that I had to do some calcing to arrive at my "shippable" criteria before I did any summing up. This means that the key field in the Backorder file was a calc field & FM told me a relationship based on it wouldn't work. So I set up a text field & did a replace-with-calc to fill it with the calc key & related to *this* one. Eventually I can add this step into the script. It's a bit irritating as it takes a while, but I shouldn't need this report more than twice a month.

I handed the printed result to our customer service people and they were exceedingly grateful. I told them to silently thank Cap'n Kurt and LiveOak, that I couldn't take credit for it.

Steve Brown

[LiveOak]

Happy to be of assistance.

-bd (Brent Dussia, Live Oak Systems)

captkurt (Kurt Knipple, Database Resources)