Easy to Create, Easy to Change - Easy to use!

Function::Internal

Highest Of

highest of RELATIONSHIP column highest of TABLENAME column Syntax: highest of TABLENAME|RELATIONSHIP [ named "UNIQUE RELATIONSHIP NAME" ] [ with {selection criteria} ] FIELDNAME

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.

Parameters


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.

NAMED "Unique Relationship 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 {selection criteria}
With the WITH statement you define the relational restriction of the function. Ex. MyCustomerNR=CustomerNR and MyDate>current date.
FIELDNAME
Name of the Data Column/Field that is being summed up ex. InvoiceLineSum, NumberComplaints, InvoiceTotal

Returns/Result


Numeric Value The highest value of FIELDNAME that fit the relational restriction. If no Relational restriction the highest value of FIELDNAME in the entire table.

Examples


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.


Reference

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:

 

 

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.

See Also


Count Of Sum Of Lowest Of Mean Of

On the forum about Highest Of

Highest of messing up a previous selection

I have a procedure that selects data, creates a report and exports it:for joborders with (closed = 0 OR closed =blank ) ;   --Some calculations here  for production with ( phase = 10 AND stb not = "1" );&nbsp...

Product: Dataease [{8}]FIVE. Written by George Washington 14/01/15 at 09:06:00

Re:Highest of messing up a previous selection

The result of any selection/filter etc, in DataEase is a MultiView i.e. a Data-Set that contain all the columns and rows of the selection you query.As a relational database everything is also relationships. Any Script, Table Definition, Relati...

Product: Dataease [{8}]FIVE. Written by DataEase 14/01/15 at 10:35:33

Re:Re:Highest of messing up a previous selection

Thanks for the tip, it worked....

Product: Dataease [{8}]FIVE. Written by George Washington 14/01/15 at 10:53:34

Re:Re:Re:Highest of messing up a previous selection

Glad it did.Sorry for the long lecture, this forum is part of the "documentation" which we will soon publish so we try to give as extensive answers as possible so future generation will get something out of it too ;-)...

Product: Dataease [{8}]FIVE. Written by DataEase 14/01/15 at 11:01:00

On the blog about Highest Of