17 Replies Latest reply on Mar 11, 2011 3:50 PM by ldieffenbach

    AF atribute on query definition of ProcessBook

    juan.critical

      Hi,

       

      I'm using a value field (on Process Book) to get data from an oracle table. In the query definition I'm using:

       

      select * from TABLE where FIELD = 9
      

       

      Instead "9", I wanted to use an atribute from AF, it's possible? Someone can help me?

       

       

       

      Tks!

       

       

        • Re: AF atribute on query definition of ProcessBook
          Ahmad Fattahi

          Juan,

           

          Referencing AF SDK methods to get attributes is not necessarily too straightforward from within PI ProcessBook's VBA. To see how you can achieve that I please see [DEAD LINK] this recent thread. It talks about a very similar topic with a few suggestions.

            • Re: AF atribute on query definition of ProcessBook
              AlistairFrith

              I gather OSI have no plans to expose the AF SDK to VBA so you have to do it yourself. Create a .NET dll and set a COMVISIBLE attribute to True on the class. I was given an example which I could post here when I get back to the office.

               

              Would this be suitable for a VCampus community project? With no signs of ProcessBook or VBA scripting being replaced in the near future (I know we can write ProcessBook addins but sometimes that's overkill) and AF becoming more central, surely lots of people will want to access AF from VBA.

               

              --- Alistair

                • Re: AF atribute on query definition of ProcessBook
                  ldieffenbach

                  Hi,

                   

                  Product Manager for PI ProcessBook here....

                   

                  Alistair, we always encourage our vCampus community members to share their work. The potential downside of wrapping calls to the AF SDK within PI ProcessBook is the exchange between the COM-based ProcessBook and the .NET-based AF SDK. It will work just fine, but you need to watch performance carefully. Or, so I hear.

                   

                  Juan, I wonder if you couldn't do the query you want within AF itself and then you wouldn't need to program it in ProcessBook. Maybe I'm missing something significant in your question? Maybe that was the nature of the other post Ahmad pointed to (I'll go check that).

                   

                  Regards,

                   

                  Laurie

                    • Re: AF atribute on query definition of ProcessBook
                      juan.critical

                      Hi everyone,

                       

                      In process book, when I double click a value field, it open a display called "Define Value" with a button with "ODBC" option.

                       

                      Clicking in ODBC option, will open a display called "ODBC Data", there are a place to write an SQL code, Eg: "select * from TABLE where FIELD = 9".

                       

                      9 will be an code, in AF Tree I configure attribute "code" with the value of the code.

                       

                      What I need? When I click on AF tree the sql code has to receive the value code instead of 9.

                       

                      So I wanted to know how I can substitute 9 for the attribute..

                       

                      I don't know if it's is possible...

                       

                      Sorry my english.

                        • Re: AF atribute on query definition of ProcessBook
                          ldieffenbach

                          Hello again Juan,

                           

                          I did take a look at the other entry that Ahmad referenced above. In that case, the customer wanted the substituted value to be entered by the user and expected that each user would want to enter a different value, so what I'm about to suggest is not as appropriate for a case like that.

                           

                          What I'd like to suggest is that you define the SQL query in a PI AF attribute using a Table Lookup and use the available substitution parameters to fill in the value. The advantage of this approach is that it only needs to be configured once and anyone who uses the PI AF attribute (e.g., using the AF2 data set option in PI ProcessBook) gets the right information. For example, if you have a tank for which you want to see the maximum fill level and you have those values in a table for each tank in your farm, you could create a PI AF attribute that pulls the value for that specific tank from your relational table (e.g., select MaxLevel from Specs where TankName = %Element%). This approach uses the Table Lookup data reference, as described below.

                           

                          ·         Table Lookup: The table lookup data reference provides a convenient way to look up data in one of the AFTables using SQL Select statements. This data reference accepts all contexts, which includes the time contexts, as well as case, model, and model analysis. The context must be specifically configured. The GetValue method is supported. The GetValues method is also supported as long as the Table Lookup Data Reference uses an attribute that supports GetValues.
                          The data reference typically returns the column value specified in the Result Column box that matches the first row found using the "Where" clause. However, if the attribute's data type is an array type, and the value in the Result column box is not an array type, then the data reference returns an array, where each element of the array corresponds to a row that satisfies the condition of the "Where" clause. In this mode, if no matching rows are found, an empty array is returned.

                           

                          Data References can use Substitution Parameters to make their configuration more generic. This is part of the Context-setting aspect of PI AF. If you are creating a data reference template, you can use substitution parameters, such as %Element% for the current element name to be used by default in objects created from the template. For example, when an element named Tank1 is created from an Element template with the configured tag name of "%Element%_TT," the name "Tank1_TT" is substituted.

                           

                          On the other hand, you mentioned Oracle as the data source. I don't know that PI AF can natively connect to Oracle. I also believe that Oracle no longer supports ODBC connections (which would leave out the ProcessBook ODBC data set, anyway).

                           

                          Hopefully, I haven't confused you further.

                           

                          Regards,

                           

                          Laurie

                          • Re: AF atribute on query definition of ProcessBook
                            jstarnes

                            Laurie is correct. You could create a linked table in AF that links to the source table. Then, you could create an attribute that uses the table lookup data reference to get the value from that AFTable. It can use the value from the code attribute in its query. The query would look like:

                             

                            SELECT myValue FROM Table1 WHERE Field = @Code

                             

                            However, since the AFTable will be in memory on the client, you should limit the amount of data returned by the link.