16 Replies Latest reply on Jan 25, 2012 10:27 PM by gkrueger

    Processbook VB code to get list of Aliases for a UnitModule from PI AF

    gkrueger

      Can anyone provide me some guidance on how to get a list of aliases for a UnitModule using the AF SDK but in VBA in ProcessBook?  I've searched the forum a bit and it appears that I need to do something with a .NET Add-in wrapper in order to expose the AF SDK in VBA.  I don't have any real experience with .NET and I was wondering if there was a procedure somewhere I could follow, or some pre-built code that would be straight forward to implement that would help me get this done. 

       

      If I'm going to need to go out and get a .NET book or a take a class to learn how to do this stuff, I understand, I figure I would just ask to see if there's a way to get an out of date COM programmer like myself up to speed quickly to do a specific function like this.

       

      I did figure out how to do this using the PI SDK with Module Database objects and methods, but it sounds like the MDB may be going away eventually, so I don't want my displays becoming obsolete too soon.

       

      Regards,

       

      Greg

       

       

        • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
          Asle Frantzen

          Gregory,

           

          I don't have any code at hand right now (on the iPad at the moment), but I would like to point you in the right direction.

           

          1. Since the MDB is synch'ed with AF now with PI 2010, you don't really have to think about the MDB anymore. So what you need to do is locate the af database where these synch'ed elements reside

           

          2. You don't need to use the AF SDK, ProcessBook itself is more than capable to read directly from AF. The MDB expressions Module, Alias and Property are now known as AF Element, AF Attribute (type: pi point data reference) and attributes of other data types (string, int, float, etc)

           

          Start out by finding code either here on vCampus or in the VBA help file in ProcessBook, look for how to create value symbols programmatically!

           

          Let me know how it works out, and if you have any problems!

            • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
              gkrueger

              Asle,

               

              I searched the PB VBA reference doc I have (which is for Version 3.2 from Sep 2009) for "AF Element" and did not get a single hit.  If I search for "Element" I get a lot of hits on the TrendElement object but not on AF.  If you could post some sample code with PB VBA examples for accessing AF element information, that would be great.  

               

              I suspect there's something involved with using the Add-in Custom Dataset, but I can't find any examples of syntax for that.  I tried downloading the AF Modeler Add-In reference manual, but my site has issues getting docs from OSi, so I'm waiting on an e-mail from my local rep of that document to see if that has any info.

               

              Regards,

               

              Greg

                • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                  andreas

                  Gregory,

                   

                  You can't access the AF SDK directly from VBA. One is .NET, the other is COM. What Asle is suggesting is that you might not need the AF SDK at all because the element relative display could be the way to go.

                   

                  So let us start from scratch and ask - what is the general problem you are trying to solve? why do you want to get the list of properties in an element?

                    • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                      gkrueger

                      Andreas,

                       

                      My customer has 2 labs and each lab has several bioreactors.  My goal is to create a display with a trend on it, and then have 2 comboboxes, one for the lab and one for the reactor.  After the user selects the lab and reactor, I'd like to populate a listbox with the Aliases configured for that reactor.  Then they select the aliases they want to see a trend for in the listbox, and hit a button to add those aliases to the trend.  

                       

                      Regards,

                       

                      Greg

                        • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                          hanyong

                          Just a thought here, but what I am going to suggest would different from what you describe but still may suit your requirement.

                           

                          Instead of using combo boxes for users to select the lab and reactor, how about using the Element Relative Display feature in ProcessBook to show a tree view of the asset structure? Which I assume it is going to be something like:

                           

                          Lab1
                          -> Reactor 1
                          -> Reactor 2
                          Lab2
                          -> Reactor 3
                          -> Reactor 4

                           

                          Users can just choose which reactor they want from the tree view. We can create a trend that uses the selected context from the treeview and show the attributes of the reactor. The current version of ProcessBook allows you to hide a trace by clicking on it. So it is possible to do it in an opt-out fashion that the trends shows the traces from all the attribute of the selected reactor, users can choose to hide the traces they do not want to see. Instead of an opt-in way where users have to choose the traces they want to see from the listbox.

                           

                          The end outcome still allow users to choose the reactor they want to look at, and choosing the attributes they want look at. This way, you are using all existing features of ProcessBook to achieve this without customization using VBA.

                            • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                              Asle Frantzen

                              Gregory, did you find a solution yet?

                               

                              Here's a small vba code snippet which can create a value symbol for a PI tag, and set it to be invisible. ProcessBook will handle the data updates, so all you have to do is worry about how to use this data:

                               
                              Sub CreatePIValue()
                                  Dim oValue As Value
                              
                                  Set oValue = ThisDisplay.Symbols.Add(pbSymbolValue)
                                      
                                  With oValue
                                      .Name = "MySinusoid"
                                             
                                      'set the position, size and visible properties
                                      .Top = 14950
                                      .Left = -14700
                                      .Width = 1000
                                      .Height = 750
                                      .Visible = False
                                          
                                      .SetTagName ("\\piServer\sinusoid")
                                  End With
                              
                              End Sub
                              

                               

                               

                              And the same thing for AF:

                               
                              Sub CreateAFValue()
                                  Dim oValue As Value
                              
                                  Set oValue = ThisDisplay.Symbols.Add(pbSymbolValue)
                                      
                                  With oValue
                                      .Name = "MyAFAttribute"
                                             
                                      'set the position, size and visible properties
                                      .Top = 14950
                                      .Left = -14700
                                      .Width = 1000
                                      .Height = 750
                                      .Visible = False
                                          
                                      .SetTagName("AF2.\\piServer\afDatabase\myElement|TAG:myAttribute")
                                  End With
                              
                              End Sub
                              

                               

                               

                              I think you can remove the prefix "TAG:" for attributes which are not PI Point Data References, but this is something you can test for yourself if you need it.

                               

                              Of course you'll have to do a couple more things to make this work properly in all situations, like checking if the symbol is already created for this ProcessBook file.

                                • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                  andreas

                                  @Asle - thanks for sharing the code snippet.

                                   

                                  @Gregory - we are looking into a community project for an add in that exposes some access to the PI AF, would you be interested to participate on this?

                                    • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                      gkrueger

                                      Andreas,

                                       

                                      Sure...I haven't participated in a community project before, so let me know what's involved and I'll see if I can contribute.  I did just buy a book on VSTO for VBA programmers, so I'll see if that can get me started on working with all this new-fangled .NET stuff.  

                                        • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                          Asle Frantzen

                                          Gregory,

                                           

                                          The AF string is something I came up with after having added AF attributes to a ProcessBook display, and then hovering the mouse pointer over them. There you will see this string, or something which resembles it.

                                           

                                          We use this when clients wish to define alarm limits for a multistate symbol as meta data in AF, and also other cases where the tag count is critial for our clients. Alarm limits are in some cases better off being stored in AF - it is time series data but you don't necessarily need to save its history.

                                           

                                          Good luck with finishing your project - there are still PI client products relying on the MDB so it's not going away just yet

                                           

                                           

                                           

                                          (And btw - if you click the "CODE" button in the toolbar while editing your post, you can select which syntax formatting you want to use for your code here)

                                            • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                              hamort

                                              Gregory,

                                               

                                              "If anyone can figure out how to write a recursive function that is scalable to any depth, that would be great."

                                               

                                              The following should work:

                                               
                                              Private Sub GetModule(ByVal PIMods As PIModules, ByVal PreviousLevel As Integer, ByVal UnitName As String, ByRef PIMod As PIModule, ByRef Level As Integer)
                                              
                                                Dim CurrentLevel As Integer
                                                CurrentLevel = PreviousLevel + 1
                                              
                                                For Each TempMod As PIModule In PIMods
                                                  If Not PIMod Is Nothing Then
                                                    Exit Sub
                                                  End If
                                                  If String.Compare(UnitName, TempMod.Name) = 0 Then
                                                    PIMod = TempMod
                                                    Level = CurrentLevel
                                                    Exit Sub
                                                  End If
                                                  If TempMod.PIModules.Count > 0 Then
                                                    GetModule(TempMod.PIModules, CurrentLevel, UnitName, PIMod, Level)
                                                  End If
                                                Next
                                              End Sub
                                              

                                               

                                                • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                  gkrueger

                                                  Holger,

                                                   

                                                  Thanks for the reply and sorry for my delay...I got busy on another assignment.  But I'm back working on this and wondered if you had a chance to test this code above, or if was more as a framework for me to follow and debug.  My main question is about the IF Not PIMod Is Nothing part...to me that seems it would cause the routine to exit immediately, since the PIMod should be something when the routine starts since the calling function will be supplying it.

                                                   

                                                  I'm going to keep working on it on my end, but I wanted to just check to see if this was tested and should run without modifications, or if you expected that it might need a little cleanup.

                                                   

                                                  Thanks,

                                                   

                                                  Greg

                                                    • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                      gkrueger

                                                      Holger,

                                                       

                                                      It's running fine...but it is a mind-bender...I haven't worked with recursion much.

                                                       

                                                      I'm still confused about the "If Not PIMod is Nothing" statement...if you could explain what that is for, it might help me understand how this whole thing is working.  (I never like to use code I don't understand if I can help it.)

                                                       

                                                      Thanks again for the code...this is a very elegant solution for the issue I had.

                                                       

                                                      -Greg

                                                        • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                          hamort

                                                          Greg,

                                                           

                                                          I just added "If Not PIMod Is Nothing" so that any sub routine returns once PIMod is found.

                                                           

                                                          So it should speed up the routine, since it soesn't have to crawl through the entire database.

                                                           

                                                          I'm glad you like ot.

                                                           

                                                          Holger

                                                          • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                            gkrueger

                                                            Holger,

                                                             

                                                            I'll have to be more patient in my posts...I spent a bit more time and figured out exactly how the code works...so sorry for all the previous questions.  See my comments in the code below for my understanding of how it works and for some minor updates I made.  The main improvement is to make it a function that returns a false if the module is not found.  That makes it easier to have code in the calling routine that can ensure the PIMod object does not get used if the function didn't find anything...it helps to prevent an "Object not defined" error if the Module name supplied doesn't exist.

                                                             

                                                            Thanks again for the help.

                                                             

                                                             

                                                             
                                                            Public Function fnGetModule(ByVal PIMods As PISDK.PIModules, ByVal PreviousLevel As Integer, ByVal UnitName As String, ByRef PIMod As PIModule, ByRef Level As Integer) As Boolean
                                                            
                                                            'This recursion subroutine developed by Holger Amort and supplied via the OSi vCampus Forums  Jan2012
                                                            
                                                            'The function requires the PIMod passed to it with initial call to be a Null object which will be populated with the found module
                                                            
                                                            Dim CurrentLevel As Integer
                                                            Dim tmpMod As PISDK.PIModule
                                                            Dim dummyBool As Boolean
                                                            
                                                            CurrentLevel = PreviousLevel + 1
                                                            
                                                            'This is a limiter to prevent an accidental endless recursion loop which might crash the terminal.
                                                            'This also limits searches to databases with less than 30 levels.
                                                            If CurrentLevel > 30 Then
                                                                fnGetModule = False
                                                                Exit Function
                                                            End If
                                                            
                                                            For Each tmpMod In PIMods
                                                                        
                                                                   'This statement provides the Exit test for recursion instances that are shallower than where the module is found.
                                                                   'It becomes True as soon as a module has been found at a deeper level and enables the recursion to close out all the instances
                                                                   'of the sub that have been instantiated
                                                                   If Not PIMod Is Nothing Then
                                                                       fnGetModule = True
                                                                       Exit Function
                                                                   End If
                                                                       
                                                                   'This test passes when the module is found and by setting PIMod to an object causes the Not PIMod = Nothing test above to pass
                                                                   'and the recursions to exit
                                                                   If StrComp(UnitName, tmpMod.Name) = 0 Then
                                                                       Set PIMod = tmpMod
                                                                       Level = CurrentLevel
                                                                       fnGetModule = True
                                                                       Exit Function
                                                                   End If
                                                                       
                                                                   'If the current module has submodules, then a new instance of the function is called to parse them.
                                                                   If tmpMod.PIModules.Count > 0 Then
                                                                       dummyBool = fnGetModule(tmpMod.PIModules, CurrentLevel, UnitName, PIMod, Level)
                                                                   End If
                                                               'End If
                                                            Next
                                                            
                                                            fnGetModule = False
                                                            
                                                            End Function
                                                            

                                                             

                                                             

                                                             

                                                             

                                                             

                                                • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                  gkrueger

                                                  Asle,

                                                   

                                                  Thanks for the AF code.  Where did you get the syntax for the AF string in the SetTagName method.  The VBA reference doc I have for PB shows \\server\tag name for PI tags, but doesn't have syntax for AF references in it.

                                                   

                                                  As far as having a solution, for now I'm planning to use the MDB solution I came up with.  As long as PIBaGen relies on the MDB, then it will be available for my displays to use so this solution will still be OK.  I'll work on getting the displays fully migrated to AF in the future months after my initial deliverables to the customer are done.

                                                   

                                                  Here are the code snippets I came up with.

                                                   

                                                  This gets the aliases...it relies on a function I created (fnFindModule) that is listed after the sub at the end of this post.  That function is what does the search of the MDB and returns a PIModule object based on a supplied string.  I checked with tech support if there was a built-in search function for the MDB in VBA, but they couldn't find one, so I built that one myself.  It's limited in that it can only go 10 levels deep, but I figure that should cover most cases.  I'm going to use that function for batch queries too.  I've found a BatchDB.Search function which finds PI Batches, but to find Unit batches, it looks like the quickest way is to start with the PIModule object for the unit, so this function will help there as well.

                                                   
                                                  Private Sub cmdGetAliases_Click()
                                                  
                                                  Dim blnModuleFound As Boolean
                                                  
                                                  Dim modVessel As PIModule
                                                  
                                                  Dim objAlias As PIAlias
                                                  
                                                  blnModuleFound = fnFindModule(modVessel, cmbVesselNames.Value & "." & cmbLabNames.Value)
                                                  
                                                  If blnModuleFound = False Then
                                                  
                                                     MsgBox ("Error finding selected Vessel in the Module Database.  Contact System Admin.")
                                                  
                                                     Exit Sub
                                                  
                                                  End If
                                                  
                                                  If modVessel.PIAliases.Count = 0 Then
                                                  
                                                     MsgBox ("Selected Vessel does not have any aliases defined.")
                                                  
                                                     Exit Sub
                                                  
                                                  End If
                                                  
                                                  lstAliases.Clear
                                                  
                                                  For Each objAlias In modVessel.PIAliases
                                                  
                                                     lstAliases.AddItem (objAlias.Name)
                                                  
                                                  Next objAlias
                                                  
                                                  End Sub
                                                  

                                                   

                                                   

                                                  Here is the fnFindModule code.  If anyone can figure out how to write a recursive function that is scalable to any depth, that would be great.  I'm not that good of a programmer, so I hardcoded it to 10 levels.  

                                                   
                                                  Public Function fnFindModule(modTargetModule As PIModule, strModuleToFind As String) As Boolean
                                                  
                                                  Dim modLevel_1 As PIModule
                                                  
                                                  Dim modLevel_2 As PIModule
                                                  
                                                  Dim modLevel_3 As PIModule
                                                  
                                                  Dim modLevel_4 As PIModule
                                                  
                                                  Dim modLevel_5 As PIModule
                                                  
                                                  Dim modLevel_6 As PIModule
                                                  
                                                  Dim modLevel_7 As PIModule
                                                  
                                                  Dim modLevel_8 As PIModule
                                                  
                                                  Dim modLevel_9 As PIModule
                                                  
                                                  Dim modLevel_10 As PIModule
                                                  
                                                  Dim blnModuleFound As Boolean
                                                  
                                                  Dim blnTooManyLevels As Boolean
                                                  
                                                  Dim dbModule As PIModuleDB
                                                  
                                                  Set dbModule = PISDK.Servers.DefaultServer.PIModuleDB
                                                  
                                                  'Does a 10 level deep parse of the module database looking for a module with the name supplied
                                                  
                                                  'Once module is found it assigns it to the target module object which is returned to the calling routine
                                                  
                                                  blnModuleFound = False 'Set true once module is found to speed exit out of parse routine and allow checking in calling routine
                                                  
                                                  blnTooManyLevels = False 'Set to true if module not found after searching deepest level and some modules on deepest level had submodules
                                                  
                                                  For Each modLevel_1 In dbModule.PIModules
                                                  
                                                         If UCase(modLevel_1.Name) = UCase(strModuleToFind) Then
                                                  
                                                             Set modTargetModule = modLevel_1
                                                  
                                                             blnModuleFound = True
                                                  
                                                             Exit For
                                                  
                                                         ElseIf modLevel_1.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                             For Each modLevel_2 In modLevel_1.PIModules
                                                  
                                                                     If UCase(modLevel_2.Name) = UCase(strModuleToFind) Then
                                                  
                                                                         Set modTargetModule = modLevel_2
                                                  
                                                                         blnModuleFound = True
                                                  
                                                                         Exit For
                                                  
                                                                     ElseIf modLevel_2.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                         For Each modLevel_3 In modLevel_2.PIModules
                                                  
                                                                             If UCase(modLevel_3.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                 Set modTargetModule = modLevel_3
                                                  
                                                                                 blnModuleFound = True
                                                  
                                                                                 Exit For
                                                  
                                                                             ElseIf modLevel_3.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                 For Each modLevel_4 In modLevel_3.PIModules
                                                  
                                                                                     If UCase(modLevel_4.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                         Set modTargetModule = modLevel_4
                                                  
                                                                                         blnModuleFound = True
                                                  
                                                                                         Exit For
                                                  
                                                                                     ElseIf modLevel_4.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                         For Each modLevel_5 In modLevel_4.PIModules
                                                  
                                                                                            If UCase(modLevel_5.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                Set modTargetModule = modLevel_5
                                                  
                                                                                                blnModuleFound = True
                                                  
                                                                                                Exit For
                                                  
                                                                                             ElseIf modLevel_5.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                                 For Each modLevel_6 In modLevel_5.PIModules
                                                  
                                                                                                    If UCase(modLevel_6.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                       Set modTargetModule = modLevel_6
                                                  
                                                                                                       blnModuleFound = True
                                                  
                                                                                                       Exit For
                                                  
                                                                                                    ElseIf modLevel_6.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                                         For Each modLevel_7 In modLevel_6.PIModules
                                                  
                                                                                                             If UCase(modLevel_7.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                                 Set modTargetModule = modLevel_7
                                                  
                                                                                                                 blnModuleFound = True
                                                  
                                                                                                                 Exit For
                                                  
                                                                                                             ElseIf modLevel_7.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                                                 For Each modLevel_8 In modLevel_7.PIModules
                                                  
                                                                                                                     If UCase(modLevel_8.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                                         Set modTargetModule = modLevel_8
                                                  
                                                                                                                         blnModuleFound = True
                                                  
                                                                                                                         Exit For
                                                  
                                                                                                                     ElseIf modLevel_8.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                                                         For Each modLevel_9 In modLevel_8.PIModules
                                                  
                                                                                                                             If UCase(modLevel_9.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                                                 Set modTargetModule = modLevel_9
                                                  
                                                                                                                                 blnModuleFound = True
                                                  
                                                                                                                                 Exit For
                                                  
                                                                                                                             ElseIf modLevel_9.PIModules.Count <> 0 And blnModuleFound = False Then
                                                  
                                                                                                                                 For Each modLevel_10 In modLevel_9.PIModules
                                                  
                                                                                                                                     If UCase(modLevel_10.Name) = UCase(strModuleToFind) Then
                                                  
                                                                                                                                         Set modTargetModule = modLevel_10
                                                  
                                                                                                                                         blnModuleFound = True
                                                  
                                                                                                                                         Exit For
                                                  
                                                                                                                                     ElseIf modLevel_10.PIModules.Count <> 0 Then
                                                  
                                                                                                                                         blnTooManyLevels = True
                                                  
                                                                                                                                     End If
                                                  
                                                                                                                                 Next modLevel_10
                                                  
                                                                                                                                 If blnModuleFound = False And blnTooManyLevels = True Then
                                                  
                                                                                                                                      MsgBox ("Module Database Hierarchy has too many levels for Module search parse function. Contact System Admin")
                                                  
                                                                                                                                      Set modTargetModule = Nothing
                                                  
                                                                                                                                 End If
                                                  
                                                                                                                             End If
                                                  
                                                                                                                         Next modLevel_9
                                                  
                                                                                                                     End If
                                                  
                                                                                                                 Next modLevel_8
                                                  
                                                                                                             End If
                                                  
                                                                                                         Next modLevel_7
                                                  
                                                                                                     End If
                                                  
                                                                                                 Next modLevel_6
                                                  
                                                                                             End If
                                                  
                                                                                         Next modLevel_5
                                                  
                                                                                     End If
                                                  
                                                                                 Next modLevel_4
                                                  
                                                                             End If
                                                  
                                                                         Next modLevel_3
                                                  
                                                                     End If
                                                  
                                                             Next modLevel_2
                                                  
                                                         End If
                                                  
                                                  Next modLevel_1
                                                  
                                                  If blnModuleFound = True Then
                                                  
                                                     fnFindModule = True
                                                  
                                                  Else
                                                  
                                                     fnFindModule = False
                                                  
                                                     Set modTargetModule = Nothing
                                                  
                                                  End If
                                                  
                                                  End Function
                                                  

                                                   

                                                • Re: Processbook VB code to get list of Aliases for a UnitModule from PI AF
                                                  gkrueger

                                                  I've looked at the Element Relative Display and have not found it very user friendly.  Do you know how to get it to just show the parts of my AF tree that I want to expose?  It has this search function, then the Element of Interest filter, but if I set that to * to see all my reactors, it gives me every level of my asset tree at the root in addition to all the trees.  How do I tell it to just show Lab1 and Lab2 as trees?  Can I set the search and filter via VBA in the Workbook_Open event so my users doesn't have to do this?  If someone can point me to documentation on how to do those things with the ERD window, I might consider using it, but for now, I'm still planning to use my Comboboxes and VBA customization...keeps it very simple for the end user, I don't have to train them on what an Element of Interest is or how to work the ERD window, just pick a lab, pick a reactor, select the aliases, and very quickly they have the trend they need.