Electrical Design Software | Elecdes Design Suite by Scada Systems Ltd

Formulae

An Instrument Manager template formula is used to lookup data from records in the project database. Formulae can contain references to columns in the tag record for a component. Formulae can reference columns from records relationally linked to a tag record. Formulae can reference data from records in external tables linked to the project database.

Columns

The columns that the formula will reference appear in the formula string with a '#' character at the start and the end of the column name. Hence '#MANUFACTURER#' will lookup the data from the MANUFACTURER column of the tag record of a component. Columns from records relationally linked to the tag record can be referenced in a similar fashion. For example: '#R1:SIZE#' will lookup the SIZE column from the record linked as a rating (R1: link) to the component record.

Relational Columns - "P1:Tagname"

Instrument Manager can resolve relationships to lookup data from other records. Relationships are stored in Instrument Manager by storing the table and IDX value of the related record. Each type of relationship is assigned a letter and number code. For a relationship assigned the letter code 'P' and the number '1', the table and IDX are stored in the columns P1_Table and P1_IDX. This relationship can be used by specifying "P1:" before a column name from the related record.

For example: For the formula "P1:Tagname", Instrument Manager will reference the record from the table specified in P1_Table, that has the IDX value specified in P1_IDX. From that record it will fetch the data from the "Tagname" column.

Formulae can have multiple relationships, e.g. "C1:P1:Tagname". Read these from right-to-left as follows: fetch the tagname (Tagname) of the parent (P1) of the component (which would be a terminal) connected via C1 to the component record that is your point of reference (which would be a wire or a cable-core/conductor).

Relationship Codes used by Instrument Manager

Some of the letter and number codes for relationships are known and maintained by Instrument Manager. Any letter or number code that is unknown to Instrument Manager can still be used to fetch relational data.

Delimiter special meaning for " : " and " | "

The meaning and function of the delimiters in the formulae is important for the relationships. You need to understand how these operate before using them. The colon and the vertical bar have opposite meanings when used in the formulae.

The colon tells the program that you wish to use data from a second record that is pointed to by the relational column in the first and primary record. For example "P1:" for a device (where the device is the primary record of reference) will refer to the enclosure containing the device; the tag record of the device will have a P1_IDX column that points to the enclosures record.

The vertical bar tells the program that you wish to use data from a second record that POINTS to the first and primary record with its P1_IDX column. For example "P1|" for a device (where the device is the primary record or reference) will refer to the first terminal of the device. The tag record of the first terminal will have a P1_IDX column that points to the tag record of the device.

The colons and bars work in all relationships, not just the parent-child ("P") relationship that has been used in the examples above.

Reverse relationships

There are multiple forms of reverse relationship, described below, and each form can often be used to fetch the same data. Generally you can use the formula that you prefer.

For example: "P1+2|Tagname", "~P1+2:Tagname", "K1+2:Tagname" and "K3:Tagname" all fetch the "Tagname" field from the same record.

To fetch data from a terminal group as child of a device card you must use a "Gn" formula because the reverse of the "P1" relationship from the device would find the terminals, not the terminal groups. "G1+1:Tagname" and "G2:Tagname" will both fetch the "Tagname" from the same second terminal group record.

Special relationship codes

Code Relationship The related record:
P1: Parenting

The tag record of the "parent" or "container" component of the main component.

P1| Child

This shows the first child of a containing parent item, for example a cable's first core / conductor. The child relationship is signalled by the "|" (vertical bar) delimiter.

If you need the second child you can index the children by an offset from the first ( P1 | = P1+0 | ) e.g. P1+3| is the fourth core / conductor of a cable. See also Siblings.

This is a generalised reverse lookup and can be used for relationships other than P1 but that would be an unusual requirement.

~P1: Child

This shows the first child of a containing parent item, for example a cable's first core / conductor. The child relationship is signalled by the "~P" indicating a reversed P lookup.

If you need the second child you can index the children by an offset from the first e.g. ~P1+1: is the second core / conductor of a cable. See also Siblings.

This is a generalised reverse lookup and can be used for relationships other than P1 but that would be an unusual requirement.

K3: Child

This shows the third child of a containing parent item, for example a cable's third core / conductor. The child relationship is signalled by the "K".

A "K" child relationship always indicates a child by a "P1" relationship. It cannot be used as a generalised reverse lookup.

For a generalised reverse lookup, use "~Xn:" or "Xn|" described above.

Note: Use T1:, T2: etc to fetch the terminals in a terminal group - see below.

G3: Terminal group (child)

This shows the third terminal group of a device, for example a PLC card's third I/O channel. The terminal group child relationship is signalled by the "G".

A "G" child relationship is used specifically for terminal groups. It cannot be used as a generalised reverse lookup.

Note: Use T1:, T2: etc to fetch the terminals in a terminal group - see below.

R1: Ratings

The ratings record for the main component.

R2:,

R2>...:

External data

A record from an external P&ID database table linked to the component.

See below for a description of formulae that fetch data from external P&ID database tables linked to Instrument Manager.

D1: Loop Diagram

The record of the loop diagram to which the component is assigned.

D2: Datasheet

The record of the datasheet to which the component is assigned.

D3: Hook-up Diagram

The record of the hook-up diagram to which the component is assigned.

D4: Terminal Strip Diagram

The record of the terminal strip diagram to which the component is assigned.

L1:, L2:, L3:, ...

Component - custom loop

The record of the component that is assigned to a link on a custom loop diagram. These custom loop relationships are always resolved from the loop diagram. E.g. "L2:" is the "second component assigned to the loop diagram".

L1+1:, L1+2:, ...

Sibling - custom loop

The record of a sibling of the component that is assigned to a link on a custom loop diagram. E.g. If L3: refers to a terminal from a terminal strip, then L3+1: refers to the next terminal of that strip.

Sibling relationships occur most often in custom loop formulae, but they can be used with relationships other than Ln.

See below for more information about sibling formulae.

C1: and C2: Connection

Two terminal records to which a wire or cable core / conductor is connected. These connection relationships are always resolved from the conductor.

T1:, T2:, T3:, ... Terminals in a terminal group

Each device or instrument terminal assigned to this terminal group.

Compare this to K1:, K2: etc which fetch the child terminals of an instrument, device or terminal strip.

A terminal group is not the parent of the terminals, therefore the Kn relationship does not apply. Instead the terminal group has its own Tn link to the terminals in its group.

A2: Association between components

The record of the associated component, e.g. an instrument, to which a device is associated.

The A2: association is used between any types of "main" component: instruments, devices, terminal strips and I/O cards.

A11:, A12:, A13:, ... Association with a user type

The record of the user-defined component, e.g. a tank, line or valve, to which the component is associated.

A different numbered relationship is used for each user-defined type and that numbered relationship is specified in the INI file that defines the user type, see User Defined Component Types.

A10: Association with a PLC I/O channel

The record of the PLC I/O channel, a terminal group, to which this instrument is assigned.

~A10: Reversed association

The record of the instrument that is assigned to this PLC I/O channel, a terminal group.

This is a reversed A10 lookup.

PJ: Project Data

The one and only record of the ProjectData table - a global table from which you can fetch project-wide data without any requirement for a relationship to a component.

PJ:R2: External Project Data

The global project data record from an external P&ID database table linked to the component.

See below for a description of formulae that fetch data from external P&ID database tables linked to Instrument Manager.

Sibling relationship - L5+2:

If the primary component to which a relationship refers has a parent, then you can fetch a "sibling" of that primary component from its parent (another child of the parent) by specifying a sibling number after the relationship (e.g. "+2") before the relationship delimiter (i.e. before the ":" or "|").

For example, if "L5:" refers to a terminal from a terminal strip, then "L5+2:" refers to the third terminal of that strip. "L5+0:" refers to the first or primary component, i.e. the same as "L5:".

The sibling number is particularly useful for custom loop links, which are "L" formulae, and reverse relationships, "Pn|", and "~Pn:", which are usually used to fetch child components such as terminals of devices.

Sibling ordering

Generally the next sibling will be identified in the order of the IDX column of the records. You have the option to order the siblings by their Tagname or by any column of your choice.

If you put a single 'A' after the sibling number in the formula, then the next sibling will be identified in alphanumeric order by the Tagname column. For example: "~P1+2A:" will fetch the third child in name order. The same ordering can be applied to the 'K' formulae, for example "K3A:" will fetch the third child in name order, the same child as the previous example.

You can also specify your own choice of ordering column after the sibling number in the formula. For example "~P1+2Function:" or "K3Function:" will both fetch the third child as they appear when sorted alphanumerically by the Function column.

Relational Column Aliases - "<AREA>"

As well as using columns or relational columns in the formula, aliases for relational columns can be used if available. An alias makes it easier to enter the formula without having to remember and re-enter the full relationship. Relational-column aliases are configured on a per-component-type basis using the Relational-Column Alias Editor. Any aliases configured in this way can be used in formulae for the appropriate component type.

You can and should create your own aliases to simplify your formulae on output diagrams.

Relational-column aliases always appear in formulae surrounded by <> brackets. For example "#<AREA>#" will use the AREA alias configured for the component.

An alias can replace the column name in another relationship. For example "#P1:<AREA>#" will use the AREA alias configured for the container of the component, however it would be wise to create another relational-column alias that resolves this information directly.

A relational-column alias is actually hiding another formula behind a friendly name. If you were to use the formula behind the alias directly on a template, then it would achieve the same result as using the alias on the template. For example, an <AREA> alias on a device would most likely be hiding the formula "P1:P1:Tagname", i.e. the tagname of the parent area of the parent enclosure of the device.

Numbered Formulae - "Size_3"

When multiple components are to be shown on one diagram, for example a tabular datasheet, numbered formulae are required. A numbered formula has an underscore followed by a number, following the column name. For example "#<XTAG>_1#".

The data from the first instrument is placed into the formulae with the number "_1". The data from the second instrument is placed into the formulae with the number "_2". The data from the third instrument is placed into the formulae with the number "_3" and so on.

There must be no other text or symbols following the number and before the closing # symbol, otherwise the formula is not treated as a numbered formula. Suffix text following the closing # symbol is valid.

In the example above, each row of the data sheet will contain the data for a different instrument. The data for the first instrument will be written to row 11. The data for the third instrument will be written to row 13 etc.

Custom Loop Link: Description, Type and Hint

Custom loop templates have special formulae that are not used to lookup data. Instead they are used to provide a description, component type and hint for link formulae on the template.

These special custom loop formulae begin with a link reference, e.g. L11 followed by "=", rather than a ":" and a column name.

For example: #L11=Field Junction Box - First term; Terminal; (TNE; L2, L1:K1)#

Custom loop formulae are fully described in the section describing custom loop templates.

Custom Loop Templates

Data from External P&ID Database Tables - R2: and R2>...:

When an external P&ID database table is linked to components in Instrument Manager, you can fetch data from that external table using a similar formula to fetching data from the standard ratings record for a component.

Data from an external P&ID database table is linked to the tag record of the component via the R2 link, where R2_Table contains the name of the external table and R2_IDX contains the value from that table that identifies the correct record to fetch (the "primary key" value for that table).

Note: R1 fetches data from the Instrument Manager ratings table linked to the component and R2 fetches data from an external P&ID database table linked to the component.

Note: If fetching P&ID data is turned OFF then formulae linking to external P&ID data will return a blank value.

For example: #R2:LoopNumber#

If an "Instrumentation" table from a P&ID database was linked to Instrument Manager, then the formula #R2:LoopNumber# would fetch the value from the "LoopNumber" column of that Instrumentation table of the P&ID database for use in Instrument Manager. Compare this to #R1:Size#, which would fetch the value from the "Size" column of the standard ratings table in the Instrument Manager database.

The P&ID database may contain data separated into various tables linked by a common primary key. In the Instrument Manager formula you can specify which table contains the column you wish to query by adding a ">" and specifying the name of the table before the ":".

For example: #R2>EngineeringItems:ClassName#

Following from the previous example where the "Instrumentation" table from a P&ID database is linked to Instrument Manager, this formula fetches the value from the column "ClassName" from a different table, "EngineeringItems". Both the "Instrumentation" and "EngineeringItems" tables should use the same primary key to identify their records. The value to find in that primary key column is fetched from the column "R2_IDX" from the tag record of the component in the Instrument Manager database.

The P&ID database may contain data in a different table that uses a different column to identify the records. In the Instrument Manager formula you can specify the alternate key column by adding a "," and specifying the name of the column before the ":".

For example: #R2>PnPDataLinks,RowID:RowClassName#

Following from the first example where the "Instrumentation" table from a P&ID database is linked to Instrument Manager, this formula fetches the value from the column "RowClassName" from the row identified by the column "RowID", from the table "PnPDataLinks". The value to find in the column "RowID" is fetched from the column "R2_IDX" from the tag record of the component in the Instrument Manager database.

The formulae may be chained together to fetch data from a P&ID database table that is linked within its own database via a linking table. Reading the formula from left-to-right, each part fetches the key value to identify the record for the next part.

For example: #R2>PnPDataLinks,RowID:DwgID>PnPDrawings,PnPID:Dwg Name#

Following from the first example where the "Instrumentation" table from a P&ID database is linked to Instrument Manager, this formula fetches a value from the column "Dwg Name" from the row identified by the column "PnPID", from the table "PnPDrawings". The value to find in the column "PnPID" is fetched from the column "DwgID" from the row identified by the column "RowID" from the table "PnPDataLinks". The value to find in the column "RowID" is fetched from the column "R2_IDX" from the tag record of the component in the Instrument Manager database.

See also

Folder Settings

Output Diagram Templates

Editing Templates

Aliases