Skip to Main Content
AVEVA™ PI System™ Feedback Portal

Welcome to our new feedback site!


We created this site to hear your enhancement ideas, suggestions and feedback about AVEVA products and services. All of the feedback you share here is monitored and reviewed by the AVEVA product managers.

To start, take a look at the ideas in the list below and VOTE for your favorite ideas submitted by other users. POST your own idea if it hasn’t been suggested yet. Include COMMENTS and share relevant business case details that will help our product team get more information on the suggestion. Please note that your ideas and comments are visible to all other users.


This page is for feedback specifically for AVEVA PI System. For links to our other feedback portals, please see the tab RESOURCES below.

186 VOTE
Status No Status
Created by Guest
Created on Aug 19, 2022

Allow substitution parameters to be used in the SELECT and FROM clauses of the TableLookup Data Reference

As a user, I would like TableLookup Data Reference to be able to use substitution parameters to specify the column name(s) and table name for the SELECT and FROM fields respectively. This would allow for the use of attribute values like those from String Builder attributes to be used to identify the desired column(s) or table and facilitate the use of table lookup within Element Templates.
  • Attach files
  • DavidMFairchild
    Reply
    |
    Feb 23, 2023

    I have a lookup table for determining the water spill over a damn based on the lake level (across the top) and the gate openings (down the side). Allowing substitution parameters in the select clause would make it possible to use this table in PI AF/Analysis.

  • Guest
    Reply
    |
    Aug 19, 2022
    Here's an example from a TechSupport case: Customer wants to create a Table Lookup attribute template that uses the value of a String Builder attribute which includes substitution parameters to select the Table Name associated with it's Parent Element. For example, customer has a table named after each Element and they want to build out their Element hierarchy. They would want to do something like this to link the table lookup attributes to the desired table: Element: 11D01 Table Name: 11D01 Stable String Builder attribute: %Element%;" Stable"; Table Lookup Data Reference: SELECT FROM %@String Builder attribute% WHERE The substitution paremters in the FROM clause would resolve to "11D01 Stable" as intended.
  • Guest
    Reply
    |
    Aug 19, 2022
    Please do it ! It would help us a lot.
  • Guest
    Reply
    |
    Aug 19, 2022
    Here is another user case: A customer has a federated PI System with several remote AF units, and one central AF showing the same content from each of those units. Each unit has the same set of templates and tables. The remote AF structures are supposed to be copied to the central unit via PI System Connector, each one appearing in the right branch of the central AF hierarchy. The central AF server has also the same set of templates and table names. Today, PI System connector cannot merge table data, but instead it replaces the existing central table by the content from the last unit which table was synchronized by PI System Connector. As a solution, the tables at each unit could be renamed by appending a unique identifier (such as unit code). Then, the element template could be configured with a TABLE LOOKUP containing a substitution parameter in the FROM clause to allow pulling data from different tables (i.e., unit specific tables). Some benefits of this solution: - Less element templates needed (easier to maintain in a federated PI system environment) - Better lookup performance (each branch on the central unit will be pointing to smaller tables, instead of pointing to a single big table)
  • Guest
    Reply
    |
    Aug 19, 2022
    Here is another user case: I would like TableLookup Data Reference to be able to use a reference to an attribute value (i.e @Attribute) to specify the column name(s) for the SELECT
  • Guest
    Reply
    |
    Aug 19, 2022
    Unfortunately cannot vote multiple times, but new use case. Using templated lookups, but still need to override individual lookups to use the correct result column. Quite tricky currently, as one unwanted reset-to-template starts delivering faulty lookups...
  • Guest
    Reply
    |
    Aug 19, 2022
    This Feature would be Awesome. Please Make It Happen. Need it ASAP!
  • BradR
    Reply
    |
    Aug 19, 2022
    I would like to use a substitution parameter in the WHERE clause to reference one of a group of attributes in the template. select mycolumn from mytable where table-ref-column = @template-ref-column%which -one% %which-one% would contain a numeric value. Is there a way to reference an array of "template-ref-column" to obtain the correct data from "mytable"?
  • Kelsey Bobeck
    Reply
    |
    Aug 19, 2022
    Each OEM has its own braking list and sometimes it differs model to model, so we have different templates for each so we can reference the correct table. Substitution parameters to select which table to choose from would be very beneficial in reducing the number of templates we need to have and therefore improving manageability of our AF.
  • Guest
    Reply
    |
    Aug 19, 2022
    We are using a table lookup to help quickly assign many pi tags to the right attributes, but we have been cautioned not to have tables that get too big. Therefore we need to break the tables up into multiple different tables, and then update either the elements or templates to point to the right table. Which breaks up the streamlined nature of using templates. The ability to specify the table with substitution parameters would make this much smoother.
  • Gael
    Reply
    |
    Aug 19, 2022
    Hello OSIsoft, Could we have a status regarding this request? Thanks
  • Guest
    Reply
    |
    Aug 19, 2022
    Hello, is the status = "no status" ? It seems this is ranked in the TOP 10 of PI Server UserVoice in #Votes.
  • Guest
    Reply
    |
    Aug 19, 2022
    Our current development cycle does not include this request. We will update this idea if the status changes.
  • Guest
    Reply
    |
    Aug 19, 2022
    We use TableLookup to assign performance targets to IAQ trends. The TableLookup works fine if the targets are the same across attributes. The problem we have is that we assign different targets for different buildings and we cannot use the original Table. We use a single attribute to manage the changed variable. This is where the problem exists. I copied below the setting for the TableLookup we to use, along with the desired setting. This is the constant setting, it works: SELECT [Performance Target] FROM CO2 WHERE [Sustainability Standard] = '%Attribute%' This is the variable setting, it works: SELECT [Performance Target] FROM [DASC OPR] WHERE [Air Parameter] = '%..|Attribute%' ORDER BY Performance Target This is the desired setting: SELECT [Performance Target] FROM [%@..\..\|Shortname% OPR] WHERE [Air Parameter] = '%..|Attribute%' ORDER BY Performance Target I can elaborate if necessary.
  • Jordan_Sellers
    Reply
    |
    Aug 19, 2022
    This would be extremely useful to us. Client has a table of product material dependent constants. The table is structured with material IDs on columns and constant_types on each row. There is a Material ID PI Tag that updates with materials that align with the column headers. The material specific constants are needed in various analyses. There are couple hundred materials and even more constant_types. All constants have an element from a constant_template. Currently, the only way we know how to do this is to create a couple hundred table lookup attributes for each material, then create a lookup analysis with a couple hundred lines for each attribute input and then a massive piece of logic to emulate a select case using individual compare functions on each attribute vs the Material PI Tag.
  • Guest
    Reply
    |
    Aug 19, 2022
    Table Lookup currently only allows you to use attribute values or hardcoded values for the Where clauses. It would be nice if we can input string functions like Right() or Concat() to allow us more flexibility in building queries.
  • andre.moreira1
    Reply
    |
    Aug 19, 2022
    This would add flexibility to leverage Element and Event Frame templates that use Table Lookup attributes.
  • +86