Link in a table

You can link in another 1010data table to the current table or worksheet.

In this tutorial, you will link the Product Master table into the Sales Item Detail table. The combination of these two tables provides you with both the product and sales information within one worksheet for items sold within your fictitious retail chain.

To link in a table:

  1. Open the Sales Item Detail table (pub.demo.retail.item).
    1010data displays the Sales Item Detail table.

  2. Click Columns > Link in Another Table.

    1010data displays the Link in Another Table dialog.

    This dialog is used to select the foreign table (Product Master) you want to link into the current table (Sales Item Detail). By default, the dialog displays the current folder, which is where the Product Master table is located.

  3. Under the Select table section, click the Product Master table link.

    1010data displays the Select columns section in the dialog.

In the Select columns section, you select at least one column in the foreign table (Product Master) that matches a column from the base table (Sales Item Detail). For example, an item in the Sales Item Detail table that has a value of 366 in the Item SKU column matches with the first row in the Product Master table that has a value of 366 in the SKU column. These columns provide a common data point where the two tables can be linked. While you can link two tables with more than one column at the same time, this tutorial focuses on linking a single column.

Note: When linking two tables, make sure that the columns from each table have the same data type. For example, an error will occur if you attempt to link a column with integer values in one table to a column with text (or string) values in the other. See Linking Columns with Different Types in the 1010data User's Guide for more information.

  1. Select Item SKU from the first drop-down list.
    This is the column from the Sales Item Detail table you currently have open.
    Note: It is important to point out that the items in each drop-down list are the column headings from the table, not the column names.

Next, select a column in the foreign table you are linking in that corresponds to the one you just selected in the table you currently have open.

  1. In the first drop-down list under the Corresponding Column(s) section, select SKU.
    This is the column in the Product Master table you are linking in. Again, this is the heading of the column, not the name.
  2. In the Suffix field, enter _pm.
    While not required, this field is highly recommended as it differentiates the column names from the two tables you are linking. By adding the _pm suffix to the columns from the Product Master table, it will be clear in the final worksheet which columns came from the Sales Item Detail table and which came from the Product Master table.

  3. Click Submit.
    1010data links the Product Master table into the Sales Item Detail table and displays the columns from the two tables combined.

    With the columns combined, not only can you see that an item with the SKU 3B7 was purchased on 5/15/12 at store 1, but you can also see its corresponding item description (PEPSI 20 OZ) and department (SNACKS).

  4. Click the Column Information () icon in the Item Description column.

    A window opens and displays information about the Item Description column.

    Notice in the Name field that the _pm suffix that you specified is added to the end of the column name. This indicates that the column is a linked column from the Product Master table.

    Note: If you prefer, you can change your preferences to show the column name instead of the column label in the worksheet. For details, see User Interface Preferences in the 1010data User's Guide.
  5. When you are finished with this tutorial, close the Sales Item Detail worksheet.
In this tutorial, you linked the Product Master table, which had not been changed in any way, into the Sales Item Detail table. However, what if you had done some work to the Product Master table, thus making it a worksheet? Depending on exactly what you want to do with this worksheet, there are two additional linking options: Link in Another Worksheet and Link and Select Rows. These options are covered in these two tutorials: