Status Field

  • Thread starter Thread starter FIECA
  • Start date Start date
F

FIECA

Hi All!

I want to create a ("status") field in my contracts tracking table to tell
me the status of the contract based on whether or not some other fields in
the table (yes/no) are checked (yes). Basically, I want to create code
where an inspection of the other fields is made in order. For instance the
fields being

1) received (yes/no)
2) reviewed (yes/no)
3) signed (yes/no)
4) returned (yes/no)
5) executed (yes/no)

the code would inspect each field (preferrably in order) and produce a
result based on what fields have been checked yes or no

again, for instance

1) received yes
2) reviewed yes
3) signed no
4) returned no
5) executed no

would produce a result of "awaiting signature" in the "status" field based
on the first "no" found at 3) signed

any help would be greatly appreciated....


Cheers!!

tom pratt
 
That is calculated data and should not be stored in the table. If the data
needed to determine the "status" is in the table, then you don't need the
"status" in the table. Instead, create your code/condition in your queries,
forms, and reports to create the status on the fly.
 
I want to create a ("status") field in my contracts tracking table to tell
me the status of the contract based on whether or not some other fields in
the table (yes/no) are checked (yes). Basically, I want to create code
where an inspection of the other fields is made in order. For instance the
fields being

1) received (yes/no)
2) reviewed (yes/no)
3) signed (yes/no)
4) returned (yes/no)
5) executed (yes/no)
Click to expand...

For an alternative (to bit mask) approach:

Transition Constraints
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko35

Jamie.

--
 
I want to create a ("status") field in my contracts tracking table to tell
me the status of the contract based on whether or not some other fields in
the table (yes/no) are checked (yes). Basically, I want to create code
where an inspection of the other fields is made in order. For instance the
fields being

1) received (yes/no)
2) reviewed (yes/no)
3) signed (yes/no)
4) returned (yes/no)
5) executed (yes/no)
Click to expand...

Even if you aren't interested in validating transitions between
states, you should consider *replacing* your five bit columns with a
single 'status' column, with either a validation rule for the five
valid states or a lookup table containing the five state values.

And just to prove that there's a Celko article for everything SQL:

Bad SQL: Byting the Hand that Feeds You
by Joe Celko
http://www.dbazine.com/ofinterest/oi-articles/celko23

Jamie.

--
 
Rick,

Any way you could help me with the code, maybe an example of the structure.
Unfortunately, I am not versed in code, so I wouldn't even know where to
begin. Thanks for the pointer on having the code in forms and queries and
not stored also in the table. Any help with the code would be greatly
appreciated.

Thanks,

Tom
 
In a query, you'd create a new column and put something like the following
in it...

Status: Status: IIf([Signed],"Signed","Awaiting Signature")


Now, if you have more than one status, and it depends on the various
checkboxes, then you will have to use nested IIF statements, or you will
have to include "and" or "or" conditions.

Status: Status: IIf([Executed],"Contract Executed",IIf([Returned],"Contract
Returned but not executed",IIf([Signed],"Signed","Awaiting Signature")))

Post back if you run into trouble.
 
Oops. In the nested example, I included "status" twice. It should read:

Status: IIf([Executed],"Contract Executed",IIf([Returned],"Contract Returned
but not executed",IIf([Signed],"Signed","Awaiting Signature")))


--
Rick B



Rick B said:
In a query, you'd create a new column and put something like the following
in it...

Status: Status: IIf([Signed],"Signed","Awaiting Signature")


Now, if you have more than one status, and it depends on the various
checkboxes, then you will have to use nested IIF statements, or you will
have to include "and" or "or" conditions.

Status: Status: IIf([Executed],"Contract
Executed",IIf([Returned],"Contract Returned but not
executed",IIf([Signed],"Signed","Awaiting Signature")))

Post back if you run into trouble.

--
Rick B



FIECA said:
Rick,

Any way you could help me with the code, maybe an example of the
structure. Unfortunately, I am not versed in code, so I wouldn't even
know where to begin. Thanks for the pointer on having the code in forms
and queries and not stored also in the table. Any help with the code
would be greatly appreciated.

Thanks,

Tom
Click to expand...
Click to expand...
 
Thanks for the pointer on having the code in forms and queries and
not stored also in the table. Any help with the code would be greatly
appreciated.
Click to expand...

If you still think five status columns are better than one, consider:

1) Why are you looking to display a single status in the front end?
2) Do you have validation rules to stop nonsense combinations e.g.
received=no signed=yes?
3) How much grief would it cause you to add another status e.g.
'countersigned'?
4) Do you realise if you'd named your columns status1, status2,
status3, ... status5 you'd have had at least three regulars pounce on
you with, "Fields are expensive, records are
cheap", accusations of "committing spreadsheet", etc ;-)

Jamie.

--
 
Back
Top