Defining Search Criteria

[adminboy]

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…

[SupportKeymaster]

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

ForcedSearchFields=friendly_name|fld_name|data_type

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!

Click here for more information on joining the ClinicOffice Support Plan.

[adminboy]

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.

[SupportKeymaster]

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!

[adminboy]

Perfect. Thank you. 😀

[Author]

Posts