Order on Calc Field

[bruce]

I have a database of customers and one of invoicing.

How do I print a report that orders the customers by the most invoicing amount to the least.

There seems to be no way to do this in the report ordering. I can not order on a calculated or summary field.

It seems so simple.

[Al Buchholz]

RE: Order on Calc Field

Bruce

You should add a field to the table and call it report_total. Have a procedure that zeros out the field, puts the total value in the field, and then the value can be used in the filter.

It's easier to do than to explain.

[bruce]

RE: Order on Calc Field

I would agree it is easier to do.

You lost me.

Could you attach an example?

[Ken L Herrold]

RE: Order on Calc Field

Bruce:

I do this with a field-rule add post. The customer record has a field [n] 'purch'. Each invoice's total is posted to that field accumulative [add]. When report time comes it is quite easy to order on that field ascending or decending.

Reconfigure and insert this field in the customer db then you can globally update the field as calculated wih value with DBSUM() from you invoice records. After the update, create the field rule to post the totals from now on.

I'll make you a model and attach it when I get the time, if you don't understand.

Ken

[bruce]

RE: Order on Calc Field

I completely understand.

I have always ben leary of the post command.

It takes a great deal of planning to make sure things aren't posted twice or what about alterations, deletions, .....

Would it really be that tough to include as part of Alpha? I was shocked to find that there are few threads on this topic. It seems like a fundamental part of database reporting.

[Tom Cone Jr]

RE: Order on Calc Field

Bruce,

Here is an example of the kind of thing I think Al was referencing.

Unzip the attachment to an empty folder.

Enter new child table records, or edit existing records.

Then run the only update operation you'll find.

The update opn will calculate new values for the Customers->ReportTotal field. This field can be used to set the report sequence in ascending or descending order.

The scenario would be like this:

1) Data entry session ends
2) Update opn run
3) Report run

Simple.

-- tom

[Tom Cone Jr]

RE: Order on Calc Field

oops.

Here's the example.

[Al Buchholz]

RE: Order on Calc Field

Bruce/Tom

This is a sample database that Bruce sent me with the code that I'm suggesting.

There are two approaches that I've shown.

1. The first is to total all the sales for a customer. I added a field to Bruce's table called:
Customer->Posted_Total_Sales.

This field is kept up to date with a posting rule for sales->sales_amount for new and changed records. Since Bruce sent me data in the table, so I wrote a posting operation (Post sales 2 cust post) to fill the field for existing records.

I changed his report to use the new field to sort on.

This is very similar to what you did, it just uses posting instead of the update and dbsum approach.

This should give the results needed if total sales is calculated based on all the sales for a particular customer. Since most of the time what is really needed is just a selected group of sales records, I created approach two.

2. The second approach uses a query to find a subset of sales records and then figures out the total sales by customer for only those sales records.

In this case I created another new field:
customer->Report_Total_Sales

The sequence is a little more involved but is:
a. Query by form to get the records desired
b. Zero out the field customer->Report_Total_Sales
c. summarize the queried records to get the total by customer
d. post the summary table to the customer table
e. report on the queried records and use the Report_Total_Sales field

These 2 routines are in the buttons on the MenuMenu form and can be followed from there. The second approach requires knowledge of query by form.

I also have this in version 5 code since it didn't see that this was in version 4 forumn until I was ready to post it. The only difference is that the query by form genie is more user friendly.

The caveat that I see in the second approach is that is not multi user friendly. Two users doing the same sequence at the same time could overwrite each other in the total field. It may be necessary to use a control table/field to allow only one person at a time to do this.

Hope this helps.