Tech Tip: Using the Number of Elements in an Object Array Attribute
PRODUCT: 4D | VERSION: 16 | PLATFORM: Mac & Win
Published On: December 16, 2016
When performing a query on an Object Type Field, an aggregate function similar to the SQL COUNT function can be applied to obtain the count of elements an array type attribute. This is performed by using the virtual "length" property and appening it to the attribute in the function. This property can be applied in queries.
For example with the following Table and Objects:
Using the QUERY BY ATTRIBUTE command:
C_LONGINT($result1) QUERY BY ATTRIBUTE([Table_1];[Table_1]Field_2;"Attribute1.length";>;0) $result1:=Records in selection([Table_1]) C_LONGINT($result2) QUERY BY ATTRIBUTE([Table_1];[Table_1]Field_2;"Attribute1.length";>;1) $result2:=Records in selection([Table_1]) C_LONGINT($result3) QUERY BY ATTRIBUTE([Table_1];[Table_1]Field_2;"Attribute1[].SubAttri1.length";>;1) $result3:=Records in selection([Table_1]) |
- In the first query, 4 records are returned since this function only applies to Attributes with an Array of elements and there for the 5th record does not apply in the fifth record.
- The second query only matches records 2 and 4 since they have more than 1 elements in each of their arrays.
- The third query matches the third record and shows that the "length" property can be applied to any valid path.
Using the DISTINCT ATTRIBUTE VALUES Command:
ARRAY LONGINT($arrayResult1;0) ALL RECORDS([Table_1]) DISTINCT ATTRIBUTE VALUES([Table_1]Field_2;"Attribute1.length";$arrayResult1) ARRAY LONGINT($arrayResult2;0) ALL RECORDS([Table_1]) DISTINCT ATTRIBUTE VALUES([Table_1]Field_2;"Attribute1[].SubAttri1.length";$arrayResult2) |
The results are an array of each distinct count of the number of elements in an array of the path in numerical order.