DISTINCT VALUES

version 6.5 (Modified)

DISTINCT VALUES (aField; array)

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.

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.

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

ON ERR CALL, SELECTION RANGE TO ARRAY, SELECTION TO ARRAY.