Home > System 6000 > Hints and tips

Search

System 6000: hints and tips for reports


Report for patients who have not been referred

This is to pull out any referrals intended which had not been actioned by attached referral letter. It's quite a tricky concept, but when you know how it is actually very easy to do.

A letter is attached to a Note using an Attribute, so you need to look at the attributes rather than the Subject Type. Attributes are joined to Notes, rather than directly to Patients, (patients have notes, notes have attributes). Hence you want to start 

"report for each note...".

What the problem boils down to is:

Try this

Title "Referral code but with no attached letter"
Report for each Note where
Note.Read_Code Between "8H4.." and "8H5Z."
and Note.Start_Date in L3M
without Attribute where Attribute.Attrib_Type = 99
with Patient where Patient.Registration_Type = "Full GMS"
;

You get a list of notes, which should enable you to identify the patients. Some patients could have more than one note. You can right click each note in the CSV window to load that patient straight into Clinic and examine them. If you really need to you can turn this list of notes into a list of patients by adding

into "refernotes";

then adding a second query that says

report for each patient with note in "refernotes" ;

That will ensure each patient shows exactly once and you can get the full patient details eg phone number.


S6 Reports and dates in Excel

John Robinson's Excel report formats automatically handle CSVs neatly but sometimes you just need to load a raw CSV (CSV is the Comma Separated Data file, produced on your C: drive after running an S6 Report).

When you open a CSV file in Excel it does not recognise System 6 dates as dates. S6 dates in CSVs are in a format called ANSI dates, which is YYYYMMDD eg 19990908 for 8/9/99. Excel thinks these are just numbers. If you try to reformat the column to be a date it still doesn't recognise them properly.

The trick is to use Text to Columns on the Data menu. You need to select the column to convert first. Excel then gives you a mini wizard with 3 steps. The first two are to do with delimiters for separating multiple fields. Since you are already dealing with only one column (field) you can just hit Next > twice. Step 3 has Column Data Format. Select Date and then pick YMD, then Finish. The column should be converted to display DD/MM/YY. You can then reformat to other date styles, and the full YYYY is preserved.

Some caveats:

Sorting

The quickest and best way to get this report to sort is to change the query. Add a line that sorts by date_of_birth - you can't sort by Age because Age is not actually stored in the database, it's calculated on the fly from DOB.

report for each patient in "fileX"
where patient.patient_id <> 0 # padding
sort by patient.date_of_birth;

The padding is needed as the editor just won't let you go straight to Sort, normally you would have some "body" to the query. Don't worry about adding a "Print" line. It is not needed. No Print line means "give me the normal fields". Only use Print if you want to see extra fields that are not in the normal default list. And when you do this bear in mind you only get what you specify, so printing one or two fields will actually give you a lot less than the default set. What normally happens is people miss out the crucial patient_id (the internal patient identifier) so the format cannot tell patients apart. The Query Advisor will kick in and warn you against doing this, but if in doubt don't use Print.


"Last high result" reports

This one is an old chestnut. "Last" does have a conceptual hurdle that most people stumble on at some point. Have a look at Custom/Example/Last for Rik's last attempt to explain it.

The crucial thing is that The last one, if it was high is different from The last high one.

Have a think about that. There really is a difference, and it is not just an MRL issue, it is a general language issue. In this case you will need two steps. Find all the last records, put them into a list, and then see which ones were actually high. "Last" is a filter applied after the rest of the query, similar to print and sort lines.

eg Last 1 reports
Using the query builder you can add (last N) to a Note or Entry query, but not to a patient one. 

...with Entry (last 1) ... is not supported

Try this

Title "Last entry over 5 years old"
Report for each Entry ( last 1 ) where
Entry.Read_Code Between "4K21." and "4K2z."
and Entry.Start_Date >= -5Y ;

This list of entries will be the last one per patient that is older than 5 years. You will only get the patient name and id though, as well as the
read code, and entry date etc. If you need extra patient details eg address, phone number, instead first put these entries into a list

Report for each Entry ( last 1 ) where
Entry.Read_Code Between "4K21." and "4K2z."
and Entry.Start_Date >= -5Y
into "5ysmear";

then

Report for each patient with entry in "5ysmear" ;

The latter query will be a lot slower than just getting the entries, but is available if needed.


Authorisations

On the subject of drugs, authorisations are the way to go to get full control and they are more complicated than plain Entries. The simplest thing is to use is Authorisation.Last_Issued_Date. This has always been maintained by the system, but you couldn't report on it until recently. That should give you people who have actually been prescribed recently. This isn't quite the same as people who are "currently on" a given drug, although it will be similar in most cases.

If you did want the actual "current medication", current_authorisation_flag is not really what you need. This field is used for seeing which of several Authorisations for a given treatment is the current one. Treatment being a higher level concept, that can have several authorisations at different times, but all for the same drug (though possibly in different formulations). So current flag means "most recent" of the authorisations (ie. not superseded or discontinued), but not necessarily a "current medication", because it may have expired. The actual rule for current medication that matches the view in S6 is complicated and overkill for most purposes. Things can be current by date or by number for instance. Have a look at the Single report Current medication query for full details.


Using CSV files of patient numbers

The only way to use a CSV of patient numbers is as a parameter ("loop") file. Your report then runs once for each patient in the CSV list. This is no good for examining the patients as a whole, but you can use it to mark the notes of all the patients with something unique (an admin code and specific free text maybe). Then find those patients with a separate report, possibly putting them in to a database list at that stage for convenience. This will clutter the notes if done regularly of course. What you really want is "Upload CSV to list". It has been on the wish list for a while - probably deserves a priority boost.
You can loop on any field, eg patient ("external") number. Anything that can be "runtime = VALUE" in a query can get substituted by the list from the file.

report for each patient where patient.patient_number = runtime PATIENT_NUMBER
add note read_code "ABCD.", free_text "Patients for XXX reporting" ;

run the folder as a whole to activate the loop, then once marked

report for each patient
with note where note.read_code = "ABCD"
and note.free_text = "Patients for XXX reporting"
into "SpecialPatients" ;

If you recreate the list later, you would end up two notes.
Avoid this by checking for absence of the note in the query that adds them. (without note...add note).

There is no way to directly use a CSV file with "in" or "into". "in CSV" would be very handy. "into CSV" would be a bit pointless because you normally get a CSV file if you don't specify "into". You can name it anything you like by renaming the query, but it always appears in the folder directory on C:.


Printing off all attached WP files

Unfortunately there is no standard report that does this. However it can be done. If anyone wants to try creating a report here's how you might go about it.

Create a new report and add this query:

Title "Word Processing docs for any patient"
Report for each Entry where
Entry.Attrib_Type = 99
and Entry.Patient_Id = Runtime PATIENTID
print Entry.Attrib_String, Entry.Patient_Id, Entry.Surname, Entry.Forename_1, Entry.Attrib_Type, Entry.entry_id ;

The query will display all the filenames attached to entries for a certain patient.

Attachments are stored as attributes, 99 is the magic number for "this is a document", as opposed to say a drawing. This assumes one file per entry. S6000 1.3 forces only one document per entry, most practices only attach one per entry anyway. Run the query, pick a patient and you should get a csv with a list of filenames eg

[Attrib_string, Patient_id, Surname, Forename_1, Attrib_type, Entry_id]
"/meddata/s6000/document/c/cb9.doc|Journal of patient notes",45,"Arbuckle","John",99,4603
"/meddata/s6000/document/e/eqj.doc |New document",45,"Arbuckle","John",99,5442
"/meddata/s6000/document/e/er3.doc |Skeleton patient letter",45,"Arbuckle","John",99,5472

The document name is coded into the attribute as you can see. What is now needed is a report format that loads this csv and instead of printing the actual csv, prints the files that are referenced in the csv. You should find a format called PRINTER.DOC in report/formats/docs, which does exactly this. It is a Word 97 format, and it loads and prints each file in a csv. Set this as the report format and do a repreview.


Scheduling reports

Scheduling reports uses the same syntax as putting them on the Utilities menu of clinic. I think Config Editor has a limited length of line it can edit, and it is not always long enough for the Utilities menu. This is a good reason to edit the INI file directly. Stick to the [Utilities] section though (and make a backup first).

The easiest way to add a new report to the utilities menu is to find an entry that works, copy it (to a new numbered entry) and change the report it refers to.

example for FP report

Util8=Full Patient Report|C:\MEDAPPS\S6000\S6REPMAN.EXE folder=meditel\fp RPN1=PATIENTID RPV1=$patientid

So for your report, assuming it has a runtime parameter that is also called PATIENTID, this is how you get Clinic to fill in it's patient id (which it refers to as $patientid)

Util9=Super Summary|C:\MEDAPPS\S6000\S6REPMAN.EXE folder=custom\super RPN1=PATIENTID RPV1=$patientid

Your report uses

"...where patient.patient_id = runtime PATIENTID"

and so expects to be fed something with the name PATIENTID to use as the patient's id. Clinic has a variable called $patientid (or $PATIENTID) which is always set to the value of the currently loaded patient. So you tell reports that the parameter named PATIENTID has the value $patientid and away it goes.


Adding reminders

The add reminder can be made to skip patients who already have a reminder. The query can itself check for a reminder eg

report for each patient ...
without reminder @!124 and status = "whatever"..
add reminder @!124...

Prescribing costs report

You only need to add one line to an issue format to total the costs, so I've just knocked one up. If anyone wants to try it out email me (Rita ?). Untested, unsupported, unofficial etc, etc.

Title "Cost of all issues in any date range"
Report for each Issue where
Issue.Start_Date Between Runtime START and Runtime END
with Patient where Patient.Registration_Type = "Full GMS"
print Issue.Cost ;

This will give a CSV with all the prices in pence, one per issue. The query can be refined for any drug range, GP etc. Quick way to add it up is to open the CSV file in Excel. It will be on your C drive in c:\meddata\s6000\report\custom\wherever. Once in excel just select all the rows by clicking on the "A" column heading. The status bar in excel shows the total.

John Robinson has an excel format ("Single Query CSV to Excel", patdat.xls) that loads any CSV automatically. You need to change the print line to

print Issue.cost, issue.patient_id

to use this, because the macros like to know about patients.
You can still just click the top of the "A" column and the total appears on the status line. Excel knows to ignore fields with text in. It would be easy to change the standard Count format to total instead then you wouldn't need to load Excel and click the column each time, it would just print out. You could then have a runtime GP and loop on this to show totals for each one.


Randomisation

Maybe there is a shorcut to do this in excel, but I'd expect you need to write something in VBA that uses the built in basic random function (rand() ?). That's probably the most flexible way and truly (well virtually) random. But for hackers only. Making it repeatable might also be difficult. And you would not be able to do further reporting on the group once it's gone to excel. However there are other possibilities using reports itself.

If you pick some aspect of the patient that can be considered pseudo-random (eg the last couple of digits of NHS number) then finding certain values will give a random group of a fairly well defined size.

There is an MRL trick you can use (similar to that to find patients born in a month, that was discussed in AMUG News last year). I've played with this only briefly but it seems to work.

Report for each Patient where
Patient.NHS_Number like "%2_"
print patient.surname, patient.forename_1, patient.nhs_number ;

Gives (approx) 10% of the database, all those with a "2" in second to last digit of NHS number.

This is a repeatable set which could be an advantage or a disadvantage.

How it works:

If you were happy with 8% you could always select all patients born in a month. Try a short
month February and it might be less than 8% (!).

eg Report for each Patient where
Patient.date_of_birth like "%02__" ;

Another idea is to sort by something that is "random" in the first few letters, eg forename, and take the first 5% from the top. You can't take the top 5% in reports and then put the results back in to another query, but you could use that trick in Excel perhaps.


Report query on 5 or more drugs

You need to count authorisations. Treatments are a higher level again that group several authorisations of similar drugs (eg different formulations).

No single authorisation "knows" about the others so there cannot be a field such as

Authorisation.Number_of_Authorisations.

(An authorisation knows about the number of issues authorised within itself but that's different).

This is something that cannot easily be done in a query alone. What you could do is:

What ?! Scanning a massive list of entries in a CSV looking for 5 or more for one patient, that sounds pretty tedious. What you need is a report format that counts them for you. There are a couple of standard formats that can help. Both take a CSV of entries (or notes, or authorisations etc).

ENTRYC.FP (Counts for lists of entries) shows "banded" counts of entries for every patient, with Xs to say which bands the patients are in

eg 1...5...10...20
Smith X
Jones X

so you can see who has totals in certain areas. Good for getting an overview of how many patients are on how many drugs. All patients are shown though, so it will be a long list.

Better in this case is FREQUENF (Note Frequency N or More). You put a value eg '5' in the report free text (Edit report, Advanced>>) and this format displays only patients who have at least that many entries.
(I say entries but you will be talking about Authorisations). Roughly:

report for each authorisation
where Authorisation.Current_Auth_flag = True # not discontinued, or superseded
and ( Authorisation.Number_Outstanding > 0 or Authorisation.Number_Authorised = 0 )
and ( Authorisation.End_Date >= TODAY or Authorisation.End_Date = 0 )
with patient where reg_cat = full gms
and date_of_birth >= -60Y ;

(That's not the dictionary definition of current medication, but good enough if the intention is to review them anyway.) No need to Sort because the format does it for you. Then add the frequenf.fp format and preview the results.

One thing to bear in mind is that the formats will take a while to do the working and they can only cope with a certain total amount of entries (in the thousands). If it hangs forever or crashes you've given it too many. The way around this is to limit the patients to surname A-M then change it to M-Z etc. This limit will hopefully be removed in future. It might be an idea to test it with just all the "A" patients anyway as it will be quicker eg

...and patient.surname between "A" and "B"

Searching for Consultant referrals


Searching for authorisations under a current problem

Title "Auths under current problems"
Report for each Authorisation with
       Problem where Problem.Dormant = False
print Authorisation.read_code, Authorisation.problem_id ;

"With" implies there is something there (rather than nothing, as in the case of unlinked),
and then you can test any field in the problem eg particular read codes, dates, or current/dormant.
This also works with Issues, or any entry type.

Is unlinked a "problem" with a read code?

You mean is "unlinked" a problem in it's own right, like a special dummy problem ?
The answer is no, unlinked items just have no owning Problem.
All entries have a Problem_ID and this is either the ID of the problem they are linked to
or a Problem_id of zero meaning unlinked.

So you can find unlinked by seeing if problem id is zero.

Title "Auths unlinked"
Report for each Authorisation
 where Authorisation.problem_id = 0
print Authorisation.read_code, Authorisation.problem_id ;

The Unlinked Notes queries in Single and FP reports use this.


Printing reports

Reports always go to the default windows printer.

The only exception is if you edit the report format itself, you can set override the destination for all reports that use that format. Edit Format, File, Print Setup I think. As the list of printers to chose from is PC specific it might not print to the correct place for everyone sharing a report format. Only edit copies of formats or they will be overwritten at upgrades.

In general I just change the default printer before the report. Script destination is not affected by this, so you can have one printer for those and another for reports, and even a third for labels.

There is probably shareware to quickly change default printers, or it could be macro-ed.


Reporting on linked and unlinked entries

You can find an entry wherever it is, (unlinked/linked, current/dormant) with

"report for each entry"

You can find a note or problem wherever it is with

"report for each note"

Notes and problems are the same really. Problems are just "heading" notes. Main difference between "entry" and "note" is notes don't have medication or reminder read codes (rare exceptions from conversions).

You can find just problems wherever they are with

"report for each problem"

To specifically find unlinked notes you use

...note where note.problem_id = 0

or ...

...note where note.problem_id <> 0 

for only linked.


To run a report on patients with a specific problem, eg alcohol dependence, and determine whether they have seen the GP

  1. for any reason and/or
  2. for that problem

within, say, the last 6 months.

(a) is a patient with two separate things (a problem, and another later encounter/entry)

 Pat
/  \
Pr  E

and is

Report for each Patient where Patient.Registration_Type = "Full GMS"
with Problem where Problem.Read_Code = "E43.."          # alcohol dependence
with Encounter where Encounter.Start_Date >= -6M ;      # any encounter about anything

(b) is patient with a problem, which has later had something else linked to it

Pat
|
Pr
|
E

You need to find anything recently added to an E43 problem. If you find the actual entry, that also has the patient's name.

To find "anything" use the generic "entry" type. Not encounter in this case as encounters aren't linked to problems, since they can span several, eg:

Report for each Entry where
       Entry.Start_Date >= -6M
with Problem where Problem.Read_Code = "E43.." ;

Note that you don't separately need to check the patient has an E43, the fact that they have a recent entry attached to one implies this.

Being entries you may get multiple records per patient, which you can avoid by throwing away all apart from the last 1, since you are not interested in the entries themselves. And if you want to ensure GMS you can add a further "with patient" line.

Try this :

Report for each Entry ( last 1 ) where
       Entry.Start_Date >= -6M
with Problem where Problem.Read_Code = "E43.."
with Patient where Patient.Registration_Type = "Full GMS"
print Entry.Surname, Entry.Forename_1, Entry.Forename_2, Entry.Patient_Id ;

Most recent BP

I am trying to find out how many of our patient's most recent BP is high. We use system 6000. From previous discussion on the list I have been able to create a database of people whose systolic is raised and another database of people whose diastolic is raised. However, some people will be in both groups. I want to know how many people are in one group OR the other (or both). I can set up a query "patients in raised_systolic_patients" but can't workout how do the rest of the query. (To get as far as I have I have set up a query to find the last systolic BP note and put this into a database and then quizzed this database to find the notes whose numeric is above a certain value and then queried that database to find pts with a note in that database).

There is not an elegant way to do this in current S6. The choices are :

  1. Have 2 queries.
    One finding patients A the other patients B. Append the two queries in the report to give a CSV C. C will have some patients twice. Use a report format that only shows each patient once. This can show the list and count as well. It has to be sorted by patient id but apart from that it is a normal format and could be used to print letters etc. I can send you a format that does this. This approach works with any number of general patient queries. In this case A would be something like report for patient with entry in "sysbp" B would be report for patient with entry in "diasbp"
  2. Use 3 queries to find who is in list A only, who is B only and who is in both. Combine all 3 and you have everyone exactly once. eg report for each patient with entry in "sysbp" without entry in "diasbp" report for each patient with entry in "diasbp" without entry in "sysbp" report for each patient with entry in "diasbp" with entry in "sysbp" Append these and your CSV should have everyone once only. This only really works for combining two groups.

Those are the current choices. To make this easier to do I plan to introduce a new feature "append into" which allows combining lists in an arbitary fashion, making all this a lot simpler (and faster).

Rik


Reporting on fictitious patients

Need to add: and reg_qualifier_type = Fictitious

Title "fictitious patients"
Report for each Patient where
Patient.Registration_Type = "Non GMS"
and reg_qualifier_type = "Fictitious"
print Patient.Patient_Id, Patient.Title, Patient.Surname,
Patient.Forename_1, Patient.Forename_2, Patient.Other_Forenames,
Patient.NHS_Number, Patient.Date_Of_Birth

Searching on a phone no


I don't think you can search on phone number, it does seem a bit of a strange omission.
As a workaround you could set up a query.

Report for each Patient where 
Patient.Phone like Runtime PHONE 
print Patient.Patient_Number, Patient.Title, Patient.Surname, Patient.Forename_1, patient.phone, Patient.patient_id 
sort by Patient.surname, Patient.forename_1 ; 

No need for a report format.
If you run this query and enter, say "0121 44%" you will get a list of matching patients in the CSV window. Right-click the patient you want and they will be loaded into 6000 as if you had used F3.

Might not be practical, but if you really can't figure out who it is it could be useful occasionally.

Rik


A full patient report that also prints scanned letters in the patients record

You need the right query to add to a new report in that folder (any folder) namely

Title "Word Processing docs for any patient" 
Report for each Entry where 
       Entry.Attrib Type = 99 
           and Entry.Patient Id = Runtime PATIENTID 
print Entry.Attrib String ; 

Then you can try the printer.doc that is already in the formats/docs directory. This will print .DOC word attachments. But if your scans are not OCRed but are TIF, XIF or PDF you will want the updated Printer.doc.


Find a given code after another code in the patients notes

Can any of you guys'n'gals get your head around this one? I tried John Robinson's excel format too, but couldn't get it to output the start date for each item...

  1. patients with hypertension and start date - no problem > list
  2. form list...
  3. I want to check if the following were done within 6 months of diagnosis
    • chol level
    • ecg
    • smoking history

Converting dates from s6000 format to excel format is no problem

Any suggestions?

Brad Cheek

Hi Bradley

That is quite tricky I have to say.

I once wrote something in s6format that would find a given code after another code in the patients notes. I could dig that out for you, but you may be more familiar with the excel route.

Rik

OK then here it is.  
I'll explain how it works and my reservations about it. This format will find existence of one read code dated after another. Both need to be present for one patient and the date for one of the two (the second one specified naturally) must be later - any time later. The two codes are specified in report free text.eg P317. R40.. 2 read codes separated by a space Only the first 2 words are used so the rest were just comments to myself. It uses 2 queries and a sort/detail approach to the format but the principle is similar to that described in last mail.Go through patients one at a time and through notes sorted into order. 
Title "Patients with 2 codes"
Report for each Patient with
       Entry where Entry.Read_Code = "P317."
with Entry where Entry.Read_Code = "R40.." ;
Title "Notes for both codes"
Report for each Entry where
       Entry.Read_Code = "P317."
       or Entry.Read_Code = "R40.."
sort by Entry.Patient_Id, Entry.Start_Date ;
I draw your attention to the comments in the format : There are 2 calc fields IsFirstCode, and IsSecondCode as well as a third with a lower calc order that checks
the LAST IsFirstCode. Main section prints when current code is CODE2 and last code was CODE1.
 For this to work notes must be date sorted. Codes used are specified in report free text eg "1234. 5678." Calc fields in the report header extract the first 2 words in free text to use as codes.
Some other notes I made to myself at the time:
 
This doesn't work with between, which is a bit of a limitation.
which is because the actual code found could be any one of a series
[It] Does a [one to one] match on code in csv to code extracted from free text.
[To handle betweens it].Would need to split free text into two hypenated codes and do a isBetween check
 
To be really practical this would need to cope with one of a range of codes dated after another from a range of codes. The read codes could in fact be hard coded for your purposes. The date difference will probably need to be hard coded. I am not even sure how you would do this calculation but converting two dates to days then subtracting to give 150 odd days is probably the way forward.
 
Look in the FPI to see a dump of all the calculated fields which may help understand it. This applies to any format that has been edited since about v3.0. These are only copies of the actual calculations written out by the formatter for convenience. In other words you can't edit them via the FPI.
 
a few examples
6=CALC->FIRSTCODE=word(report->report_free_text,1)
this gets the code to look for from the free text
later I tried to allow "abc-xyx" style ranges in free text but the hyphen is seen as a new word which complicates things.
 
11=CALC->ISFIRSTCODE=first((entry->read_code+"....."),5)=first((calc->firstcode+"....."),5)
This is how it tries to match current code to the target code.
 
7=CALC->LASTTIMEWASFIRSTCODE=calc->isfirstcode
This calc just copies calc->isfirstcode value and has a sequence number (not shown in FPI) that is earlier than calc->isfirstcode so in effect it remembers that calculation just before calc->isfirstcode gets overwritten with a new value.
 
Detail 3 section is the one that prints in the event of a "hit".
The filter on this is a calc too but these do not show in the FPI
it is
calc->issecondcode.and.calc->lasttimewasfirstcode
 
So if it finds the second target code and the last record was the first code it prints.
 
To extend this to work on dates it would need to also check the dates elapsed here. There are already calcs
CALC->THISCODEDATE=entry->start_date2
and
CALC->LASTCODEDATE=calc->thiscodedate
that do this but they are not used in the calculations at the moment.
 
If you then want to find multiple codes that all must be present you are going to need to extend it to have several sets of these calculations.
Instead of a hit immediately printing the section you will just want to set a flag to say "codeXfoundInDateRange".
Repeat this whole idea for "codeYfoundInDateRange" and then the section filter would become
calc->codeXfoundInDateRange and calc->codeYfoundInDateRange
 
The main problem I can see is that with only two codes you only ever need to look back one record to do the comparing.
With 3 codes you may need to look back 2 records and so on. Also with 2 records, where one must be later than the other, you can rely on there being a pair of the codes next to each other, even if there are multiple copies of one code.
 
eg
 
1111
1111
1111
2222 <---- hit goes off here because last one was 1111
2222
 
 
You only need to remember the last value because there will always be adjacent pairs.
This is not the case if you have 3 or more codes to worry about.
 
1111
1111
2222   
2222
2222
3333 <-- this needs to compare back 4 records but it could be more that 4 in theory
It is not adjacent to any 1111
 
So remembering the 1 or 2 previous records won't work. You need to detect each code specifically and remember it for the whole of that patients record. Only when you hit the end of the patient can you then check all the stored values to see if they all went off. Then print a hit and clear the codes for the next patient.
 
The Sort (patient) footer is probably the place to test all the checks you have set up for each Detail (note) record. I think this goes off after all the last note. This approach is more like the suggestion in the last mail. It all starts to get very complicated and it is already pushing the limits of what you can do with s6format. There are no "global variables" which is really what you want.
 
My feeling is that unless you are pretty confident with s6format it may be easier to start again in excel. I'm not 100% sure what you want to achieve is actually possibly in s6format although it most likely is if you try hard enough ! At least in excel you can pretty much guarantee that what you want to do is possible although it may actually be more difficult to get started. If you work from with one of John Robinson's formats you will have code that knows how to do the basics such as load the correct CSV file etc. Converting the dates may have to be involve a manual step, so the final version may not not be as slick as if you had an s6format that does the work. However excel has other advantages such as save to HTML.
 

A lot to take in there, but anyway good luck and let me know how you get on. Obviously if this problem can be cracked in any sort of reusable way it would be very useful.

Rik


100 most commonly used read codes

You can do this for S6 with a combination of reports and excel macros, but need to be fairly confident at excel. 1. dump the read code for every entry into one CSV (sorted by read_code).
    report for each entry where entry.read_code <> ""
        print entry.read_code 
        sort by entry.read_code;
 You may to also print the Entry.Term if you want to see what the codes are. You might want to limit it to the last year or so keep it small but statistically ok. 2. Use an excel macro to count the similar codes. As it is sorted, they will be vertically next to each other. So you can write something that compares one cell to the previous and increments a running total if same, or resets to 1 if different.This will give you eg : a234. 1
 a234. 2    a234. 3    <- running totala235. 1 a236. 1a236. 2  
 3. Remove duplicates, leaving the highest running total. This can be done by deleting or hiding all lines where the next line is the same read code, which will leave the item with the highest running count, and delete the others eg :1234. 3  <- 1, 2 deleted1235. 1  <- none deleted1236. 2  <- 1 deleted  4. Sort by second column. 5. Take first 100 All this is complicated by the fact that read codes are really grouped into similar concepts. eg anything that is represented by a range of read codes will be counted separately. You could modify the excel to compare read codes only up to 3 digits.  Something very similar to this will be built into the reports for TPS.You can get a CSV with a list of all read codes and a count for each one. Then sort this by the count on the CSV view, just by clicking on the count column. You can get counted "breakdowns" of anything you can report on. S6 only allows one count on a query. TPS will give you a count of the unique values of whatever you print. eg
report for each 
patient print registered_gp
would give a counted break down of all patients in the query registered to each gp (by gp initials) "AA", 2356"JS", 2189 It doesn't have to be all patients, it could be all in an age band or all asthmatics. or
report for each issue
print added_by 
 will break down issues by who added them. You can have any range of codes or dates, anything you can do in a query. Rik


Report for more than n repeats

Report for each Authorisation where 
       Authorisation.Auth_Type = "Repeat" 
           and Authorisation.Last_Issued_Date > -6M 
           and Authorisation.Current_Auth_Flag > True 
           and ( Authorisation.Number_Outstanding > 0 
       or Authorisation.End_Date > TODAY ) 
with Patient where Patient.Registration_Type = "Full GMS" 
sort by Authorisation.Authorisation_Id, Authorisation.Surname, 
Authorisation.Forename_1 ; 

Use the format "Note freq. N or more (N set in report free text)".


Reporting on entries in unlinked

Although the definition of an unlinked entry is indeed "Problem_Id = 0" this will, as you have seen, include problems themselves because they are not linked to anything. Technically speaking problems are unlinked.

A confusing aspect here is that Entry.Entry_id is always the id of that entry, whatever sort of entry Entry.Problem_id is always the id of the PARENT problem

The Entry.Problem_Id for a Problem is NOT the id of the problem entry itself.
It is the id of what would the problem would be linked to if super-problems were allowed, hence currently always 0.

The way around this is to specifically exclude problems

Report for each Entry in "unlinked_1" where
Entry.Problem_Id = 0 
and entry.entry_type <> "Problem" ;

Try that, or if you really only want Notes rather than Problems, Issues, Reminders
etc

Report for each Note in "unlinked_1" where
Note.Problem_Id = 0 
; 

ould do


Thanks particularly to Rik Smithies for his advice, much of the wisdom that appears here is his!