System 6000: hints and tips for reports

Home > System 6000 > Hints and tips

Search

Report for patients who have not been referred

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

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

“report for each note…”.

What the problem boils down to is:

  • Referral coded notes without an attribute of type <letter>.
  • Attributes are somewhat unhelpfully given numbered codes for the types. Letters are attrib_type “99”. A pick list that says “Letter” would be much better than this cryptic number.

Try this

Title “Referral code but with no attached letter”Report for each Note whereNote.Read_Code Between “8H4..” and “8H5Z.”and Note.Start_Date in L3Mwithout Attribute where Attribute.Attrib_Type = 99with 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 inthe CSV window to load that patient straight into Clinic and examine them. Ifyou really need to you can turn this list of notes into a list of patients byadding

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 fullpatient details eg phone number.

S6 Reports and dates in Excel

John Robinson’s Excel report formats automatically handle CSVs neatly butsometimes 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 asdates. S6 dates in CSVs are in a format called ANSI dates, which is YYYYMMDD eg19990908 for 8/9/99. Excel thinks these are just numbers. If you try to reformatthe 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 thecolumn to convert first. Excel then gives you a mini wizard with 3 steps. Thefirst two are to do with delimiters for separating multiple fields. Since youare 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, thenFinish. The column should be converted to display DD/MM/YY. You can thenreformat to other date styles, and the full YYYY is preserved.

Some caveats:

  • Dates before 1900 do not seem to be recognised. Excel just leaves these as they were eg 18150618.
  • S6 uses 0 for dates to mean “no date”. These get converted to 00/01/1900, which is a bit strange, but reveals what Excel’s base date must be.
  • I have once seen Excel offer to do this conversion as the file is loaded. Maybe it does it with .DAT or .TXT files, but not .CSV, which it already knows are comma separated. The same trick should apply if you are prompted on opening, but you will be converting the whole file, not just one column, so select “,” as a delimiter.

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 isnot actually stored in the database, it’s calculated on the fly from DOB.

report for each patient in “fileX”where patient.patient_id <> 0 # paddingsort 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 aboutadding a “Print” line. It is not needed. No Print line means”give me the normal fields”. Only use Print if you want to see extrafields that are not in the normal default list. And when you do this bear inmind you only get what you specify, so printing one or two fields will actuallygive you a lot less than the default set. What normally happens is people missout the crucial patient_id (the internal patient identifier) so the formatcannot tell patients apart. The Query Advisor will kick in and warn you againstdoing this, but if in doubt don’t use Print.

“Last high result” reports

This one is an old chestnut. “Last” does have a conceptual hurdlethat most people stumble on at some point. Have a look at Custom/Example/Lastfor Rik’s last attempt to explain it.

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

Have a think about that. There really is a difference, and it is not just anMRL 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 wereactually 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 notto 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 ) whereEntry.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 5years. 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 ) whereEntry.Read_Code Between “4K21.” and “4K2z.”and Entry.Start_Date >= -5Yinto “5ysmear”;

then

Report for each patient with entry in “5ysmear” ;

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

Authorisations

On the subject of drugs, authorisations are the way to go to get full controland they are more complicated than plain Entries. The simplest thing is to useis Authorisation.Last_Issued_Date. This has always been maintained by thesystem, but you couldn’t report on it until recently. That should give youpeople who have actually been prescribed recently. This isn’t quite the same aspeople who are “currently on” a given drug, although it will besimilar in most cases.

If you did want the actual “current medication”,current_authorisation_flag is not really what you need. This field is used forseeing which of several Authorisations for a given treatment is the current one.Treatment being a higher level concept, that can have several authorisations atdifferent times, but all for the same drug (though possibly in differentformulations). So current flag means “most recent” of theauthorisations (ie. not superseded or discontinued), but not necessarily a”current medication”, because it may have expired. The actual rule forcurrent medication that matches the view in S6 is complicated and overkill formost purposes. Things can be current by date or by number for instance. Have alook 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 CSVlist. This is no good for examining the patients as a whole, but you can use itto mark the notes of all the patients with something unique (an admin code andspecific free text maybe). Then find those patients with a separate report,possibly putting them in to a database list at that stage for convenience. Thiswill 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. Anythingthat can be “runtime = VALUE” in a query can get substituted by thelist from the file.

report for each patient where patient.patient_number = runtime PATIENT_NUMBERadd 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 patientwith 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).

  • “into” creates a list in the database (only)
  • “in” uses a list from the database (only)

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’tspecify “into”. You can name it anything you like by renaming thequery, 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 bedone. 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 whereEntry.Attrib_Type = 99and Entry.Patient_Id = Runtime PATIENTIDprint 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 certainpatient.

Attachments are stored as attributes, 99 is the magic number for “thisis a document”, as opposed to say a drawing. This assumes one file perentry. S6000 1.3 forces only one document per entry, most practices only attachone per entry anyway. Run the query, pick a patient and you should get a csvwith 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 nowneeded is a report format that loads this csv and instead of printing the actualcsv, prints the files that are referenced in the csv. You should find a formatcalled PRINTER.DOC in report/formats/docs, which does exactly this. It is a Word97 format, and it loads and prints each file in a csv. Set this as the reportformat and do a repreview.

Scheduling reports

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

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

example for FP report

Util8=Full Patient Report|C:MEDAPPSS6000S6REPMAN.EXE folder=meditelfp RPN1=PATIENTID RPV1=$patientid

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

Util9=Super Summary|C:MEDAPPSS6000S6REPMAN.EXE folder=customsuper 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 thepatient’s id. Clinic has a variable called $patientid (or $PATIENTID) which isalways set to the value of the currently loaded patient. So you tell reportsthat 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 areminder. 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’vejust 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 whereIssue.Start_Date Between Runtime START and Runtime ENDwith 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 querycan be refined for any drug range, GP etc. Quick way to add it up is to open theCSV file in Excel. It will be on your C drive in c:meddatas6000reportcustomwherever.Once in excel just select all the rows by clicking on the “A” columnheading. 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 lineto

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 totalappears on the status line. Excel knows to ignore fields with text in. It wouldbe easy to change the standard Count format to total instead then you wouldn’tneed to load Excel and click the column each time, it would just print out. Youcould 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 towrite 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 forhackers only. Making it repeatable might also be difficult. And you would not beable to do further reporting on the group once it’s gone to excel. However thereare 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 willgive 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 amonth, that was discussed in AMUG News last year). I’ve played with this onlybriefly but it seems to work.

Report for each Patient wherePatient.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 secondto last digit of NHS number.

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

How it works:

  • % matches any number of any chars.
    (nothing to do with percent by the way, this just a wildcard character like “*” in DOS)
  • _ matches any one char.
  • So “2_” is 2 followed by any single char (and not more than one char – implies end of the string).
    “%2_” means any number that has 2 as second to last digit. You could take this to 1 percent by checking the third to last digit too (10% of 10%)
    …and Patient.NHS_Number like “%2__”
    and then build up to 5% with four more lines “%3__” or “%4__” etc.

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

eg Report for each Patient wherePatient.date_of_birth like “%02__” ;

Another idea is to sort by something that is “random” in the firstfew letters, eg forename, and take the first 5% from the top. You can’t take thetop 5% in reports and then put the results back in to another query, but youcould use that trick in Excel perhaps.

Report query on 5 or more drugs

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

  • You don’t normally need to deal with Treatments when reporting.
  • There is only one active authorisation per treatment so it amounts to the same thing.
  • For medication in reports think “Authorisation”.

No single authorisation “knows” about the others so there cannot bea field such as

Authorisation.Number_of_Authorisations.

(An authorisation knows about the number of issues authorised within itselfbut that’s different).

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

  • finding all current auths for all 60+ patients,
  • sort by patient id, so the auths are grouped,
  • then eyeball the results to see who has 5 or more.

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

ENTRYC.FP (Counts for lists of entries) shows “banded” counts ofentries 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 overviewof how many patients are on how many drugs. All patients are shown though, so itwill be a long list.

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

report for each authorisationwhere Authorisation.Current_Auth_flag = True # not discontinued, or supersededand ( 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 gmsand date_of_birth >= -60Y ;

(That’s not the dictionary definition of current medication, but good enoughif the intention is to review them anyway.) No need to Sort because the formatdoes 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 theworking and they can only cope with a certain total amount of entries (in thethousands). If it hangs forever or crashes you’ve given it too many. The wayaround 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 itwith just all the “A” patients anyway as it will be quicker eg

…and patient.surname between “A” and “B”

Searching for Consultant referrals

  • Take the csv file, open in Excel. Select Data menu then Pivot table report.
  • Choose next default to Excel worksheet.
  • Select the range of your data,
  • select next.
  • Drag the patient_id button into the data area of the table picture make sure it says count of ids,
  • drag the Refer_Con_Surname field into the row or column area
  • and to finish off, I suggest you put usually seen gp as the other row or col filed or as a page field then you can filter by GP.
  • NExt this or new worksheet and finish and the wizard is your uncle!

Searching for authorisations under a current problem

Title “Auths under current problems”Report for each Authorisation with Problem where Problem.Dormant = Falseprint Authorisation.read_code, Authorisation.problem_id ;

“With” implies there is something there (rather than nothing, as inthe 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 specialdummy 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 arelinked 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 = 0print 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 setoverride 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 specificit 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. Scriptdestination is not affected by this, so you can have one printer for those andanother for reports, and even a third for labels.

There is probably shareware to quickly change default printers, or it couldbe 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 notesdon’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 laterencounter/entry)

 Pat/ Pr E

and is

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

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

Pat|Pr|E

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

To find “anything” use the generic “entry” type. Notencounter in this case as encounters aren’t linked to problems, since they canspan several, eg:

Report for each Entry where Entry.Start_Date >= -6Mwith Problem where Problem.Read_Code = “E43..” ;

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

Being entries you may get multiple records per patient, which you can avoidby throwing away all apart from the last 1, since you are not interested in theentries 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 >= -6Mwith 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 wherePatient.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 think you can only do this in a formatting stage as you have realised.
    There is no way in an MRL query to compare the dates of two notes to each other or subtract them.
    You can only have dates relative to other dates, not to the dates of other notes.
    I think this rules out doing it with plain S6 queries.
  • It will need a new format creating.
    It is possible with calculated fields in an S6format format or you can certainly do it with excel. You can write VBA to do anything.
  • You need to be confident with VBA or willing to have a go !
  • If you are interested in trying I think the steps would need to be something like this :
  • You would a list of all the entries (hypertension plus the other 3 you want within 6M), sorted by patient id and date.
  • Presumably within 6 months means afterwards and you would not be bothered about coincidentally being done 6 months before.
  • This means the hypertension will be the first record of interest for a given patient.
    Run down all the records (cells) one by one.
    For each record see if the code is hypertension. If not go to next record until you find a hypertension. When you find one remember the patient id and the date.
  • You need another array of 3 flags (integers) for Found/Not Found for each of the 3 codes (or groups of codes) you are looking for. Clear this array when you get to the hypertension and for the next few records (assuming same patient id) check the read codes and see if dates are in 6M. If so set the flag for that code.
  • When you get to a different patient id you know there are no more entries for the last patient. Examine the array and print “last patient had all 3” or “last patient missed something”, as appropriate,  perhaps in an adjacent cell.
  • Keep going down the whole list.
  • Remember that printing results when the patient changes will not print anything for the last patient, so you need to treat that specially.
  • It is quite tricky to write a general purpose format to do this since it will need configurable read codes or probably ranges of read codes.

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.egreport for each patient print registered_gpwould 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. orreport for each issueprint 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” whereEntry.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” whereNote.Problem_Id = 0 ;

ould do

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

Exit mobile version