specs/models/M_TABLE_RECORD_FIELD
Table Record Field
A Table Field, aka a table column, describes a single property of a table record (see Table Record for details). The properties of a Table Record cannot be arbitrary; instead they must come from the fields for that table. This corresponds neatly with the concept of a field or column in a database.
All tables have a special field called the ID field which is added automatically and cannot be edited. In addition, it has a few special properties such as uniqueness and the fact that it cannot be edited after the record is created.
All tables also have 2 metadata fields “Date Created” and “Date Updated”. These are datetime fields that cannot be directly edited by the user, only viewed. They track when the record was created and last updated respectively.
All other fields are added by the user. Other fields are always optional. A record may or may not have an entry for any given field. In addition, when creating a field, the user may select the data type for that field which constraints what types of data can be stored in that field for any given record.
Properties of Fields
| Table ID | Fields are tied to a table. This is implicit in the UI. |
| Unique Name | A unique name for a column that cannot change after creation. This is not visible in the UI, but is relevant when accessing the Tulip API. |
| Label | The display name of the field. This is shown in the UI. This property can be changed after a field is created. |
| Description | A short description of the field which can be added by the user. |
| Order | The order fields are displayed. This is implicit in the UI. |
| Field type | Entries in this field must be of this data type. See Field Types below for a full list. Cannot be changed after the field is created. |
| Hidden | When deleting fields from a table, they are only soft deleted, which hides them in the UI. Soft deleted fields can later be restored, along with the corresponding data. |
| Unique | Only true for the ID field. Displays an error to the user if they ever tries to create a record with a duplicate value in this field. |
| Table Link (Linked Record field type only) | The table that the Linked Record field is linked to |
Field Types
| Text | Sometimes known as a string, this corresponds to the “text” type in postgres and can include any series of characters. The only restriction is that it must be less than 1 Gb as enforced by Postgres. |
| Number | Otherwise known as a float, corresponds to a Postgres “double precision”. It can represent any real number with 15 decimal digits of precision. |
| Boolean | A true of false value. Commonly displayed as a localized “Yes” or “No” in the UI. |
| Integer | A number without a decimal point. Uses the Postgres “bigint” type. It can represent an integer between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
| Interval | An interval of time. Internally, we represent this as a decimal number of seconds, though we often display with an hour:minutes:seconds.millseconds format. |
| Image | A URL of an image. Users can manually enter a URL or upload the image to our storage. |
| User | A string corresponding to the ID of a user within this Tulip instance |
| Datetime | A Postgres “timestamp with time zone”, this is a specific time on a specific day. By convention, a day with time midnight is often used to refer to whole days. |
| Color | An RGB color corresponding to the standard CSS representation for that color. How To Specify Color Value Often we display the color itself in a small palette in the UI. |
| Table Link | A link to the ID field of another table with one of four types of mappings: One to One, One to Many, Many to One, Many to Many |
| Video URL | A URL of a video. Users can manually enter a URL or upload the video to our storage. |
ID Field
The ID field has a few special properties:
- Every table includes an ID field when it is created which cannot be edited or removed
- The ID field must be unique across all records in this table
- The ID field is always data type "Text"
- The ID field must be specified when adding a record, and cannot be edited after the record is created.
Date Created & Date Updated Fields
These fields share a few special properties:
- By default, these fields are not visible. To see them, a user needs to toggle “View Metadata Fields”.
- Every table is given an ID field when it is created and the field properties cannot be edited not can the fields be removed.
- The value of these fields are updated automatically when the record is created or updated. (To be more precise, the Date Created field is only updated when the record is first created)
- The data in these fields cannot be updated by the user.
- These fields have type Datetime.
User Specified Fields
- Can be created and deleted from the UI.
- Users specify the label, type, and description for their field. Label and description can be edited after the field is created, but type cannot.
- All user specified fields are optional, as in a record can either have a value for a given field or leave it blank
- All user specified fields are not unique, so multiple records in the same table can have the same value for a given user specified field.
- “Deleted” fields are really “deactivated” and can be restored
- A table can have no more than 97 user specified fields.
Tests
| ID | Name |
|---|---|
| QA-T84 | Table Management : 05 - Add columns to a table |
| QA-T85 | Table Management : 06 - Edit a table column |
| QA-T86 | Table Management : 07 - Edit a table column description |
| QA-T87 | Table Management : 08 - Delete a table column |
| QA-T88 | Table Management : 09 - Restore a deleted table column |
| QA-T89 | Table Management : 10 - Reorder table columns |
| QA-T93 | Table Management : 13 - Sort table records by column in table view |
| QA-T242 | Embedded Tables : 02 - Sort an embedded table |
| QA-T245 | Embedded Tables : 05 - Handle deleted fields in embedded tables |
| QA-T464 | Tables CSV Import : 01 - Import to table |
| QA-T467 | Tables CSV Import : 03 - Import from export |
| QA-T702 | Table Management : 05 / Failing to add Columns to a Table |
| QA-T705 | Table Management : 06 / Failing to edit a table column |
Requirements
| ID | Requirement |
|---|---|
| 16 | Manage the release to use of content based on approval status. |
| 118 | Ability to configure steps and states for work on orders, materials or equipment in a workflow |
| 119 | Ability to configure procedural elements for work on orders, materials or equipment in a workflow |
| 125 | Ability to define conditions to route process between steps in the workflow. These conditions can be based on state change in other steps or time constraints, equipment status, critical alarms, completion of other steps or defined timers. |
| 148 | Ability to provide multi-media work instructions |
| 179 | Ability to assign material properties to any level of the material tracking hierarchy |
| 188 | Standard non configurable material attributes must include quantity, UOM, status, expiration date, location, process step |
| 253 | Disallow steps from completing until the correct material, material quantity, has been consumed or identified |
| 258 | Ability to complete steps with process values that are not within the predefined limits if appropriate exception/deviation information and approval is entered |
| 260 | Ability to track equipment use in production execution by material unit and work order. |
| 261 | Ability to track equipment substitution during process execution. |
| 274 | Ability to start processing for work orders by starting to track material (batches, lots, units, etc.). Tracking information should respect tracking hierarchy (kits, trays, wells, flasks, jigs, etc.) |
| 276 | Work order have to be approved and released for production before any material can be started. Ie starting batches/lots |
| 278 | Dispatch priority of work orders and batches/lots can be controlled using predefined configurable dispatching strategies |
| 303 | Ability to track batch/lot and material rework based on the Master Recipe or material workflow configuration. |
| 326 | Ability to route and disposition materials based on sampling plans Eg. hold or future hold materials until sample testing results are available |
| 327 | Ability to route and disposition materials based on results of sample testing (off-line) and/or quality testing (in-line) Eg. hold, reject, scrap, or release materials based on sample result |
| 337 | Ability to track material consumption in a process step. Material consumption may include processing, assembly, weighing, dispensing, etc. |
| 350 | All material tracking history will include context of material hierarchy levels |
| 406 | Ability to consume by pre-configured unit of measure (UoM) quantities such as weight, volume, etc. |
| 463 | Ability to browse (view and display) forward and backwards batch/lot and material genealogy at all level of the material tracking hierarchy. |
| 809 | All records and electronic signatures have to include an accurate date and time stamp. Date & time stamps shall be configurable with the possibility to include the day, month, year, hour, minutes, seconds and time zone. |
| 830 | All data changes must be captured in audit trail. Audit trail can be turned off or on GxP needs. This may only be performed by an administrator; it cannot be performed by a user executing an APP. |