Sequence
- Former user (Deleted)
- Fiona Sargeant (Unlicensed)
- Chris Welford
- Eleanor Brodie
Overview
Use a sequence item to create a series of unique values. You can use the values in an attribute.
A sequence requires a Start Value and a Block Size. PhixFlow:
- reserves values in its database, from the Start Value up to the Block Size
- marks the last reserved value in the database
- stores the values in memory, as it is quicker to retrieve the values from memory.
When all the numbers in the block have been allocated, PhixFlow reserves the next block in the database from the marked value.
Assigning a Sequence
To assign a sequence value from memory to an attribute in a record, use the following functions in an Attribute properties → Basic Settings → Expression:
nextValue
: allocate the next available value in the sequence.
When all the numbers in the sequence have been used, the next time thenextValue
function is called PhixFlow reserves the next block of numbers before assigning the next value.curValue
: find the last assigned number.
Primary Key Values
By default, PhixFlow treats an attribute called UID as the primary key and automatically allocates a unique integer to any new records that are created.
If the primary key attribute has a name other than UID, you must configure PhixFlow to allocate a unique integers to any records that are added.
For actionflow → insert action nodes, specify the following Table properties → Advanced:
- Primary Key Generator: Sequence
- Primary Key Generator Sequence: select a configured sequence from the drop-down list.
For legacy table-actions, use the expression field in the Record-Action Attribute properties to allocate new primary key values based on a sequence.
Gaps in the Number Sequence
Sequence numbers are unique, but there may be gaps between sequence numbers. This depends on:
- the block size specified when the sequence is set up
- unexpected server shut-down.
Server shut-down clears any remaining numbers from memory. When the server restarts, PhixFlow reserves a new block from the last reserved value. This can leave a gap between:
- the last number allocated before PhixFlow was shut down
- and the first number allocated after PhixFlow is restarted.
For example,
- You create a sequence with a block size of 100. It has the values 0-99.
- You have allocated the numbers 1 to 50 from memory.
- PhixFlow shuts down.
- On restart, PhixFlow reserves a new block from 100 to 199.
- PhixFlow starts issuing numbers from memory, starting with 100.
- The numbers from 51 to 99 will never be issued.
Block Size and Performance
Use the block size to specify the range of values in the sequence. The block size affects PhixFlow performance.
- A very small block size increases the number of times that PhixFlow has to go to the database to reserve another block. This slows down performance.
- A very large block size increases the risk that PhixFlow is shut down with unallocated numbers in memory.
We recommend you set a large block size, to optimise performance.
If you require a continuous sequence of values for items:
- in a small recordset, you may want to accept the overhead of assigning every value in the sequence from the database. In this case, set the Block Size to 1.
- in larger recordsets, consider an alternative method of assigning the value.
Sequence Properties
To create a sequence, in the repository, right-click Sequences and click Create New.
For information about the properties toolbar, and about the sections Parent Details, Analysis Models, Description and Audit Summary, see Common Properties. We recommend you always add a Description that explains the purpose of the item you are creating. For a full list of all the PhixFlow property tabs and windows, see Properties, Windows, Menus and Toolbars.
Basic Settings
Field | Description |
---|---|
Name | Enter the name of the sequence. |
Start Value | Enter the first value in the sequence. This will only have an effect when the sequence is created. No further changes to the start value will alter the next value to be allocated. |
Block Size | Enter a number to set the size of the sequence of numbers. This will depend on the number of records in the recordset to which you are allocating unique numbers. For example, if the recordset has 10,000 records, the block size should be at least 10,000. Sequences provide unique numbers, but they the numbers may not always be contiguous. Block size has an effect on performance; see Block Size and Performance, above. |
Current Value | PhixFlow displays the highest value already allocated. |
Example: Using a Sequence
To set an attribute to a unique order ID:
- Create a sequence and set its Name to
order_id
. Create a table with an Integer attribute.
Set its attribute expression to:
nextValue('order_id')
Every time a new record is generated in this table,
nextValue
will return a new unique value for the attribute.