Enterprise Custom Field config question

[jremmc]

MOPS 2007 SP1 + IU (SP2 soon). In process of setting up custom enterprise
fields.

We need to identify every task by a 7-digit charge code number comprised of
a 4-digit contract code number + a 3-digit task code number. Example:
1250-701. With 50 contract code numbers and 300+ task code numbers, and task
codes repeated among contracts (e.g. 1250-701, 1354-701, 1632-701...), we are
starting out with a possible 1,500+ 7-digit numbers; right now we have over
900.

If I use a two-level hierarchical lookup table (1st level = 4-digit contract
charge code, 2nd level = 3-digit task charge code), the table would be large
and maintenance would be significant given the frequency of occurance. When a
new contract code is added I could be adding 60 or more task codes for that
contract. When a new task code is added, I could be adding it to 20 or more
contract codes.

I would prefer two more manageable lookup tables with two corresponding text
fields and a third calculated field that concatenates the fields' text
strings. That way, when a new contract or task code code was created I'd only
need enter it once into the appropriate lookup table. The concatenated field
would yield the 7-digit charge code.

What, if any, consequences in *Project Server* are there to using calculated
fields that concatenate strings, e.g. will we run into an issue trying to
tally costs/schedule status using the calculated field as a filter? (I know
WSS lists can sort, filter, and group on fields using concatenate, but I
don't know/use Project and googling turns up very little on concatenating
strings in Project or Project Server so I guess it is done rarely or not
done?)

Thanks,
jremmc

 

[Dale Howard [MVP]]

jremmc --

I understand your pain! There are several issues I can see you will run
into almost immediately using a custom field with a formula that
concatenates the two values from the fields containing the Lookup Tables:

1. You will not be able to display the calculated field on the My Tasks
page for each team member. Unless Microsoft has changed this behavior in
SP2, you cannot add custom fields containing a formula to the My Tasks page.

2. You cannot include the custom field containing the formula as a
Dimension in the OLAP cubes, and thus, cannot add it to Data Analysis views.

These are a couple of issues I think you will face. However, I gladly
invite the others to share their thoughts with you as well. Hope this
little bit helps.

 

[jremmc]

Hi Dale,

Thanks. It's the hierarchical table route then. Have your books, they're
great.

jremmc

 

[Dale Howard [MVP]]

jremmc --

You are more than kind, my friend, and you are more than welcome for the
help as well!

 

[Ben Howard]

But if you use two lookup tables you can put them both into the OLAP cube and
you would still be able to get at the data and simulate all a specific code...

Also, I suspect that resources don't really mind about the charge codes, so
putting a concatenated custom field in the my tasks might not be an issue,
and if it is, again, put both ECFs in the My Tasks page.

The end question is how and where you need to report on the codes. From
what you say, I'm with two fields and a custom formula to concatenate them.
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/

 

[jremmc]

Hi Ben,

Ok. I've decided to try both ways for pilot phase (one project), which we
are doing in a second instance of PWA, and take it from there (feedback,
testing...)

Thanks to both you and Dale, much appreciated.

jremmc

 

[Ben Howard]

Hi Jremmec,

Good luck and pls post your results!
--
Thanks, Ben.

Please rate this post if it helped.
http://appleparkltd.spaces.live.com/