Determining "nullness" of custom fields using VBA (not PSI)

[Sungard]

I have built a custom field editor addin in MS Project (using VSTO/VS 2008).
Since it is an addin, it must deal with the local changes made by PM's that
are cached in the local cache rather than the PSI values in the server which
may become outdated while the project is open due to local caching. (I used
the PSI for awhile, but the local cache is not properly updated when writing
back to the server - so reverted back to "VBA"-style code).

I need to determine if a custom field (for example a cost) is "null" or if
it has a value in it. Using the PSI, this is easy to check, however using the
client-side VBA calls, I am stuck on determining this. Here are the calls I
use for example:

‘ For
‘ Read project custom fields
fieldValue = proj.ProjectSummaryTask.GetField(field)
‘ Write
proj.ProjectSummaryTask.SetField(field, projectFieldValue)

When I read a cost field, this call returns "$0.00" even though the field is
"null". So, I can't tell if the user reallyl input "$0.00" or if the field is
"null".

*
Please let me know how I can determine if the the field is null using the
client side VBA.
*

Also, using PSI bypasses the local cache and introduces too many issues when
creating an addin. Is there an API for the local cache to force refresh of
the cache etc. after writing using the PSI?


Thank you in advance for your advice!

 

[Jack Dahlgren MVP]

I have built a custom field editor addin in MS Project (using VSTO/VS
2008).
Since it is an addin, it must deal with the local changes made by PM's
that
are cached in the local cache rather than the PSI values in the server
which
may become outdated while the project is open due to local caching. (I
used
the PSI for awhile, but the local cache is not properly updated when
writing
back to the server - so reverted back to "VBA"-style code).

I need to determine if a custom field (for example a cost) is "null" or if
it has a value in it. Using the PSI, this is easy to check, however using
the
client-side VBA calls, I am stuck on determining this. Here are the calls
I
use for example:

‘ For
‘ Read project custom fields
fieldValue = proj.ProjectSummaryTask.GetField(field)
‘ Write
proj.ProjectSummaryTask.SetField(field, projectFieldValue)

When I read a cost field, this call returns "$0.00" even though the field
is
"null". So, I can't tell if the user reallyl input "$0.00" or if the field
is
"null".

*
Please let me know how I can determine if the the field is null using the
client side VBA.

There is no "null" for cost fields. If the user inputs no value the value
will be 0.
Same with number fields. Project will represent them with 0 as a default
value.

-Jack Dahlgren

 

[Peter Dunn]

Hi Jack,

I am a colleague of Patrick who raised this question. Can I just clarify
your answer please. Patrick has indicated that even though Project
Professional will display number and cost fields as 0 when no value has been
entered the Project Server database that holds these can distinguish between
a real 0 that has been entered (i.e. not null) and a 0 that is purely due to
it being null. This distinction is able to be determined using PSI calls
directly to the server, but there appears to be no equivalent to these for
access the equivalent value in the local cache. The GetField call does not
return anything that indicates whether the 0 value is real or null.

Here is the simple GetField call to local cache:


Dim field As Microsoft.Office.Interop.MSProject.PjField =
Globals.ThisAddIn.Application.FieldNameToFieldConstant(fieldName, pjProject)
Dim fieldValue As String = Nothing
fieldValue = proj.ProjectSummaryTask.GetField(field)

Here is the PSI call to the server where you can get a custom field value
and determine whether it is null:

Dim pcf As ProjectWebSvc.ProjectDataSet.ProjectCustomFieldsRow
For Each pcf In psds.ProjectCustomFields
Dim value As String = Nothing
Dim dataType As PSDataType = pcf.FIELD_TYPE_ENUM

If dataType = PSDataType.STRING Then
If pcf.IsTEXT_VALUENull = False Then
value = pcf.TEXT_VALUE.ToString()
End If
ElseIf dataType = PSDataType.NUMBER Then
If pcf.IsNUM_VALUENull = False Then
value = pcf.NUM_VALUE.ToString()
End If
ElseIf dataType = PSDataType.COST Then
If pcf.IsNUM_VALUENull = False Then
value = pcf.NUM_VALUE.ToString()
End If
ElseIf dataType = PSDataType.DATE Then
If pcf.IsDATE_VALUENull = False Then
value = pcf.DATE_VALUE.ToString()
End If
Else
MsgBox("Unknown data type")

End If
Dim pcfFieldName As String =
cfds.CustomFields.FindByMD_PROP_UID(pcf.MD_PROP_UID).MD_PROP_NAME
If value Is Nothing Then
MsgBox(pcfFieldName & " is NULL")
Else
MsgBox(pcfFieldName & " = " & value)
End If
Next pcf

So seeing as nullness is held we are wanting to know if this is exposed in
the GetField type calls to local cache as that's all we can safely use for
our purposes.

 

[Jack Dahlgren MVP]

The database schema is different than the Project object model in VBA.

It was developed at a later date and it manages the data in a different way
than the project binary file which is what the project application uses.
So what is shown in the database is another representation of what is in the
file that project stores in the cache.. In other words, if you look at what
is in the cache, you don't see a mini-database. You see a binary file.

Since there was no need for null (or even in the original developers mind,
any concept that a cost would be null - just my guess) there is no method to
determine whether a cost is null or zero within VBA. For some fields there
is a sort of null value. For example, baseline fields where no baselines are
stored return a value of 2^32 - 1. Project displays and interprets this as
the value "N/A".

I guess the point is that Project was not developed as a database
application. That functionality was bolted on later and is not how project
really works internally. Project always seriallizes the data from the native
format to the db and then back. The details of how it does this are not
exposed. And on top of that, much of the internal representation is not
accessible to VBA. For example there are a number of "write only" properties
out there which can't be read, but can only be written. Using the project
object model you can do almost everything you can do manually in the
application. Since you can not tell in the application whether the
underlying value is null or 0, you can't tell that from project vba either.

The question I have is why do you care? The value of 0 means that there is
no cost. By not entering a value, the user is accepting the default value of
0.
Are you expecting the user to enter a value of 0 when the value in the field
is already 0? Why would they do that?

Maybe if you could explain what you are trying to do, it would be easier to
give an alternative solution.

-Jack Dahlgren


----- Original Message -----
From: "Peter Dunn" <[email protected]>
Newsgroups: microsoft.public.project.developer
Sent: Wednesday, August 19, 2009 5:23 AM
Subject: Re: Determining "nullness" of custom fields using VBA (not PSI)

 

[David F-H]

Hello Jack,
Peter has asked me to reply on his behalf.
We are using many project and task level custom fields to assign information
to each project task (used for reporting purposes later). In order to
alleviate the enormous burden on the PMs of populating all the custom fields
for every task in the project plan, we allow the PM to enter the custom field
value at a higher summary task level, which then applies to all subordinate
summary task / detail tasks (unless overriden at a lower level). Thus,
fields are inherited from up the "tree", and it is for this reason that it is
imperative that we recognise where a value has been explicitly set (even if
zero), or whether it is null. A null value indicates that the PM has not
explicitly set the value and it should be taken from the first higher level
summary task that has it set explicitly. The resolution of all of these
inherited fields is done by our reporting system. Unfortunately, the native
functionality available to custom fields (roll down to assignments) does not
cater for rolling down from summary tasks.

We have found that by deleting custom field values, they are then set to
null in the database; however, there does not appear to be an equivalent
method in the object model to determine whether a returned custom field has
been set to null. From what you are saying, it sounds as though our
impressions are correct; however, if you have any ideas as to how we may
achieve a similar outcome we would be very interested to hear them.

Regards,
David

 

[Peter Dunn]

Hi Jack,

Just to add to David's reply ... the key to o urdilemma is your sentence
"Since you can not tell in the application whether the underlying value is
null or 0, you can't tell that from project vba either". We agree that we
have not found a visual representation of null in the application, but the
application itself does indeed know about the difference between a null and a
0 (if its a numeric field) or a $0.00 (if its a cast field), as our testing
has proved that if you Edit>Cut Cell such a custom field value or you delete
the custom field value inside the Task Information dialogue box and click the
tick to accept it, the application stores a null (not a 0 or $0.00) in the
database custom field. If we instead enter a deliberate 0 or $0.00 it stores
this and not a null. So the application does know the difference. Its just
that it doesn't expose that difference (i.e when a null has been entered) in
the object model. In both cases it only exposes the visual value being the 0
or $0.00. So unless we're missing something in the object model that is held
elsewhere, we are stuck with not being able to tell the difference.

And as David has set out we use this nullness of a custom field in our own
SQL script to extract task/assignment level data from the MS Project Server
Reporting tables and to resolve the inheritance of custom field values from
higher level tasks to their subordinate lower level tasks so as the relieve
the PM from the burden of entering these on all tasks (i.e. they only have to
enter then on some key summary tasks). But the downside of that is the
resolved inheritance of custom fields is only done at extract time and is not
fully resolved in the Reporting database, so they are not available to native
MS Project Server reports of its cubes.

We therefore want to move the resolution of the custom field inheritance
from the SQL extract script into the VBA code inside the 'real-time' Project
Pro plan, so that this code can then be made to update the fully resolved
inherited custom field values into a shadow set of custom fields in the plan
so that these final values can be stored in the Reporting database and used
by native MS Project Server reports and its cubes.

At this stage as we don't think we can reliably determine the nullness of
custom field values in the Project Pro cached environment with the Getfield
method, we plan to have the input set of custom fields being only of 'text'
data type so that we can signify a null with a null string. But then we have
a little bit of extra work to do in converting from text to the real data
types of the shadow custom fields. We had hoped we could avoid this extra
work by having real data types in both the input custom field set and the
shadow inherited custom field set.

 

[Jack Dahlgren MVP]

Peter,

Nice bit of investigation. It is unfortunate that VBA did not not solve the
problem. Just another one of the examples that leave you scratching your
head wondering "what were they thinking?".

-Jack