- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
-
Features
- Workbook
- Worksheet
- Rows and Columns
- Headers
- Cells
- Data Binding
- TableSheet
- GanttSheet
- ReportSheet
- JSON Schema with SpreadJS
- SpreadJS File Format
- Data Validation
- Conditional Formatting
- Sort
- Group
- Formulas
- Serialization
- Keyboard Actions
- Shapes
- Floating Objects
- Barcodes
- Charts
- Sparklines
- Tables
- Pivot Table
- Slicer
- Theme
- Culture
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
- Formula Reference
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
Lookup Column
A Lookup column is a column that you can mark as "lookup" in the data source schema and present it as a combo box cell type or a multi-column drop-down list in the TableSheet. With the Lookup column, you can select column values from a fixed lookup column or a set of related rows in an efficient way for the TableSheet view. The lookup column concept is introduced by the TableSheet Cross Column.
TableSheet lets you mark columns as “lookup” in the data schema in the following ways:
As an array
When a column's lookup is an array in a data source schema, a combo box cell type will be applied to the column automatically and all the items of the lookup array will appear in the combobox.
The following code depicts how to set a lookup array in the TableSheet. For example, you can mark the Type of the column as lookup and set array values to it.
var workItemTable = dataManager.addTable("WorkItems", {
data: [
{
"ID": 1,
"Date": "9/12/2020",
"Description": "Know your numbers",
"TotalPoints": 10,
"Type": "Homework"
},
//...
],
schema: {
columns: {
Date: { dataType: "date" },
// set lookup column as an array
Type: { lookup: ["Homework", "Quiz", "Exam"] }
}
}
}); As a relationship name
You can mark the column’s lookup as a relationship name in a data source schema while creating the relationship between two tables. In such a case, a cell button, and a multi-column drop-down will be applied to the column automatically and the dropdown will display data from the related table with default columns.
In the following example, you can create the relationship between two tables that is Students and Grades and further mark the 'Student ID' column as lookup in the Grades table with 'student' as the relationship name, so that the related table Students can be displayed in the dropdown with the default columns.
var studentTable = dataManager.addTable("Students", {
data: [
{
"ID": 1,
"Name": "Ellen Robinson"
},
//...
]
});
var gradeTable = dataManager.addTable("Grades", {
data: [
{
"StudentID": 1,
"WorkItemID": 1,
"Grade": 4
},
//...
],
schema: {
columns: {
StudentID: {
lookup: 'student'
}
}
}
});
dataManager.addRelationship(gradeTable, "StudentID", "student", studentTable, "ID", "grades"); As an option
You can mark the column lookup as an option that contains a relationship name and some column options in a data source schema. In such a case, a cell button, and a multi-column dropdown will be applied to the column automatically. When a column’s lookup is an option, the options column definition only accepts four options, that is, the value, width, visible, and caption. Hence, the dropdown will display the related table with specified columns, where each column could specify the field name, caption, width, and visible properties.
In the following example, you can create the relationship between two tables, WorkItems and Grades, and mark the 'WorkItem ID' column as the lookup option in the Grades table with 'workItem' as the relationship name, so that the related table WorkItems can be displayed in the dropdown with specified columns.
var workItemTable = dataManager.addTable("WorkItems", {
data: [
{
"ID": 1,
"Date": "9/12/2020",
"Description": "Know your numbers",
"TotalPoints": 10,
"Type": "Homework"
},
//...
]
});
var gradeTable = dataManager.addTable("Grades", {
data: [
{
"StudentID": 1,
"WorkItemID": 1,
"Grade": 4
},
//...
],
schema: {
columns: {
WorkItemID: {
// lookup: { name: 'workItem', columns: ["ID", "Description", "TotalPoints", "Type"]}
lookup: {
name: 'workItem', columns: [
{ value: "ID" },
{ value: "Description", width: 150 },
{ value: "TotalPoints", width: 100 },
{ value: "Type", width: 150, caption: "WorkItem Type" }
]
}
}
}
}
});
dataManager.addRelationship(gradeTable, "WorkItemID", "workItem", workItemTable, "ID", "grades"); Using SpreadJS Designer
You can set up a Lookup column in a TableSheet using the DataSource Tab of the SpreadJS Designer. The DataSource tab provides the Lookup option in the Schema section of the Columns tab in a Table entry.
By default, you can set a 'static array' as a Lookup in the TableSheet from the Schema, in one of the following ways:
By inputting an array string directly to the Lookup editor, for example, say, "123, 628, 325".
By choosing the array string from the Lookup dropdown list.
After setting the array lookup, you can view the array items in the combobox dropdown of the TableSheet column. For instance, here, you can display the array string as lookup in the shipVia column of a TableSheet.
When you are working on a TableSheet where tables are related to each other, you can set the 'relationship name' as a lookup in the Schema in one of the following ways:
By inputting a relationship name directly in the Lookup editor, for example, say, "Orders_customId".
By choosing the “relationship name” item from the Lookup dropdown list.
Usually, when you set the relationship name as a lookup, the related columns will also be displayed under the Lookup's columns option in the Schema. You can check any columns which you want to display in the multi-column dropdown of the TableSheet.
For instance, here, you can add the relationship name 'Customer_phone' as the lookup in the address column of the TableSheet. The address column depicts button cells which when clicked display the column dropdown with the columns you selected from the Lookup's columns options.
