0x4 Fields

Field Categories

Once again, let me introduce this diagram. Currently, we have 24 different field types. While there are some similarities between the fields in an APITable and those in a database, there are also many differences.

For a visual database, the field types need to be closer to the user and farther away from technical concepts. They need to be just enough abstraction so that they can be combined freely to describe a real-world model.

Field interface in the Widget SDK

The Field interface in the Widget SDK is an interface we provide to third-party developers after packaging the concept of field types. It has a clear and definite definition. It is a great reading for those who are new to the APITable technology framework.
https://developers.apitable.com/widget/api-reference/classes/model_field.Field/
For basic fields, the fieldType and its corresponding fieldProperty are key elements in determining how a field is presented to the user.

In the chapter on "Snapshots," we discussed the concept of computed fields in addition to the basic and advanced field types. In this section, we will delve deeper into field classification and categorize them into 9 major types, which are distinguished by the common data structure behind them. They all implement the base class of their respective major types to share common methods.

• Text Types

  • Single-Line Text

  • Long Text

  • URL

  • Phone

  • Email

• Number Types

  • Number

  • Currency

  • Rating

  • Percent

  • Autonumber

• Date and Time Types

  • Date

  • Created Time

  • Last Edited Time

• Member Types

  • Member

  • Created By

  • Last Edited By

• Boolean Types

  • Checkbox

• Attachment Types

  • Attachment

• Single-Select Types

  • Select

• Multi-Select Types

  • Multi-Select

• Linking Types

  • Magic Link

Similar to the concept of database fields, how to parse the cellValue in a cell is defined by the 9 different types mentioned above.

Cell Data Structure: The CellValue Interface

The CellValue interface is defined in https://github.com/apitable/apitable/blob/develop/packages/core/src/model/record.ts. The below code is excerpted from this file.

export type ICellValueBase =
  | null
  | number
  | string
  | boolean
  | ISegment[]
  | IMultiSelectedIds
  | ITimestamp
  | IAttachmentValue[]
  | ILinkIds
  | IUnitIds;

export type ILookUpValue = ICellValueBase[];

export type ICellValue = ICellValueBase | ILookUpValue;

Text Types

Text types are array structured (i.e. arrays of ISegment s as shown in the below code), designed to facilitate the implementation of lightweight rich text capabilities such as @someone and other styles.

The following code is excerpted from https://github.com/apitable/apitable/blob/develop/packages/core/src/types/field_types.ts.

export interface IBaseSegment {
  text: string;
}

export interface ITextSegment extends IBaseSegment {
  type: SegmentType.Text;
}

export interface IHyperlinkSegment extends IBaseSegment {
  // omitted
}

export interface IEmailSegment extends IBaseSegment {
  // omitted
}

export interface IMentionSegment extends IBaseSegment {
  // omitted
}

export type ISegment = ITextSegment | IMentionSegment | IHyperlinkSegment | IEmailSegment;

Number Types

Date and Time Types

Values of date and time types are Unix timestamp numbers.

Single-Select Types

Values of single-select types are strings of option IDs.

Multi-Select Types

Values of multi-select types are arrays of strings of option IDs.

Linking Types

Values of linking types are arrays of strings of record IDs.

Attachment Types

Values of attachment types are arrays of IAttachmentValues.

export interface IAttachmentValue {
  /** id is used as follow key, currently same as attachmentToken */
  id: string;
  name: string;
  /** mime type of the file */
  mimeType: string;
  /** The file is uploaded to the back-end token, and the final address is accessed through the front-end assembly. */
  token: string;
  /** Storage location, backend returns */
  bucket: string;
  /** file size, backend returns byte */
  size: number;
  width?: number;
  height?: number;
  /** Preview address (files like pdf) */
  preview?: string;
}

OpenValue

The openValue is the value used for external calls after the conversion of cellValue, primarily for use by external developers, including robot, widget-sdk, and FusionAPI. openValue will supplement some metadata on the basis of the original cell value, such as the optionId value of the string type for single-select cell value. In order to facilitate developers to obtain cell value for next-step calculation, the openValue of the cell will become much richer.

Formatter

Formatter is an important concept for fields containing numeric and date values. Formatter refers to how the value stored in that field is displayed.

For example, if you have a numerical cell field with a value of 3.4 and the formatter is set to '1', then the displayed value would be rounded down (with no decimal places) to become 3. However, the actual value remains unchanged at 3.4. When involved in mathematical calculations like sorting, filtering, formula comparisons, etc., the real value is used rather than the formatted one. To view the actual data stored behind a formatted value, simply double click on top of the numerical cell to activate the edit box.

In other words, setting the format of any given formatter type won’t affect the precision/accuracy of data that has been saved.

Magic Links

Magic Link is one of the key advanced fields in the APITable. Currently, for the magic link field, we use the "bidirectional association" strategy to maintain the relationship between the two tables.

BrotherField

Taking the teacher-student relationship as an example, when the student table and the teacher table establish a connection, it doesn't matter which table the magic link is created in. The final result is a bidirectional relationship established between the two tables. Therefore, the magic link field must appear in pairs, mutually as BrotherField. When creating a teacher field in the student table, a student field will be created in the teacher table at the same time.

Note that the association relationship is "equal." No matter where the creation action occurs, the final result will be two interrelated fields. This requires that the creation action must be completed within a transaction. The write transaction strategy will be discussed in subsequent chapters.

In the data structure of MagicLinkField.property, we use foreignDatasheetId + brotherFieldId for mutual reference.

Why is it not enough to represent the relationship between the two tables with a foreignDatasheetId, and why do we need to add a brotherFieldId?

This is because there can be multiple related magic link fields between the two tables, which can be used to represent relationships between different dimensions. For example, a mentor may have both an academic mentor relationship and a life mentor relationship, requiring two pairs of magic link fields for association.

Linked Records

The brother field creates a relationship between two tables, and the specific record association will be maintained by the associated record value. The cell value of a magic link is an array of strings of associated record IDs.

When we add a recordB1 from table B to the magic link field of recordA1 in table A, recordA1 will be automatically added to the magic link field of recordB1 in table B.

Formulas

People who have used Excel formulas know that Excel formulas pass parameters through cell coordinates. For example, in the formula =A1, A is the horizontal coordinate and 1 is the vertical coordinate. The entire A1 represents a variable, and the value of the variable is read from the corresponding cell. The formula field of APITable have two core differences from Excel, which are column variables and basic value types.

Column Variables

Formulas in APITable are written in the field, and variables are obtained through {field name}. After writing the formula, it is automatically applied to the entire column. The formula is executed for each record, and the value under {field name} in this record is taken as a parameter for calculation.

Basic Value Types

In the previous section, we divided different fields into seven different categories. Different types of field values have specific characteristics. So, what type of value does a formula field have? Obviously, the type of value in a formula field depends on how the formula is written. If the result of the formula is a number, then the formula field is a number type. We can attach a formatter attribute to the field, so that the formula field can be formatted freely like a number field. Similarly, if the result is a date, it can be formatted as a date.

From the above screenshots, we can see that whether it is a simple literal expression or a direct reference to a field, the formula can infer the type of the final result, and thus provide options for formatter. Obviously, when configuring the formula, the formula itself is not executed, and type inference is completed during the "compilation" phase. Analogous to static and dynamic programming languages, you can think of APITable formulas as a "static" expression. From the perspective of formulas, APITable fields are divided into 5 basic types:

 export enum BasicValueType {
  String = 'String',
  Number = 'Number',
  DateTime = 'DateTime',
  Array = 'Array',
  Boolean = 'Boolean',
}

Field Table

Rollups

In a nutshell, rollup means "extracting the specified field values from the associated records". This means that the value type of the rollup is determined by the field it references. Also, because there is a one-to-many association, rollup also supports array-type basic values.

Here is the data structure for the rollup field property in the Widget SDK. The key point is the entityField, which contains the properties of the field that the rollup ultimately references.

export interface IOpenMagicLookUpFieldProperty {
  /** The associated field ID of the current table referenced */
  relatedLinkFieldId: string;
  /** Field ID queried in the associated table */
  targetFieldId: string;
  /** When the dependent associated field of the magical reference is deleted or converted, the reference value may not be obtained normally */
  hasError?: boolean;
  /** The final referenced entity field, does not contain fields of magical reference type. Entity fields may not exist when there is an error. */
  entityField?: {
    datasheetId: string;
    field: IOpenField;
  };
  /** Aggregate function */
  rollupFunction?: RollUpFuncType;
  /** return value type */
  valueType?: 'String' | 'Boolean' | 'Number' | 'DateTime' | 'Array';
  /** Format, because the reference field is different, the format is different (number, percentage, date, currency) */
  format?: IOpenComputedFormat;
}

References and Statistics

In addition to "original values", rollup fields also support calling aggregate formulas.

Aggregate formulas are actually a set of special formulas that accept an array parameter and return a result after calculation. We can also find these formulas in formula fields.

Homework

When the "displayed value" of the two number fields is the same as shown in the above figure, is the result of the formula {Field 1} = {Field 2} always true?