Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #31432

    scaramanga
    Participant

    Hi,

    I have made a contact rule which should print a letter to patients who have not been for, say, a couple of months. I set up the rule successfully but when it runs, it processes the letters but they fail to complete when the mail merge screen comes up. I can see the letter in the editor with the merge fields but an error comes up and it doesn’t perform the merge to allow me to print the letters. The letter is from a template in the ‘Reports and Templates’ screen.

    The error looks as follows:

    ElevateDB Error #700 An error was found in the statement at line 1 and column 2437 (Expected column name expression but instead found “app”.”id”)

    I have updated to most recent version (v4, build 1079) as suggested already but the same error occurs.

    Help to resolve greatly appreciated as this would be a useful function for me.

    Thanks.

    #33275

    William
    Keymaster

    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.

    #33276

    scaramanga
    Participant

    Ok thanks.

    SQL source:

    select
    recall.id, recalldate, recall.per_id,
    recall.apptype_id, recall.staff_id, recall.clinic_id,

    @ADDRESSNAME:per as name,
    @FORMALNAME:per as formalname,
    @INFORMALNAME:per as informalname,
    @ADDRESS:per as address,
    staff.knownas as staff_name,
    apptype.name as appointment_type,
    clinic.name as clinic_name

    from recall
    left join per on (recall.per_id=per.id)
    left join staff on (recall.staff_id=staff.id)
    left join apptype on (recall.apptype_id=apptype.id)
    left join clinic on (recall.clinic_id=clinic.id)

    Options:

    Favourites=recalldate;
    RecallBased
    HiddenFields=name;formalname;informalname;address;staff_name;appointment_type;clinic_name;

    #33277

    William
    Keymaster

    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.

    #33278

    scaramanga
    Participant

    Ok, great. Will give it a try. Thanks.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.