Number Records
The Number Records tool generates a sequence of numeric identifiers for individual records of a table, or for groups of adjacent records containing the same values in one or more selected fields. The tool can also sequentially number the items within each group. Use this tool for tasks like:
Assigning each record a unique numeric key.
Converting a flat table to hierarchical parent/child tables.
Identification of event groups in time-sequenced data, where there are clusters of activity that you want to identify as discrete events.
In the example shown below, a Number Records tool has assigned an ID. Note that in this set of records shown, the names "John" and "Martha" appear twice:
NAME | ITEM | ID |
|---|---|---|
John | 1230 | 1 |
Fred | 4583 | 2 |
John | 1234 | 3 |
Laura | 0962 | 4 |
Martha | 9802 | 5 |
Martha | 9802 | 6 |
Emmanuel | 7523 | 7 |
Using the Number Records tool with the NAME field specified as the grouping field puts "Martha" in the same group and "John" in different groups:
NAME | ITEM | GROUP_ID |
|---|---|---|
John | 1230 | 0 |
Fred | 4583 | 1 |
John | 1234 | 2 |
Laura | 0962 | 3 |
Martha | 9802 | 4 |
Martha | 9802 | 4 |
Emmanuel | 7523 | 5 |
If the records are sorted by NAME, using the Number Records tool on the NAME field puts both "John" and "Martha" in their own groups:
NAME | ITEM | GROUP_ID |
|---|---|---|
Emmanuel | 7523 | 0 |
Fred | 4583 | 1 |
John | 1230 | 2 |
John | 1234 | 2 |
Laura | 0962 | 3 |
Martha | 9802 | 4 |
Martha | 9802 | 4 |
Sorting the records by NAME and then using the Number Records tool on the NAME and ITEM fields puts "John" in different groups, because the ITEM fields do not match:
NAME | ITEM | GROUP_ID |
|---|---|---|
Emmanuel | 7523 | 0 |
Fred | 4583 | 1 |
John | 1230 | 2 |
John | 1234 | 3 |
Laura | 0962 | 4 |
Martha | 9802 | 5 |
Martha | 9802 | 5 |
Finally, if the records are sorted by NAME, and you specify both group ID and member ID, the items within a group are also numbered sequentially:
NAME | ITEM | GROUP_ID | MEMBER_ID |
|---|---|---|---|
Emmanuel | 7523 | 0 | 0 |
Fred | 4583 | 1 | 0 |
John | 1230 | 2 | 0 |
John | 1234 | 3 | 1 |
Laura | 0962 | 4 | 0 |
Martha | 9802 | 5 | 0 |
Martha | 9802 | 5 | 1 |
Number Records tool configuration parameters
The Number Records tool has one set of configuration parameters in addition to the standard execution options:
Number records | If selected, generates record IDs. |
Record ID field | Field to receive the record IDs. This is optional and defaults to ID. |
Records ID start | Value with which to start numbering records. This is optional and defaults to 0. |
Number groups | Generates group IDs. If selected, you must also specify Grouping field. |
Group ID field | Field to receive the group IDs. This is optional and defaults to GROUP_ID. |
Group ID start | Value with which to start numbering groups. This is optional and defaults to 0. |
Number group members | Generates group member IDs. If selected, you must also specify Grouping field. |
Member ID field | Field to receive the member IDs. This is optional and defaults to MEMBER_ID. |
Member ID start | Value with which to start numbering group members. This is optional and defaults to 0. |
Grouping field | Fields to compare when determining whether the group has changed between input records. |
Configure the Number Records tool
Select the Number Records tool, and then go to the Configuration tab on the Properties pane.
Optionally, select the Number records box, and then select the Record ID field box and specify a new or existing field to receive record numbers. If you want the numbering to start at something other than zero, specify a Record ID start value.
Optionally, select the Number groups box, and then select the Group ID field box and specify a new or existing field to receive group numbers. If you want the numbering to start at something other than zero, specify a Group ID start value.
If you selected Number groups in step 3, click the Grouping grid and choose one or more input Fields to define the groups.
If you selected Number groups, you can optionally select the Number group members box and specify a new or existing Member ID field to receive group sequence numbers. If you want the numbering to start at something other than zero, specify a Member ID start value.
Optionally, go to the Execution tab, and then set Web service options.
Remember that the tool assigns numbers to groups of adjacent records. See the example at the top of this page.