Useful Type lookups for writing SQL reports

In this guide we will cover:

- Fields associated with the IDs returned by UCFieldID, DCFieldID and ELTableID

The below list of Tables, Columns and References can be used when writing reports for tables such as Devicechange or Userchange so that you can properly label the field that was changed.

Table = UserChange  

Column = UCFieldID

Add = -1,

SiteChange = 1,

Inactive = 2,

[ProRata]

Delete = 3,

ServiceAccount = 4,

IgnoreAutomatedBilling = 5,

PopUpNotes = 6,

Username = 7,

Email = 8,

Firstname = 9,

Lastname = 10,

PhoneNumber = 11,

HomeNumber = 12,

MobileNumber = 13,

MobileNumber2 = 14,

Web_Access_Level = 15,

Asset_Access_Level = 16,

Role_Added = 17,

Role_Removed = 18,

ClientChange = 19,

Table = DeviceChange 

Column - DCFieldID

User:-1

Moved:-2

Status:-3

Assembled:-4

Used in Assembly:-5

Linked to Ticket:-6

Passed Commission:-7

Failed Commission:-8

Reset Commission:-9

Asset Tag:-11

Note:-12

Commission Date:-13

Unchanged Commission:-14

Attachment Added:-15

Attachment Removed:-16

Image Added:-17

Image Removed:-18

Status Changed:-19

Child asset linked:-20

Child asset unlinked:-21

Parent asset linked:-22

Parent asset unlinked:-23

Sibling asset linked:-24

Sibling asset unlinked:-25

Created:-50

Type Changed:-51

Software Added:-52

Software Deleted:-53

Software version updated:-54

Software install date updated:-55

Software licence changed:-56

Software licence count changed:-57

Software licence role changed:-58

Linked Asset relationship changed:-59

Inactive:-60

Teamviewer connection:-61

Business Owner:-62

Change Advice Board:-63

Technical Owner:-64

Priority:-65

Supplier:-66

Contract:-67

Supplier Priority:-68

Supplier Reference:-69

::-70

Service Linked:-71

Service Unlinked:-72

Name:-73

Status:-74

Sales Description:-75

Purchase Description:-76

Price:-77

Cost:-78

Tax Code:-79

Accounts Code:-80

Item Group:-81

Parts Warranty Start Date:-82

Parts Warranty End Date:-83

Labour Warranty Start Date:-84

Labour Warranty End Date:-85

Licence Linked:-86

Licence Unlinked:-87

Contract Linked:-88

Contract Unlinked:-89

Taxable:-90

Recurring Price:-91

Recurring Cost:-92

Not Consignable:-93

Item Project Template:-94

Do not receive on Purchase Order:-95

Budget Type:-96

Manufacturer SKU:-97

Is a Contract:-98

Don't Invoice on Sales Orders:-99

Custom Table Row Added:-100

Custom Table Row Deleted:-101

Cusotm Table Row Edited:-102

Table = ExternalLink

Column = ELTableID

   NavigationMenu = -1,

   System = 0,

    Faults = 1,

    Area = 2,

    Site = 3,

    User = 4,

    Asset = 5,

    Contract = 6,

    Action = 7,

    Item = 8,

    Supplier = 9,

    LiveChatHeader = 980,

    PurchaseOrderLines = 987,

    QuotationLines = 988,

    Invoice = 989,

    SalesOrder = 990,

    FAQList = 991,

    KB = 992,

    CRMNotes = 993,

    Attachment = 995,

    Quotation = 996,

    AssetGroup = 997,

    PurchaseOrder = 998,

    Service = 999,

    SupplierContract = 1001,

    Agent = 1002,

    Organization = 1003,

    DistributionLists = 1005,

    DistributionListsLog = 1006,

    ECommerceOrder = 1007,

    MailCampaignLog = 1008,

    MailCampaign = 1009,

    OutboundIntegrationMethod = 1010,

    MailCampaignEmail = 1011,

    Document = 1012,

    DeviceChange = 1013,

    EventManagement = 1014,

    ServiceAvailability = 1015,

    Downtime = 1016,

    Appointment = 1017,

    Lookup = 1018,

    IntegrationRequest = 1019,

    DeviceChangeBackup = 1020,

    ToDoGroups = 1021,

    ForecastDetails = 1022,

    SavedForecast = 1023