Colour task by resource

[Andrew]

I am trying to colour tasks by the resource allocated to them. I am using the
formula below in a flag field to turn the flag on and off.

IIf([Resource Names]="Demolition",Yes,No)

This work fine. However, when I add another resource this no longer works,
we use resources to fileter on ie phase 1 etc... is there something I can add
that acts as a filter ie "contains" or something similar?? Please do not
reply saying dont add additional resources as that would be too obvious, and
I am trying to find a way around this particular problem, rather than trying
to change a company's way of working.

Andrew

 

[Dave]

I am trying to colour tasks by the resource allocated to them. I am using the
formula below in a flag field to turn the flag on and off.

IIf([Resource Names]="Demolition",Yes,No)

This work fine. However, when I add another resource this no longer works,
we use resources to fileter on ie phase 1 etc... is there something I can add
that acts as a filter ie "contains" or something similar?? Please do not
reply saying dont add additional resources as that would be too obvious, and
I am trying to find a way around this particular problem, rather than trying
to change a company's way of working.

Andrew

Do you mean "if you add another resource to the task"? If so, then you
have to look inside the [Resource Names] string to find the string you want.

Try something like:

Iif( 0 = InStr( 1 , [Resource Names] , "Demolition" , 1 ) , No , Yes )

To make this completely rigorous, you may have to wrap in in further
tests which check for null values etc.

 

[Andrew]

Dave

Thankyou for your quick response, I will give it a whirl

Andrew

I am trying to colour tasks by the resource allocated to them. I am using the
formula below in a flag field to turn the flag on and off.

IIf([Resource Names]="Demolition",Yes,No)

This work fine. However, when I add another resource this no longer works,
we use resources to fileter on ie phase 1 etc... is there something I can add
that acts as a filter ie "contains" or something similar?? Please do not
reply saying dont add additional resources as that would be too obvious, and
I am trying to find a way around this particular problem, rather than trying
to change a company's way of working.

Andrew

Do you mean "if you add another resource to the task"? If so, then you
have to look inside the [Resource Names] string to find the string you want.

Try something like:

Iif( 0 = InStr( 1 , [Resource Names] , "Demolition" , 1 ) , No , Yes )

To make this completely rigorous, you may have to wrap in in further
tests which check for null values etc.

 

[Andrew]

Dave

That worked perfectly. However, it has led to another issue. I have a whole
set of pre-construction activities allocated to flags. When I put the formula
in the flag field it locks the field so that you cannot put a yes in the
cell. What would I have to add to the formula to allow an OR statement, ie if
the Resource name is "Demolition" or "Tender Information Required", so that
say Flag1 would be allocated to that activity if either resource was added.
Obviously I would not be able to show them in the same view. Or is there
another way to resolve this ?

Regards

Andrew

I am trying to colour tasks by the resource allocated to them. I am using the
formula below in a flag field to turn the flag on and off.

IIf([Resource Names]="Demolition",Yes,No)

This work fine. However, when I add another resource this no longer works,
we use resources to fileter on ie phase 1 etc... is there something I can add
that acts as a filter ie "contains" or something similar?? Please do not
reply saying dont add additional resources as that would be too obvious, and
I am trying to find a way around this particular problem, rather than trying
to change a company's way of working.

Andrew

Do you mean "if you add another resource to the task"? If so, then you
have to look inside the [Resource Names] string to find the string you want.

Try something like:

Iif( 0 = InStr( 1 , [Resource Names] , "Demolition" , 1 ) , No , Yes )

To make this completely rigorous, you may have to wrap in in further
tests which check for null values etc.

 

[Dave]

Doesn't

Iif( ( 0 = InStr( 1 , [Resource Names] , "Demolition" , 1 ) ) OR ( 0 =
InStr( 1 , [Resource Names] , "Tender Information Required" , 1 ) ) , No
, Yes )

do the trick? It should set the flag if one or the other or both
resources are added.

Haven't tested this so there may be cut and paste errors but the
conditional test must be the first argument to the IIF statement.

As the complexity of this goes up you might find it easier to break this
out into more fields for clarity.

Dave

That worked perfectly. However, it has led to another issue. I have a whole
set of pre-construction activities allocated to flags. When I put the formula
in the flag field it locks the field so that you cannot put a yes in the
cell. What would I have to add to the formula to allow an OR statement, ie if
the Resource name is "Demolition" or "Tender Information Required", so that
say Flag1 would be allocated to that activity if either resource was added.
Obviously I would not be able to show them in the same view. Or is there
another way to resolve this ?

Regards

Andrew

I am trying to colour tasks by the resource allocated to them. I am using the
formula below in a flag field to turn the flag on and off.

IIf([Resource Names]="Demolition",Yes,No)

This work fine. However, when I add another resource this no longer works,
we use resources to fileter on ie phase 1 etc... is there something I can add
that acts as a filter ie "contains" or something similar?? Please do not
reply saying dont add additional resources as that would be too obvious, and
I am trying to find a way around this particular problem, rather than trying
to change a company's way of working.

Andrew

Do you mean "if you add another resource to the task"? If so, then you
have to look inside the [Resource Names] string to find the string you want.

Try something like:

Iif( 0 = InStr( 1 , [Resource Names] , "Demolition" , 1 ) , No , Yes )

To make this completely rigorous, you may have to wrap in in further
tests which check for null values etc.