DISTINCT ATTRIBUTE VALUES

Home

4D v16.3


DISTINCT ATTRIBUTE VALUES ( objectField ; path ; valuesArray )

Parameter	Type	Description
objectField	Field	Object field from which to get the list of distinct attribute values
path	Text	Path of attribute whose distinct values you want to get
valuesArray	Text array, Longint array, Boolean array, Date array, Time array	Distinct values in attribute path

Description

The DISTINCT ATTRIBUTE VALUES command creates and populates the valuesArray with non-repeated (unique) values coming from the path attribute in the objectField field for the current selection of the table to which this field belongs. Note that objectField must be of the Object type, otherwise an error is returned. The command can be used with indexed or non-indexed fields.

Pass a valid attribute path in path. Use the standard dot notation to define paths to nested attributes, for example "company.address.number". Keep in mind that object attribute names are case-sensitive.

The array you passed in valuesArray must be of the same type as the values stored in the attribute path. Values must be scalar and can be of the Text, number, Boolean, Date, or Time type (pointers, objects, BLOBs or images are not supported). Make sure that all field attribute values are of the same type; otherwise, an error is returned. For example, if the path attribute contains "Monday" in one record and 10125 in another record, an error will be returned.

If the command is called from within a transaction, records created during the transaction are taken into account.

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.

Using the .length virtual property

You can use the "length" virtual property with this command. It is automatically available for all attributes of the array type, and provides the size of the array, i.e. the number of elements it contains. This property is designed to be used in queries (see QUERY BY ATTRIBUTE). You can also use it with the DISTINCT ATTRIBUTE VALUES command to get the different array sizes for an attribute.

Example

Your database contains a [Customer]full_Data object field with 15 records:

If you execute this code:

 ARRAY LONGINT(aLAges;0)

 ARRAY LONGINT(aLAgesChild;0)

 ARRAY LONGINT(aLChildNum;0)

 ALL RECORDS([Customer])

  //get the distinct values for the "age" attribute

 DISTINCT ATTRIBUTE VALUES([Customer]full_Data;"age";aLAges)

  //get the distinct values for the "age" attribute within the "Children" array

 DISTINCT ATTRIBUTE VALUES([Customer]full_Data;"Children[].age";aLAgesChild)

  //get the distinct numbers of children by using the length virtual property

 DISTINCT ATTRIBUTE VALUES([Customer]full_Data;"Children.length";aLChildNum)

The aLAges array receives the following elements:

Element	Value
1	33
2	35
3	36
4	40
5	42
6	44
7	52
8	54
9	60

The aLAgesChild array receives the following elements:

Element	Value
1	2
2	3
3	4
4	5
5	6
6	10
7	12
8	14
9	15
10	16

The aLChildNum array receives the following elements:

Element	Value
1	1
2	2
3	3