Tables Used by DataMajor


Appendix A - Tables and Fields

When you use ODBC (or want to write your own SQL) you will need to know what data is available. This document lists all the tables and fields that you can use and report on.

Tables_in_premvet
analysis Analysis Codes and descriptions.
agroup Groups the analysis codes are in.
animal Animal details
client Client details
clinana Clinical breakdown by analysis code.
clinical Clinical records
created Date the snapshot was created.
diary Appointment Details.
notes Warning and Notes Usage
lab Lab results (from screen 1).
recall Recall details
users Users Named and Groups
stock Stock Descriptions
stkhist Stock History File
medication Repeat Prescription Records
sheets Client and Animal Sheets
dates Various Date related details.
conlog Consultation Times
waitlog Waiting Room Statistics
address Address Book
history Condensed History
diary Diary Usage
attachments Attachments / Letters


animal - Animal Details

Field Description
SiteAnimal Unique identifier for multiple sites.
ClientKey An internally generated client number. This number is NOT related to any field within Premvet.
PVID Premvet Animal number
AnimalName Name of the animal/horse.
Species Species (EQ records will be set to 'Equine', FC records will be set to 'Farm')
Breed Breed (FC records this is Map Reference)
Sex Sex (FC records this is Herd Type)
Neutered Has it been neutered (Yes or No)
ORT 'Old' Record type. SA = Small Animal, FC = Farm Client, EQ = Equine Record
DateOfBirth Date of Birth
Age Age of animal in years
IDNumber ID number (or tattoo number, Herd Reference)
Colour Colour
Weight Weight of the animal (Null for Equine records).
LastWeightDate Date the last time the Weight was updated via AP code)
Height Height (Equine records only).
Insured Is the animal insured Yes or No.
InsuredWith Who is it insured with?
TBU Is the record card to be updated (New animal via diary).
Classification Classification
Ref1 Ref 1 field on Additional screen
Date1 Date 1 field on Additional screen
FNote Farm Record - Notes
Class Classification as a number
RefPrac Referral Practice (as a Number - See Abid in address book).
ReferredBy Referred by
PreferredUser User the client prefers.
AddedBy User who added the record.
LoyaltyPoints Number of Loyalty point accumulated.
ChronicStatus The Chronic status (if any) this animal is tagged with.
Mileage Mileage on record card.
SurgeryNumber Surgery Number the animal usually attends.
Status Status codes on the card.
Dead Is the animal Alive or Dead.
DateofDeath Date the animal died.
IsClient Is the animal still valid (Yes / No) - Uses 'Moved Away' 'No Longer Client' Status Code
IsClientDate If no longer a client what was last transaction Date.
ESDAmount The discount amount the animal is offerred for Early Settlement Discount.
ESDType What does the ESD Amount related to P=percentage, A=Absolute.
SurcAmount The surcharge amount related to this animal.
SurcType Is the Surcharge a Percentage or Asolute amount.
SDDAmount Does any Special Drug Discount apply to this animal.
SDDType Is the SDD a Percentage or Absolute amount.
TOSAmount Any Time of Sale discount/surcharge in force.
TOSType Is the TOS amount a Percentage or Absolute amount.
TOSFees If on split TOS discounts this is the Fees amount.
TOSDrugs If on split TOS discounts this is the drugs amount.
DrugsAtCost Yes or No - does the animal receive drugs at cost?
DrugsNoVAT Yes or No - Does the animal receive drugs at 0% VAT (Charity)
SendBill What is the 'Send Bill Flag' set to (Yes or No).
BillType What invoice type does the animal receive.
FirstRegisteredDate Month and Year the animal was first registered.
LastConsDate Full date when a consultation last took place.
LastPaidDate Full date when a payment last took place.
AnimalBalance The amount outstanding for this animal.
AnimalBFAmount The Balance at the time the records were included in the clinical table.
Locked Does the animal have an Admin lock applied.
IsMaster Is this the Master record for the client (Yes/No).
Archive Has the animal been marked for archive (0-9, 0=No, 1=Status, 2=Age, 3=Last Seen etc.).
CS_xxxx Custom sheet. All fields will be prefixed with CS_
MR_Flag_X Management Flag X (1-11) either Yes or No.


client - Client Details

Field Description
SiteRegistered Surgery number the client is register at.
ClientKey Unique DM number relating to the client.
MasterRec The Premvet animal number for the Master record.
Surname Clients surname.
Title Clients title.
Initials Initials
AddressLine1 Address Details
AddressLine2
AddressLine3
Postcode
Telephone Telephone Number
EMail E-Mail address
Marketing Marketing Tag for the client.
NumberOfAnimals Number of animals this client has.
ActiveAnimals PDSA only - Number of active animals.
BadDebt Yes or No - Does the client have any animals with status 'BD'.
IsClient Yes or No - This is set to NO if ANY of the animals belonging to this client are marked with the 'Moved Away' status codes.
ClientBalance The amount the CLIENT has outstanding.
SpendY5 These fields relate to how much the client has spent in each year. DataMajor will use full calendar years. So SpendY5 is the 5th full year, that is in 2002 using the default 5 years will be spend in 1997, SpendY3 would be 1999, SpendY1 will be 2001. The SpendY field related to the CURRENT year e.g. everything since the 1st of Jan.
SpendY4
SpendY3
SpendY2
SpendY1
SpendY
VisitY5 Similar to Spend above, this relates to the number of transaction within the period.
VisitY4
VisitY3
VisitY2
VisitY1
VisitY
FirstRegistered Month and Year the client registered with you.
LastVisit Month and Year of the last visit.
LastPaid Month and Year of the last payment.
Mobile_Number Mobile number
Fax_Number Fax number
Telephone_xx Other telephone numnbers, where xx is 2 to 9 e.g. Telephone_8 is the eighth number.

Field Center PetAid Description
The following fields are only available with PDSA version.
PDSACategory Yes Yes Category of registration (Number).
PDSAWhat Yes Yes Description of the category.
UseData Yes Yes Yes/No can the data be used for mailings.
Period Yes Yes Period the data is valid for
UserItem1 Yes No User ID for Item 1
UserItem2 Yes No User ID for item 2
DateItem1 Yes No Date Item 1 was produced.
DateItem2 Yes No Date Item 2 was produced.
DetailsItem1 Yes No Item 1 details
DetailsItem2 Yes No Item 2 details.
Agent Yes Yes Agent
NatIns No Yes National Insurance Number
RegType No Yes smallint(2) unsigned
Date_Issued No Yes Date
User_Issued No Yes char(3)
Forms_Issued No Yes date
User_Forms No Yes char(3)
Forms_Returned No Yes date
User_Returned No Yes char(3)
Forms_NotR No Yes date
User_NotR No Yes char(3)
Reg_Approved No Yes date
User_Approved No Yes char(3)
Reg_Fees No Yes date
User_RegFee No Yes char(3)
Start_Date No Yes date
End_Date No Yes date
SAC_Reject No Yes smallint(1) unsigned
Resubmitted No Yes enum('No','Yes')


clinana - Clinical Breakdown by Group

Field Description
SiteRegistered Site the animal is registered at.
ClientKey Unique DM client record.
PVID Premvet animal number
AGN003 Multiple lines (one line per group configured) indicating if the group has occurred in period of group scan. This period is the last full calendar year and from 1st Jan to snapshot date.
Format is AGN followed by group number.
AGN006
AGN009
......


Diary - Appointment Records

Field Description
pvid Animal Number
Queue Queue the appointment was made for (as a number)
Apptdate Date and Time of the Appointment
Appttime Time of Appointment
ApptLength Length
QueueName Name of the Queue the appointment was made for.
Reason Sort reason for visit
WithWhom Who is the appointment for?
MadeBy Who made it.
MadeWhen When was it made
ChangedBy Who changed it
ChangedWhen When was it changed
Status Status
Arrived Time Arrived
LongReason Box entries
This table only applies to Diary Plus, you also need to build the tables via the Utility Menu option.


lab - Lab Record

Field Description
SiteRegistered Site the animal is registered at
ClientKey2 Unique DataMajor number for the client
PVID Premvet animal number
TestType The description of the test.
TestDone The date the test was carried out.
TestResult The result of the test
TestLower The lower limit for the test at the time the test was carried out
TestUpper The upper limit for this test at the time of the test
LabSpecies What species is the animal
AgeTestDone The age the animal was at the time of the test
LabMYDone Month and Year the test was carried out
LabUnit What units does the test return

history - Condensed History (Page 2)

Field Description
SiteRegistered Site the animal is registered at
ClientKey2 Unique DataMajor number for the client
PVID Premvet animal number
User_Id User ID (Who entered the details).
HistoryDate The date the test was carried out.
SurgeryThe surgery (Number) where the work was entered
History_Text Details


notes - Notes and Warnings

Field Description
ClientKey Unique DataMajor number for the client
PVID Premvet animal number
NotePos The line the entry is on (1 to 7)
NoteText The details on the line


clinical - Clinical Record

Field Description
ID Sequential Number per entriy.
SiteWorkDone Site the work was carried out at. (Name)
SurgeryDone Site the work was carried out at. (Number 1-9)
SurgeryReg Site the animal is registered at. (Number 1-9)
ClientKey DM unique client number.
PVID Premvet animal number
MonthYear Month and Year the work was done.
Month Month the work was done.
Year Year the work was done.
TransactionDate Full transaction date.
TransactionTime Transaction time.
FullDateTime Full Transaction date plus Time
AnalysisCode Analysis code associated with the work.
LineType Set the type of line this is
DT = this line has set a disease Tag
a-z = User Defined File entry
LineWhat Related to LineType
If userDefined Files used - the Entry from the UD file
If Disease Tage - the tag number
LineDisc Yes or No - Has the line been discounted?
DiscAmount The amount (to the nearest %) the line has been discounted by.
LinePDSA Yes or No - Has the line been transfered to the PDSA screen (Classification 12 only).
PDLX PDSA Classification in force.
WasScript Yes or No - Was Script generated for this item.
DispFee Yes or No - Was a dispencing code (~A-Z) used.
AnaGroup The analysis group the work fell into.
NewTra Is this a 'New Transaction' or not. Only the 1st transaction of multiple work entries will be flagged.
UserID User ID of the user who did the work.
LineQu (Priced Only) - The quantity supplied (extracted from the clinical text).
Shorttext The 1st part of a priced clinical line less any quantities.
ClinicalText The clinical text. This can be up-to 255 characters long if multiple text lines were entered.
Fees The fees component of the line.
Drugs The drugs component of the line.
VAT The VAT component.
Payments If this was a payment - what was the amount.
LineBalance The line balance (Fees + Drugs + VAT)
VatBand


created - Snapshot creation date

Field Description
Cutoff The 1st date contained in the files.
RunDate The date the snapshot was last created.
Location Where the Vet System files are located.


recalls - Recall Information

Field Description
SiteRegistered The site the animal is registered at.
ClientKey1 DM Unique client number
PVID Premvet record Number
RecallRef Premvet recall type (1-252)
RecallType Description for the RecallRef (Text)
RecallLastDone Date recall was LAST done
RecallInterval Interval in weeks for this recall
RecallNextDue Date recall is NEXT due.
RecallSpecies Species of the animal
MYDone Month and Year the recall was done
MYDue Month and Year the recall is next due.


users - Users

Field Description
SiteAt Site the user works at
SiteNo Site (1-9) the user works at
UserID User ID
Username Full Name of the user
UserType Profile (group) the user belongs to.
Retired Yes or No depending is the user is retired or not
SiteID Will use Site cost center number if setup.


agroup - Analysis Groups

Field Description
AnaGroup Analysis Group (Number)
GroupDesc Group Description


analysis - Analysis Codes/Descriptions

Field Description
AnalysisCode Analysis Code (Number)
AnalysisDesc Analysis Code Description (Text)
AnaGroup Group (Number) Analysis code belongs to.
AnaGroupDesc Group (Description) Analysis code belongs to.


sheets - Animal and Client Sheets

Field Description
SiteRegistered Site the animal/client is registered at.
ClientKey DM Unique number
PVID Premvet Animal Number
SheetType Either 'Animal' if this is an animal sheet or
'Client' if it is a client sheet.
Words The number of words contained on the sheet.
SheetText The contents off the sheet.


Stock - Stock Descriptions

Field Description
StockNumber Stock Number
StockDesc Name of the Stock Item
StkAna Analysis Code


Stock - Stock History

DispDate Date the item was sold
PVID Animal Number
SiteNo Site (Number)
LUID User logged on
WUID User who did the work
Stk_Number Stock Number
Stk_Destocked Quantity Destocked
Stk_Price Price the item was sold at (ex VAT and Ex Disp Fees)
Disp_Fee Dispencing Fee
Stk_Cost Cost price (ex VAT)
Stk_Qu Quantity Sold
Stk_Unused Not Used
Stk_Batch Batch Number
Stk_Warning Warning


dates - Date Related Facts

Field Description
PVDate Date (1st Jan 1980 to 31st Dec 2010)
PDPeriod Period using 4:4:5 from 1st Jan (1 - 12)
PVYear Year component of Date (1980-2010)
PVWeek Week number (4:4:5) 1 to 53
PVDay Which day that is.


medication - Repeat Prescription Details

Field Description
SiteRegistered The site the animal is registered at.
ClientKey DM Unique client number
PVID Premvet record Number
MedAddedDate The date the item was added to the system
MedDuration Duration of the treatment, usually this is in days but you have one additional option:
255 = Non-Specific e.g. as required.
MedLastIssue The date the repeat was last issued.
StockNumber The Stock item this repeat relates to.
MedQuantity Quantity to be issued at a time
UserFirstIssued The user who authorised the repeat.
UserLastIssued The User who last issued the repeat
MedRepeat This is usually the number of times the item can be repeated. There are two additional settings:
255 = Non-Specific e.g. repeated as often as needed and
254 = See Vet
MedTimes Number of times the item has been repeated
MedDose The dose what should be used
MedComment Any comments for this item


conlog - Consultation Times

Field Description
ConDate Date of consultation
ConTime Time of logging
PVID Animal Number
UserID User who was logged on at the time.
Duration Time (in seconds) between accing the management card and quiting from the clinical screen.
Location Screen location
Surgery Surgery Number.


waitlog - Waiting Room Statistics

Field Description
Apptdate Date of Appointment
Appttime Time of Appointment
Diary Diary Name appointment was in
Arrvdate Date Client arrived (+)
Arrvtime Time client arrived at (+)
WhoFor Who was the client allocated to
Room varchar(15)
Da_Time Time (in mins) between arrival time and appointment time.
SeenDate Date seen
SeenTime Time seen
SeenBy Who saw the client
Ws_Time Time (in mins) betwwen arrival and seen time.
PVID Animal Number
WaitNotes Comments


address - Address Book

Field Description
ABid Record Number
ABName Name as entered
ABRName Name as displayed
ABAddress1 Address Line 1
ABAddress2 Address Line 2
ABAddress3 Address Line 3
ABAddress4 Address Line 4
ABPostcode Postcode
ABPhone Telephone Number
ABFax Fax Number
ABMail Email Address
ABNotes Notes
ABRmail Send Referral via E-Mail
ABRfax Send Referral via Fax
ABSite Site address is at


diary - Diary Usage

Field Description
DiaryNo The diary number (1-50)
DiaryName Name of diary
DiaryDate Date
SlotHour The Hour of the day (untimed slots appear unger hour '0')
TotalSlots Total number of slots avalable
BlockBooked Number in the hour unavailable due to Block Bookings
FreeSlot Number in the hour free (Un-Booked)
BookedSlot Number in the hour booked out.

attachments - Letters/X-Rays - 'W' Screen

Field Description
ClientKey Client Key
PVID Animal Number
Description Description of attachment
Mime Mime type
Category Category the attachment is in
FileName File Name
FilePath Path to the attachment
FileCreate Date it was added
WhoBy Who Added it.
Control Name of Premvet Control File