The highest of operator find the highest value of FIELDNAME across all records in a related table match the specified selection criteria. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of a report.

TABLENAME

You canuse Count of directly on a table without a predefined Relationship. Just remember that if there is a Relationship defined that doesn't have an alternative relationship name, this relationship will be named the same and the table and be used. 

RELATIONSHIP

If you use a relationship (alternative name) then the count of will be automatically restricted by the relational restriction. Read under TABLENAME for functionality when using Relationships without alternative relational name.

You can define an ad hoc named relationship directly in the Count Of function. If you do this you will possibly achieve two things. 1) You will insure against DataEase using a pre-defined relationship with the same name as the table. 2) You can re-use it again in the same script i.e. on a Sum Of etc.

With the WITH statement you define the relational restriction of the function. Ex. MyCustomerNR=CustomerNR and MyDate>current date.

Name of the Data Column/Field that is being summed up ex. InvoiceLineSum, NumberComplaints, InvoiceTotal

Example 1

In a field derivation.Simplest type. We have two tables. ThisTable and MyCustomers. We have no relationships defined.

highest of MyCustomers TotalInvoiced

This will return the highest invoiced Total  that I have invoiced all my customers.

Example 2

We have two tables.CustomerType and MyCustomers. We have a relationship between them that connect CustomerType and MyCustomers on customer type.There is no Unique Alternative Name for the Relationship. I have 300 records in MyCustomer and 45 of type Good Credit. My best customer with Good Credit have been invoiced $3200 and the best customer overall have been invoiced $5000.

My active record in CustomerType is the Good Credit record.

highest of MyCustomers TotalInvoiced

This will return the number $3200 as I am now using the relationship instead of the table.

highet of MyCustomers named "AllRecords" 

This will return the number $5000 as I have now defined a unique relationship with no restriction and I will get the total invoiced across the table..

Example 3

highest of MyCustomers named "NewRel" with RegistrationDate=current date TotalInvoiced

This will return the highest invoiced total today

Example 4

for MEMBERS ;

list records

LASTNAME in order ;

highest of RESERVATIONS TOTAL DUE .

end

This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record, find all the related records in the RESERVATIONS table (those that have a matching MEMBER ID), and (3) list the highest TOTAL DUE in the set of matching RESERVATIONS records.

The output from this script, arranged in alphabetical order by LAST NAME, might look as follows:

Last Name

Highest of Reservations Total Due

Adams

$3000.00

Albert

$4760.00

Anders

$4420.00

Andersen

$2100.00

...

...

If you also want to include the highest TOTAL DUE among this group of invoices, change the fourth line of the query to read:

 highest of RESERVATIONS TOTAL DUE : item max .

Note: There's an important difference between the statistical operator max and the relational statistical operator highestof. max finds the highest value in the specified field among all the records being processed. highest of finds the highest value among the records related to the records being processed.

highest of

Type

Relational Statistical Operator

Purpose

The highest of operator finds the highest value in a specified field in all matching records in a related table. The result can appear as a list item in the detail area of a report or as a statistic in the summary area at the end of each group or the end of the report.

Syntax

highest of TABLENAME|RELATIONSHIP  [named "UNIQUE RELATIONSHIP NAME" ]  [with ( selection criteria) ] FIELDNAME ;|.

Returns

A value of the same type as the specified field. If a Text field is specified, DataEase returns the highest ASCII value. If a Choice field is specified, DataEase returns the value in the highest numbered choice (not necessarily the highest ASCII value).

Example

for MEMBERS ;

list records

LASTNAME in order ;

highest of RESERVATIONS TOTAL DUE .

end

This script tells DataEase: (1) Process all the MEMBERS records and list each member's LAST NAME in alphabetical order, (2) for each MEMBERS record, find all the related records in the RESERVATIONS table (those that have a matching MEMBER ID), and (3) list the highest TOTAL DUE in the set of matching RESERVATIONS records.

The output from this script, arranged in alphabetical order by LAST NAME, might look as follows:

If you also want to include the highest TOTAL DUE among this group of invoices, change the fourth line of the query to read:

 highest of RESERVATIONS TOTAL DUE : item max .

Note: There's an important difference between the statistical operator max and the relational statistical operator highestof. max finds the highest value in the specified field among all the records being processed. highest of finds the highest value among the records related to the records being processed.