JUSTDOIT

A JUSTDOIT is a specialized report function that uses SQL (Structured Query Language) to request and manipulate information from the data tables.  With a JUSTDOIT, you can change the sort order of data in a report, filter out unwanted data, or execute specialized report functions.  The JUSTDOIT executes the SQL commands BEFORE the report runs (before the data is displayed).

The JUSTDOIT application in Student Manager uses the SQL SELECT command and its elements to manipulate the data. The following table provides an explanation of each element of the SELECT command:

Element	Description
" ", ' ', [ ]	Delimiters used to enclose the Select statement and other elements in the JUSTDOIT.
Select	Tells the system to select specific data from the cursor.  Immediately followed by a statement telling the system what data to select.
- (asterisk)	Tells the system to select ALL data from the table that follows the FROM statement (see below).
0 as marker	Creates a new field in the cursor and enters a zero (0) for every record in the cursor.  This forces the JUSTDOIT to run only once for a given report cursor (helps avoid circular logic problems).
As	Create a new field in the cursor with either an explicit value or a value from a function.
From (cursor?)	Tells the system where to get the data that we are manipulating with the JUSTDOIT.   In most cases it will be from Cursor5 (default cursor created when the report opens).
Where	States a conditional clause that data must meet to be included in the report (e.g. where due-paid > 0.00).
Group by	Groups data in the cursor by the field you specify, then removes all but one record for each group.
Order by	Changes the sort order of data in the cursor.  NOTE: conflicting report banding will cause unexpected results.
Into cursor(cursor?)	Tells the system to place the data back into the cursor once it has finished executing all the select statements, making it available for the Report Print Preview.

The JUSTDOIT expression box may be placed anywhere on the report template but we recommend placing it in the header band of the report for purpose of quick identification.  It's a non-printing element and will not display on the report itself.

Examples

• Change the Sort Order of Report with "Order By" Statement

justdoit(‘select *,0 as marker from (cursor5) order by nmname3,nmname1 into cursor(cursor5)’)

• Eliminating Duplicate records with the "Group By" Statement

JUSTDOIT(‘select *,0 as marker from (cursor5) group by nmid,nmname3 into cursor(cursor5)’)

• Filter the report with "Where" Statement

JUSTDOIT(‘select *,0 as marker from (cursor5) where due-paid>0.00 into cursor(cursor5)’)

• Add a field to the Cursor and then Sort by it

JUSTDOIT(‘select *,addcrse(cocrse,"cobegdate") as date, 0 as marker from (cursor5) order by date into cursor(cursor5)’)

Errors

• A common problem in JUSDOITs is mismatched delimiters  Errors occur when you don't use matching ones, or when you try to use the same pair more than once.

BAD:  Mismatched Quotes are used to enclose the Select statement (double quote at the beginning and single quote at the end).

JUSTDOIT("select *,0 as marker from (cursor5) where nmzip ='55401' into cursor (cursor5)')

BAD:  Same Quotes are used for the Select statement and the value in the Where statement.

JUSTDOIT("select *,0 as marker from (cursor5) where nmzip ="55401" into cursor (cursor5)")

Note:  occasionally you may need to use a third set of delimiters in a JUSTDOIT.  In these cases, you may use the square Brackets [].

• Another common problem is leaving out the 0 as Marker statement.  Without the statement, the JUSTDOIT may execute a random number of times or become stuck in an infinite loop. No real damage done, but your report results will be unexpected and inconsistent.
• You cannot add a field to the cursor then attempt to do a comparison on the field contents in a Where statement.  To accomplish this, you will need to add two JUSTDOITs to the report (see next section):

Multiple JUSTDOITs

You may occasionally find that you need to use more than one JUSTDOIT to accomplish your goal.  For instance, if you want to add a field to the cursor then filter records based on the field contents.

To accomplish this, you will need to add two JUSTDOITs to the report.  In the first one you add the field to the cursor.  In the second one, you use the Where statement to filter the records:

justdoit(‘select *,addname(nmid,"nmocc") as Occupation from (cursor5) into cursor(cursor5)’)

justdoit('select *,0 as marker from (cursor5) where Occupation="MED" into cursor (cursor5)')

Notes

When using more than one JUSTDOIT in a report, you only put the 0 as Marker statement in the last JUSTDOIT.  If you place it in both JUSTDOITs, the second one will be ignored (it will not be executed).

Also, the JUSTDOITs must be added to the report in the correct order.  For instance, if you add the Where Statement JUSTDOIT first and the Add Field one second, the system will execute the Where statement first because that was the one that was added to the report first, and you will receive an error because the field you are using in the Where statement doesn't exist yet.

Recycle Query Mode

If you are using the Recycle Query option and you ran a report with a JUSTDOIT in it, the JUSTDOIT changed the cursor and the changes will be applied to all subsequent reports you run in the area until you exit the Recycle Query mode.