OT2 Query Reports Variables

Services

Name	Variable	Type	Description
# of Employees Used	SERVICES.EMPCNT	Numeric	Total number of employees who worked on resolving this service request. Use of this field is deprecated – use abc_lem instead.
Action Code 6	SERVICES.ACT6	Text	Not currently used.
Apartment Number	SERVICES.APT	Text	The apartment number of the service request. Use of this field is suspect as there is no way to edit this field directly within the program, it can only be entered when data is automatically imported from the constituent record to which this service is related.
Bureau of Dept or Div	SERVICES.BUREAU	Text	The bureau (division) to which this service belongs
Case is On Going	SERVICES.ACT5	Text	Controlled by the "On Going" checkbox in the lower right quadrant of the service/contact information screen. A '0' in this field indicates that no work is currently being done on this service request. A '1' indicates that work is actively being performed on this request.
Class	SERVICES.T1	Text	A user defined field which appears in the lower right hand corner of the upper right hand quadrant of the service/contact screen. The definition of this field is set in the Admin–>Data Preferences–>Constituent Defaults screen under Code 1
Completion Reported by	SERVICES.CO4	Text	The employee id of the employee who reported the service request completed.
Contact Type P–I–L–O	SERVICES.CT	Text	The method by which this service request was originally made. Contains six possible values: P – Phone W – Web M – Mail S – On Site (in person) R – Radio O – Other
Date Letter Sent	SERVICES.LETDATE	Date	The date on which the constituent was last sent updated information concerning this request.
Date Order Sent Out	SERVICES.CDATE2	Date	Tickler date (when the service request should be reviewed again, especially helpful with weather–dependant service requests)
Date Resolved	SERVICES.RDATE	Date	Date the final report on the resolution of the service request was completed.
Date Work Started	SERVICES.CDATE3	Date	Date the work was assigned to a work crew. Assumed to be the date the work actually started.
Date of Request	SERVICES.CDATE	Date	Date the service request was first entered into the system.
Department Code	SERVICES.DEPTCODE	Text	Department this request belongs to.
Employee Hours Used	SERVICES.HRS	Numeric	Deprecated – Total number of hours spent by all employees on resolving this service request. Use ABC–LEM instead.
Exact Location	SERVICES.WHERE	Text	A brief description to help locate a request location.
FEMA Repayment	SERVICES.L2	Logical	If true, then this service request should be submitted to FEMA for possible reimbursement.
Group	SERVICES.GRP	Text	The group to which this service request belongs.
House Number	SERVICES.SNUM	Text	The street number part of the address where the work to resolve this service request is located.
How Many of This	SERVICES.TCNT	Numeric	The total number of service requests associated with the constituent record to which this service request is related.
ID Number	SERVICES.SID	Text	The Constituent ID number linking this service request to a constituent record.
Key Employee ID Number	SERVICES.EMP_ID	Text	The employee id number of the key contact person for information related to this request.
Location Code	SERVICES.LCODE	Text	Not currently used
Marked by Query Select	SERVICES.MRK	Text	When this field is not empty, it indicates that this service record has been marked by a previous query run through Query–>Services/Contacts. Usually marked with a capitol 'I'.
Name of Letter Sent	SERVICES.LETSENT	Text	The name of the last letter which was sent to the constituent concerning this service request.
Note Comment Exists	SERVICES.NOTEIN	Text	Supposed to indicate that notes exist for this service record, but not currently used.
Notes Comment	SERVICES.NOTESIN	Text	The internal (for public works personnel eyes only) notes related to this service request.
Notes Explain	SERVICES.NOTES	Text	Public notes related to this service request.
Odd/Even Street Number	SERVICES.T2	Text	An 'O' in this field indicates that the address where the work to resolve this service request is located is on the odd side of the street. An 'E' indicated that the address is on the even side of the street. No entry indicates that this request has no street number associated with it.
Order Given to	SERVICES.CO3	Text	The employee id of the employee or crew leader to whom this service request has been assigned.
Order Sent Out by	SERVICES.CO2	Text	The employee id of the employee who assigned to work order to an individual or crew.
Priority Code	SERVICES.NOTE	Text	The priority code assigned to this service request.
Referred Request	SERVICES.ACT3	Text	A '1' in this field indicates that the request has been referred to an outside agency.
Request Number	SERVICES.CNT	Text	The unique id of this request related to the constituent record. The first request from this constituent will be '001', the second '002', etc.
Resolution Code	SERVICES.RESCODE	Text	Contains one of four possible values: N – New P – Pending F – Forwarded C – Complete
Resolution Tracking #	SERVICES.RESOLVE	Text	Contains a resolution tracking number. These correspond to: blank – no information 1 – New Entry 2 – Referred to another Department/Contractor 3 – Awaiting response from Resident 4 – Awaiting all clear on locates 5 – Work in Progress 6 – Awaiting Repair/Replacement Parts 7 – Weather Permitting/Seasonal Work 8 – Awaiting Cost Estimate 9 – Complete
Responder	SERVICES.RESP	Text	Not currently used.
Response Type P–I–L–O	SERVICES.RT	Text	The method by which the constituent was last updated on the service request. Contains six possible values: P – Phone W – Web M – Mail S – On Site (in person) R – Radio O – Other
Returned Call	SERVICES.ACT2	Text	A '1' in this field indicates that the constituent has been contacted by phone concerning this service request.
Scheduled Days to Complete	SERVICES.SDAYS	Numeric	The number of days this service request generally takes to complete. Passed in from the service codes table. I assume it is included in this table to allow it to be updated when it is known that this particular request will take longer than general, but since there is no way to modify this number from the service/contact screen, it does not appear to be being used in this way.
Sent Information	SERVICES.ACT1	Text	A '1' in this field indicates that information related to this service request has been sent to the constituent.
Sent Letter	SERVICES.ACT4	Text	A '1' in this field indicates that a letter has been sent to the consituent concerning this service request. (see also 'Date Letter Sent', SERVICES.LETDATE and 'Name of Letter Sent', SERVICES.LETSENT).
Service/Issue Code	SERVICES.ISSUE	Text	A code indicating the type of Service Request. (See also 'Service/Issue Definition', SERVICES.DEF).
Service/Issue Definition	SERVICES.DEF	Text	The definition of the service request. (See also 'Service/Issue Code', SERVICES.ISSUE)
State Repayment	SERVICES.L1	Logical	If this field is marked 'True', then this service request should be submitted for possible reimbursement to the state.
Street Directional	SERVICES.SDIR	Text	The direction componant of the street address where the work is to be performed: ie. N, S, NE, etc.
Street Name	SERVICES.SNAME	Text	The (base) name of the street where this service request is to be performed. Depending on what fields the client has chosen for address records, may include the direction and/or suffix of the street also.
Street Suffix	SERVICES.SSUF	Text	A three character field indicating the suffix of the street where the work on this service request is to be perfomed: ie. ST, AVE, RD, etc.
Support by Citizen = F–O–U	SERVICES.CSUPOP	Text	With legislative issues, indicates the constituent's attitude towards the proposal F – For O – Opposed U – Undecided
Support by Office = Y–N–U	SERVICES.SUPOP	Text	With Legislative Issues, indicates the position of the office. Y – Supports N – Opposed U – Undecided
Time Order Sent Out	SERVICES.CTIME2	Text	Not currently used.
Time Work Completed	SERVICES.RTIME	Text	Contains the time of day when the work order was completed in the format [A\|P]hhmm. IE. if the work was reported completed at 2:35 PM, this field would contain P0235.
Time Work Started	SERVICES.CTIME3	Text	The time of day when the service request was assigned as a work order in the format [A\|P]hhmm. IE. if the work was assigned at 2:35 PM, this field would contain P0235.
Time of Request	SERVICES.CTIME	Text	The time of day when the request was originally entered into the system in the format [A\|P]hhmm. IE. if the request was entered at 2:35 PM, this field would contain P0235.
Type I–B–O–G	SERVICES.T0	Text	Contains one of four possible values indicating the type of entity making the request. I – Individual B – Business O – Organization G – Government
Type of Request S–I–L–C–A	SERVICES.ITYPE	Text	Contains one of possible values Indicating the type of request this is: S–Service Request L–Response to Legislation I–Response to Public Issue C–General Communication A–Permit or Application E–Event
User Defined (County)	SERVICES.CTY	Text	The first of the five Geo–Political fields which appear just below the Detailed Notes window in the upper right–hand quadrant of the service/contact screen. These fields can be hidden using the Admin–>Data Preferences–>Service/Contact Defaults screen. If the "Copy Constituent's Home address to new Service Requests" box is checked on that same screen, the geo political information is also copied from the constituent record to which this service request is related. The label for this field is defined through the Admin–>Data Preferences–>Constituent Defaults screen.
User Defined (Cross Street)	SERVICES.NAME	Text	This field was designed to hold more information about the location where the service request is to be resolved. Either the name of the business where the request information is to be resolved, or the name of the nearest cross street to the service request location. This field is the first line under the words "SERVICE LOCATION" which appear in red on the service/contact screen.
User Defined (Map Grid #)	SERVICES.CUS	Text	Designed to hold a map grid number or other code used to pinpoint the location where the work is to be done, this user defined field is defined through the Admin–>Data Preferences–>Service/Contact Defaults screen and appears at the top of the upper right–hand quadrant of the Service/Contact screen.
User Defined (Precinct)	SERVICES.PCT	Text	The fifth of the five Geo–Political fields which appear just below the Detailed Notes window in the upper right–hand quadrant of the service/contact screen. These fields can be hidden using the Admin–>Data Preferences–>Service/Contact Defaults screen. If the "Copy Constituent's Home address to new Service Requests" box is checked on that same screen, the geo political information is also copied from the constituent record to which this service request is related. The label for this field is defined through the Admin–>Data Preferences–>Constituent Defaults screen.
User Defined (Tax ID)	SERVICES.TID	Text	Designed to hold a tax id number, this user defined field is defined through the Admin–>Data Preferences–>Service/Contact Defaults screen and appears at the top of the upper right–hand quadrant of the Service/Contact screen.
User Defined (Township)	SERVICES.TWN	Text	The fourth of the five Geo–Political fields which appear just below the Detailed Notes window in the upper right–hand quadrant of the service/contact screen. These fields can be hidden using the Admin–>Data Preferences–>Service/Contact Defaults screen. If the "Copy Constituent's Home address to new Service Requests" box is checked on that same screen, the geo political information is also copied from the constituent record to which this service request is related. The label for this field is defined through the Admin–>Data Preferences–>Constituent Defaults screen.
Ward	SERVICES.WRD	Text	The second of the three Geo–Political fields which appear just below the Detailed Notes window in the upper right–hand quadrant of the service/contact screen. These fields can be hidden using the Admin–>Data Preferences–>Service/Contact Defaults screen. If the "Copy Constituent's Home address to new Service Requests" box is checked on that same screen, the geo political information is also copied from the constituent record to which this service request is related. The label for this field is defined through the Admin–>Data Preferences–>Constituent Defaults screen.
Who Took Request	SERVICES.CO	Text	The employee id of the employee who originally took the service request.
Work Days to Complete	SERVICES.WDAYS	Numeric	The number of days between the "Date of Request" (SERVICES.CDATE) and the "Date Resolved" (SERVICES.RDATE). This information is used to update the average turnaround time in the issue definition table.
Work Order Form Printed	SERVICES.ACT	Text	A 'P' in this field indicates that the work order has been printed. Controlled by the top checkbox in the lower right–hand quadrant of the Service/Contact screen.
Work Requested by	SERVICES.RQBY	Text	Used to indicate the name of the individual making the request in the case of a business or organization with more than one contact person.
c–Apartment	SERNAME.APT	Text	The apartment number from the home address part of the constituent record to which this issue record is related.
c–Area	SERNAME.CODE2	Text	Contains the street direction from the sername record to which this issue record is related, from the home address if the record is marked as an individual, business address otherwise.
c–Business City	SERNAME.WCITY	Text	Contains the street direction from the sername record to which this issue record is related, from the home address if the record is marked as an individual, business address otherwise.
c–Business Name	SERNAME.BCNAME	Text	The name from the work address part of the constituent record to which this issue record is related.
c–Business Street Directio	SERNAME.WTDIR	Text	Contains the street direction from the sername record to which this issue record is related, from the home address if the record is marked as an individual, business address otherwise.
c–Business Street Name	SERNAME.WTNAME	Text	The name of the street from the work address part of the constituent record to which this issue record is related.
c–Business Street Number	SERNAME.WTNUM	Text	The street number from the work address part of the constituent record to which this issue record is related.
c–Business Street Suffix	SERNAME.WTSUF	Text	The street suffix (ex: ST, WAY, DR, etc.) from the work address part of the constituent record to which this issue record is related.
c–Business Suite	SERNAME.BCADRSZ	Text	The Suite number from the work address part of the constituent record to which this issue record is related.
c–Business Zip Code	SERNAME.WZIP	Text	The Zip code from the work address part of the constituent record to which this issue record is related.
c–Car/Mobile Phone	SERNAME.CPHONE	Text	The car/mobile phone number from the constituent record to which this issue record is related.
c–City	SERNAME.CITY	Text	The city name from the home address section of the constituent record to which this issue record is related.
c–Constituent ID	SERNAME.SID	Text	The unique constituent ID of the constituent record to which this issue record is related. See also "ID Number" (SERVICES.SID)
c–Contact Name	SERNAME.TITLE	Text	The formal name by which the constituent is to be addressed to which this issue record is related.
c–County	SERNAME.CTY	Text	The county code associated with the constituent record to which this issue record is related.
c–Fax	SERNAME.FPHONE	Text	The fax number from the constituent record to which this issue record is related.
c–Garbage Zone	SERNAME.CODE3	Text	The third of the four 'political' fields on the constituent screen. This field was designed to hold the garbage collection zone of the constituent record associated with this issue record.
c–H2O Billing Number	SERNAME.TID	Text	TID (default is water billing number) from the constituent record to which this issue record is related.
c–Home Phone	SERNAME.HPHONE	Text	The home phone number from the constituent record to which this issue record is related.
c–Last Name	SERNAME.LNAME	Text	The last name of the individual from the constituent record to which this issue record is related.
c–Mail to H or W or A	SERNAME.MAIL	Text	Contains three possible values indicating to which address from the constituent record to which this issue record is related correspondence should be mailed to, H–Home (Individual Address) W–Work (Business Address) A–Alternate (Alternate Address).
c–Marked by Query Select	SERNAME.MRK	Text	If the constituent record to which this issue record is related has been marked by a query through the Query menu, this field will not be blank. Usually a capitol "I" is used to mark records.
c–MiscCode 02	SERNAME.TC2	Text	Currently unused.
c–MiscCode 03	SERNAME.TC3	Text	Currently unused.
c–MiscCode 04	SERNAME.TC4	Text	Currently unused.
c–MiscCode 05	SERNAME.TC5	Text	Currently unused.
c–MiscCode 06	SERNAME.TC6	Text	Currently unused.
c–MiscCode 07	SERNAME.TC7	Text	Currently unused.
c–MiscCode 08	SERNAME.TC8	Text	Currently unused.
c–MiscCode 09	SERNAME.TC9	Text	Currently unused.
c–MiscCode 10	SERNAME.TC10	Text	Currently unused.
c–MiscCode 11	SERNAME.TC11	Text	Currently unused.
c–MiscCode 12	SERNAME.TC12	Text	Currently unused.
c–MiscCode 13	SERNAME.TC13	Text	Currently unused.
c–MiscCode 14	SERNAME.TC14	Text	Currently unused.
c–MiscCode 15	SERNAME.TC15	Text	Currently unused.
c–Number of Cases	SERNAME.SCNT	Text	The total number of issues associated with the constituent record to which this issue record is related.
c–Odd/Even Address	SERNAME.TC1	Text	Contains three possible values: 'E' if the address ends in an even number, 'O' if the address ends in an odd number, and nothing if no address information has been entered. Keyed to the home street address if the constituent record to which this issue record is related is marked as 'Individual', the work street address otherwise. If the address ends in 1/2 then 1/2 is ignored, so 321 1/2 Baker ST will be odd but 320 1/2 Baker ST will be even.
c–Pager	SERNAME.PPHONE	Text	The pager number from the constituent record to which this issue record is related.
c–Pin Number	SERNAME.CUS	Text	This user defined field is located right above the ID field on the constituent record to which this issue record is related.
c–Precinct	SERNAME.PCT	Text	The precinct code of the constituent record this issue record is associated with.
c–Sex	SERNAME.SEX	Text	Designed to hold the gender of the contact individual from the constituent record associated with this issue record. However, I see nowhere to enter that information, and for the most part the field appears to be empty.
c–Street Direction	SERNAME.STDIR	Text	The street direction (ex: N, S, NW, etc.) from the home address part of the constituent record to which this issue record is related.
c–Street Name	SERNAME.STNAME	Text	The name of the street from the home address part of the constituent record to which this issue record is related.
c–Street Number	SERNAME.STNUM	Text	The street number from the home address part of the constituent record to which this issue record is related.
c–Street Suffix (Ave)	SERNAME.STSUF	Text	The street suffix (AVE, ST, DR, etc.) from the home address part of the constituent record to which this issue record is related.
c–Township	SERNAME.TWN	Text	The second in the list of user defined 'Political' fields, this was designed to hold the town code for the town in which this constituent to which this issue record is related is located.
c–Type I–B–O–G	SERNAME.IBCO	Text	Contains a single character determining the type of constituent record to which this issue record is related: I–Individual B–Business G–Government O–Organization
c–Universal Direction	SERNAME.CDIR	Text	Contains the street direction from the sername record to which this issue record is related, from the home address if the record is marked as an individual, business address otherwise.
c–Universal House Number	SERNAME.CNUM	Text	The street number from the constituent record associated with this issue record. Contains the street number from the home address if the record is marked as an individual, from the work address otherwise. This field is in character format, so should not be used to sort addresses, as "12" will be sorted as less than "2".
c–Universal Name	SERNAME.CNAME	Text	Contains the street name from the constituent record to which this issue record is related. Contains the street name from the home address if this record is labeled as an individual, business address otherwise.
c–Ward	SERNAME.WRD	Text	The third of the four political information fields on the constituent screen, this field was designed to hold the ward code of the ward where the constituent associated with this issue record is located.
c–Work Phone	SERNAME.WPHONE	Text	The work phone number from the constituent record associated with this issue record.
c–Zip Code	SERNAME.ZIP	Text	The zip code from the 'home' address from the constituent record associated with this issue record.
c–Zone	SERNAME.CODE1	Text	The first of the four 'political' fields on the constituent screen. This field was designed to hold the zone code for the zone of the constituent record associated with this issue record.
n–Universal House Number	SERNAME.NNUM	Numeric	The street number from the constituent record associated with this issue record. Contains the street number from the home address if the record is marked as an individual, from the work address otherwise. This field is in numeric format as opposed to the c–Universal House Number which is in character format. This field should be used for sorting street addresses.