Cross-table formula is a hit feature of Bitable that enables data references across tables within the same Bitable file.
Let's use the grid below as a demonstration. First, change the field type to Formula.
Grid name: Table 1
Field/column: Column 1 (Multiline field), data reference (Formula field)
Existing data: "1" in the first row, "2" in the second row, "3" in the third row, and so on.
- •Reference a column in the same table
Double-click a cell in the data reference column. In the pop-up formula pane, select Column 1 to reference data in this column.
- •Reference a dataset in the same table
Double-click a cell in the Formula field. In the pop-up formula pane, select Table 1 > Column 1 to reference all the data in this column into this cell.
Formula: [Table 1].[Column 1]
- •Reference a dataset across tables
Double-click on a cell and click Table 2 in the formula pane. Then click Table 2 Column 2 to reference all the data in this column into this cell.
Formula: [Grid 2].[Table 2 Column 1]
- •Scenario: Calculate percentages
Formulas can calculate the percentage of the value of each cell relative to the sum of the values of the entire column.
Note: The field type for this column should be Number for calculations.
You can use the following formulas:
- •Formula 1: [Column 1]/SUM([Table 1].[Column 1])
- •Formula 2: Write a formula with the chaining syntax directly: [Column 1]/[Table 1].[Column 1].SUM()
Tips: Double-click on the column header or click the down arrow icon to change the field type.
Scenario: In budget management, you can use the formulas above to work out the share of cost for each purchased item relative to the total spending.
Cross-table value matching
Use the LOOKUP function to precisely find and match values across tables. We'll illustrate the use of this function in a real-world scenario: Order management.
- •Match values across tables
You can match and display the order information of each salesperson using the LOOKUP function.
Formula: LOOKUP(Person in charge, sales record (Order).Person in charge,Sales record (Order).Order record)
The formula above means:
Find the [sales person in charge] column in the (use the [Person in charge] column to match the [Pperson in charge] column in the [Sales record(Order)] grid. After a match is found, display [order information]).
To calculate total sales of each salesperson, you can use a chaining syntax like the one shown below:
LOOKUP([Person in charge],[Sales record].[Person in charge],[Sales record (Order)].[Sales]).SUM()
To count the number of orders made by each salesperson, you can use a chaining syntax as below:
LOOKUP([Person in charge],[Sales record (Order)].[Person in charge],[Sales record (order)].[Order record]).COUNTA()
To examine the highest sales value made by each salesperson, you can use a chaining syntax as below:
LOOKUP([Person in charge],[Sales order (order)].[Person in charge],[Sales order (Order)].[Sales]).MAX()