Original Post Date: Thursday, April 5, 2012 

Introduction

The previous blog covered getting started with the TruePlanning API using VBA. Now it is time to tackle collections. Collections in the TruePlanning API are simply objects that hold a list of objects where all the objects are of the same type.  For example, the Project object contains a collection of the Cost Objects that belong to the project.  In order to use the TruePlanning API, mastering collections is essential, but “mastering” is a bit strong a word given that they are easy to use.

By the end of this blog TruePlanning API users should be up and running with the many different collections that are found in the TruePlanning API.

Setup

This blog will start where the first blog left and will be based on the Excel file that was created in the first blog.  Perform the following steps to get setup:

   1. Open or create the Excel file from the first blog. You should see the following code:

 


   2. Delete lines 9 through 15 in the above code (counting all line from the top). The code should now look like:

 

   3. Create a TruePlanning project and name it “T1”.

   4. Give the “T1” project the following PBS:

 

   5. Calculate and save the “T1” project.
   6. Make at least three copies of the “T1” via the Project Manager dialog in TruePlanning. This will ensure that the following        code will be able to loop through more than one project.

   7. Close the “T1” project.


Working with Collections:

As stated above, the collection objects in the TruePlanning API are really just lists of like-type objects such as Projects, Cost Objects, Activities, Resources, Worksheet Sets, Inputs, and Metrics.

The following is a list of the collection objects found in the TruePlanning API:

 

Notice that the names are all plural. The standard naming convention is that a collection is the plural form of the objects that make up the collection. So the Projects collection is made up of Project objects and the Cost Objects collection is made up of Cost Object objects.

There are really only two ways to work with collections: direct access, which means to get a specific element of the collection based on an ID or name, or to go through each element of the collection.  How you access the contents of a collection depends on the context of the code being written.

For example, a user might loop through all of the projects available in the Projects collection that is contained in the Session object in order to display a pick list for users to select a project. Once a user has selected a specific project, the code could then easily obtain the project by calling one of the direct access functions that are part of the Projects collection and providing the name of the selected project.

Both of these methods will be covered.

Rolling up our sleeves and getting to work:

The following steps will show how to work with the collections found in the TruePlanning API.

Iterating through each element of the collection:

The following code will show how to loop through each element of the collection. There are two ways to do this: indexing based on an incrementing variable, or if your language supports it, using a built in iteration looping mechanism such as a “for each” loop.

Manually iterating by index

This is the time honored, hairy-chested approach to iterating through a collection of elements and is based on the arrays found in most programming languages.  The core idea is that the total number of elements in an array is found and then a variable is incremented in a loop from a starting point (that will be discussed in a moment) up to the total number of elements (determining the total will also be discussed), and each element of the collection is accessed by using the incrementing variable as an index into the array.

To walk through the example of finding all of the projects available to a TruePlanning user, the following steps would be performed:

  1. Define a variable to hold an incrementing value that will represent the current index into the collection.   For this example, it will be called “pCnt

Dim pCnt as Long

 

  1. Define a variable to hold the starting index value of the collection. For this example, it will be called “pStart”

Dim pStart as Long

 

  1. Determine the starting index. Assuming the goal is to start at the beginning of the collection, there are only two options: zero (0) or one (1). The zero may seem odd to some people, but in some programming languages collections, or arrays, use zero as the first index value.  This is the case with collections in the TruePlanning API. Please note that this may or may not be the case with other arrays in the programming language you are working with (such as VB or VBA), but it IS always true with collections in the TruePlanning API without regard to the language being used. This means that our starting value for pStart is zero (0).

pStart = 0

  1. Define a variable to hold the highest valid index value in the Projects collection, and then obtain that value from the targeted collection.  All TruePlanning collections have a method that returns the total number of elements in the collection called: Count. So for example,  if a user has access to 10 projects then the Count method  for the Projects collection will return a value of 10 when its Count method is called.

What needs to be noted is that because TruePlanning collections are zero index based the total count of elements will always return a value that is one greater than the highest valid index value in the collection. Using our 10 project example: while the Count method returns 10, the actual list of valid indexes is: 0,1,2,3,4,5,6,7,8,9.This means that if the Projects collection were to be accessed by index using the value returned by the Count method (10), an error would occur.

The result is that when determining the highest valid index value in a TruePlanning index, the value returned from the Count method needs to be subtracted by one because TruePlanning collections are zero index based.

Add the following code:

Dim pTot as Long

 

 

pTot = ses.Projects.Count - 1

 

  1. Now that a start and end index have been obtained (assuming 10 projects, the start is 0 and the end is 9), a way to loop through those indexes needs to be employed. Most programming languages have some type of “for” loop that supports looping based on an incrementing variable. The exact formats will differ, but structurally they have the same components: a variable to be incremented, a maximum value for that variable, and some mechanism for defining the code that will be executed with each loop. In VBA the “For” loop does the job.  The variable to be incremented will be “pCnt”, the maximum value is stored in “pTot”. The code to be executed will be all code that is placed between the start of the “For” loop and the “Next” statement. 

Add the following code below the line ():

For pCnt = pStart to pTot

   Set proj = ses.Projects(pCnt)

   If (proj.Name = “T1”) Then

      MsgBox(“Found It!!!!”)

   End If

Next pCnt

  1. The code between the “For” and the “Next” keywords is doing the work. Notice how the Projects collection is being indexed through the use of the current index count in parenthesis.  Also notice how the Project object is set equal to an element in the Projects collection through the use of the indexing. Don’t forget the “Set” keyword.  Once a Project object has been assigned to an object the methods on the Project object can be used. In the example the project name is being tested.

 

  1. The following is a complete code listing:

Private Sub CommandButton1_Click()

   Dim app As TruePlanningApi.Application

   Set app = New TruePlanningApi.Application

   Dim ses As TruePlanningApi.Session

   Set ses = app.Login("(local)")

   Dim proj As TruePlanningApi.Project

 

 

 

   Dim pCnt As Long

   Dim pStart As Long

   Dim pTot As Long

   pTot = ses.Projects.Count – 1

   For pCnt = 0 To ses.Projects.Count - 1

      Set proj = ses.Projects(pCnt)

      If (proj.Name = "T1") Then

         MsgBox ("Found It!!!!!")

      End If

   Next pCnt

   ses.Logout

   Set proj = Nothing

   Set ses = Nothing

   Set app = Nothing

End Sub

Use built-in looping mechanism

The manual method for iterating over each element in a collection, as described above, provides developers with great control over how the elements of a collection are accessed. The elements could be accessed in reverse order, or the loop could be set at some arbitrary start point in the collection to eliminate searching the total set of elements.  With that control comes overhead. Variables need to be set up correctly, there is the potential to end up with an index value that is not valid and the developer needs to know if the collection is zero index based or one index based. 

Looping over a collection of elements is a very common need so many programming languages provide mechanisms that make this easier. These mechanisms will find the right start point and will continue to loop until the end of the collection, and then exit gracefully. They reduce the work for the developers and reduce the potential for error. Unless there is a specific need, using one of these mechanisms will usually save time, produce safer code and be easier for other developers to understand. 

As VBA is the language being used for this example, the mechanism to be used is the “For Each” loop and it does what is sounds like it will do: it will iterate over each element in a collection or array.  To use it a variable is needed to hold the currently selected element, so a variable of the type contained in the collection needs to be defined. The code executed by the “For Each” loop is bound by the “Next” keyword. All code between the start of the “For Each” loop and the “Next” keyword is executed with each iteration of the loop.

Once set up the “For Each” loop will iterate over each element in the targeted collection until it reaches the end or a special command is given to exit the loop early. You would use such a commend if you were looking for a specific element in the collection, and once you found it, did not need to continue looping through all of the elements in the collection.

To walk through the example of finding all of the projects available to a TruePlanning user, the following steps would be performed:

  1. Create the variable that will hold the element items as they are sequentially accessed:

Dim curProj as TruePlanningApi.Project

 

  1. Set up the “For Each” loop. Here the Session object ses contains the Projects collection so it is referenced:

For Each curProj in ses.Projects

 

Next

  1. Now add some code to be executed with each loop:

For Each curProj in ses.Projects

   If(curProj.Name = “T1”) then

      MsgBox(“Found using for each!!!!!”)

   End if

Next

 

  1. Escaping the loop! As mentioned above, it is sometimes desirable to exit the loop before the end of the collection has been reached.  In this example, after finding the project that we are looking for we can exit the loop. Different programming languages will have different mechanisms for achieving this. In VBA the keyword is “Exit For”.  Add the following code just before the “End If” line:

      Exit For

 

  1. That’s it! Notice how much simpler the “For Each” loop was. Admittedly the code for the manual iteration could be streamlined to reduce its “bulk”, but the “For Each” is safer and easier.  The following is a complete code listing containing both approaches to iterating over the collection:

 

Private Sub CommandButton1_Click()

   Dim app As TruePlanningApi.Application

   Set app = New TruePlanningApi.Application

   Dim ses As TruePlanningApi.Session

   Set ses = app.Login("(local)")

   Dim proj As TruePlanningApi.Project

   Dim pCnt As Long

   Dim pStart As Long

   Dim pTot As Long

   pTot = ses.Projects.Count - 1

   For pCnt = 0 To ses.Projects.Count - 1

      Set proj = ses.Projects(pCnt)

      If (proj.Name = "T1") Then

         MsgBox ("Found It!!!!!")

      End If

   Next pCnt

   Dim curProj As TruePlanningApi.Project

   For Each curProj In ses.Projects

      If (curProj.Name = "T1") Then

         MsgBox ("Found using For Each!!!!!!!")

         Exit For

      End If

   Next curProj

 

   ses.Logout

   Set proj = Nothing

   Set ses = Nothing

   Set app = Nothing

End Sub

 

Direct Access of Collections

The example of supplying a user with a pick list of available projects, and then opening the selected project has been described above. The techniques for iterating over a collection would be used for the first part of this example. Each project found could be added to some type of control that would allow a user to pick the desired project.

 

Once the user has picked the project and wants the project opened, what options are there for getting the project from the collection of projects maintained by the Session object? The entire collection of projects could be iterated over until the desired project is found, but as the user has already identified the project, it would faster and easier to get the project directly from the collection using a direct access method. This can be a big performance savings when the collections are large.

 

Direct access methods allow users to get elements in a collection using some type of identifier. The two most common identifiers are a TruePlanning API specific ID variable, and the name of the element.  In the previous code examples, the name of the project being sought was “T1” so it is a simple matter to call on a method of the collection that returns the element that matches the identifying data.

Every TruePlanning API object that can be part of a TruePlanning API collection will have an “ID” attribute. “ID” attributes are a string representation of a Globally Unique ID (GUID) and are long and ugly, but helpful because they uniquely identify that specific TruePlanning API entity.   The “ID” values are set by the TruePlanning API so they should be treated as read only.

 

Coming back to the example of having users pick from a list of projects and then opening a project they selected, when the projects are iterated over to develop the pick list, their IDs could be obtained and stored so that when a user selects a project, the ID for the selected project is known and can be used in a direct access call against the Projects collection. On

e reason for using the “ID” attribute would be because it is a faster lookup than the name field.

One important consideration when directly accessing elements of TruePlanning API collections: if the data used to identify the element does not match any of the elements in the collection an error will be generated. This means when using any of the direct access methods on a collection the code must be robust enough to handle the possibility that the TruePlanning API will generate an error. In VBA this means using the “On Error” methods, but that is a bit beyond the scope of this blog.

Item by Name

As the name of the target project is known: “T1”. Getting the project with the name “T1” is straightforward.

1.                   Add the following code before the line “ses.Logout”:

Set proj = ses.Projects.ItemByName("T1")

 

2.                   Test what happens if the wrong data is provided to a direct access method. Change the project name to a project name that does not exist. The result will be a “Run-time error: Automation error”:

Set proj = ses.Projects.ItemByName("T1zzzzzz")

 

Item by ID

To provide an example of obtaining a collection element by ID, a bit of fancy code will be added. Above code was added to iterate over each project and test if the project matched a specific name.  Once the desired project has been found, the ID for that project can be obtained and then used later to get the specific element based on it’s ID.  Admittedly the example is a bit unnatural as the name of the project is known from the start, but that will be maintained to keep the example simple.

1.                   Declare a variable to hold the ID of the project once it is found. IDs are the string representation of a GUID.  Add the following code after the line “Dim pTot as Long”:

Dim projId as String

 

2.                   In the code that executed once a project with the right name is found add code to store the ID for the current project.  Add the highlighted line of code to the existing code block:

   For Each curProj In ses.Projects

 

      If (curProj.Name = "T1") Then

 

         MsgBox ("Found using For Each!!!!!!!")

 

         projId = curProj.ID

 

         Exit For

 

      End If

 

   Next curProj

 

3.                   Now that the ID has been obtained, call into the Projects collection to get the desired project. Add the following code below the code block in the previous step:

   Set proj = ses.Projects.ItemById(projId)

 

   MsgBox ("Current project is: " & proj.Name)

 

4.                   That’s It! The following is a complete code listing from all of the examples:

Private Sub CommandButton1_Click()

 

   Dim app As TruePlanningApi.Application

 

   Set app = New TruePlanningApi.Application

 

   Dim ses As TruePlanningApi.Session

 

   Set ses = app.Login("(local)")

 

   Dim proj As TruePlanningApi.Project

 

   Dim pCnt As Long

 

   Dim pStart As Long

 

   Dim pTot As Long

 

   Dim projId As String

 

   pTot = ses.Projects.Count - 1

 

   For pCnt = 0 To ses.Projects.Count - 1

 

 

      Set proj = ses.Projects(pCnt)

 

      If (proj.Name = "T1") Then

 

 

         MsgBox ("Found It!!!!!")

 

      End If

 

   Next pCnt

 

   Dim curProj As TruePlanningApi.Project

 

   For Each curProj In ses.Projects

 

      If (curProj.Name = "T1") Then

 

         MsgBox ("Found using For Each!!!!!!!")

 

         projId = curProj.ID

 

         Exit For

 

      End If

 

   Next curProj

 

   Set proj = ses.Projects.ItemById(projId)

 

   MsgBox ("Current project is: " & proj.Name)

 

   Set proj = ses.Projects.ItemByName("T1")

 

   ses.Logout

 

   Set proj = Nothing

 

   Set ses = Nothing

 

   Set app = Nothing

 

End Sub

Conclusion:

While the example used in here focused on the Project object, all of the collections in the TruePlanning API behave the same way. Some of the collections may have a few different methods such as adding new elements or deleting elements, but iterating over the collection or going after specific elements using direct access methods is the same.  The example code is a good start to helping users become proficient at leveraging the TruePlanning API.