Calculated fields and button scripts

Calculated fields and button scripts use the Python programming language. The calculation or script is the implementation of a function whose signature is provided for you.

Figure B-1Editing the definition of a calculated field

B.1. Field values

For instance,

record["name_first"]
is the value of the name_first field in the current record.

B.2. Related Records

For instance,

record.related["location"]
provides the related records for the current record.

B.2.1. Single related records

For relationships that specify a single record, you can get the value of a field in that record. For instance,

record.related["location"]["name"]
is the value of the name field in the table indicated by the location relationship (often called location::name).

B.2.2. Multiple related records

For relationships that specify multiple records, you can use the aggregate functions (sum, count, average) to get overall values. For instance,

record.related["invoice_lines"].sum("total_price")
.

B.3. Testing for empty values

How you test for empty values depends on the type of field:

B.3.1. Non-text fields

Non-text fields may be empty, indicating that the user has not entered any value in the field. For instance, Glom does not assume that an empty value in a numeric field should mean 0.

You can test whether a field is empty by using Python's None. For instance:

  if(record["contact_id"] == None):
    return "No Contact"
  else:
    return record.related["contacts"]["name_full"]
  

You might also test whether there are any related records. For instance:

  if(record.related["contacts"] == None):
    return "No Contact"
  else:
    return record.related["contacts"]["name_full"]
  

B.3.2. Text fields

For text fields, you should check for zero-length strings. It is not possible in Glom to distinguish between zero-length strings and the absence of any string, because there is no advantage to doing so. For instance:

  if(record["name_full"] == ""):
    return "No Name"
  else:
    return record["name_full"]
  

B.4. Using the full pygda API

pygda is a python API for the libgda API. The record's connection attribute provides a gda.connection that can be used to access the current database directly. This allows you to run any SQL query, for instance to read data from the database with a SELECT, or to change values in the database with an UPDATE. Note that the connection is already open so you can avoid the difficult work of specifying the connection details.

The record's table_name attribute also provides the name of the current table.

This example reads all data from the current table and prints the values to the terminal:

# Use the current database's connection 
# to get all the data for the current table.
#
# record.connection is an already-open gda.connection object,
# saving us the bother of opening the connection,
# or even knowing the name of the database.

query = "SELECT * FROM %s" % record.table_name
data_model = gda.gda_execute_select_command(record.connection, query)

rows = data_model.get_n_rows()
columns = data_model.get_n_columns()
print "    Number of columns: ", columns

for i in range(columns):
    print "      column ", i;
    print "        name=", data_model.get_column_title(i)

    # Find out whether it's the primary key:
    field = data_model.describe_column(i)
    if field.get_primary_key():
        print "        (primary key)"

    print "\n";
     
print "    Number of rows: ", rows

for row_index in range(rows):
    print "      row ", row_index;

    for col_index in range(columns):
        print "        value=", data_model.get_value_at(col_index, row_index).get()

    print "\n";