8 Replies Latest reply on May 19, 2011 1:56 PM by dtakara

    A programatical reference to an attribute in an element

    AlejandraLanier
      Good morning, Quick Question.... In AF, Attribute, Settings: This works: SELECT [ARC1] FROM [M_Linked_Table] WHERE UNIT = 'BB1' How can I make the value [ARC1] a reference to an attribute in that element?  The attribute is called Unit_Name and is a string. (And has the value ARC1) I have tried, @[|Unit_Name] to no avail.
        • Re: A programatical reference to an attribute in an element
          dtakara

          Hi Alejandra,

           

          Just to confirm: is M_Linked_Table an AF Table within your AF database?

           

          It looks like a substitution parameter that returned the value of an attribute estipulated by the user (named Unit_Name, in your case) would fit perfectly in your case. You would use it in the SQL query of an attribute with data reference of type AF Table Lookup.

           

          Unfortunately, the substitution parameter %Attribute% won't do it, because it returns just the name of the attribute that holds this data reference, instead of the value of attribute which you need to refer to (Unit_Name, in your case).

           

          Therefore, this seems to require the creation of a custom AF Data Reference plug-in.  The vCampus Library has a white paper with some examples of implementation of AF Data References that you might be interested in. It is under:

           

          vCampus Library > White Papers and Tutorials > PI AF > White Paper - Implementing AF 2.0 Data References

           

          I'll work on an example that is a little more specific to your case and I'll post it as soon as it is ready.

            • Re: A programatical reference to an attribute in an element
              skwan

              Alejandra:

               

              Within the config string of the Table Lookup Data Reference, we currently only support substitution parameters on the WHERE clause.  I'll add this to the requested enhancements list for the Table Lookup Data Reference.  Additionally, we have had a few requests to support substitution parameters on the Tabel Name in the past.

                • Re: A programatical reference to an attribute in an element
                  AlejandraLanier

                  Thanks for your posts, Steve.  Found a workaround that works....  I'll try to explain in words.

                   

                  The goal:  I wanted to look up data tags from a table (in Excel) and reference the GT unit as part of the SQL syntax in order to maximize use of templates.  I created an attribute for the GT unit name... you'll see after the "=" sign.

                   

                  I did it using the following syntax:  SELECT [Row Name] FROM [Linked_Table] WHERE UNIT = @[|A reference to a value in an  attribute]

                   

                  It was working fine.  However, a co-worker need to transpose the linked file in Excel for various reasons, and so the syntax had to change.  I thought needed to find a way to reference the unit, (the value in the attribute) after the "SELECT" statement in the syntax and that is not allowed.

                   

                  Workaround:  If I name the Element exactly the name of the unit (which in the transposed table is also the name of the column), then SELECT [%Element%] FROM [Linked_Table] WHERE......

                   

                  So I no longer need the attribute that holds the GT unit name.

                    • Re: A programatical reference to an attribute in an element
                      Ahmad Fattahi

                      Glad you found a work around. Would it still work if, in the future, you would like to repeat the same things for more elements in the same unit?

                      • Re: A programatical reference to an attribute in an element
                        dtakara

                        Hi Alejandra,

                         

                        Thanks for sharing your use case with us. I was going to ask you about that too!

                         

                        I am glad you found a workaround, even before I finished the sample code I was preparing for your use case! For what it's worth, I got finished with the sample code now and decided to post it for future reference, for whoever finds oneself in a situation where building a custom DR is the most appropriate solution.

                         

                        This is to be used in a Class Library project in VB. Besides the references added automatically by Visual Studio in a new Class Library project, it is required to add a reference to the AF-SDK COM interop (OSIsoft.AFSDK.dll) and compile it against .NET 3.5 (or earlier, but it can't be .NET 4.0).

                         

                        5164.References.jpg

                         

                        As every Data Reference plug-in, it expects its user to setup its configuration string property (ConfigString), either directly in the ConfigString itself or indirectly via a provided GUI. The off-the-shelf DR plug-ins (as Formula, PI Point and Table Lookup) have a GUI that will setup the ConfigString automatically for the user.

                         

                        As it is today, this sample plug-in (that I "nicknamed" AF_DR_TableLookupPlus) needs to be configured directly in its ConfigString.

                         

                        6710.Custom_5F00_DR_5F00_Attribute_5F00_Properties.jpg

                         

                        It expects a string with 3 fields separated by semicolon:

                         

                        A;B;C

                         

                        where
                        A = name of the attribute whose value is the name of the column that will be requested in the AF Table
                        B = name of the AF Table that will be queried
                        C = clause of the AF Table query

                         

                        In order to test it, I created a table in AF named M_Linked_Table, with columns PROPERTY, ARC1, ARC2 and ARC3, where ARC1, ARC2 and ARC3 are names of process equipments (units).

                         

                        2477.M_5F00_Linked_5F00_Table.jpg

                         

                        Also, I created an element with attribute named Unit_Name, whose value will contain the name of some process equipment (units).

                         

                        1300.Custom_5F00_DR_5F00_Attribute.jpg

                         

                        Hope it helps whoever needs to develop a custom AF Data Reference plug-in.