Custom fields analysis

[jospore]

Hi Im trying to use the patient List under the "reports and templates" section for analysis- how do I add custom fields from patient records and custom fields from clinical notes.
I wanted to do a general analysis of the patient nationality, sex,age, complaints, etc under one table or does COv4 have this tailored analysis function already?
txs
jose

[William]

Hi Jose

Thanks for the post. To insert custom fields into the patient list please do the following:

[1] Go to the Report and Document Templates section in ClinicOffice
[2] Right click on the Patient List report
[3] Select Report Source
[4] Go to the SQL Source tab
[5] Delete everything that is the main field on this window
[6] Copy and paste the code below into this main field

Code: Select all

select per.id, per.code, per.lastname, per.firstname, per.title, per.middlename, per.knownas,
per.company, per.address1, per.address2, per.address3, per.towncity, per.county, per.postcode, per.country,
per.telephone, per.worktel, per.mobile, per.fax, per.email, per.dob, per.consent, per.sex,
per.insuranceref, per.createdon, per.updatedon, per.registrationdate,

(coalesce(clinic.prefix, '') || cast(per.code as varchar(20))) as _patientcode,
per.clinic_id, clinic.name as _clinic_name,
per.patcat_id, patcat.name as _patient_category,
per.patstatus_id, patstatus.name as _patient_status,
per.referredcat_id, referredcat.name as _referred_by,
per.maritalstatus_id, maritalstatus.name as _marital_status,
per.occupation_id, occupation.name as _occupation_name,
per.practitioner_staff_id, staff.knownas as _practitioner,
per.gp_per_id, rtrim(gp_per.lastname || ', ' || coalesce(gp_per.firstname,'') || ' ' || coalesce(gp_per.title,'')) as _gp_name,
per.invrecip_per_id, rtrim(invrecip_per.lastname || ', ' || coalesce(invrecip_per.firstname,'') || ' ' || coalesce(invrecip_per.title,'')) as _invoice_recipient,
per.insurance_per_id, rtrim(insurance_per.lastname || ', ' || coalesce(insurance_per.firstname,'') || ' ' || coalesce(insurance_per.title,'')) as _insurance_company,
per.refby_per_id, rtrim(refby_per.lastname || ', ' || coalesce(refby_per.firstname,'') || ' ' || coalesce(refby_per.title,'')) as _referred_by_person,
per.familyhead_per_id, rtrim(familyhead_per.lastname || ', ' || coalesce(familyhead_per.firstname,'') || ' ' || coalesce(familyhead_per.title,'')) as _family_head,
per.updatedby_staff_id, up_staff.knownas as _updatedbystaff,
per.createdby_staff_id, cr_staff.knownas as _createdbystaff,

@FIELDS:custom_patient_fields

from per

left join clinic on (per.clinic_id=clinic.id)
left join patcat on (per.patcat_id=patcat.id)
left join patstatus on (per.patstatus_id=patstatus.id)
left join referredcat on (per.referredcat_id=referredcat.id)
left join maritalstatus on (per.maritalstatus_id=maritalstatus.id)
left join occupation on (per.occupation_id=occupation.id)
left join staff on (per.practitioner_staff_id=staff.id)
left join per as gp_per on (per.gp_per_id=gp_per.id)
left join per as invrecip_per on (per.invrecip_per_id=invrecip_per.id)
left join per as insurance_per on (per.insurance_per_id=insurance_per.id)
left join per as refby_per on (per.refby_per_id=refby_per.id)
left join per as familyhead_per on (per.familyhead_per_id=familyhead_per.id)
left join staff as up_staff on (per.updatedby_staff_id=up_staff.id)
left join staff as cr_staff on (per.createdby_staff_id=cr_staff.id)
left join custom_patient_fields on (custom_patient_fields.per_id=per.id)

where (per.ispatient=true and per.deleted=false)

[7] Click OK

You should now see your custom patient fields on the Patient List report.

[jospore]

Thanks William,

Actually, I wanted to extract " main area complaints and no complaints" from the case files custom fields and analyse my patients based on common complaints and determine any trends.

For the moment I am interested in analysing new patients and their complaints profile together with their gender, age, occupation, area where they live etc

Questions
Can I use data from custom fields entered in each case file (no) with the rest of the patient data (patient record) without having to do a double entry?

hope my question makes sense
jose

[William]

Hi Jose

You can pull data from the custom case notes fields and insert them into a patient list. The only problem is that if a patient has more than one case note then the patient's record will appear up more than once. The reason is that ClinicOffice will have to display the additional information.

If you still wish for this to be done it might be wise to make a copy of the patient list by right clicking on the report in the Reports and Templates section of ClinicOffice and selecting Copy. Rename the patient list to something like "Patient List with Case Notes". You can rename the report by again right clicking on the report and selecting Rename.

The SQL source for such a report is shown below:

Code: Select all

select per.id, per.code, per.lastname, per.firstname, per.title, per.middlename, per.knownas,
per.company, per.address1, per.address2, per.address3, per.towncity, per.county, per.postcode, per.country,
per.telephone, per.worktel, per.mobile, per.fax, per.email, per.dob, per.consent, per.sex,
per.insuranceref, per.createdon, per.updatedon, per.registrationdate,

(coalesce(clinic.prefix, '') || cast(per.code as varchar(20))) as _patientcode,
per.clinic_id, clinic.name as _clinic_name,
per.patcat_id, patcat.name as _patient_category,
per.patstatus_id, patstatus.name as _patient_status,
per.referredcat_id, referredcat.name as _referred_by,
per.maritalstatus_id, maritalstatus.name as _marital_status,
per.occupation_id, occupation.name as _occupation_name,
per.practitioner_staff_id, staff.knownas as _practitioner,
per.gp_per_id, rtrim(gp_per.lastname || ', ' || coalesce(gp_per.firstname,'') || ' ' || coalesce(gp_per.title,'')) as _gp_name,
per.invrecip_per_id, rtrim(invrecip_per.lastname || ', ' || coalesce(invrecip_per.firstname,'') || ' ' || coalesce(invrecip_per.title,'')) as _invoice_recipient,
per.insurance_per_id, rtrim(insurance_per.lastname || ', ' || coalesce(insurance_per.firstname,'') || ' ' || coalesce(insurance_per.title,'')) as _insurance_company,
per.refby_per_id, rtrim(refby_per.lastname || ', ' || coalesce(refby_per.firstname,'') || ' ' || coalesce(refby_per.title,'')) as _referred_by_person,
per.familyhead_per_id, rtrim(familyhead_per.lastname || ', ' || coalesce(familyhead_per.firstname,'') || ' ' || coalesce(familyhead_per.title,'')) as _family_head,
per.updatedby_staff_id, up_staff.knownas as _updatedbystaff,
per.createdby_staff_id, cr_staff.knownas as _createdbystaff,

@FIELDS:custom_patient_fields,
@FIELDS:custom_case_fields

from per

left join clinic on (per.clinic_id=clinic.id)
left join patcat on (per.patcat_id=patcat.id)
left join patstatus on (per.patstatus_id=patstatus.id)
left join referredcat on (per.referredcat_id=referredcat.id)
left join maritalstatus on (per.maritalstatus_id=maritalstatus.id)
left join occupation on (per.occupation_id=occupation.id)
left join staff on (per.practitioner_staff_id=staff.id)
left join per as gp_per on (per.gp_per_id=gp_per.id)
left join per as invrecip_per on (per.invrecip_per_id=invrecip_per.id)
left join per as insurance_per on (per.insurance_per_id=insurance_per.id)
left join per as refby_per on (per.refby_per_id=refby_per.id)
left join per as familyhead_per on (per.familyhead_per_id=familyhead_per.id)
left join staff as up_staff on (per.updatedby_staff_id=up_staff.id)
left join staff as cr_staff on (per.createdby_staff_id=cr_staff.id)
left join custom_patient_fields on (custom_patient_fields.per_id=per.id)
left join custom_case_fields on (custom_case_fields.per_id=per.id)

where (per.ispatient=true and per.deleted=false)

This will display both custom fields from the patient's record and the case notes. Please let us know if you require anything else.

[jospore]

Thanks william,

after pasting the new codes an error appeared "syntax error at or near "from" "

jose

[William]

Hi Jose

The code seems to be working back here fine. Please make sure that you delete everything that is under the SQL source and click the SELECT ALL on the code that is above and re-paste it back in. This should work after pressing OK. The only reason why it may not is if you do not have custom case notes, in which case your copy of ClinicOffice will not recognise the custom case notes field and will throw a similar error.

[jospore]

Hi William,
The custom fields that appear at the patient record list and templates/patient list only show the custom list from patient records.

a)There are 3 where i have custom fields 1) patient record 2) patient case 3) patient session.
b)There are 2 two ways of seeing these custom fields 1) patient record list 2) templates/patient list.

Would it be possible to choose which custom field is shown in scenario (b)?

jose

[Support]

Hi Jo,

>> The custom fields that appear at the patient record list and templates/patient
>> list only show the custom list from patient records
The code that William provided correctly pulls out CUSTOM PATIENT fields and CUSTOM CASE FIELDS.

>> There are 3 where i have custom fields
>> 1) patient record
>> 2) patient case
>> 3) patient session.
SESSIONS and CASES are two completely different things and the custom fields for those records are stored in different tables. You only mentioned custom CASE fields before (not SESSIONS) so William didn't provide you with the code to include SESSIONS.

However, it's simply not possible to present this data on one tabular report. To understand why, you first need to understand the relationship between the records.

A patient can have ANY NUMBER of CASES and a CASE can contain ANY NUMBER of SESSIONS. This is represented in ClinicOffice by a TREE structure e.g.

Patient
|- Case 1
| |- Session A
| |- Session B
| |- Session C
|- Case 2
| |- Session D
| |- Session E
| |- Session F

Here there are SIX different SESSION records, 2 different CASE records and 1 PATIENT record, a total of 9 different records (although it can potentially be unlimited). How can this be displayed on one line in a tabular list report? Which CASE would you display next to the Patient's name and which SESSION would display? You would have to display a separate line for every SESSION and every CASE which means each patient would appear multiple times. This would then make totals at the bottom of the report meaningless.

Please can you explain in detail exactly what it is you're trying to achieve? If possible, please also provide us with a complete mockup of the report that you want and then our technicians can take a look to see if it's something that we can do for you.

Many thanks!

[jospore]

Hi Support Team

Thanks for the explanation, I understand the complexity of the situation.

My first aim is to analyse my patient profile with their complaints - this can now be done thanks to william from the patient record list. We realised however that it more appropriate for each therapist to document these additional information under "enter session notes / case # / custom fields" for new patients and old patients.

For administrative function, we found it easier to use patient record list as a means of checking patients data, and to alter/ammend each patient record by clicking on it then going back to patient record list and checking the remaing data.

Purpose of these patient record list
1) we can easily see which records are incomplete and amend quickly by clicking on them (this cannot be done with reports)
2) We've had patients who unsubscribed to our newsletter and for now, we have deleted their emails and classified them as inactive. we can then sort patients and choose active patients only and export them for emailing. In this way we can update our email list easily.
3) We only use one case # record for each patient and all sessions are in one record. If we can see this "Case #/custom field" shown in the patient record list then it will be easier to amend missing data before analysis.
4) We have data under "enter session notes/ case #/ session custom fields" for patients who receive trigger point therapy. we would like to analyse common trigger point areas for certain groups of patients.

Based on your feedback (3) maybe its possible from our database since 99% of patients have only one case # record.
Do you have a suggestion for (4) which will mean as you point out " You would have to display a separate line for every SESSION and every CASE which means each patient would appear multiple times". It is important for us to analyse average no of trigger point per patient and profile of problem areas.

With the complexity of this case, I would be happy with a report that will provide us with data for analysis.

jose

[Support]

Hi Jose,

I'm afraid that due to the complexity of what you're asking for, and the fact that it is highly specific to your database (which we haven't seen) we don't really understand what you're asking for. Please can you do the following :-

[1] Create a complete mock-up of the report that you require (in Word, Excel or something similar) to show us exactly what you're trying to achieve. This should include the fields that you want and where these fields come from (PATIENT, CASE or SESSION) records.

[2] Take a backup of your database and upload it to our server. Instructions to do this can be found here :-
viewtopic.php?f=29&t=269

Once we have both of the above, we'll take a look and see if something bespoke can be done to meet your needs, however there will most likely have to be a charge as it sounds like there will be quite a lot of work involved.

Many thanks.

[Alfred]

Thanks for the examples. I got them working, and I can see all custom fields in my reports.

Is it possible to
a) see only *some* of the custom fields?
b) filter by one of these custom fields?

Much appreciated!

[Support]

>> Is it possible to
>> a) see only *some* of the custom fields?

To add a single field, replace the line :-

Code: Select all

@FIELDS:custom_patient_fields,

with

Code: Select all

custom_patient_fields."my_field_name",

>> b) filter by one of these custom fields?
All fields in the grid are automatically included in the SEARCH form. You will need to look under "Additional Search Fields".

[jospore]

Hi Support Team,

I have found the custom field list very useful in patient list report, could i get custom field list in appointment list report?

jose

[Support]

Yes - it follows the same convention as our previous post.

To add a single custom field, add the following line to the field list :-

Code: Select all

custom_app_fields."my_field_name",

To add ALL custom fields, add this line to the field list :-

Code: Select all

@FIELDS:custom_app_fields,

[jospore]

Im not sure I understand
the "Appointment List" report, after doing a right click and within "SQL Source" only shows

select * from view_appointments

where do I add the line that you suggested?

custom_app_fields."my_field_name",