Extract Strings from a Dot Notation Field

[wwalkerbout]

Hi June7

Finally got the time this weekend to take a shot at getting this dot notation string parsing to work. There was light at the end of the tunnel after all.

While the Project Server 2010 environment provides for a very limited set of string functions, it does allow the creation of multiple formula driven "Enterprise Custom Fields". So my work around was to create 8 shadow fields.

In the first of the shadow fields (ShadowLvl1), pulled in the full string and appended a period. This was to overcome the limitation of an IIf statement with no else clause. That way, each element of the dot notation string could be parsed on the bases of the first period located. Then, if no period was found (the string didn't extend to the full 8 levels), I could write a null value to the shadow fields after the last located period.

Then in the Level fields (I just used Level 1 to Level 8), I grabbed the first value from the source field and all of the rest of the values from the shadow fields. I have included the code below for the first couple of fields in each set (Shadow verses Level fields):


Shadow Fields
-------------
ShadowLvl1:

IIf([Org_Structure] <> "", ([Org_Structure] + "."), "")

ShadowLvl2:

IIf([ShadowLvl1] <> "" And InStr([ShadowLvl1], ".") <> 0, (Mid([ShadowLvl1], InStr([ShadowLvl1], ".") + 1)), "")

ShadowLvl3:

IIf([ShadowLvl2] <> "" And InStr([ShadowLvl2], ".") <> 0, (Mid([ShadowLvl2], InStr([ShadowLvl2], ".") + 1)), "")

Etc...

Level Fields
------------
Level 1:

IIf([Org_Structure] <> "", (Left([Org_Structure], InStr([Org_Structure], ".") - 1)), "")

Level 2:

IIf([ShadowLvl2] <> "", (Left([ShadowLvl2], InStr([ShadowLvl2], ".") - 1)), "")

Level 3:

IIf([ShadowLvl3] <> "", (Left([ShadowLvl3], InStr([ShadowLvl3], ".") - 1)), "")

Etc...

Like I mentioned originally, I am very rusty with this type of function coding, so there may well be a more elegant way to handle this. But for my purposes, this hack works exactly as I need it. I wouldn't have been able to to it without your jump start. I used your code to pull each of the elements from the dot notation string.

So thanks very much for your help with all of this. It was very much appreciated!

Cheers,

Wayne

[June7]

ww, see me slap my head and 'Doh!'

Your description of 'shadow' fields got me thinking. This exact same technique works in an Access query. What I did accomplished the parsing with only 5 calculated fields, 1 for each part. It was doable but after 5 parts I was lost in the complex nesting. Use 9 calculated fields to parse out 5 parts and it becomes very simple even beyond the first 5. Thank you for perceiving the solution. I should have.

[wwalkerbout]

Like I mentioned, I couldn't have done it without your help. You got me pointed in the right direction, for which I am very grateful.

I'll now have one very large client who will also be very appreciative. It puts us in a situation where we can handle a very large organizational structure in a very dynamic manner.

So many thanks!