The lowest of operator finds the smallest value in the 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 at the end of each group or at the end of the report.

There's an important difference between the statistical operator min and the relational statistical operator lowest of. min returns the lowest value in the specified field among the records being processed. lowest of returns the lowest value in the specified field among the records related to the records being processed.

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.

lowest of MyCustomers TotalInvoiced

This will return the lowest 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 worst customer with Good Credit have been invoiced $320 and the worst customer overall have been invoiced $50

My active record in CustomerType is the Good Credit record.

lowest of MyCustomers TotalInvoiced

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

lowest of MyCustomers named "AllRecords" 

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

Example 3

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

This will return the lowest invoiced total today

Example 4

for MEMBERS ;

list records

LAST NAME in order ;

lowest 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 selected, find the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the lowest TOTAL DUEvalue for each of the matching RESERVATIONS records.

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

Last Name

Lowest of Reservations Total Due

Adams

$2740.00

Albert

$4100.00

Anders

$3690.00

Anderson...

$1720.00...

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

lowest of RESERVATIONS TOTAL DUE : item min .

lowest of

Type

Relational Statistical Operator

Purpose

The lowest of operator finds the smallest value in the 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 at the end of each group or at the end of the report.

Syntax

lowest 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 lowest ASCII value. If a Choice field is specified, DataEase returns the value in the lowest numbered choice (not necessarily the lowest ASCII value).

Usage

There's an important difference between the statistical operator min and the relational statistical operator lowest of. min returns the lowest value in the specified field among the records being processed. lowest of returns the lowest value in the specified field among the records related to the records being processed.

Example

for MEMBERS ;

list records

LAST NAME in order ;

lowest 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 selected, find the related records in the RESERVATIONS table (those that have the same MEMBERID), and (3) list the lowest TOTAL DUEvalue for each of the 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 smallest TOTAL DUE among this group of reservations, change the fourth line of the query to read:

lowest of RESERVATIONS TOTAL DUE : item min .