Wild Card Searching Overview
SapphireOne Wild Card searching functionality is a comprehensive and efficient data searching and wrangling instrument. This functionality can be utilised in all dynamic data entry field in SapphireOne that are indicated by a blue background as seen below. The fields will accept the Wild Card search character entries as outlined below.
If you do not know the exact ID required simply enter the ‘ @ ‘ , ‘ ? ‘ or ‘ ! ‘ symbol into the ID field and select the Tab or return key to initiate a search.
Wild Card Searching Using the @ symbol
Entering the ‘ @ ‘ symbol alone will display a list containing basic details.
- If there are more than 200 records the user will be asked in an alert if they would like to list them all.
- This list can be narrowed down if you know the first or more letters of the ID of various related fields within the data entry that you are searching for. For Example. Entering ‘KEN@’ will display all records which have an ID beginning with KEN. This action results in a much shorter list for you to look through and is faster and easier to find the ID that you are looking for.
By selecting the precise ID that you wish, in this example Kens Super Stores Pty Ltd, you can click the Ok button in the bottom right or you can double click the ID and SapphireOne will automatically populate the data fields with the information from the selected ID, in this case the data related to the specific Client. As there are various data entry screens throughout SapphireOne, the data that is produced by a Wild Card search will only include data relevant to that specific data entry table and field, i.e for a Quote Client Invoice users will only be presented with data for Clients.
However, when using functionality that draws upon data from various different fields, e.g a Job Project ID which may incorporate a Client that is linked to that particular Job Project even though a Job Project is an entirely seperate table to the Client table.
Additionally, a Client Quote will include an Inventory ID that may have a Back to Back function turned on and as a result it will automatically link that Inventory to a Vendor ID, so when the Client Quote is converted to a Client Order it will simultaneously create an Order Vendor Invoice (OVI)
Using the ? symbol
Using the ‘ ? ‘ symbol on its own will display a searchable list of all records in the form of a standard or Custom Inquiry screen depending on how the users has their Inquiry screens configured. From here, users can filter the dataset by any of the available columns or perform a Custom Inquiry where the user can select which columns they wish to include or exclude furthering the ability to format the data. The columns can also be selected to order the data according to that column, for example, users could select the “Date Created” column which would result in all records being listed chronologically.
Additionally, the Find function on the main toolbar can be selected and all fields can be searched within to locate the specific Client record or records that are required. This will then limit the results of your search and present you with a more manageable list to select from within. Alternatively, you may type a new Client ID into the field and you will then be presented with this pop up asking if you want to create a new record.
A New Client data entry screen will be displayed and it will allow you to create a New Client. You must then select the Tick button to Save the new record before you return to the Transaction entry screen. Alter them there and save with the usual Tick or the Enter Key.
Entering in any ID that is not in the data base will cause SapphireOne to ask if you want to create a new record. This will happen if the user does NOT enter in either the exact ID, or SapphireOne is unable to determine an existing ID using the characters the user has entered with the ‘@’ symbol.
[/wonderplugin_cond]Using the ! symbol
The ‘ ! ‘ exclamation symbol may also be used in any data field that has a blue background indicating that it’s searchable. The Detailed Query pop up will be displayed immediately enabling a Detailed Query to be performed when a user is looking for a particular item or ID. More details on how to build a Detailed Query are outlined at the bottom of this page.
Building a Detailed Query
To create a standard query in the Detailed Query editor, simply set up a line in the form of “field, operator, value”.
- To designate the field, use the hierarchical list found to the right of the editing area. SapphireOne will display a list of all tables in the data file with the current table detail on screen opened ready for the user to make their selections. This is shown in the example image on the left below.
- By collapsing the currently expanded folder, the user will be able to view all of the tables within the data file and select from any desired area to be queried upon. This is shown in the example image on the right below.
You can also right-click in the editing area and enter the first letter(s) of the table of the field (do not type the ‘[‘ character): a text prediction mechanism displays proposals matching what you type. Once you have set the table, press the right arrow button to validate the proposition and access the list of fields. Enter the first letter(s) of the field or use the up/down arrows to scroll through the fields of the table. This list displays all the database tables and their fields. If a virtual structure is defined using the SET TABLE TITLES and SET FIELD TITLES commands, it is taken into account.
- Type the value you want to search for.
- In a Text or Alpha field you can use the wildcard character ‘@’ at the end of the value to specify a “Begins with” search.
- If the field you selected is associated with a choice list, SapphireOne displays the list and prompts you to select a value. If the field you selected is a Boolean field, SapphireOne displays a pair of radio buttons.
- If you want to specify a multiple query, click on the
button to add a line. SapphireOne duplicates the contents of the row corresponding to the button. If you want to add a query line using a formula, hold down Alt (Windows) or Option (MacOS) while you click on the add button. This point is detailed in the Query by formula section.
- If you want to add a query line using a formula, hold down Alt (Windows) or Option (MacOS) while you click on the add button. This point is detailed in the Query by formula section.
- Select the desired conjunction (And, Or, Except).
- By default, SapphireOne sets the And conjunction next to the line that is added.
- Repeat steps 1 through 3 in order to specify new criteria.
- When you build a compound query, SapphireOne evaluates the simple queries in the order in which they appear in the Query editor (i.e. from top to bottom). There is no precedence among the conjunctions. In other words, And does not have priority over Or. Therefore, if you use more than two simple queries when building the compound query, the order in which you enter the simple queries can affect the results of the query. As you build the compound query, you can modify existing parts of the query by clicking the line you want to change and clicking a new field or operator, or typing a new value. You can remove a simple query by clicking on the
next to it. Be careful, deleting lines cannot be undone.
- When you build a compound query, SapphireOne evaluates the simple queries in the order in which they appear in the Query editor (i.e. from top to bottom). There is no precedence among the conjunctions. In other words, And does not have priority over Or. Therefore, if you use more than two simple queries when building the compound query, the order in which you enter the simple queries can affect the results of the query. As you build the compound query, you can modify existing parts of the query by clicking the line you want to change and clicking a new field or operator, or typing a new value. You can remove a simple query by clicking on the
- Choose the destination of the query using the Selection action menu: Create new selection, Search in selection, Add to selection, Remove from selection (see previous section).
- (Optional) To save the query to a disk file, select Save… from the edit menu.
- Click on Query to launch the search.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.