Find Objects

standard queries creation the find objects feature allows you to create queries and search for information in the database the results of these queries can be used to analyse the organisation and its outputs imposing certain conditions ensures that only objects matching the conditions will be listed, and results limited to a specific number of items for example, you can set up a query to report on all encounters that are owned by a place of service (pos) and have a status of unresolved the query design can be saved for repetitive use the results of the query can be printed, stored, or exported to disk queries can also be imported and exported for backup purpose or to be shared profile functionality is the same for primary care clinics and larger enterprise organisations depending on the needs, you may not always need to perform all steps described in this procedure for all queries the find objects window the find objects window is where queries are set up open it by going to report/find objects from the main menu the find objects window will display in the query tab where you design the query the output tab is where query results will be displayed object panel the object dropdown field allows you to select the main object you are querying, for example, encounters once an object is selected, relevant fields and folders appear in this area please note that the same object must be used throughout the query select panel this is where you determine what should appear in your query results, organised in columns you can also choose how to sort the information where panel this is where you set the conditions that need to be met for data to appear in the results for example, you may want to report on encounters for a specific month only, and for a specific pos and/or provider commonly used toolbar icons icon description creates a new query opens a saved query in edit mode saves the currently opened query runs the query displays sql view when pressed before running the query exports/imports the query shows all available fields for selection when depressed if not depressed, less fields are displayed allows registry of forms to run queries on them sets time after the query will time out query design a query design depends on the type of the data you would like to collect for example, if you want to know how many patients/clients within specific age range and disease code were seen by a clinician, select patient as the query object go to report/find objects on the main menu to display the find objects window in the object field, click the dropdown arrow to display objects available select an object that you want to collect data aboutall the properties relating to that object will be displayed in alphabetic order as a list of sub folders or items below select the required property and add it to the select panel by doing one of the following double click the property drag and drop the property to the select panel right click on the property and select add to select from the right click menu click in the select panel toolbar note properties can only be selected from one object according to context you cannot mix properties from other objects as it will generate irrelevant data properties in the select panel may be changed by using the buttons as follows to delete selected property from list to move selected property up the list to move selected property down the list in the select panel, click in the sort column next to the property that you wish to sort the results by and select the required option from the dropdown field to specify in what order the results should be displayedfor example, if you select ascending for the patient dob property, the list of patients will be sorted form the oldest to youngest check the required grouping checkbox(es) for example, if you check the count checkbox next to ethnicity property, the results will be grouped by ethnicity and will show the total number for each ethnicity found, meeting the query criteria note greyed out checkboxes indicate that they are not applicable for this property in the object panel, search for the required property that you want to use to define your search criteria and add it to the where panel by doing one of the following drag and drop the property to the where panel click in the where panel toolbar right click on the property and select add to where from the right click menu properties in the where panel may be changed by using the buttons as follows to delete selected property from list to move selected property up the list to move selected property down the list for each property in the where area, specify the required conditions in the column field, select the required option from the dropdown list in the value field, if applicable, enter the required value directly in the field hot tip for date properties, you can either select the date(s) from the calendar by clicking in the value field, or manually enter it also refer to the advanced find objects techniques section for more information change the timeout period if required if the search is not complete after the timeout period, it will automatically stop click to execute the search to display the results in the output tab note if you want to change the maximum number of results, go to the output tab and enter the required amount in the limit to field note that if the number of items found exceeds that specified in this field, a dialog will appear, prompting you to either limit the results to the set number, or keep searching query results sometimes, you may simply need to get some data from the find objects query, and may not need to save it, print it or do anything else with it in most cases, however, you will probably need to print it the procedure below describes how to format the query results to suit your needs and how to print it in the title field of the output tab, enter an appropriate title for the query click to add and format a header to explain what the report shows click to change the columns properties change the column properties by selecting the column line and edit the properties described below column caption enter a new name for the column if required column type select text, currency, quantity, date or use the default type by checking use default column type justification select left, center, or right width enter the required width, or check auto width column calculations select count, sum, average, minimum, or maximum press update when finished altering a column, to save the changes, before you select the next one press ok when finished, to close the window the changes will apply to the way the information is displayed on the screen as well as on the printout hot tip if you check sum for a column that already had count checked in the query set up, then you will get a grand total at the bottom of the query results for that column click to re run the query if you have made changes to the columns properties the query results will show with the new column settings click to export the query to excel if you want to reformat it from there when prompted, save the query externally open the saved file by double clicking on its name click to print the query results to save the query results as a text file ensure you are in the output tab go to file/save as on the main menu to save it outside profile open the text file in a suitable program, e g , notepad, but not in profile this could be useful if you need to save the results as a ‘snapshot’ and come back to them at a later stage to analyse and compare them with other ones over time query management if you are likely to use the same or a similar query again in the future, you can save it so that it can be re run at any time go to the query tab and click to display the save query as window appears select the folder that the query is to be part of, if applicable in the name field, enter the query name in the description field, enter appropriate text if you wish this query to only be available to users that are members of a specific pos, select the required option from the dropdown list in the pos field once a find objects query has been saved, it can be run by going to report/stored queries on the main menu hot tip if the filenum property is added to the select panel of a query, then the resulting list can be used like a found list if you click an icon or select a menu option, it will be performed for the patient selected in the query results similarly, if the refnum property is added for a case, when a line is selected in the results, it will set the case context in other words, when clicking any icon on the toolbar, or performing menu actions, it will be for the selected case, if relevant, or for the selected case client query import or export you can import and export find objects queries an example of when this could be useful is when you are perhaps setting up queries on a testing database, and you then want to use it in the production database query export go to report/stored queries on the main menu to display the stored queries window select the query you wish to export click in the toolbar to open the export to file window select the appropriate folder or disk drive and enter a file name click save to store the query query import go to report/stored queries on the main menu to display the stored queries window click in the toolbar to open the import from file window navigate to and select the file, then click open the save query as window appears change the name and description , if required click ok to import the query registering forms with find objects find objects allows for compiling data entered in forms first, the form has to be registered with find objects, then query can be setup go to report/find objects on the main menu to display the find objects window click the maintain list of registered forms icon in the toolbar to open the window select the form you wish to register by using any of the following methods select it from the most used form templates , if available type a key word in the look for field, press find then select the form from the search results list below press browse and select it from the exploring window (skip next step if using this method) press add or drag and drop it into the forms registered with find objects panel press apply and repeat steps 3 and 4 to register other forms press ok when you have finished registering all the forms you can now create queries specific to the forms you have registered an object called form (registered) is created with the registered form(s) appearing as a subfolder the patient/client object also has a form (registered) subfolder, with the registered form(s) appearing under it note if you have saved a find objects query for a specific form as a stored query, you will be able to unregister the form and still run the stored query to unregister a form, simply select it in the list of registered forms with find objects window and press remove group queries creation grouping queries enables the running of individual queries to be combined into a single process this allows properties to be defined for some or all queries, as well as saving time in formatting reports go to report/stored queries on the main menu in the stored queries window either click on the toolbar, or right click on a query and select new group query from the right click menu in the group query window, click on the toolbar to add and format a header to explain what the report shows enter a title for the group query in the title field define which individual queries are to be part of the group, by double clicking on them in the individual queries list change the order of the queries if required, by selecting one and clicking or , as appropriate for the selected query in the selected queries panel, check title to include the individual query's title header to include the individual query's header page break to insert a page break before the individual query click to save the group query select the folder that the group query is to be part of, if applicable in the name field, enter the query name in the description field, enter appropriate text press ok group query execution go to reports / stored queries on the main menu group queries are preceded by the group query symbol click a new group query window appears listing the individual queries press run queries a check appears next to each query as it has been completed, along with the number of objects that have been found select one of the following select an individual query in the list and click to view results for that query only these can be printed or saved click to save the group query click to print the group query results press close and close the remaining windows like standard queries, once a group query is saved, it can be run by going to report/stored queries on the main menu stored queries when queries are saved, they become stored queries, which can be run at any time stored queries structure you can set up a structure for stored queries to group them into folders for convenience and/or associate them with a pos, so that only members of that pos will be able to run them this is particularly useful in large, multi pos organisations, with a high number of queries, some of which only apply to a specific pos go to report/stored queries on the main menu to display the stored queries window click to create the new folder if you want to create a subfolder, select the main folder first in the enter name field, enter a suitable name in the pos field, select the required pos from the dropdown list if you want this folder to be available only to users belonging to that pos stored queries execution standard and group queries that have been saved can be run at any time as stored queries, as described below stored queries results are valid at the time of running the query, as what was saved was the query, not its results go to report/stored queries on the main menu to display the stored queries window select the query you want to run you can open the query first, if required this will allow you to view its set up and also, if required, alter it click on the toolbar the query results appear in a new window note if a group query is being run, a group query window appears first press run queries advanced find objects query techniques query order the order that criteria are specified can significantly change the results of a query the query where sex = f and age < 50 orusual doctor code = tu will return patients who are female and under 50 as well as any patients with usual doctor tu regardless of sex or age the query where age < 50 or usual doctor code = tuand sex = f will return a list of all female patients who are either younger than 50 or who’s usual doctor is tu adding another search function will change the query again this query will return all female patients under 50 or who’s usual doctor is tu plus a patient named william regardless of sex, usual doctor, or age hot tip ‘patient first name is equal to william’ will not return patients/clients who are registered with an initial such as ‘william b ’ or with dual names such ‘william john’ to match all instances use the starts with option as a condition also operator there might be a situation when you are attempting to search for more than one criteria match in the same object the also operator will create a secondary query within the statement in this example, patients might have a problem with description ‘asthma’ and a second problem with description‘diabetes’ using and operator in the above example would look for a match for both criteria specified in a single record it will return a list of patients with problem description recorded as ‘diabetes and asthma’ another query example will return patients that have an invoice billing for an injection and a second invoice billing for a visit in office also not operator will search for all patients meeting the first criteria that do not meet the second criteria for example, you can run a query that lists patients who have diabetes but have not been diagnosed with asthma hot tip using contains condition will list patients who have the word ‘diabetes’ used anywhere in the problem description field using is equal to would limit search to patients/clients where problem description is recorded exactly as ‘diabetes’ is in condition is in condition allows you to specify multiple values as possible matches for a criteria in a single line for example, you can create a simple query to search for allpatients/clients with a label of either black, blue, or magenta is not in is the opposite; it will return any results that are not a match to any of the specified values unique records adding the user defined function distinct will cause only unique records for the selected property to be returned use that function only on a column that is a unique identifier and make sure that it is the top column on the list this query example will return a list of any patients who have an appointment booked with tu patients will only appear once on the list, however, they might be seen more than once by this doctor select the line in the select panel and click the tool type ‘distinct’ in front of (\&column) press ok relative dates instead of a specific calendar date, you can enter a relative date that will be estimated each time the query is run in the find objects’ window, select the line in where panel in the following example, the query will return patients that have not been seen in last seven years use one of the following options in the value column today inserts today’s date today xd inserts today’s date minus x days, e g , today 10d today xw inserts today’s date minus x weeks, e g , today 2w today xm inserts today’s date minus x months, e g , today 6m today xy inserts today’s date minus x years, e g , today 7y dynamic entry values you can enter a variety of dynamic entry values which resolve at run time by entering \ param (a colon and parameter) under the value column this will prompt for input of a value when the query is run entering only will work, but assigning the parameter description will provide better guidance to the user as to what they should enter in the fields to be used as value in the example below, the query is setup to return a list of patients who have the specific usual provider the user will be asked to provide the usual doctor’s code during run time a code for any existing provider can be typed in the ask value field creating a query for this specific provider in our example, ‘ws’ is typed for the provider’s code ( preview column will autopopulate) ranges you can also use specific range for your returned values for example, you can find all patients with last names between a and c create a query with is between condition that will use two \ param entries when you run this query, you need to fill in the information in this example, it's the alphabet letters that create a range the process is similar for the situations where date ranges need to be used when running this entry, you will enter the start and the end date when using more than one criterion, as it is typical for a range, one element can be static and the other can be dynamic for example, listing of billing that starts at the beginning of calendar year but the end date will be modified by the user each time this query is run can be set this way when running this query, you need to enter only the end date which can be a specific date or simply today’s date as in the example below it is also possible to prompt the user for a specific portion of a search term when the rest of the term is static use the ?ask? statement as a dynamic part for instance, create a foq where the user is allowed to specify only whether they want ‘visit in office’ or ‘counseling in office’ as a return for searching for service codes the foq should be designed as follows when running this query, user will be prompted to provide information in ask value column attachments