Handling Requests And Statuses (Was "Re: Fw: Thanks")

Hey there...

There are several ways to handle this. 

The simple way would be, as you surmised, to set a bit field in the
table as a toggle...sort of an on/off, pending/accepted thing...when a
job is accepted, set the toggle to 1 and in your Pending Jobs query
just get the records where the pending/accepted field is still 0.

If you're moving the request to another table once it's assigned, you
may not need to keep it in the request table and may want to delete it.
We treat requests as things with a certain "status" attribute, be it
assigned or not, and they always stay in the requests table.

Here's the technique I'd suggest:

Add a smallint field to your requests table (rather than a single bit
on-off field) called "RequestStatus" or somesuch, where you store a
single integer indicating the status of each request (presumably keyed
off a "Request Status" table that lists all possible statuses). In this
way, rather than just saying a request is pending or accepted, you
could indicate the request's status from a whole range of statuses
(e.g., Quote in Preparation, Quote Delivered, Quote Accepted, Job
Begun, Job Completed, Job Submitted to Client For Approval). If you
really want a data goldmine, add a table like "RequestStatusHistory",
where you record each request's movement *thru* the statuses. With a
date field and some other info, you could then generate reports on WHEN
and WHO prepared the quote, WHEN and WHO submitted the request, WHEN
and WHO approved it, etc., all for each request.

This kind of methodology is the one we've used for both our internal
Time Accountability module, as well as for a client site that uses a
similar function. If you're interested in that approach (but like most
of us crunched for time :-), I would suggest ramping up to it. Add the
Status field to your Request table, keyed off a StatusList table that
lists all possible statuses. Then later you can add a Request History
table to track the progression of each request thru the request
process.

<StickingNoseWhereItDon'tBelong>
Also on a side note, I noticed in your cfquery tag that your datasource
name ("Survey Status") has a space in it...you may be able to get away
with it for now, but I wouldn't expect it to last forever. With all the
communication going back and forth between CF, web server (which may
change), and your database (which may also change), not to mention new
funtionality down the road like stored procedures and so forth, it may
cause a problem. May be easier to change now rather than later.
</StickingNoseWhereItDon'tBelong>

:-) 

Hope this helps...

----Judd Miller
Vertical Alliance, Inc.

--- dwiese@xxxxxxxxxxxxx wrote:
> 
> This is a real elementary question, but again I 'm new to this stuff.
> I
> have a table that holds work requests for bidding and estimates. 
> Once the
> job is accepted I don't want that job to show up in the pending job
> list.
> Up to this point I query all jobs in the REQUEST table and give the
> supervisor a pick list of the all REQUEST to make assignments.
> 
> Maybe simply delete the request once it has been assigned. Maybe
> just
> adding a field called ASSIGN =yes/no and not deleting it from the
> REQUEST.
> Any suggestions, advantages and disadvantages to the ASSIGN field
> thing.
> 
> <CFQUERY DATASOURCE="Survey Status" Name="Assign">
> SELECT *
> FROM request
> </CFQUERY>
> 
> <HR WIDTH="100%" align="center">
> <CENTER><TABLE BORDER="0" WIDTH="700" >
> <CAPTION valign="bottom"></CAPTION>
> 
> <TR BGCOLOR="CCCCCC">
> <TH COLSPAN=6>
> <cfoutput>
> <CFIF Assign.recordcount IS 0>
> <CENTER><H2>There are no new Survey projects!!!!</H2>
> <CFELSE>
> <H2>The following #Assign.recordcount# project(s) need
> Assignments!!!</H2>
> <BR>
> <H2>Select Township and Range, to assign Assignments and
> Surveyor</H2></CENTER>
> </CFIF>
> </cfoutput>
> </TH>
> 
> <TR>
> <TH BGCOLOR="CCCCCC">Requestor</TH>
> <TH BGCOLOR="CCCCCC">Request Date</TH>
> <TH BGCOLOR="CCCCCC">Gourp No.</TH>
> <TH BGCOLOR="CCCCCC">T/R</TH>
> <TH BGCOLOR="CCCCCC">Project Name</TH>
> <TH BGCOLOR="CCCCCC">Estimator</TH>
> </TR>
> 
> <CFOUTPUT QUERY="Assign">
> <TR>
> <TD BGCOLOR="FFFFFF">#Requestor#</TD>
> <TD BGCOLOR="FFFFFF">#DateFormat(Left(RequestDate,10),
> 'MM/DD/YY')#</TD>
> <TD BGCOLOR="FFFFFF">#RGrpNo#</TD>
> <TD BGCOLOR="FFFFFF"><A HREF="assign.cfm?ID=#ID#">T.#RT# #RN#.,
> R.#RR# #RE#.</A></TD>
> <CFIF RProjectName IS "">
> <CFSET #RProjectName# = ".">
> <TD BGCOLOR="FFFFFF">#RProjectName#</TD>
> </CFIF>
> <TD BGCOLOR="FFFFFF">#Estimator#</TD>
> </CFOUTPUT>
> </TABLE>
> 
> 


__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail ? Free email you can access from anywhere!
http://mail.yahoo.com/