DISTINCT VALUES

version 6.5 (Modified)

DISTINCT VALUES (field; array)

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

You can pass to DISTINCT VALUES any indexable field or subfield, 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 looses the current record.

Note: As this command now functions with indexed and unindexed fields, its execution mode can now be set by using the SET DATABASE PARAMETER command.

If you pass the field of a table, DISTINCT VALUES browses and retains the non-repeated values present only in the currently selected records. However, if you pass a subfield, DISTINCT VALUES browses all the subrecords present in each currently selected record.

Note: Starting from 4D 6.5, 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 or subfield 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.

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

2. The following example returns in asKeywords all the keywords that are attached (using a subtable) to the 4D Write documents stored in the table [Documentation] and whose theme is "Economy":

   QUERY ([Documentation];[Documentation]Theme="Economy")
   DISTINCT VALUES([Documentation]Keywords'Keyword;asKeywords)

After this array has been built, you can reuse it to quickly locate all the documents associated with the selected keyword:

   QUERY ([Documentation];[Documentation]Keywords'Keyword=asKeywords{asKeywords})
   SELECTION TO ARRAY ([Documentation]Subject;asSubjects)
      ` ...

ON ERR CALL, SELECTION RANGE TO ARRAY, SELECTION TO ARRAY, SET DATABASE PARAMETER.