Advanced Functions

Advanced functions allow you to filter results and/or get user input to add to your 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. 

Open

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

Example

Return orders for a certain date.

Localized Date Function 

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

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.

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.

Sub Select Drop Down Box

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

Open

There are two values.

1. OrderTypeID which will be passed into the find. 

2. Description of the drop down results.  

Example

Return all orders with a particular order type.

Input Box

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

Example

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

Business Unit Filtering

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

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 logged in user. 

Example 2

Return the current Operations App user. 

Useful Information

Best Practices

• If you are modifying a query create a backup copy prior to making any modifications. 

• 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.

Ignore Max Records Restrictions

Place the following in the query as its own line will cause the query to ignore any max records restrictions. Grids have a limitation of 250,000 rows, and any dataset that exceeds this will not be visible.*

_{*A possible solution to view datasets over 250,000 is selecting the last row of the recordset and clicking the column header which will reorder the dataset and make all rows visible.}

Multiple Select Statements

SQL will return the number of records returned for every select statement that is executed in a batch execution. ADO only allows for one record count to be returned for it to return results.  If you have a query that requires multiple select statements, you will need to disable returning the number of records affected until the last select statement.

To disable the returning of the number of records affected, you will need to place the following on its own line before any select statements are executed. 

When you reach the last select statement, you need to add the following on its own line before the last select statement begins to re-enable the record count. 

Disable Warnings

SQL will return any warnings that may occur during the execution such as summing a column that may contain NULL values, and ADO will not return the data properly if any warnings are present. To get around this, place the following on its own line before any select statements. 

For Developers and/or Administrators