Outputting date using OCONV

X.TEXT = OCONV(DATE(), "D") 09 Oct 2013
X.TEXT = OCONV(DATE(), "DDMY") 09 10 2013
X.TEXT = OCONV(DATE(), "DMDYA") October 09 2013
X.TEXT = OCONV(DATE(), "DMDYA3") Oct 09 2013
X.TEXT = OCONV(DATE(), "D4/") 10/09/2013
X.TEXT = OCONV(DATE(), "DWA") Wednesday
X.TEXT = OCONV(DATE(), "DD") 09 
X.TEXT = OCONV(DATE(), "DM") 10
X.TEXT = OCONV(DATE(), "DW") 3 (this is quarter)
X.TEXT = OCONV(DATE(), "DDMY,A,Z4") 09 October 2013

Found this gem in Ellucian's documentation: 2909: Explanation of Rules and Connectives

Colleague
Mnemonic RLDE

The internal documentation on the RLDE Rule Definition screen offers these connectives:

1: WITH With
2: AND And
3: EVERY and every
4: OR Or
5: OREVERY Or every
6: ORWITH Or with
7: OWE Or with every
8: WE With every

Explanation of each connective:

1. WITH
WITH works as a parenthetical AND to start a new true/false condition.

2. AND
AND works with the previous line to determine true or false. The AND statement does *not* start a new parenthetical.

3. EVERY
The EVERY connector evaluates every value within a multi-valued field. Each value in the multi-valued field must equal the defined condition for a true result.

4. OR
OR works with the previous line to determine true or false. The OR connective does *not* start a new parenthetical.

5. OREVERY
Combines the OR and EVERY connectives. As such, it does not start a new parenthetical but works with the previous line to determine true or false. Each value in the multi-valued field must equal the defined condition for a true result.

6. ORWITH
ORWITH begins a new parenthetical to establish a true or false condition. This is in contrast to the WITH connective which is inclusive (AND) whereas ORWITH is exclusive (OR).

7. OWE (ORWITHEVERY)
OWE functions just like ORWITH except that it is used to evaluate multi-valued fields. OWE begins a new exclusive parenthetical and every value in the multi-valued field being evaluated must be equal to the defined condition for a true result.

8. WE (WITHEVERY)
WE combines the WITH and EVERY connectives. As such, it *does* start a new parenthetical. Each value in the multi-valued field being evaluated must be equal to the defined condition for a true result.


To properly build a rule with combinations of OR/AND, keep these facts in mind:

1. The syntax processor within UniData will process AND and OR in the order it finds them from left to right of your sentence. A and B or C or D and E won't necessarily produce the results you expect. For example, if your criteria are as follows:
WITH LAST.NAME EQ 'Smith'
AND STATE EQ 'Virginia'
OR STATE EQ 'Maryland'
OR STATE EQ 'Pennsylvania'
AND FIRST.NAME EQ 'William'

Your result set would consist of persons with last name Smith that live in Virginia and first name William, OR people who live in Maryland or Pennsylvania with first name of William.

2. In the table above, #1 WITH and #2 AND are NOT synonymous and cannot be used inter-changably. They are only inter-changable in a simplest case like A and B and C and D. Here WITH A AND B AND C AND D is the same as WITH A WITH B WITH C WITH D. The key point to remember is that WITH *does* start a new parenthetical while AND does *not* start a new parenthetical.

3. Putting 2 values on the right-hand side of an expression acts as an implied OR. If you want everyone in the states of New York and New Jersey, you can write it as STATE = 'NY','NJ'. This translates as 'with state equal New York OR New Jersey. Note that a comma must be placed between the right-hand side values, which is different than the normal query structure.

4. The best method to get the syntax accurate is to write what you want as a query sentence, being careful to use the word WITH as parentheses around groups. Then use the examples below to create the rule. The second column shows every possible variation of OR and AND with 4 variables. The third column repeats the 2nd, but only uses parentheses when they are necessary to keep the logic accurate.

1 (A and B) and (C and D) A and B and C and D
2 (A and B) and (C or D) A and B and (C or D)
3 (A and B) or (C and D) (A and B) or (C and D)
4 (A and B) or (C or D) (A and B) or C or D
5 (A or B) and (C and D) (A or B) and C and D
6 (A or B) or (C and D) A or B or (C and D)
7 (A or B) or (C or D) A or B or C or D

Here's how the rules should look for each case:
1 A and B and C and D
1st choice 2nd choice (synonymous)
WITH A WITH A
AND B WITH B
AND C WITH C
AND D WITH D

2 A and B and (C or D)
WITH A
AND B
WITH C
OR D

3 (A and B) or (C and D)
WITH A
AND B
ORWITH C
AND D

4 (A and B) or C or D
WITH A
AND B
OR C
OR D

5 (A or B) and C and D
WITH A
OR B
WITH C
AND D

6 A or B or (C and D)
WITH A
OR B
ORWITH C
AND D

7 A or B or C or D
1st choice If you want several values of one variable, use
WITH A WITH variable = 'A' , 'B' , 'C' , 'D'
OR B
OR C
OR D

Form Hooks with Active Variables and Statements CheatSheet

How do dynamically populate a valcode values for dropdown menu in Web UI or Webadvisor

First, add DYNAMIC.VALCODE.ID to the form. 

Second, put this in FIELD.ACCEPT of DYNAMIC.VALCODE.ID

* Note: Populate XL.INTERNAL.CODE, XL.DESCRIPTION with the codes and descriptions of the dropdown values first

X.WEB.VALCODE.NUMBER = "PHAM.TEST.CODE"  ß (Notice this has to be the same name as $CR.PHAM.TEST.CODE below)

X.WEB.VALCODE.KEY = ""

CALL S.STORE.DYNAMIC.VALCODES(X.WEB.VALCODE.NUMBER, XL.INTERNAL.CODE, XL.DESCRIPTION,"","",X.WEB.VALCODE.KEY,"","")

INPUT.DATA = X.WEB.VALCODE.KEY

Third, put this in the validation field of the dropdown field.

$CR.PHAM.TEST.CODE;DYNAMIC.VALCODE;DYNAMIC.VALCODES.ID;DYNAMIC.VALCODES.CODES;DYNAMIC.VALCODES.DESCS;DYNAMIC.VALCODES.FLD3;DYNAMIC.VALCODES.FLD4

This subroutine could be useful to modify existing valtable:
S.GET.VALCODE.RECORD

Commonly used keyboard shortcuts for Desktop UI2.3

( alternative keystroke combinations separated by | ) 

Move to the next field Tab

Move to the previous field Shift+Tab

Move forward one row Down Arrow

Move back one row Up Arrow

Move forward one screen Page Up

Move backward one screen Page Down

Move to the beginning of the current field Home

Move to the end of the current field End

Move to the first row of a window Ctrl+Home

Move to the last row of a window Ctrl+End

Field insert Ctrl+I | Insert | F3

Field delete Ctrl+D | Alt+F, F | Alt+E, D | F4

Open the File menu Alt+F

Cancel Alt+F, C | Shift+F8

Save (Update) Alt+F, S | F10

Close (Finish) Alt+F, L | F9

Detail Alt+F, D | F2

Direct Access Alt+F, A | F8

Record Delete Alt+F, R | Shift+F10

Exit Alt+F, X | Shift+F9

Open the Edit menu Alt+E

Open the Apps menu Alt+A

Open the Help menu Alt+H

Field Help Alt+H, F | F1

Process Help Alt+H, P | Shift+F1

Keyboard Shortcut Help Alt+H, U | Shift+F2

Pull down the Favorites menu Alt+V

Pull down the Options menu Alt+O 

How to test computed column in MS SQL Server Management Studio

To test computed column, I would fire up SQL server management studio, in Object Exploere, drill down to your dev/test/prod database, then to Programmability, then Functions, then Scalar-valued Functions. Mine is:

Colleague18_test_db/Programmability/Functions/Scalar-valued Functions/

For example, to test out computed column C70_PER_TODAYS_DATE, which takes a PERSON.ID as an argument, here is the query I would use:

SELECT [colleague18_test_db].[dbo].[C70_PER_TODAYS_DATE] ('myIDNumber')

If you don't see your computed column in Scalar-valued Functions, I recommend recompiling/reinstalling the computed column again.

Envision string manupulation

String manipulation using square brackets [ ] takes two arguments.

X.STRING[x, y] where x is the starting position of the character, and y is the number of characters we want starting from x. If x is not provided, the position will be set to the end of the string, and counts backward.

X.STRING = '123456789'

X.STR1 = X.STRING[1,2]       -      X.STR1 = '12'

X.STR2 = X.STRING[1]          -      X.STR2 = '9'

X.STR3 = X.STRING[2]          -      X.STR3 = '8'

X.STRING[1,1] = 'FOO'          -      X.STRING = 'F23456789'

X.STR4 = X.STRING[1,2]       -      X.STR4 = 'F2'

X.STRING[1,2] = 'BAR'          -       X.STRING = 'BA3456789'

Submit the form when a drop down menu item is selected in Webadvisor

In field output of a VAR variable, enter the following:

OUTPUT.DATA := '<script type="text/javascript">'
OUTPUT.DATA := "document.getElementById('VAR1').onchange = submit;"
OUTPUT.DATA := 'function submit() {document.datatelform.submit();}'
OUTPUT.DATA := '</script>'

In this case, VAR1 field contains the drop down menu. This code needs to be parsed after VAR1. For instance, if you insert the above code in VAR2 output field, VAR2 needs to be below VAR1 in UI Form Field Sequence.

Note that this has only been tested with standalone webadvisor. I am not sure if this will work with webadvisor webpart inside colleague portal. If it does, the document and field name may need to change accordingly.

Delete record from a table

V.DYNAMIC.VALCODES.ID = A.DYNAMIC.VALCDS.ID
FOR_THIS DYNAMIC.VALCODES.ID DELETING
END_THIS DYNAMIC.VALCODES.ID

We need to have one element from the table in the demand element window for this to work.

Bargraph in web UI 4

* Input:  GRAPH.NUM.SELECTED     Used to calculate scaling  factor.
*         GRAPH.IDX              Used to compare with scaling  factor.
*         GRAPH.TITLE            Used to print a title at the top of the page.
*         GRAPH.CLEAR            Used to clear the part of the screen you
*                                want cleared.
*      1.  Must use following inserts:
*
*          $INSERT I_COMMON FROM UT.INSERTS
*          $INSERT I_GRAPHIC_CHAR FROM UT.INSERTS
*          $INSERT I_BAR.GRAPH FROM CORE.INSERTS
*
*      2.  GRAPH.NUM.SELECTED must be set to the total  number of items
*          that the bar graph is being run for.
*
*      3.  GRAPH.TITLE must be set. (name of the Progress Bar)
*
*      4.  GRAPH.CLEAR must be set. This should be set to  how much
*          of the screen you wish to clear. i.e. @(-1) for the  screen
*          to be completely clear at the start.
*
*      4.  Must use GOSUB INIT.BAR.GRAPH before the loop
*
*      5.  GRAPH.IDX must be set somewhere in the process loop and should
*          be set before GOSUB UPDT.BAR.GRAPH.
*
*      6.  GOSUB UPDT.BAR.GRAPH should be somewhere in  the main process loop.
*
*      7.  There are two ways to finish the bar graph.
*
*          a)  GOSUB FINI.BAR.GRAPH
*              (The program controls whether or not to leave the
*              final statistics on the screen.)
*
*          b)  GOSUB FINI.BAR.GRAPH.PAUSE
*              (This routine will pause after printing the final  statistics.)
*
* You have to turn off "USE BAR GRAPH" on the BGP (batch global parameters) screen to manually create bargraphs
* Sub graphs are done the same way as above just with slightly different commands as illustrated below
* Note: graphs will not update correctly if HUSH is turned on when update to graph is called!

Example:

* Inserts: required
$INSERT I_COMMON FROM UT.INSERTS
$INSERT I_GRAPHIC_CHAR FROM UT.INSERTS
$INSERT I_BAR.GRAPH FROM CORE.INSERTS
$INSERT I_SUB.GRAPH FROM CORE.INSERTS

* Set or calculate max size of the graph here:
X.GRAPH.SIZE = 30

* Required, Main progress bar
* I do not believe you can change the Title/num selected without creating a new graph, clearing the correct stuff,
*  re-initing a graph, etc.  Probably not worth doing.  Use subgraphs and graphs if you have need of a mutable graph.
GRAPH.TITLE="Progress Bar Title"
GRAPH.NUM.SELECTED=X.GRAPH.SIZE
GRAPH.IDX=0
GRAPH.CLEAR=@(-1)
GOSUB INIT.BAR.GRAPH

*Required IF you are using sub bar
SUB.GRAPH.NUM.SELECTED=@SYSTEM.RETURN.CODE
SUB.GRAPH.TITLE = "Subgraph Title"
SUB.GRAPH.IDX=0
GOSUB INIT.SUB.GRAPH

* Insert your code here.

* when you want to update the Bar count, do the following two lines:
* (note: I haven't tried counting down/backwards, I would suggest not doing so just from a design perspective)
GRAPH.IDX=*your number here
GOSUB UPDT.BAR.GRAPH

*If you want to update the sub-bar:
SUB.GRAPH.IDX=*Your number here
GOSUB UPDT.SUB.GRAPH

* finish subgraph after you are done processing the subunit (don't wait until entire process is done,
* I believe you need to call FINI.SUB.GRAPH or FINI.SUB.GRAPH.PAUSE every time you use the subgraph)
GOSUB FINI.SUB.GRAPH

* finish main graph, can use FINI.BAR.GRAPH or FINI.BAR.GRAPH.PAUSE, see documentation above
GOSUB FINI.BAR.GRAPH

Prompt user to download a file in web UI 4

Insert the following inserts:

$INSERT I_RIA_COMMON FROM UT.INSERTS
$INSERT I_RIA_TRANSFER_TYPES FROM UT.INSERTS
$INSERT I_COMMON FROM UT.INSERTS


Insert this code:

* ------------------------------------------------------------------------------------*
X.TRANS.TYPE = 'S'
X.DIRECTORY.FILE = "HOLD_SHARED_TESTDIR"
X.SOURCE.RECORD = "testFile.txt"
X.TARGET.RECORD = "testFile.txt"
X.FILE.ACTION = RIA.XFER.SAVE
X.NOTIFY.CLIENT = 1
X.ERROR.OCCURRED = ""
XL.ERROR.MSGS = ""

CALL S_RIA_TRANSFER(X.TRANS.TYPE, X.DIRECTORY.FILE, X.SOURCE.RECORD, X.TARGET.RECORD, X.FILE.ACTION, X.NOTIFY.CLIENT, X.ERROR.OCCURRED, XL.ERROR.MSGS)
IF (X.ERROR.OCCURRED) THEN
   CALL S_RIA_MESSAGES("", "Save failed. Please contact admin.")
END
* ------------------------------------------------------------------------------------*

User will be prompted to save file "testFile.txt" to his/her computer. For the above code to work, directory TESTDIR must be public, or whoever runs the program needs to have write access to the folder. Directory path for HOLD_SHARED_TESTDIR is "apphome/_HOLD_/SHARED/TESTDIR"

How to write to a file in Hold directory

============================================
Sequential I/O Subroutines
============================================

Each subroutine in the sequential I/O sub-system allows for up to 10 separate open files in a single session, each of which is represented by a file position number. All of these subroutines pass in a position number that represents the file you are opening. The sequence of calls must be done in the proper order.

When modifying a program that uses UniData Sequential I/O, you simply replace the functions for I/O with subroutine calls passing in the appropriate arguments. First, you need to open a sequential file, and then you may either read or write to the file. You can never perform a "read" function AND a "write" function on the same open sequential file. In other words, if you open a file for writing, then you may only write to that file. If you open it for reading, you may only read. You can also open the file to "append" but this is simply another form of writing. You can also use S.WRITE.EOF to truncate a file. You must finish by calling S.CLOSE.SEQ. (All of these rules and nomenclature are the same as when you are using the UniData functions for sequential I/O, but in this case are representative of a Unix environment).

----------------------------------------------------
The Subroutines
----------------------------------------------------

S.OPEN.SEQ: This routine is called first, and takes the following arguments:

-- A.FILE.NAME -- the directory path where the sequential file resides or will be created
-- A.RECORD.NAME -- the file to create in the directory path or the file to read.
-- A.POS -- is a number between 1 and 10. You must pass in the number of the file position you are manipulating. This allows you to manipulate up to 10 Sequential files at one time.
-- A.MODE -- is how you are opening the file. You can enter either "R", "W", or "A" (Read, Write or Append, respectively). If nothing is entered, this will default to "R".
-- A.ERROR.OCCURRED -- is what will be returned if there is an error; if the directory doesn't exist, if the file doesn't exist, etc...
-- A.MSG -- contains the exact reason for failure.

S.READ.SEQ or S.WRITE.SEQ, depending on the mode, is called next:

S.READ.SEQ: Arguments, in order, are:

-- A.OUTPUT.FILE -- contains the next record in the file.
-- A.EOF -- end of file flag
-- A.POS --
-- A.ERROR.OCCURRED --
-- A.MSG --

S.WRITE.SEQ: Arguments, in order, are:

-- A.OUTPUT.FILE -- the record to write into the sequential file
-- A.POS --
-- A.ERROR.OCCURRED --
-- A.MSG --

S.WRITE.EOF: Once you reach the end of a file or if you wish to truncate a file or overwrite the file contents, you call this. Its arguments, in order, are:

-- A.POS --
-- A.ERROR.OCCURRED --
-- A.MSG --

S.CLOSE.SEQ: You must always finish sequential I/O with a call to this. Its arguments, in order, are:

-- A.POS --
-- A.ERROR.OCCURRED --
-- A.MSG --

*--------------------------------------------------------------------------------------------------------------

Example:

*

GOSUB OPEN.FILE.WRITE

GOSUB WRITE.FILE

GOSUB CLOSE.FILE.WRITE

RETURN

*  ---------------------------------------------------------------*

*opens TestFile.txt in HOLD directory, write access, to buffer '1'

OPEN.FILE.WRITE:

CALL S.OPEN.SEQ("HOLD","TestFile.txt","1","W",X.ERROR,X.MSG)

RETURN

* Closes the file write buffer

CLOSE.FILE.WRITE:

CALL S.CLOSE.SEQ("1",X.ERROR,X.MSG)

RETURN

* write the text file to the directory

WRITE.FILE:

   CALL S.WRITE.SEQ("Something to write to the file here","1",X.ERROR,X.MSG)

RETURN

* ----------------------------------------------------------------*