Forms in Coda can best be described as "tables with workflow." They derive their name from real-life paper forms, where documents are filled out and shuffled around to different places to be processed. The salesman takes down the order and sends it to the office. The accounts receivable group reads it and figures out if the customer has the credit needed; if so they pass it to fulfillment, otherwise they give it to a credit agent. And so on. The form is processed in stages, with each person that touches it changing or adding a little more data until it reaches a final status.
A form has two parts: fields and statuses. Fields are more or less identical to those in tables. Statuses are unique.
They are defined as "leads to" relationships. Take the following state diagram of an order process:
In this illustration we can see the following relationships.
- BUILT leads to PLACED
- PLACED leads to PAID, BACKORDERED, and CANCELLED
- PAID leads to SHIPPED, PARTIALLY SHIPPED, and CANCELLED
- ...and several others.
Additionally, we can see that BUILT is an initial status; it is a starting point for an order since it can lead to any other status.
Specifically in Coda, statuses have the following properties:
- Adjective Name: Completes the sentence "The form is currently ___"
- Verb Name: Completes the sentence "Please ___ the form"
- Initial Flag: Indicates that the status can be an initial one for a form.
- Relationships: Specified in the "leads to" direction.
Whereas regular SQL tables (and Coda tables, for that matter) use INSERT, UPDATE, and DELETE to manipulate data, forms use the status verbs (and UPDATE if no state change is needed) instead. To ship some orders in the example above, one might type
SHIP ORDERS SET shipped_date = CURRENT_TIMESTAMP WHERE id IN (1, 2, 3)
Presuming the orders with IDs 1, 2, and 3 are currently in status "paid" or "partially shipped" and the user has permission to ship, the command will execute and update the statuses accordingly. Additionally, Coda allows developers to add triggers either before or after a particular status change. This allows additional business rules to be applied and data to be validated, and for canceling the transaction if needed.
create_form := CREATE [ GROUP ]? [ FORM <form_name> | SUBFORM <form_name> OF <parent_form_name> ] [ DISPLAYED AS <display_name> ]? [ IN APPLICATION <application_name> ]? ( <field_definition> [ , ... ]* ) WITH STATUSES <form_status_definition> [ , ... ]* field_definition := <field_name> <field_type> [ DISPLAYED AS <display_name> ]? [ NULL | NOT NULL ]? [ DEFAULT [ <system_variable> | <string_literal> | <array_literal> ] ]? field_type := [ <type_name> | REFERENCE TO <table_name> ] [ ARRAY ]? system_variable := CURRENT_TIMESTAMP | CURRENT_GROUP_NAME | CURRENT_USERNAME form_status_definition := ( <adjective_status_name> [ DISPLAYED AS <adjective_display_name> ]? , <verb_status_name> [ DISPLAYED AS <verb_display_name> ]? [ INITIAL ]? )
Creates a new database form.
All forms automatically have an autonumbering primary key column called ID and audit fields called CREATE_USER, CREATE_DATE, MOD_USER, and MOD_DATE which contain the appropriate information and are updated automatically. When creating a new form, these names as well as several others are reserved.
Forms can be defined as either GROUP or normal forms, with the latter being the default. GROUP forms have a string column added called GROUP_NAME which is used to bind the row to a particular group. This is only really useful in group applications where each user's session has a current group defined. This group is used as the default on statements that create new rows in the form table.
Forms can also be declared as SUBFORMs of other forms. This is used to define one-to-many relationships between forms and subforms, and automatically adds an integer column called PARENT_TABLE_ID to the subform that is used to refer back to the associated row in the parent form table.
Form fields require a name and type, and can have an optional default value and array flag.
To create a form at least one status must be specified. Such a specification includes an adjective name for the status, followed by an optional human-readable display name, followed by a verb name with a similar optional display name. If the status can be a starting point for the form, an INITIAL flag can be specified.
To define the relationships between statuses subsequent ALTER FORM statements must be executed.
alter_form := ALTER FORM [ <application_name> . ]? <form_name> [ RENAME TO <form_name> | SET DISPLAY '<display_name>' | ADD FIELD <field_definition> | ALTER FIELD <field_name> SET <field_definition> | DROP FIELD <field_name> | SET IDENTITY ( <field_name> [, ... ]* ) | ADD STATUS <form_status_definition> | ALTER STATUS <adjective_status_name> SET <form_status_definition> | DROP STATUS <adjective_status_name> | SET STATUS ORDER <status_leads_to_definition> [ , ... ]* ] field_definition := <field_name> <field_type> [ DISPLAYED AS <display_name> ]? [ NULL | NOT NULL ]? [ DEFAULT [ <system_variable> | <string_literal> | <array_literal> ] ]? field_type := [ <type_name> | REFERENCE TO <table_name> ] [ ARRAY ]? system_variable := CURRENT_TIMESTAMP | CURRENT_GROUP_NAME | CURRENT_USERNAME form_status_definition := ( <adjective_status_name> [ DISPLAYED AS <adjective_display_name> ]? , <verb_status_name> [ DISPLAYED AS <verb_display_name> ]? [ INITIAL ]? ) status_leads_to_definition := <adjective_status_name> LEADS TO [ NOTHING | ( <adjective_status_name> [ , <adjective_status_name> ]* ) ]
Modifies an existing form. The application name is only required if there is not a current application set in the session. When altering a field, the entire field definition must be entered, including the name of the field or a new name if that is desired.
Every form can have one or more fields set as its IDENTITY. These fields together form what would be in SQL parlance a unique key, and can be used to identify a row in addition to the table's ID column. It is also possible to add additional indexes to a form using the CREATE INDEX command.
Each time a form status is listed on the left hand side of a LEADS TO statement, all prior relationships are erase. It is not necessary to redeclare ever relationship every time the ALTER FORM SET STATUS ORDER command is called, as previously defined relationships will remain.
drop_table := DROP FORM [ <application_name> . ]? <form_name>
Drops a form from the database. The application name is only required if there is not a current application set in the session.
THIS IS A HIGHLY DESTRUCTIVE PROCESS. BE VERY CAREFUL.
First, it drops any reference columns in other tables that refer to subforms of this form. Then it drops any reference columns that point to this form. Then it drops any subforms, and finally it drops this form itself.
CodaServer's philosophy can best be described as "The developer knows what they are doing." Don't let it down.