4D v12.4

DISTINCT VALUES

Home

 
4D v12.4
DISTINCT VALUES

 

DISTINCT VALUES 


 

DISTINCT VALUES ( aField ; array ) 
Parameter Type   Description
aField  Field in Indexable field to use for data
array  Array in Array to receive field data

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.

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. The keyword index is always taken into account, even when the field is also associated with a standard index.

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  

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.")

 
PROPERTIES 

Product: 4D
Theme: Arrays
Number: 339

Different in remote mode

 
HISTORY 

Modified: 4D v11 SQL

 
SEE ALSO 

ON ERR CALL
SELECTION RANGE TO ARRAY
SELECTION TO ARRAY

 
TAGS 

index mots-clés, mots-clés***