Quick Tip: Working With Additional Fields In Project Accounting
Additional fields in Project Accounting are stored in a highly normalised way that can make them difficult to work with, especially for the kind of sorting and filtering operations that are key to workspaces.
LINQ helps us out a little by allowing us to get straight to the collection of analysis fields associated with a project item. The slightly tricky (and dataset specific step) is getting at a specific field by using its primary key – not elegant, but effective. The other aspect of additional fields in project accounting that can make them tricky is that they model a number of different data types and – again – making use of a field means knowing its data type ahead of time, making queries against this schema specific to a particular dataset.
Below is a LINQ query example (built in LINQPad) of the kind of approach we can take to getting analysis codes into workspaces, and working with them effectively. In particular, note the ‘WeeksTaken’ field is a derived field based on the actual value of the days taken additional field – something that is only really possible because we know ahead of time (and assert in the query), that this is a numeric field
PCProjectItems
.Where(w=>w.PCProjectItemTypeID==0)
.Select
(
s=>new
{
s.Code,
s.Title,
Status=s.ProjectStatus.Description,
Region=s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105957).First().PCAnalysisFieldValue.TextValue,
DaysTaken=s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105908).First().PCAnalysisFieldValue.NumericValue,
WeeksTaken=(s.PCProjectItemAnalysisFields.Where(w=>w.PCAnalysisFieldID==105908).First().PCAnalysisFieldValue.NumericValue)/7
}
)
.Dump();
and the results are as follows (using Demodata):
