4D Doc Center : DISTINCT VALUES

4D v13.4

DISTINCT VALUES


DISTINCT VALUES ( aField ; array )

Parameter	Type	Description
aField	Field	Indexable field to use for data
array	Array	Array to receive field data

Description

The DISTINCT VALUES command creates and populates the array array with non-repeated (unique) values coming from the field aField for the current selection of the table to which the field belongs.

You can pass to DISTINCT VALUES any indexable field, that is, whose type supports indexing without necessarily being indexed.
However, executing this command on unindexed fields will be slower. Also note that, in this case, the command loses the current record.

DISTINCT VALUES browses and retains the non-repeated values present only in the currently selected records.

Note: When the DISTINCT VALUES command is called during a transaction (that has not yet finished), it will take into account records created during that transaction.

If you create the array prior to the call, DISTINCT VALUES expects an array type compatible with the field you pass. Otherwise, in interpreted mode, DISTINCT VALUES will create an array of the proper type. However, if the field or subfield is of type Time, the command expects or creates a LongInt array.

The array used by DISTINCT VALUES must be of the same type as the field passed as first parameter, otherwise the array is retyped. There are two exceptions to this rule:

If the field is of the Time type, the corresponding array must be of the Longint type.
If the field is of the Picture type (and is associated with a keyword index), the corresponding array must be of the Text type.

After the call, the size of the array is equal to the number of distinct values found in the selection. The command does not change the current selection or the current record. The DISTINCT VALUES command uses the index of the field, so the elements in array are returned sorted in ascending order. If this is the order you need, you do not need to call SORT ARRAY after using DISTINCT VALUES.

Note: When DISTINCT VALUES is executed with a text field associated with a keyword index, the command fills the array with the keywords of the index. Unlike other types of data, the values returned differ according to the existence of the index. In the case of a Text field, the keyword index is always taken into account, even when the field is also associated with a standard index. If the Text or Picture field is not associated with a keyword index, the array is returned empty.

WARNING: DISTINCT VALUES can create large arrays depending on the size of the selection and the number of different values in the records. Arrays reside in memory, therefore it is a good idea to test the result after the completion of the command. To do so, test the size of the resulting array or cover the call to the command, using an ON ERR CALL project method.

4D Server: The command is optimized for 4D Server. The array is created and the values are calculated on the server machine; the array is then sent, in its entirety, to the client.

Example 1

The following example creates a list of cities from the current selection and tells the user the number of cities in which the firm has stores:

 ALL RECORDS([Retail Outlets]) ` Create a selection of records

 DISTINCT VALUES([Retail Outlets]City;asCities)

 ALERT("The firm has stores in "+String(Size of array(asCities))+" cities.")

Example 2

You want to get a complete list of keywords contained in the keyword index for the "Pictures" field:

 ALL RECORDS([PICTURES])

 ARRAY TEXT(<>_MyKeywords;10)

 DISTINCT VALUES([PICTURES]Photos;<>_MyKeywords)