by PRICE Systems
| September 25, 2014
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.
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:
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:
Dim pCnt as Long
Dim pStart as Long
pStart = 0
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
Add the following code below the line ():
For pCnt = pStart to pTot
Set proj = ses.Projects(pCnt)
If (proj.Name = “T1”) Then
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!!!!!")
Set proj = Nothing
Set ses = Nothing
Set app = Nothing
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.
Dim curProj as TruePlanningApi.Project
For Each curProj in ses.Projects
If(curProj.Name = “T1”) then
MsgBox(“Found using for each!!!!!”)
Dim pCnt As Long
pTot = ses.Projects.Count - 1
Dim curProj As TruePlanningApi.Project
For Each curProj In ses.Projects
If (curProj.Name = "T1") Then
MsgBox ("Found using For Each!!!!!!!")
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:
projId = curProj.ID
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:
Dim projId As String
Set proj = ses.Projects.ItemByName("T1")
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.