What Are Participant Custom Fields?
In Collaborator, we introduced Participant Custom Fields. This allows you to create a field in a review (via its review template) to which each review participant can assign his or her own value. This allows collection of any type of data that may be different for each participant.
Representing this data in table/column style is sometimes less than useful, especially since, generally, you will want to perform some calculation on the data before displaying it. To aid you in this, we have created views in the database to help you retrieve these values and perform calculations on them.
Examples in MySQL
Below is a sample of how to query Participant Custom Fields in MySQL, using MySQL Workbench to connect directly to the database. The syntax will be similar for Oracle and MSSQL, though you may need to reference the documentation for your particular database. You will also need to use an SQL query tool that is able to connect to those databases.
Participant Custom Fields
In these examples, we will use the following Participant Custom Fields:
We will also use a review (#3919) with some sample data entered for the custom fields:
Simple Data
The simplest method of selecting the data from the database is just to show the values as they were entered by users. To do this, we will use one of the views for Participant Custom Fields that have been defined in the database.
SELECT *
FROM participant_singleline_values
WHERE reviewId IN (3919, 3918);
Note that, for the purposes of these examples, we are limiting the results to two reviews.
Review #3918 only has one participant (it is still in planing phase), who has not yet specified a value for Outside Time.
Review #3919 has three users, two of which have specified values for Outside Time.
A Note About Database Data Types
Collaborator stores all values for Custom Fields as strings, or sequences of letters and numbers. This is fine for the purposes of displaying the data (as above), but if you want to perform calculations on the data, you will need to tell your database to convert the strings into a meaningful number type, such as an integer, or a floating point number.
In MySQL, you perform this data conversion using the CONVERT() function.
SQL also contains a 'NULL' value, which represents the absence of a specified value. If you want to replace NULL values with some meaningful default value, use the IFNULL() function. Generally, aggregate functions like SUM() and AVG() will exclude NULL values from their calculations, so you will not need this function.
Sums
If we would like to find the sum of Outside Time for each review, we can modify the query to be:
SELECT reviewId, SUM(CONVERT(value, SIGNED INTEGER)) outsideTime
FROM participant_singleline_values
WHERE reviewId IN (3919, 3918)
AND fieldName = 'Outside Time'
GROUP BY reviewId;
In the SELECT line, we now explicitly list each of the columns we wish to select. We use the CONVERT() function to convert the values to (signed) integers, and then the SUM() function to add them up. We also specify that the summed value should be named "outsideTime".
In the WHERE clause, we have added an additional 'AND' clause. This makes sure that we only sum up values of the correct field type: 'Outside Time'.
Because we are using an aggregate function (SUM()), we have to specify which pieces of data we want to perform that calculation on. In this case, we want the sum for each review, so we specify "GROUP BY reviewId". The database server first groups all results with the same review ID together, then sums the values for each of those groups.
Averages
To perform averages, the query is nearly identical, we just replace the SUM() function with the average function, AVG():
SELECT reviewId, AVG(CONVERT(value, SIGNED INTEGER)) avgOutsideTime
FROM participant_singleline_values
WHERE reviewId IN (3919, 3918)
AND fieldName = 'Outside Time'
GROUP BY reviewId;
Note that these may or may not be the results you were expecting, depending on how you wish to treat NULL values. In the above results, NULL values have been excluded from the calculation of the average, so what we are really seeing is the average of 27 and 45. If you instead want to treat NULL values as if they were 0, you need to specify that in your query using the IFNULL() function. IFNULL(x,y) says that if x is NULL, return y. (Otherwise, it just returns x.) So we will modify our query like this to treat NULLs as if they were 0:
SELECT reviewId, AVG(IFNULL(CONVERT(value, SIGNED INTEGER),0)) avgOutsideTime
FROM participant_singleline_values
WHERE reviewId IN (3919, 3918)
AND fieldName = 'Outside Time'
GROUP BY reviewId;
Note that, now that we treat missing (NULL) values as zeros, the average Outside Time for Review #3919 is the average of 27, 45, and 0. Also note that the missing values for Review #3918 are also interpreted as zeros, so we get a 0 result instead of a NULL.
See Also
Custom Reports: Examples
Custom Fields
Database Installation
MySQL