OT2 Query Reports Variables

ABC-LEM

The ability to generate custom ABC-LEM reports is a new feature of OT2. The major difference between the ABC-LEM reports and other (Constituent, Services, etc.) query reports is that the reports themselves are not as freely shared between users. They will only work for and be available to the person who created the report. To share ABC-LEM reports, you must select the report you want to share and then RIGHT CLICK the "Make a copy of the selected request" button. Type a unique name for the report (something like "User's reportname") and click the save button. A list of users will pop up. Click the name of the person you want to share the report with and press "CTRL-W" to save it to that person's list of reports. This person will then have their own copy of the report. Changes you make to the report will not affect the report used by the other person, and vice versa.

When designing reports in OT2 it is helpful to keep the philosophy behind OT2 in mind. We believe that your primary goal is to respond to the person making the request, to keep that person happy. Therefore, all of the tables in ot2 are related through the unique Constituent ID number, the SID. Here is a chart showing how LEM records are related to constituent records.

So you can see that LEM records 1-4 relate to service request 2 and constituent record 2, and have access to variables from both of these. LEM records 5-8 have access to service request 3 and constituent record 2 variables, and 9-11 have access to service request 4 and constituent record 3 variables.

Our general rule (which we have broken a couple of times) is that fields prefixed with a 'c-' are from the constituent (sername) table. Fields prefixed with an 's-' are from the services table.

Here is a list of the available fields, the associated variables, and a description of how they are used in the system:

Field	Variable	Data type	Description
Bureau Code	ABC_QM.BUREAU	Text	The Bureau (aka Division) to which the employee, equipment, or material has been assigned.
Work Classification	ABC_QM.CLASS	Text	In the case of labor records, this code holds the description of the type of labor. The choices are Idle, Gear Up, Active, Clean Up, Administrative, Training, Workman's Comp, or Other. Most records associated with particular service requests will be Active. Unused in Equipment and Materials records.
Total Cost	ABC_QM.COST	Numerical	(Rate (Cost Per))(Units) or, in overtime labor records, (Overtime Rate Labor)(Overtime Hrs if Labor)
Department Code	ABC_QM.DEPTCODE	Text	The department to which the employee, equipment, or material has been assigned
Service Code	ABC_QM.ISSUE	Text	The code for the particular service request this item was used for.
Item ID	ABC_QM.ITEM	Text	The unique ID for identifying an employee, particular piece of equipment, or material.
Labor Equipment Material	ABC_QM.LEM	Text	Identifies the record as relating to Labor (L), Equipment (E), or Material (M). L, E, and M are the only values this field should contain.
Class Code (M/L)	ABC_QM.MCLASS	Text	In the case of Materials, contains the class of the material. (I'm not sure what this represents)
Type Code (M/L)	ABC_QM.MTYPE	Text	The Type code entered for Materials and Equipment. Not used (should always be blank) for labor records.
M_RATE	ABC_QM.M_RATE	Numerical	I have no idea what this field is.
Overtime Hrs if Labor	ABC_QM.OT_HOURS	Numerical	In the case of labor records, signifies the number of overtime hours worked by the employee. Equipment and Material records should always contain 0 in this field.
Overtime Rate if Labor	ABC_QM.OT_RATE	Numerical	In the case of labor records, contains the rate at which the employee was being paid for overtime at the time the work was being done.
Rate (Cost Per)	ABC_QM.RATE	Numerical	In the case of Labor records, the rate at which the employee was being paid for regular time hours at the time the work was being done. In Equipment records, the cost of using the equipment for an hour. in Material records, the cost per unit of the material
Completion Date	ABC_QM.RDATE	Date	The date on which this particular work was performed – not the completion date of the service
Constituent ID	ABC_QM.SID	Text	The unique constituent record identification code. This number is how records are related to a particular constituent record.
Unit of Measurement	ABC_QM.UM	Text	The units by which the units are measured (ex: feet, inch, cuft, lbs, hrs, each).
Units	ABC_QM.UNITS	Numerical	In the case of materials, the number of units used. In Labor and Equipment records, the number of (usually) hours used.
Work From (Date–Time)	ABC_QM.WFROM	Date and Time	You can choose to have the exact start and finish time (rather than just the number of hours) entered into Labor and Equipment records by placing a file called 'ts_time.txt' in the root directory of the server installation. If this option is used, this field will contain the exact time work began on this record.
Work To (Date–Time)	ABC_QM.WTO	Date and Time	You can choose to have the exact start and finish time (rather than just the number of hours) entered into Labor and Equipment records by placing a file called 'ts_time.txt' in the root directory of the server installation. If this option is used, this field will contain the exact time work ended on this record.
c–Apartment	SERNAME.APT	Text	The apartment number from a constituent's home address.
c–Business Suite	SERNAME.BCADRSZ	Text	The Suite number from the work address part of the constituent record to which this LEM record is related.
c–Business Name	SERNAME.BCNAME	Text	The name from the work address part of the constituent record.
c–Universal Direction	SERNAME.CDIR	Text	If this is an 'Individual' constituent record, contains the street direction from the 'home' address, otherwise contains the street direction from the work address.
c–City	SERNAME.CITY	Text	The city from the home address part of the constituent record to which this LEM record is related.
c–Universal Name	SERNAME.CNAME	Text	If this is an 'Individual' constituent record, contains the street name from the 'home' address of the constituent record associated with this LEM record. Otherwise contains the street name from the 'work' address.
c–Universal House Number	SERNAME.CNUM	Text	If this is an 'Individual' constituent record, contains the house number from the 'home' address of the constituent record associated with this LEM record. Otherwise contains the house number from the 'work' address. This is a character field and so will be sorted alphabetically (12 comes before 2 when sorting alphabetically).
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 LEM record.
c–Area	SERNAME.CODE2	Text	Code–2 from the constituent record
c–Garbage Zone	SERNAME.CODE3	Text	Code–3 (default is garbage code) from the constituent record to which this LEM record is related.
c–Car/Mobile Phone	SERNAME.CPHONE	Text	The car/mobile phone number from the constituent record to which this LEM record is related.
c–County	SERNAME.CTY	Text	The county code associated with the constituent record to which this LEM record is related.
c–Pin Number	SERNAME.CUS	Text	This user defined field is located right above the ID field on the constituent screen.
c–Fax	SERNAME.FPHONE	Text	The fax number from the constituent record to which this LEM record is related.
c–Home Phone	SERNAME.HPHONE	Text	The home phone number from the constituent record to which this LEM record is related.
c–Type I–B–O–G	SERNAME.IBCO	Text	Contains a single character determining the type of constituent record this is: I–Individual B–Business G–Government O–Organization
c–Last Name	SERNAME.LNAME	Text	The last name of the individual from the constituent record to which this LEM 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 LEM 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 this record 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.
n–Universal House Number	SERNAME.NNUM	Numerical	In the case of an 'Individual' type constituent record, holds the house number from the 'home' address of the constituent record associated with this LEM record. Otherwise contains the house number from the 'work' address. This is a numeric field, so it will sort in numerical order.
c–Precinct	SERNAME.PCT	Text	The lowest of the four political information fields on the constituent screen, this field was designed to hold the precinct code of the precinct where the constituent associated with this LEM record is located.
c–Pager	SERNAME.PPHONE	Text	The pager number from the constituent record to which this LEM record is related.
c–Number of Cases	SERNAME.SCNT	Text	The total number of service requests associated with the constituent record to which this LEM record is related. This value is not currently stable and not to be relied on.
c–Sex	SERNAME.SEX	Text	Designed to hold the gender of the contact individual from the constituent record associated with this LEM record. However, I see nowhere to enter that information, and for the most part the field appears to be empty.
ID Number	SERNAME.SID	Text	The unique constituent ID, this time from the constituent (sername) table. Should be the same as Constituent ID (ABC_xx.SID)
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 LEM 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 LEM record is related.
c–Street Number	SERNAME.STNUM	Text	The street number from the home address part of the constituent record to which this LEM record is related.
c–Street Suffix (Ave)	SERNAME.STSUF	Text	The street suffix (ie. ST, WAY, DR, etc.) from the home address part of the constituent record to which this LEM 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 this is an 'Individual' constituent record or the work street address if this is a 'business' constituent record. 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–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–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–H2O Billing Number	SERNAME.TID	Text	TID (default is water billing number) from the constituent record to which this LEM record is related.
c–Contact Name	SERNAME.TITLE	Text	The formal name by which the constituent is to be addressed to which this LEM 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 is located.
c–Business City	SERNAME.WCITY	Text	The city from the work address part of the constituent record to which this LEM record is related.
c–Work Phone	SERNAME.WPHONE	Text	The work phone number from the constituent record associated with this LEM record.
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 LEM record is located.
c–Business Street Directio	SERNAME.WTDIR	Text	The street direction (ex: N, S, NW, etc.) from the work address part of the constituent record to which this LEM record is related.
c–Business Street Name	SERNAME.WTNAME	Text	The name of the street from the work address part of the constituent record to which this LEM 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 LEM 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 LEM 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 LEM record is related.
c–Zip Code	SERNAME.ZIP	Text	The zip code from the 'home' address from the constituent record associated with this LEM record.
s–Work Order Form Printed	SERVICES.ACT	Text	A '1' in this field indicates that the service request which is associated with this LEM record has been printed. Additional printouts of this service request will have the word 'COPY' marked on them.
s–Sent Information	SERVICES.ACT1	Text	'1' in this field indicates additional literature was sent to the constituent about the service request associated with this LEM record.
s–Returned Call	SERVICES.ACT2	Text	A '1' in this field indicates that the constituent associated with the service request this LEM record is associated with was called.
s–Referred Request	SERVICES.ACT3	Text	A '1' in this field indicated that the service request associated with this LEM record was referred. A '0' indicates that it wasn't.
s–Sent Letter	SERVICES.ACT4	Text	A '1' in this field indicates that a letter regarding the service request associated with this LEM record was sent to the constituent associated with the service request.
s–Case is On Going	SERVICES.ACT5	Text	Contains '1' if the service request associated with this LEM record is marked as ongoing. '0' otherwise.
s–Action Code 6	SERVICES.ACT6	Text	Does not appear to be being used currently.
s–Apartment Number	SERVICES.APT	Text	The apartment field from the service request this LEM record is associated with.
s–Bureau of Dept or Div	SERVICES.BUREAU	Text	The Burea (or Division) under which the service request associated with this LEM record falls.
s–Date of Request	SERVICES.CDATE	Date	The date on which the service request associated with this LEM record was received by the public works department.
s–Date Order Sent Out	SERVICES.CDATE2	Date	The 'Tickler' date from the service request associated with this LEM record. The 'Tickler' date was designed to be used so that if a project was unfeasable at the moment you can be reminded when the time comes to complete the project. It's more like a 'Date work is scheduled to start' field.
s–Date Work Started	SERVICES.CDATE3	Date	The date on which the work order for the service request associated with this LEM record was assigned to a work crew.
s–Request Number	SERVICES.CNT	Text	A unique number used to order the service requests associated with a particular constituent record. The Constituent ID combined with the request number creates the work order number of the service request associated with this LEM record.
s–Who Took Request	SERVICES.CO	Text	The ID# of the employee who took the request associated to this LEM record.
s–Order Sent Out by	SERVICES.CO2	Text	The code of the employee who assigned the service request associated with this LEM record to a crew leader.
s–Order Given to	SERVICES.CO3	Text	The code of the crew leader to whome the job of completing the service request associated with this LEM record was assigned.
s–Completion Reported by	SERVICES.CO4	Text	The ID number of the employee who completed the report on the service request associated with this LEM record.
s–Support by Citizen = F–O	SERVICES.CSUPOP	Text	In the case of a Legislative record, tells whether the constituent was F–for the legislation or O–opposed. This will not usually apply to service requests associated with LEM records.
s–Contact Type P–I–L–O	SERVICES.CT	Text	The method by which the service request came in: P–Phone M–Mail W–Web(Internet) I–In Person R–Radio O–Other S–Site V–Vmail
s–Time of Request	SERVICES.CTIME	Text	The time at which the service request associated with this LEM record was received by the public works department in the format A\|Phhmm.
s–Time Order Sent Out	SERVICES.CTIME2	Text	Not currently used.
s–Time Work Started	SERVICES.CTIME3	Text	The time at which the work order for the service request associated with this LEM record was assigned to a work crew in the format A\|Phhmm.
s–Garbage	SERVICES.CTY	Text	A three character field used to capture information on the services screen. Contains the city code from the constituent record the service request associated with this LEM request is associated with.
s–PIN	SERVICES.CUS	Text	The PIN number from the constituent record the service request associated with this LEM record is associated with.
s–Service/Issue Definition	SERVICES.DEF	Text	The definition of the service code of the service request associated with this LEM record.
s–Department Code	SERVICES.DEPTCODE	Text	The Department code of the department under which the service request associated with this LEM record falls.
s–# of Employees Used	SERVICES.EMPCNT	Numerical	Number of employees reported to have worked on this request. The use of this field is now depricated with the new ABC–LEM system.
s–Key Employee ID Number	SERVICES.EMP_ID	Text	The ID number of the key employee working on the service request associated with this LEM record.
s–Group	SERVICES.GRP	Text	If the service request associated with this LEM record belongs to a subgroup of a division of a department, then this field contains the subgroup code. There is currently no user interface for adding groups to divisions, so this field is used rarely.
s–Employee Hours Used	SERVICES.HRS	Numerical	Total number of employee hours used to complete the service request. The use of this field is deprecated.
s–Service/Issue Code	SERVICES.ISSUE	Text	The issue code of the service request associated with this LEM record.
s–Type of Request S–I–L–C–	SERVICES.ITYPE	Text	The type of issue of the request associated with this LEM record. Usually all records associated with LEM records will be of type S–Service. S–Service Request L–Response to Legislation I–Response to Public Issue C–General Communication A–Permit or Application E–Event
s–State Repayment	SERVICES.L1	True/False	'T' in this field means that all LEM records (including this one) associated with this service request should be submitted to the state for (partial) reimbursement. An F in this field means that LEM records related to the service request are not eligible for state reimbursement.
s–FEMA Repayment	SERVICES.L2	True/False	Indicates whether the cost of completing the service request associated with this LEM record should be billed to FEMA or not. A 'T' indicates that the request is re–imbursable, an 'F' indicates that it isn't.
s–Location Code	SERVICES.LCODE	Text	A four character field from the service request associated with this LEM record. Does not appear to be implemented.
s–Date Letter Sent	SERVICES.LETDATE	Date	This field holds the date a letter was sent to the constituent associated with this LEM record regarding the service request associated with this LEM record.
s–Name of Letter Sent	SERVICES.LETSENT	Text	The name of a standard letter regarding the service request associate with this LEM record was sent to the constituent. Assumed to be the name of the letter sent on the s–Date Letter Sent (SERVICES.LETDATE) date.
s–Marked by Query Select	SERVICES.MRK	Text	Any value entered into this field indicates that the service request associated with this LEM record has been marked by a query select statement.
s–Cross Street	SERVICES.NAME	Text	This contains the cross street the service request associated with this service request was located on if it is not on the main street.
s–Priority Code	SERVICES.NOTE	Text	Holds a value from one to 9 indicating the priority of the service request associated with this LEM record.
s–Note Comment Exists	SERVICES.NOTEIN	Text	I don't know what this field is used for. Appears to be unimplemented.
s–Notes Explain	SERVICES.NOTES	M	Detailed notes about the service request this LEM record is associated with.
s–Notes Comment	SERVICES.NOTESIN	M	Detailed notes for internal use only about the service request this LEM record is associated with.
s–Precinct	SERVICES.PCT	Text	The Precinct code from the constituent record associated with the service request associated with this LEM record.
s–Date Resolved	SERVICES.RDATE	Date	The date on which the work on the service request associated with this LEM record was reported complete.
s–Resolution Code	SERVICES.RESCODE	Text	Contains four possible values indicating the present status of the service request this LEM record is associated with. N–New P–Pending F–Forwarded C–Complete
s–Resolution Tracking #	SERVICES.RESOLVE	Text	Contains a number from one to nine indicating the status of the resolution of the service request this LEM record is associated with. Unless they have been redefined, these status codes are: 1 – New Entry 2 – Referred to Another Dept/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
s–Responder	SERVICES.RESP	Text	Does not appear to currently be implemented.
s–Work Requested by	SERVICES.RQBY	Text	Who made the service request associated with this LEM record.
s–Response Type P–I–L–O	SERVICES.RT	Text	The method by which the constituent associated with the service request associated with this LEM record was responded to: P–Phone M–Mail W–Web(Internet) I–In Person R–Radio O–Other S–Site V–Vmail
s–Time Work Completed	SERVICES.RTIME	Text	The time at which the work on the service request associated with this LEM record was reported complete in the format A\|Phhmm.
s–Scheduled Days to Comple	SERVICES.SDAYS	Numerical	The average number of days required to complete the service request associated with this LEM record.
s–Street Directional	SERVICES.SDIR	Text	The street direction (ie. N, S, NW, etc.) of the location where work is to be performed on the service request associated with this LEM record.
s–ID Number	SERVICES.SID	Text	The unique constituent ID of the constituent record to which the service request associate with this LEM record is associated. This is the main associative field, so the SID number will be the same no matter which record it is taken from. Equivalent to Constituent ID (from the LEM record) and ID Number (from the constituent record)
s–Street Name	SERVICES.SNAME	Text	The name of the street where work is to be performed on the service request related to this LEM record.
s–House Number	SERVICES.SNUM	Text	The house number where the work is to be performed for this service request. This is a text field so these will be sorted alphabetically (12 comes before 2).
s–Street Suffix	SERVICES.SSUF	Text	The suffix of the street where work is to be performed on the service request associated with this LEM record.
s–Support by Office = Y–N–	SERVICES.SUPOP	Text	In the case of legislative issues, tells whether the office 'Y'–supports the legislation or 'N'–opposes the legislation. This field will not normally apply to service requests associated with LEM records.
s–Type I–B–O–G	SERVICES.T0	Text	The type of constituent record that the service request record related to this LEM record is related to. I–Individual B–Business G–Government O–Organization
s–Class	SERVICES.T1	Text	Classification (such as A–Administrative) assigned to the service request associated with this LEM record by the operator who keyed it in.
s–Odd/Even Street Number	SERVICES.T2	Text	Contains 'O' if the work on the service request associated with this LEM record is to be performed at an odd–numbered address, 'E' if it is to be performed at an even numbered address.
s–How Many of This	SERVICES.TCNT	Numerical	Does not appear to be being used currently.
s–H20	SERVICES.TID	Text	The tax id number of the constituent associated with this LEM record is also stored in each service request record.
s–Township	SERVICES.TWN	Text	The township code from the constituent record to which the service request related to this LEM record is related.
s–Work Days to Complete	SERVICES.WDAYS	Numerical	The number of days between when the service request associated with this LEM record was first entered into the system and when the work was reported complete.
s–Location	SERVICES.WHERE	Text	More detailed information about the location of the service request associated with this LEM record. (ie. "Around back", "Behind the bushes", "Next to the mailbox", etc.)
s–Ward	SERVICES.WRD	Text	The ward code from the constituent record to which the issue record related to this LEM record is related.