DataMajor Scripts available

All the scripts below with an update date of January 2009 or earlier will already be on your machines. They are included here for reference purposes and to allow them to imported into MSQuery/Crystal Reports is required.

The scripts have been grouped by subject matter. These also match up with the Index entries.

Analysis Code Queries

Script	Modified	Details
anaalpha	26-08-2002	Analysis code list in Alphabetical Order
anabgrp	26-08-2002	Which group do the Analysis codes fall into.
anangrp	26-08-2002	List all analysis codes that are not currently allocated to a group.
ananum	26-08-2002	Analysis Code list
group	26-08-2002	Show the current grouping used for DataMajor group queries. These are the itemised billing categories you current have.

Animal Related Queries

Script	Modified	Details
abyin	26-08-2002	Show a list of Canine/Feline records, whether they are insured or not by age.
age	17-02-2006	List of all specific species that are 20 or older on the system and when they were last seen by the practice.
birth	17-02-2006	Birthday Card List for the over 7's
idchip	26-08-2002	List of all 'Chipped' animals, listed in Chip Number order.
iddup	26-08-2002	Check for duplicate ID chips being entered.
inscount	26-08-2002	Table showing breakdown of insurance schemes
insured	20-10-2002	Insured Animals by Insurance Scheme.
locked	13-11-2002	List all animals that have had an Administrative lock applied.
mileage	24-05-2004	Mileage: Lists all records where a mileage is recorded.
ooh	24-05-2004	List out of Hours work done with client details Time should be in the 24h format HH:MM e.g. for after 7pm enter 19:00 End Date is optional.
post2	23-09-2002	Breakdown of animal base by Postcode area. Dead animals are ignored. Looks at Canine, Feline, Lagomorphs and Equine Records
pplan	28-09-2002	Table showing breakdown of Classifications used
status	17-02-2006	Search the Management screen for specific status codes that have been seen in the last XXX months; includes Client Name and Address details.

Index of Seen related scripts

Script	Modified	Details
aseen01	17-02-2006	All animals of Y species who have been registered in the last year. Dead animals are excluded.
aseen02	17-02-2006	Name and addresses of clients with animals seen in last 2yrs with no I/D chips. Dead animals, No longer Clients and Bad Debtors will be excluded. This report will do Canine and Feline only.
aseen03	17-02-2006	Search the Management screen for specific status codes that have been seen in the last XXX months; includes Client Name and Address details.
aseen04	24-05-2004	List all of a specific species registered at a specific branch where they are between 4 and 6 months old.
aseen05	17-02-2006	Client list of X species under Y age. Moved Away, Dead and bad debt clients are ignored. Can be used for PV mail merge.
aseen06	17-02-2006	Client list of X species over Y age seen since Z date. Moved Away, Dead and Bad Debt clients are ignored.
aseen07	17-02-2006	All animals who have been seen in last X days.
novisit	18-10-2007	Which animals over X yrs have NOT been seen since Y date. You can limit to those seen in the Z years before Dead, Bad Debtors, Moved Away/Re-Homed animals are ignored.

Average Transaction

Script	Modified	Details
atf	7-04-2003	Global Average Transaction Value report. This will include all staff but will exclude the System user 'PVS'.
atf01	14-05-2004	Average Transaction Value per User per Analysis code, text and date specified. User PVS will be ignored.
atf02	14-05-2004	Average Transaction Value per User per Analysis code and months specified. User PVS will be ignored.
atu	12-06-2003	Average Transaction Value per User per Month. The System User 'PVS' will be ignored.
atua	12-06-2003	ATV Per User were specific Analysis Codes were Used. (Ex VAT) The System user 'PVS' will be ignored.
fdratio	4-10-2003	Show the ratio of Fees to Drugs on a per user basis, over the last 12 months. The user must have done more than 20 transactions in the period, retired users will be excluded.

Branch Reports

Script	Modified	Details
bana	20-01-2003	Breakdown by Analysis code by Branch the Work was done. Use the Pivot Table option in Excel to alter presentation.
batf	28-11-2003	Global Average Transaction Value report. This will include all staff, broken down by Site
batu	28-11-2003	Average Transaction Value per User per Month. The System User 'PVS' will be ignored. Breakdown by Site.
binbysp	28-11-2003	Income by Species (Canine, Feline, Equine and Others) broken down by Month for the last 12 months and by Site the work was done.
bincome	28-11-2003	Practice income by Month and Year broken down by site the work was done
bra01	14-05-2004	Income per branch X client, between x and y transaction dates. This is based on where the work was actually done.
bra02	14-05-2004	Lists x analysis code in y dates. Will list the number of transactions and the financial work generated.
bra03	25-11-2005	Income by record type (SA,EQ,FC,others). Enter dates required. This will be broken down by month.
bstats	10-06-2003	How many Clients and Animals are there on the system.
new01	26-08-2002	Surgery Breakdown for animals SEEN in the last three years. Clients and Animals broken down by surgery they are registered at. Dead animals and clients with no 'live' animals are ignored.

Custom Reports

Script	Modified	Details
clin01	21-01-2009	Who has HAD a specific analysis entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days. Enter client number to start and end on.
clin02	19-05-2004	Lists the clients who have had x analysis code in y dates. Will list the number of transactions and the financial work generated.
clin04	19-05-2004	Search the clinical screen for work entered in the last XX months and include the Client Name and Address details in the output. Dead animals will be ignored.
clin05	17-02-2006	Search for animals which have not had Y analysis code in X months and include the Client Name and Address details in the output. Dead animals will be ignored. Enter species if required.
clin06	21-01-2009	Who has HAD a specific analysis entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days. Will give total number found rather than client details.
clin07	21-01-2009	Has had a specific analysis code entry but not another analysis code entry.E.g has had a vaccination but no worming treatment in the same period. Enter period in days, analysis code and user id. Will give total number rather than client details.
clin08	21-01-2009	Who has HAD a specific analysis entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days.
clin09	21-01-2009	Who has HAD a specific analysis entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days. Dead Animals are excluded and will list each CLIENT.
clin10	17-02-2006	List of dead animals who have had x analysis code in y dates.
clin11	24-05-2004	Income generated by x analysis code on z client, between x and y transaction dates. Will give total then breakdown by user. Can specify branch number.
clin12	6-07-2004	Amount of X analysis code generated in Y year.Enter analysis code(s) and year required.
clin13	6-07-2004	Amount of X analysis code generated between X and Y dates.
clin14	6-07-2004	Amount of X analysis code generated between X and Y dates. Will list fees drugs, VAT an income generated.
clin15	19-05-2004	Amount of X analysis code generated between X and Y dates. Will list fees drugs, VAT an income generated.
clin16	6-07-2004	Income and quantity of X analysis codes generated inbetween Y and Z dates. Enter dates and analysis codes required.
clin17	19-05-2004	Lists the clients who have had x analysis code in y dates of z status code. Will list the number of transactions and the financial work generated.
clin18	6-07-2004	Amount of X analysis code generated between X and Y dates by branch and day.

Client Related Index

Script	Modified	Details
class	26-08-2002	Which clients have a 'Classification' against them.
disc	19-04-2003	Who is set up to receive a 'Time of Sale' Discount or will be offered an 'Early Settlement' Discount.
drugcost	26-08-2002	Clients with a 'V' in the 4th Discount field indicating that drugs are supplied at Cost. Dead animals will be ignored.
email	26-08-2002	Client E-Mail Address List
farml	24-05-2004	List of Farm clients including Map Ref., Herd Ref and Mileage
farmp	22-10-2003	Farm Client Telephone List
hreg	26-08-2002	Total Number of animals and how referred.
locate	25-04-2003	Search for all clients with xxxx in address line. Dead animals are INCLUDED.
new02	17-02-2006	List of new Clients/Animals Registered since a specific date.
novat	28-08-2002	Clients with a 'W' in the 4th Discount field indicating that drugs are supplied Ex VAT or Classification 1. Dead animals will be ignored.
phone	26-08-2002	Client telephone list for all client types.
reg	26-08-2002	Number of new ANIMALS by Month broken down by how referred to you.
visit	26-08-2002	Clients visited the practice the most and the amount they have spent. This will limit the results to the top 200.

Analysis Code Reports

Script	Modified	Details
abytime	27-02-2003	Look for a specific range of Analysis codes, between two dates and break the work down by the hour it was done. Summarise by hour and time period. Dead animals will be included.
canafc	13-12-2004	12 Month Breakdown of Analysis Code Usage (Financial) by Site.
canatc	13-12-2004	12 Month Breakdown of Analysis Code Usage (Count Times used) by Site.
clan01	6-06-2003	Search the clinical screen for work entered in the last XX months and include the Client Name and Address details in the output. Dead animals will be ignored.
clan02	25-04-2003	Lists the clients who have had x analysis code in y dates. Will list the number of transactions and the financial work generated.
notana	21-01-2009	Who has NOT had specific work done in a period, leave 'species' blank to check ALL species. Dead animals will be Ignored. This uses Analysis Code and Age (optional) as a criteria

Clinical Record Queries

Script	Modified	Details
clin03	15-10-2003	Search the clinical screen for work entered in the last XX months and include the Client Name and Address details in the output. Dead animals will be ignored.
cnot	21-01-2009	Who has NOT had specific work done in a period, leave 'species' blank to check ALL species. Dead animals will be included.
cnotr	21-01-2009	Who has NOT had specific work done in a period, leave 'species' blank to check ALL species. Dead animals will be included. Uses Reg Exp for clinical work e.g. rimadyl|metacam for either Rimadyl OR Metacam
hasahas	17-02-2006	Who has HAD a specific clinical entry AND HAD another piece of work, that is, had a Dental AND had a Spay the order does not matter. The period should be specified in DAYS e.g. in the last 60 days.
hasanan	21-01-2009	Who has HAD a specific clinical TEXT entry BUT not had a followup of a Group, that is had 'Adv Dental' but not come in for a dental procedure (group). The period should be specified in DAYS e.g. in the last 60 days.
hasanot	21-01-2009	Who has HAD a specific clinical entry BUT not had a followup That is had a Dental BUT not bought toothpaste in the same period. The period should be specified in DAYS e.g. in the last 60 days.
listwork	5-07-2004	List work done on a specific date when 'something' was done The 'something' can either be a set of Analysis codes e.g. 80,83 for either code 80 OR 83. You can enter text to include all other work.

Diary/Consultation Times

Script	Modified	Details
conlog1	22-07-2003	Report on consultation times (in minutes) by user over last 6 months. Consultations less that 1 min will be ignored.
conlog2	16-07-2003	Report on consultation times by location over last 6 months. Consultations less that 1 min will be ignored.
conlog3	5-02-2009	Report all consultation log details for last 12 months.
diary1	3-11-2003	Report on ALL diaries. Weekend is excluded.
diary2	3-11-2003	Report on ALL diaries breakdown by day of the week.
diary3	3-11-2003	Report on diaries over a specified period. The 'Diaries to look at is optional. Multiple diaries can be entered e.g. 1,3,6 for diaries 1, 3 and 6 inclusive. The Day of week is similar with 0=Monday, 1=Tuesday etc.
diary4	3-11-2003	Report on Operation Diary by day of the week for last month. This assumses you have a diary where the name contains 'Opera'
waitlog1	17-06-2005	Report on waiting room times (in minutes) by user over last 6 months. Waiting times of less that 1 min and greater than 1 hour will be ignored. Make sure Waiting Room tables have been built (Util Menu)
waitlog2	17-06-2005	Report on waiting room times (in minutes) by user between X dates broken down on a monthly basis. Waiting times of less that 1 min and greater than 1 hour will be ignored. Please make sure the waiting room tables is built in aux tables.

Custom Reports

Script	Modified	Details
crec01	17-02-2006	List all Dogs seen in the last year where there is NO vaccination record. Dead animals will be ignored.
crec03	20-02-2009	List animals seen since a specific date but their vaccinations are not up-to-date. Enter the cutoff date for last seen. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine , feline. Enter status codes to exclude. At least one code must be entered for this report to work, dead animals are already excluded.
crec04	20-02-2009	List animals seen between X and Y dates but their vaccinations are not up-to-date. Enter one (or more) species separated with a comma e.g. canine , feline.
crec05	20-02-2009	Vaccinations for Specific Species and Specific Month Enter the month.year it will be due. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine, feline. Enter the age in years the animal should be older than. Dead animals are ignored.
crec06	20-02-2009	Gives a list of Dogs which have had a vaccination in X month and are aged between 2 and 3 months. Dead animals are ignored.
crec07	19-05-2004	Recalls for Specific Species and Specific Month of animals marked as dead(DD) Enter the month.year the recall was done. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline.
crec08	17-02-2006	Recalls for Specific Species and Specific Month of animals over Y age. Enter the age in years the animal should be over. Enter the month.year the recall was done. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline.
crec09	17-02-2006	Vaccinations for Specific Species and Specific Month Enter the month.year it will be due. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline.Enter the branch number required. Dead animals are ignored.
crec10	19-05-2004	Look for all animals who have a certain classification which have not been back for a vaccination.Dead/rehomed/moved away animals are ignored.
crec11	20-02-2009	Recalls due between specific date ranges. Enter start and end dates. Enter the recall name required. This can be done on a per species basis. Enter one (or more) species separated with a comma e.g. canine, feline. Dead animals are ignored.
crec12	19-05-2004	List animals seen since a specific date but their vaccinations are not up-to-date. Enter the cutoff date for last seen. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline. Enter age animal is less than. Dead animals and bad debt animals are excluded.
norec	21-01-2009	Animals who have NO recall details. NOTE: This will only pick up animals which have clinical history.

Custom Statistics

Script	Modified	Details
cstat01	17-05-2004	Will give number of clients, broken down by record type and user seen between two dates where clinical work has been added. Animals seen by different users will be counted once under each user id.
cstat02	24-05-2004	Number of animals seen by species between two dates.
cstat03	11-11-2005	Will give number of clients, broken down by record type, seen between two dates where clinical work has been added.
cstat04	20-02-2009	Number of animals seen between x dates where animal is over 6 months old.
cstat05	20-02-2009	Animals seen between dates breakdown by species.
cstat06	20-02-2009	Will give number of animals seen between x dates.
cstat07	17-05-2004	Will give number of animals seen between x dates by branch.
cstat08	16-05-2007	Will give number of clients seen between x dates. Priced work
cstat09	17-05-2004	Will give number of clients seen between x dates.
cstat10	17-05-2004	Will give number of clients seen between x dates. The second table will give the number of clients who have had X analysis code in the same period.
cstat11	17-05-2004	Breakdown of canine/feline animal base by sex , summarise if entire or not. Dead animals will be ignored.
new02	17-02-2006	List of new Clients/Animals Registered since a specific date.
new04	17-05-2004	Canine and Feline record numbers by branch

Stock Custom Reports

Script	Modified	Details
batchp	19-05-2004	List all Stock items including Batch numbers) sold to one or more clients over a specified period.
stk01	19-05-2004	List Stock items sold in a date range on Z client. The report will give total income. Comments (if any) in () will be ignored.
stk02	19-05-2004	List X Stock items sold in a date range BY QUANTITY. Enter your POM anaysis code(s)and clinical text if required. The report will list the number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
stk03	19-05-2004	List X stock items sold in a date range by QUANTITY and % of total drugs. Enter your POM anaysis code(s)and clinical text if required. The report will list the number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
stk04	24-05-2004	List X stock items sold in a date range by QUANTITY and % of total drugs. Enter your POM anaysis code(s)and clinical text if required. The report will list the number of times used, quantity issues income by item and group by user. Comments (if any) in () will be ignored.
stk05	19-05-2004	List X Stock items sold in a date range BY QUANTITY on Z client. Enter your POM anaysis code(s)and clinical text if required. The report will list the number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
stk06	17-02-2006	List of clients who have had X drug of ancode 254 between X and Y dates.Will list the quantities and number of times used. Comments (if any) in () will be ignored.
stk07	17-02-2006	List of clients who have had X drug of Y ancode between X and Y dates.Will list the quantities and number of times used. Comments (if any) in () will be ignored.
stk08	19-05-2004	Will list all drugs with batch numbers on X client number within certain transaction dates. Enter dates and client number(s).
stk09	19-05-2004	Will list all clients with X drug and Y batch numbers between Y and Z dates.

Financial Figures

Script	Modified	Details
average	6-03-2006	Number of animals seen over the last year. Averages over a few periods
baddebt	17-02-2006	List all clients marked as a Bad Debt. If you leave the balance question blank you will get all Clients otherwise will limit to those with an outstanding balance greater than that specified.
btf	24-09-2002	Look at how much it costs to treat different species. This will scan the work entered for the LAST 12 months and generate some averages. Will list all animals where more that 2000.00 has been generated.
class1	20-02-2009	Classification Work in last 30 days.
credit	10-10-2002	Lists all the credits entered on the system in the last XX days.
dayofw	10-05-2006	Turnover by Day of week and Time Period. Number seen included. Morning is between 8am and 1pm, Afternoon is after 1pm and before 6pm, Evening is between 6pm and 8pm, overnight is the rest (SA Only).
fdsplit	4-10-2003	Show the 'spilt' between Fees and Drugs - Clinical lines where BOTH fees and Drugs have been entered. Usually this will be dispensing fees. Report will be broken down by Analysis code, Month and Year.
inbysp	28-11-2003	Income by Species (Canine, Feline, Equine and Others) broken down by Month for the last 12 months.
income	28-11-2003	Practice income by Month and Year
ooh1	15-01-2008	Out Of Hours Work OOH is All Day Sunday, before 8:30am, after 6:30pm (after 1:00pm Sat). It is assumed all OOH work is done at surgery 1
spend	14-12-2002	List the clients who have spent the most in the last XX Days. Report can be limited to just the top xx clients.
spend01	14-05-2004	Small animal clients who have spent the most in the last 2 years. This will limit the results to the top 200. Dead animals will be excluded.
spval	20-07-2004	Average spend for Insured cats and dogs over last full three years. Option to exclude specific analysis codes.
ubygroup	12-06-2003	Breakdown the work done over a period by analysis group and the user that carried it out.
year	31-12-2002	Top spenders (clients) over the last 12 month period. Shows spend, number of visits and average spend per visit.

Growth and Lost Clients

Script	Modified	Details
growth	28-04-2003	Look at number of new animals and number of deaths over last 5 years. Will use the 'Moved Away' Status codes (if set)
lost1	15-05-2003	This will list any clients that spent money in the past but have not spent anything in the last full year (+ current year). Bad Debtors, moved away and clients with no animals will be ignored.
lost2	15-05-2003	Look back at the year ending 18 months ago, which clients were in your 'top 200' but have not been back in the last year. This uses the clinical records. Moved Away, Dead and records marked Bad Debt will be ignored.

Generic Income Reports

Script	Modified	Details
bdpost	4-02-2009	Looks at the 1st part of the Postcode and give a breakdown of the number of CLIENTS in that area who are marked as a bad debt.
caseload	10-08-2004	Income by Month, Species, broken down by user for last 12 months.
ibyuser	15-07-2003	Breakdown the work done over a period by analysis code and the user that carried it out. Summarise by User.
inc01	17-05-2004	Income by record type (SA,EQ,FC,others) by branch. Enter dates required. This is broken down by month and will give a total.
inc02	17-05-2004	Fees, drugs VAT, total income and payments between X and Y dates. This will be broken down month by month and with total.
inc03	17-05-2004	Income broken down by user. Enter dates required.
inc04	17-05-2004	Income per client, between x and y transaction dates.
inc05	24-05-2004	Income by Species ,enter species required, broken down by user. Enter the dates required.
inc06	17-05-2004	Breakdown the work done over a period by analysis code and the user that carried it out. Summarise by User.
inc07	17-05-2004	Income by Species (Canine, Feline, Equine and Others) broken down by Month. Enter the year required.
inc08	25-06-2004	Income by X analysis code generated in Y year.Enter analysis code(s) and year required.
inc09	23-03-2006	Income by record type (SA,EQ,FC,others). Enter dates required. This will be broken down by month.
inc10	17-05-2004	Will give outstanding balances between X and Y dates. Will give client name and the user who entered the work.
inc11	17-05-2004	Work done at branch 1 on branch 2 clients between X and Y dates.
inc12	17-05-2004	Income by client record. Enter dates required.

Laboratory Work

Script	Modified	Details
lab1	28-08-2002	Lab Results broken down by Species only (All Ages) for last 2 years. Will show the Minimum, Maximun and Average for the actual test. Along with the Minimum 'Lower' and maximum 'Upper'
lab2	28-08-2002	Lab Results broken down by Specific Species and Age for last 2 years. Will show the Minimum, Maximun and Average for the actual test along with the Minimum 'Lower' and maximum 'Upper'
lab3	30-08-2002	Specific Lab Results for Specific Species for last 2 years test and Species are optional
lab4	28-08-2002	Lab Results with abnormal results for last 2 years Test and Species are optional.
lab6	3-05-2003	Look at a specific test over the last 2 years and compute some statistics based on Species and Age.

Large/Equine Work

Script	Modified	Details
fcwork	28-11-2003	Printout all the batch numbers issued to a Farm in last 30 days. WHEN PRINTED IT WILL DO ONE CLIENT PER PAGE.
large01	28-11-2003	Income from Farm Clients broken down by Month for the last 12 months.
large02	28-11-2003	Income from Equine Clients broken down by Month for the last 12 months.
large03	14-05-2004	Farm Income broken down by Fees and Drugs for the last year.
large04	14-05-2004	Equine Income broken down by Fees and Drugs for the last year.
large05	14-05-2004	Farm and Equine Income broken down by Month for the last 12 months.
large06	14-05-2004	Farm Income broken down by Fees and Drugs between X and Y dates Including Herd Reference

Marketing Field

Script	Modified	Details
mdead	22-09-2002	This will set the marketing tag to NONE for all dead animals that are currently tagged. Premvet and DataMajor will be updated, this will Cross update to remote sites.
mtag	22-09-2002	List all clients marked with a marketing tag. This will include clients with NO animals
mtagb	22-09-2002	List all clients marked with a Bronze marketing tag. Will ignore clients with no animals.
mtagg	22-09-2002	List all clients marked with a Gold marketing tag. Will ignore clients with no animals
mtagp	22-09-2002	List all clients marked with a Platinum marketing tag. Clients with no animals will be ignored
mtags	22-09-2002	List all clients marked with a Silver marketing tag. Clients with no animals will be ignored.

Prescriptions + Scripts

Script	Modified	Details
med30d	7-03-2003	Who is due to come in in the next 30 days for a repeat. Will ignore the 'As required' records.
medany	7-03-2003	Who is on a 'As Required' Prescription.
medbyu	7-03-2003	List all Stock Items where Repeat Scripts are associated with.
medexp	7-03-2003	Records that have expired or where maximum times issues has been reached.
medvet	7-03-2003	Who is Flagged as See Vet?
script60	22-09-2003	List all those Clients who have had a 'Prescription' in the last 60 days.
scriptid	22-09-2003	List all the items that clients have requested 'Prescriptions' for in the last 6 months. Will only 'look' at 1st 35 characters of the item.

Misc/Old Queries

Script	Modified	Details
eqbreed	24-08-2002	Breakdown of Equine breeds, dead animals are ignored.
febreed	24-08-2002	Breakdown of Feline breeds, dead animals are ignored.
k9breed	26-08-2002	Breakdown of Canine breeds, dead animals are ignored.
newcli1	26-08-2002	Display a 'dot' graph of the number of new CLIENTS registering over the past three years.
sheets	6-06-2003	Count number of Sheets currently being used.

Postcode Related index

Script	Modified	Details
paf1	21-01-2009	Use PAF database to look at clients with invalid postcodes, that is Address line 1 is not valid for the postcode supplied.
paf2	14-09-2003	List all clients where the 1st two address lines match but the postcodes do not. If you get a message 'post table does not exist' you have not used the utility option to build the table.
paf3	14-09-2003	List all clients where the postcode on the record does not exist in the PAF database. If you get a message 'post table does not exist' you have not used the utility option to build the table.
paf4	14-09-2003	List all addresses matching a post code in the PAF database. If you get a message 'post table does not exist' you have not used the utility option to build the table.
paf5	14-09-2003	List all postcodes matching an address in the PAF database. If you get a message 'post table does not exist' you have not used the utility option to build the table.
paf6	14-09-2003	Use PAF database to exclude all client with valid postcodes, list the others. If you get a message 'post table does not exist' you need to use the Build Aux Table option on the utility menu.
weirdpc	28-11-2003	Look for records where the postcode has not been entered correctly. Or the postcode is blank.

Various Pivot Table options

Script	Modified	Details
agp	14-02-2009
pivot1	23-01-2004	Income generated by user and analysis code grouped by Month and Year in a format suitable for exporting to Excel.
pivot2	24-08-2002	Analysis Code and Billing Group number including date, Species, Breed, Sex in a format suitable for manipulating in Excel.
pivot3	21-04-2003	Analysis code usage: Including Date, Species, Breed, Sex, Insured in a format suitable for 'Pivoting' via Excel.
pivot4	23-01-2004	Breakdown by Year/Month of income generated by user and service offered.

Recall Related Queries

Script	Modified	Details
cvacc	21-03-2005	Compare Management Record Vaccination Date with Clinical Records. Only looks at living Small Animal Records. Will also report on records where there is a Vaccination date but no clinical entry for it.
noreccd	27-11-2003	List all those Dogs and Cats where there is NO vaccination record. Dead animals and those moved out of the area will be ignored.
rec01	25-04-2003	List animals who have had a vaccination since a specific date Enter the date for last seen, the species to search for. Enter amount client should have spent more than in last year. Dead Animals are ignored.
rec02	25-04-2003	List animals who have had a recall since a specific date. Enter recall number, enter the date for last seen, enter species to search for, enter amount client should have spent more than in last year. Dead Animals are ignored.
rec03	25-04-2003	List animals who have had a vaccination since a specific date Enter the date for last seen and the minimum age of animal. Dead Animals are ignored.
rec04	17-02-2006	Animals over 8 years who have had a vaccination. Prompts for surgery number
rec05	17-02-2006	List Rabbits who have had a vaccination since a specific date. Enter the date for last seen. Dead Animals are ignored.
rec2y	24-05-2004	List all animals who haven't had a vaccination for 2 years+. Will ignore Moved Away, Bad Debtors and Dead Animals.
recage	7-11-2002	Look for all animals over a certain age, for any species (you select) where they have not been back for a vaccination. e.g. OverDue Recalls
recdue	25-04-2003	Number of animals due to come in - uses Next Recall Due date.
recgen	25-04-2003	List animals seen since a specific date but their vaccinations are not up-to-date. Enter the cutoff date for last seen. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline. Enter status codes to exclude. At least one code must be entered for this report to work, dead animals are already excluded.
recmth	25-04-2003	Recalls for Specific Species and Specific Month Enter the month.year the recall will be due it. This report is also on a per species basis. Enter one (or more) species separated with a comma e.g. canine,feline. Dead animals are ignored.
recsplit	26-08-2002	Number of 'Missed' recalls - Count of when the recall was last done showing the number of non-shows by month/year. This will also show the number of animals you should see for the current runs.
recup	1-12-2003	Uptake for last 12 months - primary vaccination - Canine & Feline.
zerocall	27-11-2003	List of 'Alive' animals that have a zero interval e.g. they will not be sent recalls.

Clients seen

Script	Modified	Details
seen01	17-02-2006	Will give names and addresses of clients seen in last 3 years by a specific user.
seen02	17-02-2006	Name and addresses of clients seen in between two dates.
seen03	17-02-2006	Name and addresses of clients seen in x date range.

Statistics Menu

Script	Modified	Details
anover5	19-04-2003	Who has more the 5 animals?
branch	11-04-2003	Various figures by Record Type
breed	26-08-2002	Breakdown of Specific Species by breed, dead animals are ignored. Enter the required Species.
cstat03	11-11-2005	Will give number of clients, broken down by record type, seen between two dates where clinical work has been added.
names	13-02-2009	List the top 10 common names for Dogs, Cat and Rabbits.
newcli	25-04-2003	Table to Show number of new clients and Animals per Year and Month.
noshow	9-06-2004	Report on clients who cancelled appointments, failed to keep their appointments or arrived without an appointment. Covers the last 12 months and counts animals.
othbreed	23-09-2002	This script will ignore Farm, Canine, Feline and Equine cards and breakdown all other species. Dead animals will be ignored and the report will be sorted by breed.
post1	26-08-2002	Show the Percentage of your client base by postcode area.
postcode	11-04-2003	Looks at the 1st part of the Postcode and give a breakdown of the number of CLIENTS in that area. Clients with no animals (all dead) will be ignored.
sex	2-06-2003	Breakdown of animal base by sex (just Dogs and Cats), summarise if entire or not. Dead animals will be ignored.
species	26-08-2002	Breakdown by Species of all 'Alive' animals. Gives count and Percentage.
stats	14-05-2004	How many Clients and Animals are there on the system.

Markup and Cost Related

Script	Modified	Details
stkall	20-02-2009	List all lines in Stock History file
stkana	20-02-2009	Items Destocked over last 3 months with average markup's by Analysis Code
stkdif	20-02-2009	Items (in last 3 months) where the quantity destocked is different to the quantity sold. (May not show any).
stkndf	20-02-2009	Items in last 3 months where no dispencing fee has been charged
stksum	20-02-2009	Income from Stock (by Month) over the last year.
stksumk2	25-11-2005	Number and quantity of stock items sold by stock number. Enter stock number required analysis code or press return for all. Will give a month by month breakdown and total.
stktopa	20-02-2009	Top 50 stock items based on Quantity Sold in last 6 months
stktopb	20-02-2009	Top 50 stock items based on Income in last 6 months
stkused	20-02-2009	Items Destocked over last 3 months with average markup's

Stock Related

Script	Modified	Details
batch	5-08-2005	List all Stock items including Batch numbers) sold to one or more clients over a specified period.
batch1	19-05-2004	Will list all clients with X drug and Y batch numbers between Y and Z dates. To look for just a batch number - leave text blank.
df1	19-05-2004	List items where a dispensing/injection fee was NOT added. Stock items added in the last 60 days.
dispf1	4-10-2003	List all those Clients who have had a 'Prescription' in the last 60 days.
fdincom	24-09-2003	Show the income over the last year and how much was related to drugs. Broken down by record type.
fdratio	4-10-2003	Show the ratio of Fees to Drugs on a per user basis, over the last 12 months. The user must have done more than 20 transactions in the period, retired users will be excluded.
fdsplit	4-10-2003	Show the 'spilt' between Fees and Drugs - Clinical lines where BOTH fees and Drugs have been entered. Usually this will be dispensing fees. Report will be broken down by Analysis code, Month and Year.
finba	19-05-2004	Drug Income for the last year for all analysis codes with a drugs component.
pom10	21-05-2003	List top 50 Stock items sold in a three month period BY TIMES USED. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
pom10a	21-05-2003	List top 50 Stock items sold in a three month period BY INCOME. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.
pom10b	21-05-2003	List top 50 Stock items sold in a three month period BY QUANTITY. Enter your POM anaysis code(s) when asked. The report will list the Number of times used, quantity issues and income by item. Comments (if any) in () will be ignored.

'Tidy-Up' and Update Index

Script	Modified	Details
arch1	28-01-2008	List all animals marked for Archiving and the Reason
dup	11-05-2006	Report on Clients that have the same Surname AND Address Line 1.
dupa	11-05-2006	Report on ANIMALS that have the same Name, Surname AND Address Line 1.
dupb	11-05-2006	Report on CLIENTS that have the same 1st Address Lines
dupc	11-05-2006	Report on ANIMALS that have the same Name AND Address Line 1.
isdead	10-05-2007	List all records where the words 'EUTH', 'CREM' and 'PTS' appear and the animal is marked as Alive. This will help identify possible dead animals.
mastdd	24-01-2008	List Animals where the Master record is flagged as Dead ior Flagged as no longer a client and there are other Alive animals in the chain. Clients with NO Alive animals will be ignored. Farm records will be ignored.
noanimal	26-08-2002	If all the animals belonging to a client are dead then they will have 'no' animals. This report will list those clients. If you are suppressing dead animals then no results will be returned.
noclin	14-05-2004	If you used the '-o incnoclin' build option this report can be used to list all animals where there are no clinical records.
uaddc	10-05-2007	List all records where Sex is Un.., Species is Blank, Colour/Breed if Unknown, UnSpecified or Blank. Include Registration details if known. This will list Small Animal Records only. The 'AddedBy' user option was added in April 2007.
weirdbr	13-11-2003	List all animals where the count of any breed is less than 20. This will include dead animals (if included)
weirdpc	28-11-2003	Look for records where the postcode has not been entered correctly. Or the postcode is blank.
weirdsex	10-09-2003	List all records where their sex is 'un-common', that is less than fifty on the whole system.
weirdsp	10-09-2003	List all animals with an 'Un-Common' Species, less than 20 in the database.

Update Options

Script	Modified	Details
upbadd	7-04-2003	Scan all the 'Bad Debt' records and set the Admin Lock to Yes WARNING THIS OPTION WILL UPDATE THE DATABASE
upbird	30-03-2003	Scan all the Breeds for any Birds and update Species WARNING THIS OPTION WILL UPDATE THE DATABASE
upbreed	2-05-2003	Change the Breed from X to Y e.g. GSD to German Shepherd Dog (Regex) WARNING THIS OPTION WILL UPDATE THE DATABASE
uproad	29-03-2003	Scan all the Breeds for any Rodents and update Species WARNING THIS OPTION WILL UPDATE THE DATABASE
upsex	2-06-2003	Look through records and change any 'Weird' sex entries to 'UK'. It will change NF -> FN and NM -> MN. Male/Female/G?/EN entries will be ignored. THIS WILL UPDATE THE DATABASE
upspec	2-06-2003	Check if a breed contains something and update the Species (Regex) WARNING THIS OPTION WILL UPDATE THE DATABASE
upspec1	21-05-2003	Change the Species e.g. Rabbit to Lagomorph (Regex) WARNING THIS OPTION WILL UPDATE THE DATABASE
upspecn	21-11-2003	Check if a breed contains something and update the Species (Regex) This will only find records where the Species is blank. WARNING THIS OPTION WILL UPDATE THE DATABASE

User/Address Book Queries

Script	Modified	Details
address1	17-02-2006	List entries from the address book Names and Addresses
address2	18-06-2004	List entries from the address book Names and contact details
retired	11-04-2003	Who has left (been tagged as 'Retired'' in password maintenance)
ubg	11-04-2003	Users broken down by profile they are in and the site they work at.
ug	11-04-2003	Current User Profiles you have and number of users in each.
unowork	21-04-2003	Who has not done any work in the last 5 years - that is they exist in the password database but have never entered any work on the system. These users can be marked as retired.
users	11-04-2003	List of Active Users on the System.

Vat Related queries

Script	Modified	Details
nopay	24-01-2008	Seen but Not Paid Will list any animals seen in the last 24 hours where NO payment was received in the same period. Equine and Farm records will be ignored.
vat01	14-05-2004	Give zero rated VAT work done between x and y dates
vatbook	3-06-2004	This is the same as the Premvet Historic Vatbook. Enter a RANGE of dates, 10 days, 1 month, 3 months. Note: For end date enter the START of the next month, it will subtract 1 day.
vb1	1-01-2005	Summarise work done by month by Analysis Code All Figures are Ex VAT
vb2	1-01-2005	Summarise work done by month by User All Figures are Ex VAT
vb3	1-01-2005	Summarise Payments Received By Client rather than Payment Type
vb4	1-01-2005	Summarise Everything
vb5	1-01-2005	Credits by Analysis Code and Summary
vb6	1-01-2005	Branch Breakdown Figures
zerovat	2-12-2003	List all transactions that are Zero Vated on the system. Will ignore records where they are meant to be Zero rated.

Index Entries

• My own stored reports
• Practice Statistics
• Growth and Lost Clients
• Diary/Consultation Times
• Custom Statistics
• Misc/Old Queries
• Financial Figures
• Generic Income Reports
• Vat Related queries
• Average Transaction
• Branch Reports
• Large/Equine Work
• Client Related Queries
• Marketing Field
• Clients seen
• Animal Related Queries
• Index of Seen related scripts
• Analysis Code Queries
• User/Address Book Queries
• Recall Related Queries
• Custom Reports
• 'Tidy-Up' and Update Index
• Update Options
• Postcode Related index
• Generic/Custom Reports
• Laboratory Work
• Stock Related
• Markup and Cost Related
• Stock Custom Reports
• Various Pivot Table options
• Clinical Record Queries
• Analysis Code Reports
• Prescriptions + Scripts
• Custom Reports