EOL Asset DB – Using the the sys search

The IT Assets Database was replaced by the IT Admins CMDB and is EOL / End of Life, no further development will be done on this project.

Almost the whole application is depending on a sys search function. This system wide search module works the same and is controlled by a central module through the application. Some more details can be found in the chapter sys columns what is actually controlling some of this stuff.

Having this said – the principles are always the same

  • the search is executed automatically once you stop typing
    • no enter / return key is needed
  • if you click on a column name, it will sort ascending or descending by this column
  • if you hover over a column name, you will see
    • the full name of the column
    • the type of information in this column like text, numbers or date and times values
    • examples on how you can search in this colum
  • searching in text fields
    • any alpha-numeric column
    • wildcards are automatically added to the end
    • use a * (wildcard) before or withing the search phrase if needed
    • an ! (exclamation mark) will exclude the search phrase – everything that is NOT like the phrase
      • only an ! (exclamation mark) will show you all NOT EMPTY rows for this column
    • if you hit the space-bar in a column it will show you all empty records for this column
    • combine various search request while using the PIPE symbol
      • !retired*|!deployed*|!available*
  • searching numeric fields
    • type in the number you want to search for
      • will search for exactly this value like an = (is)
    • use > (more then) or < (less then) to show values before the number to search for anything more or less then the number
      • >4
      • <4
    • combine > or < with =
      • >=4
      • <=4
    • or say everything but the number – more or less then the number with <> – exclude the number entered
      • <>4
  • searching in date fields
    • enter a date to search for this specific date
    • use < to search for before or > to search for after the specified date
      • <1/1/2000
      • >1/1/2000
    • combine it with a = (is) to search for before or is date, respective after or is date
      • <=1/1/2000
      • >=1/1/2000
  • Most lists have an Export button that will allow you to export the currently filtered data in to a Microsoft Excel workbook so you can use it further
    • those exports can be bound to an independent view – this is defined in the VBA code and would allow you to adjust the columns independently from what you see in the current list box
  • Refresh
    • this button actually causes a requery and will refresh all data – this is useful if you want to make sure you see the latest information by executing the queries against the database again
    • you can use F5 to do something similar as well
  • Clear filter
    • the quickest and easiest way to remove all applied filters and sorting back to the configured default
  • Total records shown
    • this will give you the total amount of records / rows you see right now – based on your filters and / or available data

As of version 2 of the IT assets database, a pre-filter checkbox was introduced. This checkbox determines if certain records are shown of hidden without the need of setting manual filter values. This especially works with e.g. retired or lost/stolen statuses, as well as with inactive records of e.g. employees or reminders etc.. – the checkbox is available in many modules and controlled by a single column in the SQL source views. This column might come from a sub-table that defines the status or by a calculation in the respective SQL view. You don’t have to worry about the specifics of how this is controlled, unless you want to change the behavior itself.

Be aware that this checkbox automatically un-checks if you add a new record, this the default behavior, cause certain new records would get filtered out by this filter, cause their status would indicate that they should not be shown, until the values / fields are set correct and filled with data.

Version 3.0 introduced the feature to combine text-searches in one field with the PIPE symbol – allowing you to use multiple criteria in the same field.