Lists values from a table’s column, where specified conditions are met for each row.
ListAllConditional([Table], [Column], [ConditionColumn1] , [Condition1])
Where:
Table is the table from which to retrieve the data.
Column is the number of the column within the table to retrieve the data from.
ConditionColumn is the number of the column within the table to filter the data on.
Condition is an expression that when met will include the data from the column.
Multiple Conditions are separated by a comma and must start with the column number followed by the condition, as below:
[ConditionColumn1] , [Condition1] , [ConditionColumn2] , [Condition2]
There is no limit to the number of ConditionColumn and Condition arguments, although they do need to be used in pairs.
| Rule | Meaning |
|---|---|
| ListAllConditional(Vehicles, 1,3,"=Red")
Or ListAllConditional(Vehicles, 1,3,"Red") | The function looks in the Vehicles table, and retrieves all the data in the first column where the data in column 3 equals Red. In this case, that first column contains car makes, so the list;
"Ford|Mazda" is returned. |
| ListAllConditional(People, 2,3,">30") | The function looks in the People table, and retrieves all the data in the second column where the values in column 3 are greater than 30. In this case, that second column contains Names, so the list;
Joe Bloggs|Sandra Shield|Isabelle Jones" is returned. |
| ListAllConditional(People,2,3,">30",4,"Female") | The function looks in the People table, and retrieves all the data in the second column where the values in column 3 are greater than 30 and also where the values in column 4 equal Female (note the = symbol is not required). In this case, that second column contains Names, so the list;
"Sandra Shield|Isabelle Jones" is returned. |
| ListAllConditional(Vehicles, 1,3,"=" & ColorReturn) | The function looks in the Vehicles table, and retrieves all the data in the first column where the data in column 3 equals the value selected from the Color form control. In this case, that first column contains car makes, so the list;
"Ford|Mazda" is returned. |
Table | Column | ConditionColumn1 | Condition1 | ConditionColumn2 | Condition2 | Outcome |
|---|---|---|---|---|---|---|
| Vehicles | 1 | 3 | "=Red" | "Ford|Mazda" | ||
| People | 2 | 3 | ">30" | "Joe Bloggs|Sandra Shield|Isabelle Jones" | ||
| People | 2 | 3 | ">30" | 4 | "=Female" | "Sandra Shield|Isabelle Jones" |
Vehicles Table
| Make | Model | Colour | Mileage |
|---|---|---|---|
| Volkswagen | Golf | Blue | 40000 |
| Ford | Escort | Red | 55000 |
| Renault | Kangoo | White | 37000 |
| Ford | Mondeo | Red | 28000 |
| Mazda | 2 | Red | 82000 |
| Volkswagen | Golf | Silver | 110000 |
| Volkswagen | Polo | Blue | 72000 |
| Ford | Mondeo | Red | 54000 |
| Renault | Laguna | Black | 46000 |
People Table
| Member ID | Name | Age | Gender |
|---|---|---|---|
| 42 | Dave Sharp | 25 | Male |
| 44 | Joe Bloggs | 37 | Male |
| 96 | Sandra Shield | 42 | Female |
| 107 | Thomas Knight | 21 | Male |
| 251 | Isabelle Jones | 56 | Female |
DriveWorks Tech Tips Portal |
Table Functions A selection of available functionality when pulling data from a DriveWorks Table onto your form. |
DriveWorks Tech Tips Portal is available to DriveWorks Pro customers with an active subscription and support contract. Tech Tips provide cut down projects that highlight specific functionality for faster and more effective learning. To access the portal:
The portal provides a search facility, start typing the name of the Tech Tip (as indicated above) to display the Tip you require. |