Universal access to the SuperOffice database has been dubbed dot-syntax. This is a way of specifying columns, tables, and joins across the entire data dictionary (including partner tables). It is extremely powerful and almost unlimited.
All names are case-insensitive. Aliasing is not supported.
column := <table>[<join>, ...].<field> join := .<foreign_key> | (<table>-><primary_key>) | :<foreign_key>
In layman's terms
- Use a dot (.) to left-outer join along a dictionary relation
- Use a colon (:) to inner join
- Use an arrow (->) when the relation goes from the target table instead of from the current table.
There is always 1 root table, and it is the same for all columns.
When you do a join (regardless of type), the target table becomes the current table. From here, you can either terminate with a field or do another join.
You can have as many columns as you want! You can also build an arbitrarily complex and deep query structure. However, if the query takes too long to run, it will time out and the script will end.
If you specify the same join in multiple columns, then you mean fields from the same target (no aliasing).
Defining your starting point
Any search has to start somewhere. You need to put your boot on the ground before you start exploring. You set your point like this:
// syntax starttablename.fieldname // example ticket.title
Go to a related table
If the current table has foreign keys to other tables, you can reach those tables by automatically joining with dot-syntax.
// syntax starttablename.fieldname.fieldname // examples ticket.cust_id.firstname ticket.cust_id.company.name
If there is a many-to-many relationship between 2 tables, we need a special notation for the join. The connector is a field in the target table that has a foreign key to the current table.
// syntax table1.(table2->field2a).field2b // example ticket.(ticket_customers->ticket_id).cust_id
If we break down the details:
tickettable (table1) is our current table, origin
ticket_customerstable (table2) is the target table (destination)
- we want to access the
cust_idfield (field2b) in
- to join, we use the
ticket_idfield (field2a) in
ticket_customersand follow this foreign key back to the