...
...
...
...
...
...
...
...
...
...
...
...
...
...
Table of Contents |
---|
The advanced find allows you to create custom finds.
How to Create an Advanced Find
Click the Find button in the toolbar.
Click the Advanced tab.
Click the Query Builder button in the toolbar.
Enter your query.
Click OK.
Click Exec button in the toolbar or the Exec button on the right in the Find window.
Advanced Functions
...
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.
...
Paste code macro |
---|
SELECT * FROM tblOrder WHERE DueTimeTo = '{{{ Please choose a due date. }}}' |
...
Paste code macro |
---|
SELECT dbo.localizedate (DateTime, '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.
Tip |
---|
Quick Tips
|
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.
...
Allow the user to select from a list of values that can be passed in to filter results.
...
Paste code macro |
---|
[[[ Choose order type. | SELECT OrderTypeID, Description FROM tblOrderTypes ]]] |
There are two values.
OrderTypeID which will be passed into the find.
Description of the drop down results.
Info |
---|
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.
Paste code macro | ||
---|---|---|
| ||
SELECT * FROM tblOrder WHERE OrderType = '[[[ Please choose and order type. | SELECT OrderTypeID, Description FROM tblOrderTypes ]]]' |
Input Box
Capture any value from the user and pass it into the find to filter your results.
...
Paste code macro |
---|
'<< Please Enter a route zone number. >>' |
...
Paste code macro |
---|
SELECT * FROM tblZones WHERE RateZone1 = '<< Please enter a rate zone number. >>' |
Business Unit Filtering
Filter the results by the business units available to the user without user input.
Tip |
---|
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. |
Paste code macro |
---|
{{BusinessUnits}} |
The business units available are loaded into a comma separated list.
Example
Filter orders by business unit.
Paste code macro |
---|
SELECT * FROM tblOrder AS O INNER JOIN tblCustomer C WITH(NOLOCK) ON O.CustID = C.CustID WHERE O.OrderID > '125000' AND C.BusinessUnitID IN ({{BusinessUnits}}) |
User ID
Filter results by the user that made an edit to a database record.
Paste code macro |
---|
{{UserID}} |
Example 1
Filter orders created by the current X Dispatch Operations App user.
Paste code macro |
---|
SELECT * FROM tblOrder O WHERE O.CreatedBy = '{{UserID}}' |
Example 2
Return the current X Dispatch Operations App user.
Paste code macro |
---|
DECLARE @User VARCHAR(20) SET @User = '{{UserID}}' SELECT @User |
...
Anchor | ||||
---|---|---|---|---|
|
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.
...
Warning |
---|
Ignoring max records can lock up the system. See Best Practices WITH(NOLOCK) above. |
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 possiblesolution to viewdatasets over 250,000 is selecting the last row of the recordset and clicking the column header which will reorder the dataset and make all rowsvisible.
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 following on its own line before any select statements are executed.
Paste code macro |
---|
SET NOCOUNT ON |
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.
Paste code macro |
---|
SET NOCOUNT OFF |
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.
Paste code macro |
---|
SET ANSI_WARNINGS OFF |
For Developers and/or Administrators
Expand | ||
---|---|---|
| ||
"ÆÆ" (ALT + 1 + 4 + 6)x2 - Placing this string in the front of a query will allow for Inserts into tables, dropping columns and tables, etc. "ææ" (ALT + 1 + 4 + 5)x2 - This has the same functionality as "ÆÆ", but the string is removed from the query once the query is run.
|
...