Formula in a 2007 Task Custom field

D

DogLBer

Server 2007, SP1, w DecCU
I'm trying to perform the simple task of setting a flag for a particular
milestone task, having a second field read the flag and display the task
finish date. Creating the flag field, both Ent and Local fields is no
problem. Creating a simple IIF statement is another story. I have two
symptoms that occur with this formula
IIf( [BC Test Flag]="yes", [Finish], 'no sale' ).
1. If I build it in Pro as a local field in either a date or text field,
upon clicking OK it changes the BC Test Flag to Flag10 and evaluates it as"no
Sale" even when the flag is set. I have tried this with the flag field as a
Ent field and as a local field with no difference in symptom. I hav tried
making the flag a text field with a yes/no drop down. No difference. I have
tried single quotes (') and double quotes ("), no difference.
2. In PWA/Ent Custom Field Def I can build the flag field, but when I
attempt the formula in a Text or Date field it give me "The formula contains
a syntax error or contains a reference to a unrecgnized field or function
name."

Also I tried the function picker and free handing the formula, no
difference, and I am working at the task level not trying to go to a project
field with it.
After two days of trying and failing I have to assume I'm do something
basically wrong and won't spot it myself. Can some fresh eyes tell me where I
am erroring?
 
A

Andrew Lavinsky

I used this formula, and worked. Should work on the server as well:

IIf([Flag1]=Yes,[Finish],"No Sale")

You will need to use a text field though.

-A
 
J

Jonathan Sofer [MVP]

First of all you cannot use a Date field as one of your result options is a
text. This means you have to use a text field. Try using this formula
instead.

IIf([BC Test Flag]=True,[Finish],'no sale')
 
D

DogLBer

I tried it in a Local Text field and it gave the same results as before, upon
clicking OK "BC Test Flag" defaulted to to Flag10 and returns "no sale" no
matter what the flag conditions are. Tying it as a Ent field gave the same
error message as before "The formula contains a syntax error or contains a
reference to a unrecgnized field or function name."
I wonder if I have a corrupted server. I tried it on a couple of files so it
isn't a corrupted file and I've tried it on two different Pro installs on two
machines so it isn't the client side.

Jonathan Sofer said:
First of all you cannot use a Date field as one of your result options is a
text. This means you have to use a text field. Try using this formula
instead.

IIf([BC Test Flag]=True,[Finish],'no sale')

DogLBer said:
Server 2007, SP1, w DecCU
I'm trying to perform the simple task of setting a flag for a particular
milestone task, having a second field read the flag and display the task
finish date. Creating the flag field, both Ent and Local fields is no
problem. Creating a simple IIF statement is another story. I have two
symptoms that occur with this formula
IIf( [BC Test Flag]="yes", [Finish], 'no sale' ).
1. If I build it in Pro as a local field in either a date or text field,
upon clicking OK it changes the BC Test Flag to Flag10 and evaluates it
as"no
Sale" even when the flag is set. I have tried this with the flag field as
a
Ent field and as a local field with no difference in symptom. I hav tried
making the flag a text field with a yes/no drop down. No difference. I
have
tried single quotes (') and double quotes ("), no difference.
2. In PWA/Ent Custom Field Def I can build the flag field, but when I
attempt the formula in a Text or Date field it give me "The formula
contains
a syntax error or contains a reference to a unrecgnized field or function
name."

Also I tried the function picker and free handing the formula, no
difference, and I am working at the task level not trying to go to a
project
field with it.
After two days of trying and failing I have to assume I'm do something
basically wrong and won't spot it myself. Can some fresh eyes tell me
where I
am erroring?
 
A

Andrew Lavinsky

In the enterprise custom fields, are you first creating the enterprise flag
field and then creating the enterprise text field? Unless you do it in that
order, it won't work.

For the local test, it sounds to me like you didn't rename the custom field....try
this in your local file:

1) Insert the Flag1 field
2) Right click on the Flag1 field header, choose customize, and rename it
to BC Test Flag
3) Insert the Text1 field
4) Right click on the Text1 field header, choose customize, and rename it
to Sale Date
5) Choose the "formula" option for the Sale Date field and add the formula
as below.

That should work. If you're looking at a summary task, make sure that the
appropriate options are set for showing the data on the summary level.

If you can't get it to work on the local level, I wouldn't even attempt to
put it into Enterprise.

-A
I tried it in a Local Text field and it gave the same results as
before, upon
clicking OK "BC Test Flag" defaulted to to Flag10 and returns "no
sale" no
matter what the flag conditions are. Tying it as a Ent field gave the
same
error message as before "The formula contains a syntax error or
contains a
reference to a unrecgnized field or function name."
I wonder if I have a corrupted server. I tried it on a couple of files
so it
isn't a corrupted file and I've tried it on two different Pro installs
First of all you cannot use a Date field as one of your result
options is a text. This means you have to use a text field. Try
using this formula instead.

IIf([BC Test Flag]=True,[Finish],'no sale')

Server 2007, SP1, w DecCU
I'm trying to perform the simple task of setting a flag for a
particular
milestone task, having a second field read the flag and display the
task
finish date. Creating the flag field, both Ent and Local fields is
no
problem. Creating a simple IIF statement is another story. I have
two
symptoms that occur with this formula
IIf( [BC Test Flag]="yes", [Finish], 'no sale' ).
1. If I build it in Pro as a local field in either a date or text
field,
upon clicking OK it changes the BC Test Flag to Flag10 and evaluates
it
as"no
Sale" even when the flag is set. I have tried this with the flag
field as
a
Ent field and as a local field with no difference in symptom. I hav
tried
making the flag a text field with a yes/no drop down. No difference.
I
have
tried single quotes (') and double quotes ("), no difference.
2. In PWA/Ent Custom Field Def I can build the flag field, but when
I
attempt the formula in a Text or Date field it give me "The formula
contains
a syntax error or contains a reference to a unrecgnized field or
function
name."
Also I tried the function picker and free handing the formula, no
difference, and I am working at the task level not trying to go to a
project
field with it.
After two days of trying and failing I have to assume I'm do
something
basically wrong and won't spot it myself. Can some fresh eyes tell
me
where I
am erroring?
 
D

DogLBer

Yes, I am first creating the flag field in Ent. The flag field exists yet in
Ent fields and in Local I renamed the field. When it removes the field name
from the IIF statement it does not remove the renamed field. It also
continues to exist it just replaces it in the statement with the original
Flag 10 name. And I am using it on a milestone task, not a summary task. I've
even tried it on a non milestone, non summary task with the same results.

Andrew Lavinsky said:
In the enterprise custom fields, are you first creating the enterprise flag
field and then creating the enterprise text field? Unless you do it in that
order, it won't work.

For the local test, it sounds to me like you didn't rename the custom field....try
this in your local file:

1) Insert the Flag1 field
2) Right click on the Flag1 field header, choose customize, and rename it
to BC Test Flag
3) Insert the Text1 field
4) Right click on the Text1 field header, choose customize, and rename it
to Sale Date
5) Choose the "formula" option for the Sale Date field and add the formula
as below.

That should work. If you're looking at a summary task, make sure that the
appropriate options are set for showing the data on the summary level.

If you can't get it to work on the local level, I wouldn't even attempt to
put it into Enterprise.

-A
I tried it in a Local Text field and it gave the same results as
before, upon
clicking OK "BC Test Flag" defaulted to to Flag10 and returns "no
sale" no
matter what the flag conditions are. Tying it as a Ent field gave the
same
error message as before "The formula contains a syntax error or
contains a
reference to a unrecgnized field or function name."
I wonder if I have a corrupted server. I tried it on a couple of files
so it
isn't a corrupted file and I've tried it on two different Pro installs
First of all you cannot use a Date field as one of your result
options is a text. This means you have to use a text field. Try
using this formula instead.

IIf([BC Test Flag]=True,[Finish],'no sale')


Server 2007, SP1, w DecCU
I'm trying to perform the simple task of setting a flag for a
particular
milestone task, having a second field read the flag and display the
task
finish date. Creating the flag field, both Ent and Local fields is
no
problem. Creating a simple IIF statement is another story. I have
two
symptoms that occur with this formula
IIf( [BC Test Flag]="yes", [Finish], 'no sale' ).
1. If I build it in Pro as a local field in either a date or text
field,
upon clicking OK it changes the BC Test Flag to Flag10 and evaluates
it
as"no
Sale" even when the flag is set. I have tried this with the flag
field as
a
Ent field and as a local field with no difference in symptom. I hav
tried
making the flag a text field with a yes/no drop down. No difference.
I
have
tried single quotes (') and double quotes ("), no difference.
2. In PWA/Ent Custom Field Def I can build the flag field, but when
I
attempt the formula in a Text or Date field it give me "The formula
contains
a syntax error or contains a reference to a unrecgnized field or
function
name."
Also I tried the function picker and free handing the formula, no
difference, and I am working at the task level not trying to go to a
project
field with it.
After two days of trying and failing I have to assume I'm do
something
basically wrong and won't spot it myself. Can some fresh eyes tell
me
where I
am erroring?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top