/
Advanced Finds

Advanced Finds

The advanced find allows you to create custom finds. 

How to Create an Advanced Find 

  1. Click the Find button in the toolbar.

  2. Click the Advanced tab.

  3. Click the Query Builder button in the toolbar. 

  4. Enter your query.

  5. Click OK.

  6. Click Exec button in the toolbar or the Exec button on the right in the Find window. 

Advanced Functions

Advanced functions allow you to filter results and/or get user input to add to you find.

Calendar Function

Display a calendar to the user when they run the find from which they can select the date instead of typing it. This will help eliminate example date formats and bad input. 

Better Code Block



The description "Please choose a due date" can be changed.

Example

Return orders for a certain date.

Better Code Block

Localized Date Function 

Convert server date and time to local date and time based on the zip code. 

Better Code Block

Change DateTime to the datetime needing to be localized, and ZipCode to the zip code to use to determine the timezone for localizing the datetime value. Using the statement above will result in a YY-MM-DD hh:mm:ss.s. For example, the date output would be 2020-09-04 17:17:20.887.

Quick Tips

  • Canadian postal codes can use just the first three characters.

  • Search “SQL Date and Time Conversions”, on the internet, to find a list of format codes.

Example

Convert the server date and time based on the zip code 85002 into the 1 format (MM/DD/YY) to get 09/04/20.

Better Code Block

Sub Select Drop Down Box

Allow the user to select from a list of values that can be passed in to filter results.

Better Code Block

There are two values.

  1. OrderTypeID which will be passed into the find. 

  2. Description of the drop down results.  

This is why OrderTypeID a numeric value comes before the description which is displayed as the list of choices in the drop down box.

Example

Return all orders with a particular order type.

Better Code Block

Input Box

Capture any value from the user and pass it into the find to filter your results.

Better Code Block

Example

Find all zip codes in the zones table where RouteZone1 equals what is entered into the input box function.

Better Code Block

Business Unit Filtering

Filter the results by the business units available to the user without user input.  

Business units available to the user are set by the user's permissions. See Users (Classic Operations App) and User Permissions - Definitions (Classic Operations App) for more information.



The business units available are loaded into a comma separated list.

Example

Filter orders by business unit.

User ID

Filter results by the user that made an edit to a database record.

Example 1

Filter orders created by the current Operations App user. 

Example 2

Return the current Operations App user. 

Useful Information

Best Practices

  • If you are modifying a query create a backup copy. 

  • Add a header, or snippet, to note the creator, creation date, changelog, and other useful information.

  • Use WITH(NOLOCK) to avoid locking. 

  • See W3Schools' SQL Tutorial more references on SQL. 

  • If you are creating more advanced scripts you can send them to Technical Support for code review.

Use a static variable instead of indeterminant functions in WHERE clause. 

If you want to get the order ID and current timestamp of all the orders that will be ready in the future, you should use a determinant, instead of using dbo.GetCXTDate() which is an indeterminant function where the value would change every millisecond.

If you want to get the order ID and yesterday’s date of all the orders that were or will be ready starting yesterday, you should use a determinant.





Ignore Max Records Restrictions