Custom fields analysis

[William]

Hi Jose

You can use the SQL below to display any custom appointment fields you have into the appointment list.

Code: Select all

select
app.id, start, finish, app.per_id,

case
  when (description is not null) and (description '') then description
  else per.lastname || ', ' || per.firstname || ' with ' || staff.knownas || ' [' || apptype.name || ']'
end as _description,

trim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' || coalesce(per.title,'')) as _patient_name,
staff_id, apptype_id, appstatus_id, app.clinic_id, room_id,
staff.knownas as _staff_name,
apptype.name as _appointment_type,
appstatus.name as _appointment_status,
clinic.name as _clinic_name,
room.name as _room_name,
app.createdon, app.createdby_staff_id, cr_staff.knownas as _createdby,
app.updatedon, app.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

@FIELDS:custom_app_fields

from app
left join staff on (app.staff_id=staff.id)
left join per on (app.per_id=per.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
left join clinic on (app.clinic_id=clinic.id)
left join room on (app.room_id=room.id)
left join staff as cr_staff on (app.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (app.updatedby_staff_id = up_staff.id)
left join custom_app_fields on (custom_app_fields.app_id=app.id)

Hope this helps.

[jospore]

Hi William,
When I deleted the contents of SQL Source and pasted your code, I got "syntax error at or near "select"

jose

[William]

Hi Jose

Thanks for letting me know. From the error you have mentioned it sounds like you may have pasted the SQL I gave you in twice. Please could you go back into the SQL source, left click anywhere in the window and press the CTRL and A keys together and then delete all the text again. Then re-copy in the SQL from my previous post. Please check the SQL by scrolling down gradually and make sure there is not another "Select" below the sentence that begins with "left join custom_app_fields". You should find that the error no longer appears.

If the error is still appearing, please select the SQL code you have present in the SQL source window and paste it back onto this forum thread. Many thanks.

[jospore]

select
app.id, start, finish, app.per_id,

case
when (description is not null) and (description '') then description
else per.lastname || ', ' || per.firstname || ' with ' || staff.knownas || ' [' || apptype.name || ']'
end as _description,

trim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' || coalesce(per.title,'')) as _patient_name,
staff_id, apptype_id, appstatus_id, app.clinic_id, room_id,
staff.knownas as _staff_name,
apptype.name as _appointment_type,
appstatus.name as _appointment_status,
clinic.name as _clinic_name,
room.name as _room_name,
app.createdon, app.createdby_staff_id, cr_staff.knownas as _createdby,
app.updatedon, app.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

@FIELDS:custom_app_fields

from app
left join staff on (app.staff_id=staff.id)
left join per on (app.per_id=per.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
left join clinic on (app.clinic_id=clinic.id)
left join room on (app.room_id=room.id)
left join staff as cr_staff on (app.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (app.updatedby_staff_id = up_staff.id)
left join custom_app_fields on (custom_app_fields.app_id=app.id)

the error says "syntax error at or near "from"

[William]

Thanks for posting the SQL through and also for giving us the error that appeared. That error is usually only thrown if there is no custom appointment fields present on your database.

Did you want the custom fields from the appointment editor or is it the custom fields from the case notes again?

If it is the custom fields from the appointment editor please can you check to see what the field names are. You can do this by following the steps below.

[1] Go to the Tools menu
[2] Click on the Advanced DB Operationbutton
[3] Delete any text that might be present in the Advanced Database Operation window
[4] Copy and paste in the code below

Code: Select all

select * from custom_app_fields_design

[5] Click the Execute button

Please let us know the values for under the fieldname column if there are any. If no values appear then it means that there is no custom fields present on the appointment editor. If this is the case then you can add your custom appointment fields and then use the SQL I gave you.

[jospore]

there is no custom fields present on the appointment editor

I have no custom fields on the appointment editor but I have custom fields in:
1) Case Editor
2) Session Editor
3) Patient Record Editor

I would like to see any/all of the custom fields in the appointment list report, if possible

With the appoint list report, I am able to see eg all the patients in the last 6 months and how many times they have been to visit and whether they saw the osteopath or massage therapist. If we can see the custom field from the patient record editor, then we easily see our marketing notes that we have for each patient- a record of whether we have recently sent them an sms or a follow up letter.

jose

[William]

Thanks for letting us know. That would explain why the error was being thrown because you did not have any custom appointment fields which is what the SQL I gave you would have displayed. However the SQL below will add the custom patient fields to the appointment list.

Code: Select all

select
app.id, start, finish, app.per_id,

case
when (description is not null) and (description '') then description
else per.lastname || ', ' || per.firstname || ' with ' || staff.knownas || ' [' || apptype.name || ']'
end as _description,

trim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' || coalesce(per.title,'')) as _patient_name,
staff_id, apptype_id, appstatus_id, app.clinic_id, room_id,
staff.knownas as _staff_name,
apptype.name as _appointment_type,
appstatus.name as _appointment_status,
clinic.name as _clinic_name,
room.name as _room_name,
app.createdon, app.createdby_staff_id, cr_staff.knownas as _createdby,
app.updatedon, app.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

@FIELDS:custom_patient_fields

from app
left join staff on (app.staff_id=staff.id)
left join per on (app.per_id=per.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
left join clinic on (app.clinic_id=clinic.id)
left join room on (app.room_id=room.id)
left join staff as cr_staff on (app.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (app.updatedby_staff_id = up_staff.id)
left join custom_app_fields on (custom_app_fields.app_id=app.id)
left join custom_patient_fields on (custom_patient_fields.per_id=per.id)

Please let us know if you have any problems with this.

[jospore]

Great, Txs William, works perfectly.

Now we can see our last 6 months of patients and any recent marketing comments so we dont duplicate our messages or check if a patient does not want to be contacted.

jose