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 |
| 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. |
| 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 |
| 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 | |
| ...... |
| 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 |
| 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 |
| 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. |
| Surgery | The surgery (Number) where the work was entered |
| History_Text | Details |
| 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 |
| 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 |
| 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. |
| 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. |
| 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. |
| Field | Description |
|---|---|
| AnaGroup | Analysis Group (Number) |
| GroupDesc | Group Description |
| 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. |
| 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. |
| Field | Description |
|---|---|
| StockNumber | Stock Number |
| StockDesc | Name of the Stock Item |
| StkAna | Analysis Code |
| 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 |
| 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. |
| 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 |
| 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. |
| 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 |
| 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 |
| 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 |