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. |
| 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) |
| 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. |
| Department Code | ABC_QM.DEPTCODE | Text | The department to which the employee, equipment, or material has been assigned |
| 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) |
| 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. |
| 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 |
| Service Code | ABC_QM.ISSUE | Text | The code for the particular service request this item was used for. |
| Total Cost | ABC_QM.COST | Numerical | (Rate (Cost Per))*(Units) or, in overtime labor records, (Overtime Rate Labor)*(Overtime Hrs if Labor) |
| 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. |
| 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 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. |
| 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–Area | SERNAME.CODE2 | Text | Code–2 from the constituent record |
| 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–Business Name | SERNAME.BCNAME | Text | The name from the work address part of the constituent record. |
| c–Business Street Direction | 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 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 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–Car/Mobile Phone | SERNAME.CPHONE | Text | The car/mobile phone number from the constituent record to which this LEM record is related. |
| c–City | SERNAME.CITY | Text | The city from the home address part of 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–County | SERNAME.CTY | Text | The county code associated with the constituent record to which this LEM record is related. |
| c–Fax | SERNAME.FPHONE | Text | The fax number from the constituent record to which this LEM record is related. |
| c–Garbage Zone | SERNAME.CODE3 | Text | Code–3 (default is garbage code) from the constituent record to which this LEM record is related. |
| 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–Home Phone | SERNAME.HPHONE | Text | The home phone number from the constituent record to which this LEM record is related. |
| 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. |
| 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 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–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–Pager | SERNAME.PPHONE | Text | The pager number from 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–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–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. |
| 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–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–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–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–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–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–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–Work Phone | SERNAME.WPHONE | Text | The work phone number from the constituent record associated with this LEM record. |
| c–Zip Code | SERNAME.ZIP | Text | The zip code from the 'home' address from the constituent record associated with this LEM 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 LEM record. |
| 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. |
| 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–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–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–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–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–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–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–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–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 Resolved | SERVICES.RDATE | Date | The date on which the work on the service request associated with this LEM record was reported complete. |
| 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–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–Department Code | SERVICES.DEPTCODE | Text | The Department code of the department under which the service request associated with this LEM record falls. |
| 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–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–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–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–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–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–How Many of This | SERVICES.TCNT | Numerical | Does not appear to be being used currently. |
| 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–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–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–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–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–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–Note Comment Exists | SERVICES.NOTEIN | Text | I don't know what this field is used for. Appears to be unimplemented. |
| s–Notes Comment | SERVICES.NOTESIN | M | Detailed notes for internal use only about the service request this LEM record is associated with. |
| s–Notes Explain | SERVICES.NOTES | M | Detailed notes about the service request this LEM record is associated with. |
| 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–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–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–PIN | SERVICES.CUS | Text | The PIN number from the constituent record the service request associated with 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–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–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–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–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–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–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–Scheduled Days to Complete | SERVICES.SDAYS | Numerical | The average number of days required to complete the service request associated with this LEM record. |
| 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–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–Service/Issue Code | SERVICES.ISSUE | Text | The issue code of the service request associated with this LEM record. |
| s–Service/Issue Definition | SERVICES.DEF | Text | The definition of the service code of the service request associated with this LEM record. |
| 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–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–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–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 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–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–Time Order Sent Out | SERVICES.CTIME2 | Text | Not currently used. |
| 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–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–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–Township | SERVICES.TWN | Text | The township code from the constituent record to which the service request related to this LEM record is related. |
| 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–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–Ward | SERVICES.WRD | Text | The ward code from the constituent record to which the issue record related to this LEM record is related. |
| s–Who Took Request | SERVICES.CO | Text | The ID# of the employee who took the request associated to this LEM record. |
| 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–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–Work Requested by | SERVICES.RQBY | Text | Who made the service request associated with this LEM record. |