Documentation

    Record ID

    Record ID adds a new column that numbers each row in your table, giving you an ID you can use to refer to that row in later steps. Choose the column name, starting number and whether the IDs are stored as BIGINT numbers or VARCHAR text.

    Add row identifiers

    This example starts with a small table with one person per row. Record ID adds a record_id column, assigning 1 to Ada's row, 2 to Lin's and 3 to Sam's.

    The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.

    Result
    record_idname
    1Ada
    2Lin
    3Sam
    1. Change Start to 100. The record_id column now contains 100, 101 and 102.
    2. Change Column Name to row_id. The first column in the output table is renamed to row_id.
    3. Switch Type to VARCHAR. The identifiers keep their sequence, but the output column now contains text instead of numbers.

    When to use

    Use Record ID when rows have no identifier and later steps need one, such as keeping rows apart in a Cross Tab or restoring the original order after a Sort.

    Read the output

    The output contains the original table with one new column at the beginning: record_id. With the default settings, Ada's row has ID 1, Lin's has ID 2 and Sam's has ID 3. The name values are unchanged.

    The new identifier column contains BIGINT numbers by default. Choosing VARCHAR stores those identifiers as text. This setting only affects the new column; the original columns keep their data types.

    How row order affects the IDs

    Record ID assigns identifiers in the order it receives the rows. It doesn't use the values in name to decide which ID a row gets. For example, if Sam's row arrives first, it gets ID 1 with the default settings.

    Each run begins again at Start, so these IDs aren't permanent identifiers for the people in the table. For numbering in a particular order, place Sort before Record ID.

    For identifiers calculated from existing column values, see Formula.

    Inputs and outputs

    Inputs

    1 port
    InRequired

    The table whose rows you want to assign identifiers to.

    Accepts: Tables, Geospatial data.

    Accepts one connection on this port.

    Outputs

    1 port
    Out

    The original table with an identifier column added as the first column. Identifiers begin at Start and increase by one for each row.

    Choose the value you need

    Choose whether the new column stores identifiers as numbers or text. With Start set to 1, both options produce the sequence 1, 2, 3.

    Record ID output formats and illustrative values
    FormatExample valueUse it for
    BIGINTBIGINT1, 2, 3Keeping row identifiers as whole numbers. This is the default.
    VARCHARVARCHAR"1", "2", "3"Working with another block or system that expects text identifiers. The quotes shown here indicate text and aren't included in the values.

    Configure the block

    Column Name

    The name of the new column added to your table.

    Default: record_id

    Start

    The identifier assigned to the first row. Each following row gets the next number.

    Default: 1

    Type

    Whether the new column stores identifiers as BIGINT numbers or VARCHAR text.

    Default: BIGINT

    Options: BIGINT, VARCHAR.

    Record ID FAQs

    Does Record ID need an input connection?
    Yes. Connect the table you want to number to In. Record ID adds an identifier to each row in that table.
    Where does Record ID put the new column?
    The new identifier column appears before the existing columns. All original rows and values are kept.
    What does Start control?
    Start sets the identifier for the first row. Each following row gets the next number. For example, setting Start to 100 produces 100, 101 and 102 for a table with three rows. Use a whole number.
    What is the difference between BIGINT and VARCHAR identifiers?
    BIGINT stores the identifiers as whole numbers. VARCHAR stores them as text. Both use the same sequence; choosing VARCHAR doesn't add leading zeros or a prefix.
    Will a row keep the same ID when the data changes?
    Not necessarily. Each time Record ID runs, it numbers the input rows again, beginning at Start. If the rows arrive in a different order, or rows are added or removed, an existing row may get a different ID.

    Prepare your data. Build it in Strata.

    Strata opens in your browser, with no account.