OT2 Query Reports Variables

Services

Variable	Name	Type	Description
SERNAME.APT	c–Apartment	Text	The apartment number from the home address part of the constituent record to which this issue record is related.
SERNAME.BCADRSZ	c–Business Suite	Text	The Suite number from the work address part of the constituent record to which this issue record is related.
SERNAME.BCNAME	c–Business Name	Text	The name from the work address part of the constituent record to which this issue record is related.
SERNAME.CDIR	c–Universal Direction	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.
SERNAME.CITY	c–City	Text	The city name from the home address section of the constituent record to which this issue record is related.
SERNAME.CNAME	c–Universal Name	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.
SERNAME.CNUM	c–Universal House Number	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".
SERNAME.CODE1	c–Zone	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.
SERNAME.CODE2	c–Area	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.
SERNAME.CODE3	c–Garbage Zone	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.
SERNAME.CPHONE	c–Car/Mobile Phone	Text	The car/mobile phone number from the constituent record to which this issue record is related.
SERNAME.CTY	c–County	Text	The county code associated with the constituent record to which this issue record is related.
SERNAME.CUS	c–Pin Number	Text	This user defined field is located right above the ID field on the constituent record to which this issue record is related.
SERNAME.FPHONE	c–Fax	Text	The fax number from the constituent record to which this issue record is related.
SERNAME.HPHONE	c–Home Phone	Text	The home phone number from the constituent record to which this issue record is related.
SERNAME.IBCO	c–Type I–B–O–G	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
SERNAME.LNAME	c–Last Name	Text	The last name of the individual from the constituent record to which this issue record is related.
SERNAME.MAIL	c–Mail to H or W or A	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).
SERNAME.MRK	c–Marked by Query Select	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.
SERNAME.NNUM	n–Universal House Number	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.
SERNAME.PCT	c–Precinct	Text	The precinct code of the constituent record this issue record is associated with.
SERNAME.PPHONE	c–Pager	Text	The pager number from the constituent record to which this issue record is related.
SERNAME.SCNT	c–Number of Cases	Text	The total number of issues associated with the constituent record to which this issue record is related.
SERNAME.SEX	c–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.
SERNAME.SID	c–Constituent ID	Text	The unique constituent ID of the constituent record to which this issue record is related. See also "ID Number" (SERVICES.SID)
SERNAME.STDIR	c–Street Direction	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.
SERNAME.STNAME	c–Street Name	Text	The name of the street from the home address part of the constituent record to which this issue record is related.
SERNAME.STNUM	c–Street Number	Text	The street number from the home address part of the constituent record to which this issue record is related.
SERNAME.STSUF	c–Street Suffix (Ave)	Text	The street suffix (AVE, ST, DR, etc.) from the home address part of the constituent record to which this issue record is related.
SERNAME.TC1	c–Odd/Even Address	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.
SERNAME.TC10	c–MiscCode 10	Text	Currently unused.
SERNAME.TC11	c–MiscCode 11	Text	Currently unused.
SERNAME.TC12	c–MiscCode 12	Text	Currently unused.
SERNAME.TC13	c–MiscCode 13	Text	Currently unused.
SERNAME.TC14	c–MiscCode 14	Text	Currently unused.
SERNAME.TC15	c–MiscCode 15	Text	Currently unused.
SERNAME.TC2	c–MiscCode 02	Text	Currently unused.
SERNAME.TC3	c–MiscCode 03	Text	Currently unused.
SERNAME.TC4	c–MiscCode 04	Text	Currently unused.
SERNAME.TC5	c–MiscCode 05	Text	Currently unused.
SERNAME.TC6	c–MiscCode 06	Text	Currently unused.
SERNAME.TC7	c–MiscCode 07	Text	Currently unused.
SERNAME.TC8	c–MiscCode 08	Text	Currently unused.
SERNAME.TC9	c–MiscCode 09	Text	Currently unused.
SERNAME.TID	c–H2O Billing Number	Text	TID (default is water billing number) from the constituent record to which this issue record is related.
SERNAME.TITLE	c–Contact Name	Text	The formal name by which the constituent is to be addressed to which this issue record is related.
SERNAME.TWN	c–Township	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.
SERNAME.WCITY	c–Business City	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.
SERNAME.WPHONE	c–Work Phone	Text	The work phone number from the constituent record associated with this issue record.
SERNAME.WRD	c–Ward	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.
SERNAME.WTDIR	c–Business Street Directio	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.
SERNAME.WTNAME	c–Business Street Name	Text	The name of the street from the work address part of the constituent record to which this issue record is related.
SERNAME.WTNUM	c–Business Street Number	Text	The street number from the work address part of the constituent record to which this issue record is related.
SERNAME.WTSUF	c–Business Street Suffix	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.
SERNAME.WZIP	c–Business Zip Code	Text	The Zip code from the work address part of the constituent record to which this issue record is related.
SERNAME.ZIP	c–Zip Code	Text	The zip code from the 'home' address from the constituent record associated with this issue record.
SERVICES.ACT	Work Order Form Printed	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.
SERVICES.ACT1	Sent Information	Text	A '1' in this field indicates that information related to this service request has been sent to the constituent.
SERVICES.ACT2	Returned Call	Text	A '1' in this field indicates that the constituent has been contacted by phone concerning this service request.
SERVICES.ACT3	Referred Request	Text	A '1' in this field indicates that the request has been referred to an outside agency.
SERVICES.ACT4	Sent Letter	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).
SERVICES.ACT5	Case is On Going	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.
SERVICES.ACT6	Action Code 6	Text	Not currently used.
SERVICES.APT	Apartment Number	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.
SERVICES.BUREAU	Bureau of Dept or Div	Text	The bureau (division) to which this service belongs
SERVICES.CDATE	Date of Request	Date	Date the service request was first entered into the system.
SERVICES.CDATE2	Date Order Sent Out	Date	Tickler date (when the service request should be reviewed again, especially helpful with weather–dependant service requests)
SERVICES.CDATE3	Date Work Started	Date	Date the work was assigned to a work crew. Assumed to be the date the work actually started.
SERVICES.CNT	Request Number	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.
SERVICES.CO	Who Took Request	Text	The employee id of the employee who originally took the service request.
SERVICES.CO2	Order Sent Out by	Text	The employee id of the employee who assigned to work order to an individual or crew.
SERVICES.CO3	Order Given to	Text	The employee id of the employee or crew leader to whom this service request has been assigned.
SERVICES.CO4	Completion Reported by	Text	The employee id of the employee who reported the service request completed.
SERVICES.CSUPOP	Support by Citizen = F–O–U	Text	With legislative issues, indicates the constituent's attitude towards the proposal F – For O – Opposed U – Undecided
SERVICES.CT	Contact Type P–I–L–O	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
SERVICES.CTIME	Time of Request	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.
SERVICES.CTIME2	Time Order Sent Out	Text	Not currently used.
SERVICES.CTIME3	Time Work Started	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.
SERVICES.CTY	User Defined (County)	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.
SERVICES.CUS	User Defined (Map Grid #)	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.
SERVICES.DEF	Service/Issue Definition	Text	The definition of the service request. (See also 'Service/Issue Code', SERVICES.ISSUE)
SERVICES.DEPTCODE	Department Code	Text	Department this request belongs to.
SERVICES.EMPCNT	# of Employees Used	Numeric	Total number of employees who worked on resolving this service request. Use of this field is deprecated – use abc_lem instead.
SERVICES.EMP_ID	Key Employee ID Number	Text	The employee id number of the key contact person for information related to this request.
SERVICES.GRP	Group	Text	The group to which this service request belongs.
SERVICES.HRS	Employee Hours Used	Numeric	Deprecated – Total number of hours spent by all employees on resolving this service request. Use ABC–LEM instead.
SERVICES.ISSUE	Service/Issue Code	Text	A code indicating the type of Service Request. (See also 'Service/Issue Definition', SERVICES.DEF).
SERVICES.ITYPE	Type of Request S–I–L–C–A	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
SERVICES.L1	State Repayment	Logical	If this field is marked 'True', then this service request should be submitted for possible reimbursement to the state.
SERVICES.L2	FEMA Repayment	Logical	If true, then this service request should be submitted to FEMA for possible reimbursement.
SERVICES.LCODE	Location Code	Text	Not currently used
SERVICES.LETDATE	Date Letter Sent	Date	The date on which the constituent was last sent updated information concerning this request.
SERVICES.LETSENT	Name of Letter Sent	Text	The name of the last letter which was sent to the constituent concerning this service request.
SERVICES.MRK	Marked by Query Select	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'.
SERVICES.NAME	User Defined (Cross Street)	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.
SERVICES.NOTE	Priority Code	Text	The priority code assigned to this service request.
SERVICES.NOTEIN	Note Comment Exists	Text	Supposed to indicate that notes exist for this service record, but not currently used.
SERVICES.NOTES	Notes Explain	Text	Public notes related to this service request.
SERVICES.NOTESIN	Notes Comment	Text	The internal (for public works personnel eyes only) notes related to this service request.
SERVICES.PCT	User Defined (Precinct)	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.
SERVICES.RDATE	Date Resolved	Date	Date the final report on the resolution of the service request was completed.
SERVICES.RESCODE	Resolution Code	Text	Contains one of four possible values: N – New P – Pending F – Forwarded C – Complete
SERVICES.RESOLVE	Resolution Tracking #	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
SERVICES.RESP	Responder	Text	Not currently used.
SERVICES.RQBY	Work Requested by	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.
SERVICES.RT	Response Type P–I–L–O	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
SERVICES.RTIME	Time Work Completed	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.
SERVICES.SDAYS	Scheduled Days to Complete	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.
SERVICES.SDIR	Street Directional	Text	The direction componant of the street address where the work is to be performed: ie. N, S, NE, etc.
SERVICES.SID	ID Number	Text	The Constituent ID number linking this service request to a constituent record.
SERVICES.SNAME	Street Name	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.
SERVICES.SNUM	House Number	Text	The street number part of the address where the work to resolve this service request is located.
SERVICES.SSUF	Street Suffix	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.
SERVICES.SUPOP	Support by Office = Y–N–U	Text	With Legislative Issues, indicates the position of the office. Y – Supports N – Opposed U – Undecided
SERVICES.T0	Type I–B–O–G	Text	Contains one of four possible values indicating the type of entity making the request. I – Individual B – Business O – Organization G – Government
SERVICES.T1	Class	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
SERVICES.T2	Odd/Even Street Number	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.
SERVICES.TCNT	How Many of This	Numeric	The total number of service requests associated with the constituent record to which this service request is related.
SERVICES.TID	User Defined (Tax ID)	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.
SERVICES.TWN	User Defined (Township)	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.
SERVICES.WDAYS	Work Days to Complete	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.
SERVICES.WHERE	Exact Location	Text	A brief description to help locate a request location.
SERVICES.WRD	Ward	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.