[William]

Hi Roelof

Thanks for pointing this out and you are correct after you have acknowledged an appointment it should trigger a message asking you if you would like to delete the linked recall. I have submitted this to be fixed and all being well in the next update it will address this issue.

[William]

Hi Liz

Thanks for letting us know. I am glad to hear that it is sorted and that it was a simple re-install.

Kind Regards

William

[William]

Hi Liz

Sorry to hear about this issue you have had. This tends to happen when the “ClinicOffice Database Service.exe” has been removed from the main installation folder. Nine times out of ten we have found this to be caused by an anti-virus program falsly detecting this executable as being malicious and so quarantines or deletes it. In the security world, it is known as a false-positive, as it has been detected wrongly.

The first step is to reinstall the program. This will not effect your license unless the Windows update has updated part of your network card and hence may change the Computer ID generated from your machine. The first link below will allow you to download and reinstall ClinicOffice and the second is on how to reset your license.

Download ClinicOffice v5:
Reset ClinicOffice License: https://pioneersoftware.co.uk/forums/topic/faq-204-clinicoffice-lost-its-license-and-needs-activating?view=all

If the anti-virus program is quarantining “ClinicOffice Database Service.exe” then it will do it again. In this instance you will need to go into your anti-virus program and remove this executable from the quarantine area and then add it the white list. The default location for this is “C:Program Files (x86)Pioneer SoftwareClinicOffice v5ClinicOffice Database Service.exe”. This way it will no longer detect it falsely.

If the anti-virus program has deleted it, the next step is to temporarily disable the active/live scanning (sometimes called a resident shield). After this, reinstall ClinicOffice and run ClinicOffice so that the database engine can start up. After ClinicOffice is up and running, add the “ClinicOffice Database Service.exe” to the white list of the anti-virus program, you may need to start the program’s live scan up again to do this.

Please make sure that your anti-virus is up and running again after you have finished this.

[William]

Hi Mesologie

Thanks for your patience. The development is coming on well for version 6, but parts of it have taken us a little longer than anticipated. Although we cannot give an exact date, it should be released this year.

[William]

Hi Kier,

Thanks for your post and apologies for the delay in replying. I’m afraid it’s impossible to give figures because it depends on the circumstances.

For example, network speeds will get the biggest boost from the improved engine (especially when retrieving larger datasets or when performing a large number of small DB transactions) since less network traffic is generated.

When the database is running locally (i.e. on the same hard disk as the program) then although the performance will be better, the increase will probably be less noticeable. A simple database lookup on one record might be 5% faster for example (which will be hardly noticeably) while retrieving 10,000 records across the network might be 100% faster.

[William]

Hi lucky96

Thanks for the post. I did a quick Google and found the article below which might help you if you are using an Android phone.

https://www.howtogeek.com/166610/who-needs-a-scanner-scan-a-document-to-pdf-with-your-android-phone/

The article also provides another link that leads to the Google Play and has other PDF scanning apps for your phone.

Hope this helps.

[William]

Hi Eniola

Sorry to hear of the issues you have been having. If you have not already done so, please run a check and repair on the database to see if this resolves the issue. The steps on how to do this are listed via the link below.

https://pioneersoftware.co.uk/forums/topic/faq-324-what-to-do-if-your-database-becomes-corrupt?view=all

If you are still having problems after this then we will need to take a look at your database and give you instructions on how to upload it to us.

Please let us know how you get on.

[William]

Hi jackinthebox

This message can appear if another process is either trying to access ClinicOffice’s database files or another process is trying to access the same port as its database engine. Another issue could just be that there is a configuration issue within the database.

The steps below should help to resolve the issue. Before proceeding, please make sure no one is logged into ClinicOffice.

1) Click the Windows Start button
2) In the Start Search box, type “services.msc” (without quotes) and press Enter
3) Locate the process called “ClinicOffice EDB Server“
4) Right click on it and select Stop
5) Close down the Service window
6) Click the Windows Start button again
7) Type in the Search box, “cmd” (without quotes)
8) Right click on the top option “Command Prompt” and select Run as Administrator
9) Click Yes to the next promt
10) Type into the Command Prompt window: SC DELETE EDBSrvr
11) Press the Enter key on your keyboard
12) Run ClinicOffice and Click OK to the next message

This should recreate the database engine service and all being well resolve the process error.

[William]

Hi Lucky96

Thanks for the question. If you are on our support plan you can call us on 01205 205500 and dial 2 for the support line or email us on support@pioneersoftware.co.uk and we will be more than happy to assist with anything relating to our ClinicOffice products.

For more contact information please see:-
https://pioneersoftware.co.uk/contact

For more information about the support plan please see:-
https://pioneersoftware.co.uk/cosp

You can also post any questions for free on our forum at:- https://pioneersoftware.co.uk/forums

Hope this helps.

[William]

Hi rhcrc

Thank you for posting about this – we’ve verified that there is an issue. No records have disappeared – it’s just that the side panel isn’t refreshing correctly and hence the records (although there) are not being displayed. Clicking the REFRESH button will display them.

We’ve deployed a hotfix into the latest 1092 and re-uploaded to our website. Please re-download the latest update manually from our website and install it on each computer which has this issue :-
https://pioneersoftware.co.uk/co-updates

Apologies for any inconvenience.

[William]

Hi Medcare2017

Thanks for the post. At present ClinicOffice can only define appointment colours based on its appointment type or its appointment status. The appointment colour will also change to grey if the appointment is booked at another clinic and is being viewed from a different clinic. The colour does not change based on whether it is in the past or future though. I can however add this to our wish list if you like.

[William]

Hi Stevema

Thanks for the post. The only time you need the Document module is if you are trying to save additional letters to the patient’s record, but this would be after the letter has already been merged. From what you have described you cannot even find the option to merge a letter template with the patient via the right click menu.

[attachment=0:2ic6ufxg]right_click_patient_record.png[/attachment:2ic6ufxg]
Looking at the image above, which part of the right click menu is not visible? Is it the first part where it says “Patient: ” or is it the “Run Report/Templates” or is it “Letters” part or is it just on of the existing letters is no longer appearing?

Depending on you answer it might just be a case that the grid SQL has somehow changed or it is one of the letter template’s SQL source at fault.

[William]

Glad that was of some assistance 😀

[William]

Hi scaramanga

Thanks for the post. I can understand why you would want patients to come for 09:15 or 09:30 rather than an obscure time like 09:05. Unfortunately if the appointments are booked in at 09:05 or 09:10 then the “AppTime” merge field in the text reminder will just simple pull that time in and has no way of rounding itself up to the closest 15 minute interval.

What you could try is to keep the diary in 5 minute intervals but to instead create the appointments for 15 minutes and have 3 appointments all booked into the same 15 minute period. By keeping the diary at 5 minute intervals, it is still easy to read the appointment descriptions and the appointment reminder will say 09:00. The diary will still indicate you are taking 3 different people within a 15 minute period. Granted the appointment is not 15 minutes but you know this and the patient will make no difference from the patient’s perspective.

The example screenshot below is what I had in mind.

[attachment=0:374k3312]three_appointments_stacked.png[/attachment:374k3312]
Hope this is of some help.

[William]

Hi a.mich

Apologies for the delay in replying and sorry to hear about your speed issues. The speed you mention sounds very abnormal – we have customers with literally tens of thousands of patient records and databases which are Gigabytes in size which perform really quickly.

Before we can make additional suggestions we will just need a little more information in order to diagnose possible problems to your speed issue. Information such as:-

Which version of ClinicOffice are you running, is it Professional or Server edition?

Does the computer that stores your copy of ClinicOffice contain the database as well?

If you computer does not have the database on it, are any of your machines on the network wireless or are they all wired via ethernet?

If any computers are wireless which ones?

If they are all wired do any of them run to a mains electric socket such as through a BT Broadband Extender for example?

Roughly how many patient records do you currently have loading?

Which anti-virus software are you using?

What processor is your PC running and how much memory (RAM) does it have? [This link will help you find this information:

By answering some of the questions from above, it will help us understand your setup a little better and we can then consider other possible problems to the slow loading speeds. If you can think of any other information that might help, that will be great as well.

Many thanks.

[William]

Hi Medsec24

Sorry to hear about the problems you have been having. If you are struggling to access the User Guide via the help menu in ClinicOffice, then you can download a copy of the User Guide via the link below.

ClinicOffice Userguide

The ClinicOffice User Guide is a PDF document and so will require a program such as Adobe Acrobat or Foxit to read it.

If you are still struggling to edit a letter template, then please let us know what part of the process you are having problems with.

[William]

Hi Clive

Just following on from what Mat has mentioned. It certainly does sound like you have a mobile number for a client that is no longer valid. It could be that the mobile number was entered incorrectly the first time, such as a typo, or the person you had the number stored for has since changed their number. Mobile providers will recycle existing mobile numbers that are no longer in use and hence a new person could be using that mobile number you still have stored on your system.

As Mat mentioned the mobile number is stored in the patient’s record. Best way to find it is by going into the Patient section of ClinicOffice going to search and typing in the number into the mobile search field. As mobile numbers can be stored in multiple formats you could always search using the percent % sign. So for example searching ‘%0789%’ (without quotes) might help you find a possible match on numbers that contain 0789 in them.

Hope this helps.

[William]

Hi Sacha

Apologies for the misunderstanding. If you have to keep the record of the original invoice and thus do a 100% split to the patient from the insurance company, this acceptable practice. As you rightly pointed out, it does have the downside of potentially being counted twice if you do run an invoice based report at the end of each month. This is why my colleague suggested the alternative.

The only work around that I can think of is to use the option in the Split Invoice screen which says “Use the same date as the original invoice”. This way the invoice amount will still remain in the same month. By zero rating the first invoice you have in effect voided it and so the original date could be used as that is when the original invoice was for and probably when the original appointment was on. There would be no change to the overall amount for that month as the first invoice is now set to zero.

Hope this is of some assistance.

[William]

Hi Rob

Thanks for the post. The search towards the top right will default to whatever section of ClinicOffice you go to first. So if you go to the finances section or the invoices section, the icon and search will change to search on the finances or invoices accordingly. If you click on the Patients button it will then automatically change to search on patients. You can also manually click on the icon and change the search type from there as well.

Does the icon and search type change when you go to another section of ClinicOffice i.e. when you go from finances to patients?

[William]

Hi Mesologie

Thanks for letting us know. We have since been able to reproduce the issue with not being able to drag an appointment to another date using the diary navigator. The developers have told me that they do have a fix, unfortunately there has been a load of other modifications since 1090, so before we can release a fix we need to test to make sure that no further issues have crept in from the other changes.

The only immediate solution at the moment is if you roll back to 1089. You can do so via the link below:-

1090 uses the same database version as 1089, so there should not be any complications with rolling back.

Apologies for the problems this has caused you.

[William]

Hi Dyerstchiro

Thanks for the question. This would be a local date setting in Windows. If you are running Windows 10 you can follow the steps below, which will correct the issue.

– Right click on the date/time in the bottom right hand corner of your screen
– Select Adjust Date/Time
– Click the link Change date and time formats
– At the top is a field First day of week, change this to Monday

After doing this you will need to close ClinicOffice and open it again. You should find that your weeks start on Monday again.

[William]

Hi Stevema and rhcrc

Sorry for the delay in coming back to you on this subject. We have been able to identify the appointment status message issue and have since fixed it. To apply the fix to your copies of ClinicOffice, please download the update again via the link below and install it any computers that are you having the issue on.

Please let us know if you are still having problems.

[William]

Hi Rob

Just one other thought is to use 10 minute intervals. As you know the diary can only have one time interval per view. So as to fit appointments of different lengths you need to use the highest common denominator; in this case it will be 10 minutes. Unfortunately that does have the knock on effect of increasing the viewing size of each appointments but it will at least display the appointments at their correct lengths whether it be 30 minutes or 40 minutes.

Hope this might be of some assistance.

[William]

Hi Lisa

If you are still struggling to find the created on field, what you can try is when you are looking at the patient grid, click the Grid Options button and at the top under the Data Source is a drop down menu. Change this to “Additional Lookups“; this will add multiple new fields to the grid including the created on. You should then be able to search on this field as well as the updated on.

[William]

Hi

Thanks for the post. You can only view emails that you are allowed access to. If you cannot see your colleague’s emails this means that you do not have access to their “Inbox” and “Sent” email folders.

To enable this you will need them to login to their account right click on either the Inbox or Sent folder and select Setup Access. If the folders should be viewed by everyone then they can select the middle option “This folder is PUBLIC“. If only a limit number of people like yourself should be able to view those emails then select the bottom option “The following Staff and/or Teams have access“. From here they will need to select their name and click the top arrow pointing right to put their name in the “Granted Access” box and select your name and do the same. They will need to repeat this for those select few staff members.

Hope this helps.

William

[William]

Hi. That is slightly confusing as 1920×1080 is more than sufficient usually. By default the quick transaction screen covers an area of 699×770 pixels and will usually allow up to 5 lines. Just to help us troubleshoot the issue, would it be possible to let us know what environment you are running ClinicOffice on either of the machine i.e. Windows running on Parallels or normal Windows desktop? Also what is the DPI on your desktop? You can find this out by right clicking on the desktop and selecting Personalise and then Display on the right hand side.

[William]

Hi

Just following on from the last post in the update build 1083, which is soon to be released, there will be an additional option for the diary that can mark the text for an appointment red if the patient is in debt. This can be enabled via the Diary Options -> Visual Settings -> Indicate account balance with caption colouring.

Hope this helps.

[William]

Hi Glen

Very sorry that no one replied to your second post. It really is down to personal preference. I guess in an ideal situation the treatment/appointment notes remain in the Session Notes and this would apply to the first appointment as well as subsequent appointments. Leave the Case Notes for the medical history side. This way a session will always cover an appointment and a case will serve as an umbrella which all sessions go underneath. However as mentioned it really is down to personal preference and if it is working for you to enter initial appointment into the case notes then that is fine.

Again sorry for the very late reply and hope this helps to answer your initial questions.

[William]

We are still trying to reproduce the issue mentioned above. To aid us in this would it be possible to let us know which version and build of ClinicOffice that you are running? To find this information please go to the Help menu/tab in ClinicOffice and click the About ClinicOffice button. Also would it be possible to let us know the type of printer that you are using as well?

One test that you can run on your system is to install a PDF Printer driver. You did mention that you have exported the document to PDF but the PDF Printer works slightly differently in that you click on print and select the PDF Printer program instead and then save it. The PDF Printer works on the same principle as a standard printer.

If possible please download and install a small program called “BullZip PDF Printer” and when printing your multi-page document select to print to BullZip PDF Printer. Does this print all pages or only the first one?

Click here to download BullZip PDF Printer

Many thanks and look forward to hearing your responses.

[William]

Hi Jakee

Thanks for posting that information. Unfortunately we are currently struggling to reproduce this issue back here, although we have been trying multiple environments and databases. Would it be possible to upload a copy of your database to us? This way it will help us to reproduce the problem and then find a fix to the issue.

For instructions on how to upload a copy of your database please follow the link below:

https://pioneersoftware.co.uk/forums/topic/faq-301-how-do-i-upload-my-database-to-your-ftp-server?view=all

Many thanks.

[William]

Hi Marie

Sorry to hear about the problems you have been experiencing. It sounds like the “logon” table in your database has become corrupt. To fix this please follow the steps in this FAQ :-
https://pioneersoftware.co.uk/forums/topic/faq-324-what-to-do-if-your-database-becomes-corrupt?view=all

Once you have followed the above steps, you should be able to click VIEW | USER SESSION MANAGER and then click the SEND QUERY button. This will remove any invalid entries in the “logon” table

Please let us know if you are still having problems after doing the above – many thanks.

[William]

Hi

Sorry to hear about the problems you have been experiencing and also apologies for the late reply. Would it be possible to let us know which report/document you are trying to print off and from which location you are printing it from (i.e. printing it via a patient record)? This will then help us to pin point the issue you are having. Many thanks.

[William]

Hi Michelle

Thanks for the question. Unfortunately flags are linked or tagged to the main patient record. So if a person has the access rights to view a patient’s record then they will be able to see the flag because it is connected. If it is private information then it might be better that the information is stored on something like the case/session notes. This information can still be accessed quickly via the patient clinical summary, but at least you can restrict this access right.

I will though add this to the request list and see if we could implement some sort of visual restriction to the access rights sometime into the future.

[William]

Hi Eniola

Thanks for the post. As they suggested in their reply you could perhaps try to set up your local phone number. How you could do this is by putting your local country prefix under the field Mobile Telephone on page 2 of the TextAnyhwere Setup screen in ClinicOffice. In the case of Nigeria the prefix is +234. So as an example if your mobile number is 0708 245 2138, then you would enter +2347082452138 (please note that the ‘0’ that came before the ‘7’ was removed). Unfortunately we cannot guarantee this will work.

There are two alternative options that you could try instead. One is to specify your mobile number in the message itself and request them to reply to that number. The second option is to select the “Email Address” field under the option and insert your email. When the patient replies to your message it will be sent through to your email.

Hope this information is of some assistance.

[William]

Thanks ever so much for sending through the SQL. It looks like the letter you are using is based on a Recall Letter. As you are running an appointment based rule it will be trying to merge data from the appointments table with that of the recall fields which is why the error is bring thrown.

A way around this is if you were to copy a letter based on appointments and use this instead. To do this please follow the steps below:

– Go to the Reports and Templates section of ClinicOffice
– Locate your current letter which is throwing the error when being merged
– Right click on this and select Rename
– Put “(OLD)” (without quotes) at the end of the name and click OK
– Right click on the letter “Appointment Reminder Letter” and select Copy
– Now right click on the newly copied letter and select Rename
– Enter the new name for the letter and click OK
– Copy the contents from the old letter into the newly created one, replacing the previous content
– You may need to alter some of the merge fields
– When you are happy with this click the Save Template button

If you now go back into the rule and change the letter being used you should find that when you go to merge the letter, the error will no longer appear.

Please let us know if you are still having problems though.

[William]

Hi

Sorry to hear about the problems you are having in running your report. The error message seems to indicate that it is the SQL code of the report that is at fault. If possible please copy the SQL code and paste it into this thread of the forum.

To copy the SQL code please follow the steps below:

– Go to the Reports and Templates in ClinicOffice
– Right click on the report you are having problems with
– Select Report Source
– Go to the SQL Source tab
– Copy and paste all the text present there into your reply on this thread
– Then go to the Options tab
– Again please copy and paste all the text present there into your reply on this thread
– Click Close when you have finished

Once we have the SQL code it should be able to tell us why the error is being thrown. Look forward to hearing from you.

[William]

Hi Chris

Thanks for the post and sorry for the delay in replying to this. I don’t think I understand exactly what you mean. Is it a case that you wish to view appointments in Google by room or that you wish to synchronise appointments by room?

[William]

Hi

Thanks for the question. To send a text to a group of people please follow the steps below.

– Go to the Patient grid
– Perform a search to display the patients to whom you want to send the SMS to (or you can press F7 on to show all patients)
– Right-click on the grid
– Select Patient Record and then Send Text Message
– Insert your message
– Click the Send button when you are happy with this

You can also do this on other grids such as the appointment, invoice or payment grids. Hope this helps.

[William]

Hi

Thanks for your post. A database check and repair may resolve the problem. To do this please follow the steps below :-

– Go to the ClinicOffice “Log On” screen
– Click the Manage Databases link on the log on screen
– Right-click on your database in the list and then click Check & Repair Database
– On the “Check & Repair Database” screen, simply click OK to begin the REPAIR operation

Please let us know if you still having problems after following the above steps.

[William]

Hi Meslogie

That does sound very strange. Although this should not make a difference I wondered if you did the first table as the “inv” table instead and then you join the “view_invoices” onto that. So it would look like the SQL below.

select code

from inv

left join view_invoices on (view_invoices.id=inv.id)

Please remember that any fields you are before the “from” will have to be prefaced with “view_invoices.”

Does this fix the problem?

[William]

Hi Charlotte

Thanks for the post. Please see the following FAQ topic which will tell you the technical reason of why this could have happened a second time so quickly, without you changing anything yourself on the machine. It also explains how to resolve the issue quickly:-

https://pioneersoftware.co.uk/forums/topic/faq-204-clinicoffice-lost-its-license-and-needs-activating?view=all

Hope this helps.

[William]

Hi Stevema.

It does appear there is a genuine problem there. Would it be possible to upload your database to us and we can have a look to see what is happening? To do this please see the following topic:- https://pioneersoftware.co.uk/forums/topic/faq-301-how-do-i-upload-my-database-to-your-ftp-server?view=all

Also please could you let us know the settings you use when you go print preview the diary. Many thanks.

[William]

Hi Lee

Thanks for the question. The system is not really designed to ‘delete’ old records because you really need to retain all clinical and financial data for your own protection.

If you wanted to hide old/inactive patients, you could search on the “last appointment date” in the Patient Database and then delete Patients who have not been in for X number of years. These would then be put into the recycle bin (i.e. and hidden) … however they could be undeleted if required later on.

Is there a particular reason that you wish to delete/archive records on mass after a certain period of time?

[William]

Thanks for the post. The problem was caused by Twitter changing their API. This meant that ClinicOffice could no longer read the feeds from their site. We have fixed this in build 1079 of both ClinicOffice v5 and v4. Please update to this latest build and you should find that the ClinicOffice News Feed is restored.

Hope this helps.

[William]

That will be no problem Dee. To add the Patient Category to the Invoice List please follow the steps below.

[1] Right click on the Invoice List report and select Copy
[2] Right click on this copied report and select Rename
[3] Enter a new name like “Invoice List with Patient Categories”
[4] Click OK
[5] Right click on the report and select Report Source
[6] Click on the SQL Source tab
[7] Delete any text present in this window
[8] Copy all of the the SQL below

select inv.id, invoicenum, datetime, duedate, inv.discpercent, discamount, nettotal, taxtotal, total, printed,



inv.per_id as recipient_per_id, trim(recipper.lastname || ', ' || coalesce(recipper.firstname,'') || ' ' || coalesce(recipper.title,'')) as _recipient,

inv.clinic_id, clinic.name as _clinicname,

inv.staff_id,

(case

when inv.staff_id is null then ''

else staff.knownas

end) as _staffmember,

inv.room_id, room.name as _roomname,

inv.apptype_id, apptype.name as _apptype,

inv.patient_per_id, trim(patper.lastname || ', ' || coalesce(patper.firstname,'') || ' ' || coalesce(patper.title,'')) as _patient,

patper.code, patper.insuranceref,

(coalesce(clinic.prefix, '') || cast(inv.invoicenum as varchar(20))) as _invoiceref,



coalesce(view_invamtalloc.amtalloc, 0) as amtpaid,

(inv.total-coalesce(view_invamtalloc.amtalloc, 0)) as outstanding,



cast(

case

when (inv.total = 0) or (view_invamtalloc.amtalloc >= inv.total) then

'Paid In Full'

when (view_invamtalloc.amtalloc > 0) and (view_invamtalloc.amtalloc < inv.total) then

'Part Paid'

else

'Unpaid'

end as varchar(20)) as _status,



(case when (inv.total > 0) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)

then (cast((current_date - cast(inv.datetime as date)) as integer))

else cast(null as integer) end) as _ageing,



(case when (inv.total > 0) and (current_date > duedate) and (coalesce(view_invamtalloc.amtalloc, 0) < inv.total)

then true

else false end) as _overdue,



inv.createdon, inv.createdby_staff_id, cr_staff.knownas as _createdby,

inv.updatedon, inv.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

patper.patcat_id, patcat.name as _patient_category



from inv



inner join per as recipper on (inv.per_id = recipper.id)

inner join clinic on (inv.clinic_id = clinic.id)

left join staff on (inv.staff_id = staff.id)

left join room on (inv.room_id = room.id)

left join apptype on (inv.apptype_id = apptype.id)

left join per as patper on (inv.patient_per_id = patper.id)

left join staff as cr_staff on (inv.createdby_staff_id = cr_staff.id)

left join staff as up_staff on (inv.updatedby_staff_id = up_staff.id)

left join view_invamtalloc on (inv.id = view_invamtalloc.inv_id)

left join patcat on (patper.patcat_id=patcat.id)

[9] Paste this into the SQL Source window
[10] Click OK

When you run this report you will see whether a patient is of the private or insurance categories. Please let us know if you have any problems with the above instructions.

[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.

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 || ' '

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.

[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

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.

[William]

Hi Dee

If it is just a list of patients that have the patient category of Private or Insurance you could always use the main Patient grid in ClinicOffice or the Patient List and then search on Patient Category.

With this you can search on both categories and under Grid Options select the “By Category” layout. This will group the grid by Private and Insurance and will give you a sub total of the number of records under each group. Alternatively you could just search on each category separately and view the total count of records at the bottom of the grid.

Would this be sufficient or would you prefer to be able to see the amount of money each group have been invoiced/paid for?

[William]

Hi Jose

Looking again at your initial request I cannot see that there is an easy way to display both the Payment Method and the invoice item category i.e. service or product, certainly not one that would not duplicate information. The reason is because of a many to many relationship that can exist between an invoice and a payment. You see you could have one invoice being paid for by 3 payments. In this situation if we had the payment method on the same report it would have to show all 3 payments meaning that the same item on the invoice will be repeated 3 times. In turn this means that the invoice line total would be incorrect.

So due to this duplication of information that can occur, this means that unfortunately it is not really feasible to create a report that will show you reliable totals.

[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.

[Author]

Posts