Table name

Type

Rows @ May 09

PK

Description

cActionTaken

LO

11

nCode

Used on Post Decision Contact Screen for field Action taken.

cActionTakenLink

CD

37457

None

Holds data for Action taken for each complaint. There may be multiple entries or no entries at all.

cCode link to ccode in cActionTaken

cAddress

CD

203872

cRefNo, nPriority

Holds all address details for every complaint. There must be at least one record held in this table for all complaints, but there may be more. Therefore, care must be taken so that complaints are only counted once if this table is used.

nPriority = 1 distinguishes the main address details, shown on the Registered complaint screen from additional address details.

cAddressType

LO

15

nCode

Already in COIN. Used on Additional Address screen to indicate the relationship between the complainant and the additional address.

Lookup for cAddress

cAgeRange

LC

7 (7)

nCode

Already in COIN. Equal Ops form. Listing of fields is shown below.

cAudit

CD

8738809

None

Holds information on who did what updates when.

cAuthCatInvalidList

LO

97

Is in use

Joins to authority and category to check validity of combination

cBFSchedule

CD

1441

cRefNo, nDateDescID, cSubTeam

Holds all BFs The field nDateDescID looks up cDateDescBF for the description of the BF.

Fields: Refno, nDateDescID, cPrimeUser, dBFDate, cSubTeam.

cCategory

LC

LP

60

cCode

Already in COIN. Registered Complaint form. Holds data for the fields First Category and second category.

Fields: cCode, cDescription

cComebackBasis

LO

10

nCode

CAU/OCD. Holds drop down list for Basis of Comeback. Linked to cComeBackBasisLink.ncode

Fields: nCode, cDescription

cComebackBasisLink

CD

12723

None

CAU/OCD. Holds basis of comeback information.

Fields: cRefno, nCode, dDate

cComebackType

LC

10

nCode

CAU/OCD. Holds drop down list for Type of Comeback

Fields: nCode, cDescription

cComplainantDetails

CD

189051

cRefNo

Holds all the major information for each complaint.

Separate listing of fields in Excel spreadsheet.

cCouncilBF

LO

33370

NDateDescID, cAuthorityID

BFs

Holds the default BF duration for each Council for each BF which is used when setting the BF.

Field: nDateDescID joins to cDateDescBF, cAuthorityID joins to cComplaintdetails.cAuthorityID

cCrossReferred

CD

33.077 (25,414)

None

Holds data for Cross referred complaints for joins to cComplaintdetails crefno.

Fields: cXRefno1, cXRefno2

cDateDescBF

LO

21

nDateDescID

Holds the description for each BF in the BF schedule. The field cDateSQLFieldname does not appear to be used. Used by cCouncilBF and cBFSchedule

Fields: nDateDescID, cDateFieldName, cDateSQLFieldname, CDescription

cDateDescriptions

LO

21

None

Holds a description for every date field for use in last action field. This table is used by Comtrac even though it seems to duplicate data in the previous table cDateDescBF as they hold identical id and datefieldname data. .

Fields: cDateFieldName, nDateDescID

cDecisionRoute

LC

4

cCode

Decision form. Holds drop down list for Decision Route cComplaintdetails.cdecisionroute

Fields : cCode, cDescription

cDisabilityCode

LC

3

cCode

Already in COIN. Equal Ops form. Holds drop down list for `Disabled?'

Fields: cCode, cDescription

cDistress

LC

4

nCode

Lookup for cComplaintdetails.ndistresscode

cEthnicLanguage

LC

14

cCode

Already in COIN. Add Details form. Holds drop down list for Language.

Fields: cCode, cDescription

cEthnicOrigin

LC

20

cCode

Already in COIN. Equal Ops form. Holds drop down list for Ethnic Origin.

Fields: cCode, cDescription

cFinalOutcome

LC

38

cCode

CAU/OCD cComplaintDetails.cPDCOutcomeID = cFinalOutcome.cCode

cGender

LC

4

cCode

Already in COIN. Equal Ops form. Holds drop down list for Sex. cGender.cCode = cComplaintDetails.cSexID

Fields: cCode, cDescription

cInformationSource

LO

18

cCode

Already in COIN. Equal Ops form on Comtrac but on different page in COIN. Holds drop down list for How did you hear about the LGO.

Fields: cCode, cDescription

cInfoSourceUserLink

CD

132,797

None

Equal Ops form on Comtrac but on different page in COIN

cInvestigator

LC

LO

462

cCode

List of investigator initials for use by cComplaintdetails and cInvHistory. Do not delete entries

cInvHistory

CD

27838

None

If a complaint has been reallocated there will be a record in this table with the investigator

cKeywords

LO

1069

cCode

List of keywords and code. Lookup for cKeywordsLink

cKeywordsLink

CD

443318

None

At least one record per complaint where a decision has been entered. See knowledge management in COIN2 for how to handle

cLinked

CD

1,356

None

cLocalAuthContact

LO

1,617

cCode varchar(5)

Already in COIN. Lookup to cLocalAuthority.

cLocalAuthority

LC

LP

1,733

cCode varchar(5)

Already in COIN but Comtrac has Early adopter flag

cLocalAuthorityType

LO

39

cCode

Already in COIN. Lookup to cLocalAuthority

cPurposeOfContact

LO

11

nCode smallint 2

Lookup for cPurposeOfContactLink

cPurposeOfContactLink

CD

50567

None

CAU/OCD lookup on refno

cRefno, nCode, dDate

cRemedy

LO

10

nCode smallint 2

Lookup for cRemedyLink

cRemedyLink

CD

33248

None

LAInfor remedy

nCode, cRefno, dAchieved

cReportStatus

LC

7

None

RRP/Final screen

cCode varchar(2)

cRoles

M

416

None

cRoleType

M

8

None

Lookup for cRoles.nPermission

cStatus

LC

5

cCode c1

Lookup for cComplaintdetails.cFileStatusID

cSubTeam

LO

28

cCode c2

Lookup to cLocalAuthority and cBFSchedule

cTerminationReason

LC

39

cCode varchar(2)

Used for cComplaintdetails Primarydecisionid, Seconddecisionid, Thirddecisionid

cUsers

M

364

cUserid varchar(4)

cUserSubTeams

M

435

cUserid varchar(4)

cSubteam

Valid users per subteam

Fileman_track

M

242933

cRefno

Tracking table for file management

AuthorityBFs

CDO

1088

None

Unchanging number of rows, probably copy of cCouncilBF taken a long time ago. Lists BFs for Local Authority in cComplaintDetails

cAdhocClauses

AD

26 (26)

clauseID

Holds details of joins between tables.

cAdhocFieldsTest

AD

116 (116)

fieldID

Holds the field lists that are displayed on the Adhoc screen, which tables they relate to and how they are related.

cAdhocJoinsTest

AD

25 (25)

joinID

cAdhocSavedQueries

AD

797 (287)

nCode, cUserID

cCategoryRange

LC

LP

104 (104)

cLocalAuthType,,nCategoryID

This table was intended to limit the choice of Category depending on the Local Authority Type. However, it doesn't appear to be working within Comtrac - Deltascheme checked and it is UNUSED. It would be possible to run reports to check that Data entry was consistent with these limitations.

Fields: cLocalAuthType, nCategoryID

cCorrespondenceType

L &

8 (8)

cCode

Does not appear to be used at the moment. Taken directly from Registry.

Fields: cCode, cDescription.

cCounty

D

100 (93)

cCounty

Registered Complaint and Add Address form. Holds drop down list for County field.

Fields: cCounty

cDisabilityDesc

LO

7

cCode

Equal Ops form. Holds drop down list for Disability

Fields: cCode, cDescription

cDisabilityLink

CD

12,151

None

Equal Ops form. Holds the data on Disability for each complaint as appropriate.

Fields:

Link from Complainant Details - Left Outer Join.

cLabels

E

5,617

None

cLocks

M

13

cTablename, cUserid

cLookUpList

M

52

None

Drives Admin screen drop down. Filtering in place for cLocalAuthority and cKeywords

cMdf

T

0

cPath, cName

PROBABLY DEV TABLE

cOtherOrg

LP

1

nCode i4

cPCMedium

LP

3

cCode tinyint

Lookup to P.cContactMethod

cPreComplaintDetails

CP

51,459

cPreCompRef

cScreenOrder

AD

8

cScreenName

Order screens are listed in Ad hoc query

cSource

LP

3

nCode smallint 2

Lookup for P.cLeafletSent

cStandardDocs

LC

12

nCode tinyint

Drop down, Comtrac keeps no record of their use

cSubTeamGroup

0

Not in use.

cTeam

LO

0

Not in use. Lookup to cLocalAuthority

cTitle

LP

54

cTitle varchar(20)

Also has relationship to cGender

cYearPrefix

M

2

Year for complaint and precomplaint references. Must be reset at year end (end of April)

SEQ_RegisteredComplaints

I

1

Next number

SEQ_PreComplaints

I

1

Next number