Dossiers

The Dossiers dataset contains data regarding records.

Importing to a BI database

Records are continuously changed. This makes it necessary to ensure during periodic runs that the latest changes are included and that obsolete data is deleted.

You should do the following when importing to a BI database:

  1. Delete rows related to changed records in the existing table. Record IDs for the changed cases can be found in the latest version of the table.

  2. Import the latest version of the table to the BI database.

Column overview

The following table describes the columns available in the dataset. The table includes the following information about each column:

  • Dataset name: The column’s name in the dataset.

  • Description: Describes the column’s content and function.

  • Reference to other dataset: Specifies references to other datasets, if any.

  • Format: Specifies the format of the returned values.

  • Length: Specifies the length of the returned values, if any.

  • Examples: Examples of returned values.

Dataset name Description Reference to other dataset Format Length Examples

InternalDossierId

The internal database ID of the record.

bigint

6932

2083

Deleted

Time and date of when the record was deleted, if relevant.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

ExternalId

The external ID of the record.

bigint

12

69

InternalCaseId

The internal database ID of the case the record is attached to.

Cases

bigint

12058

69863

AccessType

The access level of the record.

varchar

8

Involved

Unit

All

DossierType

The record type of the record.

varchar

19

DraftDossier

SendMail

F2DossierNumber

The F2 record number of the record. The number is the sequential number for registered records on a case.

int

20

9786

DocumentType

The type of record, e.g. if it was received or sent.

varchar

8

DraftDossier

ReceivedMail

SendMail

LockedDossier

NotSendViaF2

SendOutputManager

SendDigitalMail

IsBinned

Whether the record has been binned.

0 = No

1 = Yes

int

0

1

IsPrivate

Whether the record is in the "My private records" list.

0 = No

1 = Yes

int

0

1

InLibrary

Whether the record is in the "Library" list.

0 = No

1 = Yes

int

0

1

FolderDisplayTitle

The archival area of the record.

nvarchar

271

Archive

Library

Private folder: Hannah Hendricks

Binned

BCCRecieverNames

Any Xbc recipients of a record sent as an email.

nvarchar

Robert Robertson

LetterDate

The letter date of the record.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

DossierDate

The letter date of the record if one was manually added.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

CCRecieverNames

Any Cc recipients of a record sent as email.

nvarchar

Robert Robertson

CprNumber

The CPR number of the record.

nvarchar

12

0123456789

9876543210

DocumentAccess TypeEnumItemId

The ID of the access type of the record.

EnumTypeItems

int

6985

1240

OriginalTitle

The original title of an incoming email record if its record title has since been changed.

nvarchar

400

Request for more information

Regarding XYZ

KeyWords

The keywords of the record.

nvarchar

400

Recruitment

Quality Assurance

RemindDate

The remind date of the record.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

DeadLine

The record deadline.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

DocumentNumber

The ID number of the record.

bigint

6

98

Archived

Whether the record has been registered for archival.

0 = No

1 = Yes

bit

0

1

MailRecievedDate

The time and date a record was received as an email.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

CreatedByUser NameInternalPartyId

The ID of the user who created the record.

PartyRegistry

int

789

4538

CreatedDate

The time and date of when the record was created.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

CaseHelp

Whether the case help is enabled for the record.

bit

1: Enabled

0: Disabled

CaseFolder

The name of the case folder in which the record has been placed.

nvarchar

256

Appendices

Quality reporting

MailSendDate

The time and date a record was sent as an email.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM

MailsDigitallySigned

Whether the record was digitally signed when sent.

bit

FALSE

TRUE

Status

The status of the record.

varchar

10

InProgress

Closed

ReplyTo DossierDocumentNumber

The ID of the record a given record is a reply to.

Dossiers

bigint

6

98

ReplyToDossierTitle

The title of the record a given record is a reply to.

nvarchar

256

Fw: Request for more information

Regarding XYZ

PreviousCaseNumber

The previous case number of the case the record is associated with.

nvarchar

256

12/23587

15/78646

RecieverNames

The recipients of an email record.

nvarchar

Robert Robertson

Title

The title of the record.

nvarchar

256

Fw: Re: Request for more information

Regarding XYZ

VersionNumber

The number of versions of the record that exists.

int

1

6

ForwardOf DossierDocumentNumber

The ID of the record that was forwarded as the given record.

Dossiers

bigint

6

98

ForwardOfDossierTitle

The title of the record that was forwarded as the given record.

nvarchar

256

Request for more information

Regarding XYZ

AdditionalCaseworkers

Any supplementary case managers on the record.

nvarchar

4000

Admin

Robert Robertson

AdditionalUnits

Any supplementary units on the record.

nvarchar

4000

IT Office

Quality Assurance

InvolvedParties

The participants involved with the record.

nvarchar

4000

Hannah Hendricks

Manny Manson

SecurityGroups

The access restriction of the record.

nvarchar

4000

SG: Team leaders

IT Office

CaseSecurity GroupsInherited

The access restriction inherited from the record’s case.

nvarchar

4000

SG: Team leaders

IT Office

LastModified

The time and date of the latest change made to the record.

datetime

7/19/2017 10:28:12 AM

1/17/2021 12:01:45 PM