February 13, 2011 at 12:28 pm #31187
I’m creating a new report from scratch. I’ve got it all working except defining the ‘search fields’. Please would you advise how I do this?
I’m sure it’s blindingly obvious…February 14, 2011 at 10:14 am #32679
Hi – thanks for your post.
If you’ve already entered the SQL source correctly for the report, then ClinicOffice will automatically build the Search Fields from the columns available in the resulting dataset.
If you want to add additional search fields which are NOT in the SELECT statement (and hence don’t appear in the resulting dataset), then add the following line to the Report Options :-
For example, if you were searching the appointment table and wanted to seach on the date range for appointments (the app.start field), but you’re not selecting the app.start field in the SQL source, then you could add this line to the options :-
ForcedSearchFields=Appt Start Date|app.start|DateTime
This is obviously quite advanced stuff and if you’re on the Support Plan and need some assistance, please feel free to contact our Support Help Desk. If you haven’t taken out any support, we strongly recommend it!February 27, 2011 at 8:52 am #32680
Thanks for your response.
Is there any specific reason why the SQL queries in the pre-defined reports have the underscore before the column alias?
Thanks.February 28, 2011 at 8:44 am #32681
The ‘_’ before a column alias simply tells ClinicOffice to ignore that field when it builds the Search Criteria form.
Typically, this is used on lookup fields. For example, if you have an ‘apptype_id’ foreign key field, the Search Criteria understands what it is and will create a multi-selection editor from which you can choose “Appointment Types”. However, the same SQL will probably also have a lookup field called ‘_apptypename’ which is the lookup name of the appointment type, corresponding to ‘apptype_id’. The lookup field is a simple text field and may as well be hidden from the Search Criteria, hence the ‘_’.
Hope this makes sense!March 3, 2011 at 9:20 pm #32682
Perfect. Thank you. 😀
You must be logged in to reply to this topic.