As we write custom WebAPI applications, we need to take extra steps to secure sensitive information after deployment. Below is one of the steps you can take to encrypt the web.config file, which is a popular place where configurable sensitive information is stored.
Powerpoint slide
Cheers!
My Ellucian's Colleague playground with all tips and tricks I learned as a programmer.
Showing posts with label datatel. Show all posts
Showing posts with label datatel. Show all posts
Thursday, July 26, 2018
Saturday, January 13, 2018
Writing records to disk immediately using MIO
Last week, I ran into a scenario where I needed to create two subroutines that are called sequentially by another subroutine. The first sub creates a record and then the second sub queries the database for the record and performs extra processing. The workflow looks like this:
Overall Subroutine BeginsThere was a problem with this workflow that I was using just a FOR_THE command to create the record in sub 1. Since the record created in sub 1 stays in the running buffer, it never gets written back to disk until the overall sub ends. Because of that, sub 2 never finds the new record.
Subroutine 1: creates a record
Subroutine 2: does a select to obtain the record created in subroutine 1
Overall Subroutine Ends
To get around this, my colleagues at Ferrilli suggested manually changing the MIO level so that sub 1's record is written to disk immediately. The code to be added is:
CALL @MIO.TRANSACTION(MIO.TX.SUSPEND)
FOR_THE record_id
* set field values
END_THE record_id
CALL @MIO.TRANSACTION(MIO.TX.RESUME)
The two MIO calls modify the level of the buffer and that's where the magic happens. Using MIO.TX.SUSPEND, in effect, temporarily starts a new process level at level 0. All I/O that is performed after the call to MIO.TRANSACTION using MIO.TX.SUSPEND will be held in MIO's buffers until the call to MIO.TRANSACTION using MIO.TX.RESUME is reached. In our case, as MIO.TX.RESUME is reached, the buffer of the transaction is flushed and the record is written to disk. Afterward, the MIO level returns to the normal running state. So my pseudo code looks like this at the end:
Overall Subroutine BeginsThanks Neal, Lea, and Geoff for the documentation, and Thomas Mantooth for verifying the code. GO FIGTEAM!
Subroutine 1: creates a record
CALL @MIO.TRANSACTION(MIO.TX.SUSPEND)
FOR_THE record_id
* set field values
END_THE record_id
CALL @MIO.TRANSACTION(MIO.TX.RESUME)
Subroutine 2: does a select to obtain the record created in subroutine 1
Overall Subroutine Ends
Wednesday, September 6, 2017
Record markers to ASCII (and vice versa)
Credits to Thomas Mantooth.
The UniData delimiters are in reverse order of their hierarchy. So, going in the other direction...
The UniData delimiters are in reverse order of their hierarchy. So, going in the other direction...
@RM (Record mark) = CHAR(255)
@FM (Field mark) = CHAR(254)
@VM (Value mark) = CHAR(253)
@SM (Sub-value mark) = CHAR(252)
@TM (Text mark) = CHAR(251)
One neat trick you can make use of with this comes into play when you use the REMOVE command. It assigns a value that indicates what type of delimiter it returns. Most of the time, you don't care what the specific delimiter is - you're just looking to see if it's zero, which is returned at the end when there's no delimiter. However, if you're dealing with something that can have multiple delimiters, you can use REMOVE and the delimiter value to build a new dynamic array with the same delimiters very simply.
LOOP
REMOVE X.VALUE FROM XL.ARRAY SETTING X.DELIMITER
* Do something to manipulate the data
XL.NEW.ARRAY := X.VALUE
UNTIL NOT(X,DELIM) DO
XL.NEW.ARRAY := CHAR(256 - X.DELIMITER)
REPEAT
Thursday, August 18, 2016
Padding preceding zeros to Colleague ID
In Colleague, ID numbers often have preceding 0s, and these are often truncated as the data is imported into Colleague. To add a small check to the ID to make sure it always is 7 number-long, you can add the following:
* Add zeros padding to PERSON ID
X.PERSON.ID.LEN = LEN(A.PERSON.ID)
X.ZEROS.TO.PAD = 7 - X.PERSON.ID.LEN
IF (X.ZEROS.TO.PAD GT 0) THEN
A.PERSON.ID = STR("0", X.ZEROS.TO.PAD) : A.PERSON.ID
END
Friday, May 13, 2016
Check if a record is locked
It is useful to check to see if the record is lock before beginning any processing. This eliminates the process to spin and wait for the record to be unlocked. When you work withe the WebAPI, it is more important to return a status of "resource is unavailable please try again" than to wait and risk the process timing out. To check to see if a record is locked, follow the below template:
The following give you different statuses of the lock check:
XKV.FILE = V.BPV.DOC.IDS
XR.FILE = ""
CALL @MIO.READ.RECORD(MIO.READU.EXIT.ON.LOCKED,XFV.FILE,XKV.FILE,XR.FILE)
IF MIO.STATUS = MIO.STAT.LOCKED THEN ;* file is locked
X.ERROR = 1
* Output your message, set the status, or do some other process
END ELSE ;* record is not lock, proceed like normal
* normal record read/write
END
The following give you different statuses of the lock check:
To read a record from a previously opened file.
@MIO.READ.RECORD(read_option, FV.FILE, KV.FILE, R.FILE)
Where FV.FILE is the previously opened file, KV.FILE is the key of the
record to be read, and R.FILE is the array into which the record will be
read.
Read_option is one of the following. These are equates so they should not
be surrounded by quotes.
MIO.READ - Reads the record without locking
MIO.READU - Reads the record and locks. If the record is already locked by
someone else, the user will get a message and will have the opportunity to
cancel the command.
MIO.READU.NO.CANCEL - Same as READU but without the option to cancel if the
record is locked.
MIO.READ.VERIFY - Checks to see if the record exists. R.FILE will be set to
1 if the record exists and 0 if the record does not exist. The entire
record is not read into the R.FILE variable if this option is used.
MIO.READ.ORIG - Will read the record from disk instead of from the MIO
memory buffers.
Friday, November 13, 2015
Using ESEARCH to find hidden process references
Colleague Studio is a very user friendly tool that can do many things with simple mouse clicks. However, it's not perfect, and sometimes it does not do what it says it does 100% to completion. Let's take "Show Reference" for example. Colleague Studio will show you a partial list of processes that reference a particular process. If you still have access to the terminal in Desktop UI, using ESEARCH command can help in these kinds of research.
We will look at one of the Ellucian's delivered subroutine, S.CHECK.IF.WITHDRAWN, for this example. When checking for references in Colleague Studio, the list looks like this:
Now that I know this subroutine is mainly called in ST, I can go to the terminal and check for the complete list.
All of this requires you to have access to the terminal, of course. You can do a similar search using the window explorer on the Colleague file server, but that takes way longer for me.
We will look at one of the Ellucian's delivered subroutine, S.CHECK.IF.WITHDRAWN, for this example. When checking for references in Colleague Studio, the list looks like this:
The above screen shows the usage of ESEARCH. You specify the folder/category of processes you want to search for. The above searches for UI/Webadvisor forms in ST. After the search, the result is saved in a savedlist named DTP. When you open DTP up, either in the terminal or in UI using SLED, you will see the more complete list of UI/Webadvisor forms that reference S.CHECK.IF.WITHDRAWN in ST.
Looking at the result, we can see more processes that reference S.CHECK.IF.WITHDRAWN than what Colleague Studio provides. I have found that Colleague doesn't not maintain references to custom processes very well, so ESEARCH is a good way to double-check. You can search for processes in corresponding applications:
appl.SUBROUTINES -> appl subroutines
appl.SOUCE -> appl screens
All of this requires you to have access to the terminal, of course. You can do a similar search using the window explorer on the Colleague file server, but that takes way longer for me.
Thursday, August 27, 2015
Killing Unidata session (softly with STOPUDT)
A while back when Colleague UI was first introduced, users started to X'ing out of the web-browser to close the session instead of using the logout button. That resulted in many runaways processes that used up the small license pool we had. I learned to kill those sessions through the terminal as following:
Open Colleague Desktop UI terminal:
LISTUSER: this lists all active sessions
LISTUSER | find "username": this finds a particular user's active sessions with "username"
Kill the session by:
STOPUDT usrnbr(user number): kill the session using the user's number, it's the number on the second column after using LISTUSER
* Note: insert a bang ("!") in front of LISTUSER or STOPUDT to use admin right if the commands don't work
You can use LISTUSER to check to see if your process is still running or the screen has frozen. Many times I waited for a process for so long just to find out the session was disconnected.
Open Colleague Desktop UI terminal:
LISTUSER: this lists all active sessions
LISTUSER | find "username": this finds a particular user's active sessions with "username"
Kill the session by:
STOPUDT usrnbr(user number): kill the session using the user's number, it's the number on the second column after using LISTUSER
* Note: insert a bang ("!") in front of LISTUSER or STOPUDT to use admin right if the commands don't work
You can use LISTUSER to check to see if your process is still running or the screen has frozen. Many times I waited for a process for so long just to find out the session was disconnected.
Wednesday, June 3, 2015
Wednesday, February 4, 2015
Using S.POPMAIL to send emails in Colleague
XL.EMAIL.ADDRESS<1,1> = 'first@email.com'
XL.EMAIL.ADDRESS<1,2> = 'second@email.com'
X.EMAIL.SUBJECT = 'This is email subject'
X.FROM = 'sender@email.com'
XL.MESSAGE<1,1> = 'First line of body'
XL.MESSAGE<1,2> = 'second line of body
XL.MESSAGE<1,3> = 'third line of body'
XL.EMAIL.BODY = "From: ":X.FROM:@VM:@VM:XL.MESSAGE
CALL S.POPMAIL("S", XL.EMAIL.ADDRESS, X.EMAIL.SUBJECT, XL.EMAIL.BODY)
XL.EMAIL.ADDRESS<1,2> = 'second@email.com'
X.EMAIL.SUBJECT = 'This is email subject'
X.FROM = 'sender@email.com'
XL.MESSAGE<1,1> = 'First line of body'
XL.MESSAGE<1,2> = 'second line of body
XL.MESSAGE<1,3> = 'third line of body'
XL.EMAIL.BODY = "From: ":X.FROM:@VM:@VM:XL.MESSAGE
CALL S.POPMAIL("S", XL.EMAIL.ADDRESS, X.EMAIL.SUBJECT, XL.EMAIL.BODY)
Wednesday, January 28, 2015
Envision Matrix and other things
I found a nice post on the forum from Patricia that could come in very handy for programmers.
Source: http://forums.datatel.com/viewtopic.php?f=28&t=12512
In the first question about adding the comments field to an existing LIST.VAR... You were only seeing the first line, because a list inside a list doesn't work. Further down in the thread, you saw where the CONVERT @VM TO \" \" IN ... converted the list for this line into a paragraph (sort of) by replacing the character that made it a list (@VM) with a space. Then, by putting the \"paragaph\" into the window's list variable, you would have been able to see all of it on a single row of the window (albeit, maybe limited by the size of the field, so maybe not see it all
)
DIM = this is a straight UniBasic command to dimension a matrix in memory. In other languages, this may also be referred to as dimensioned arrays. Referenced in Envision and UniBasic with parenthesis, most other languages with square brackets. Since Envision is an extension of UniBasic, most of the UniBasic commands can be used in Envision. Matrices can be one dimensional or two dimensional. Think of a matrix similar to a spreadsheet.
Code:
DIM XM.TEMP(15) is a single row with 15 columns
DIM XM.TEMP.TWO(15,5) would be an entire page, 15 columns by 5 rows
MAT is the companion to DIM. Where DIM sets aside the size of the matrix, MAT is the command used to initialize the dimensioned array. You usually see it initializing a matrixto null
Code:
MAT XM.TEMP = \"\"
MAT XM.TEMP.TWO = \"\"
Matrices/dimensioned arrays can be very powerful and can be equally complex.
EQUATE is a command used by the compiler. It literally takes what you type and replaces it with the equated value in the object code. Usually equates are used for programmer readability. You see every table in Datatel has an assoicated \"equate table\" in the app.INSERTS (or maybe it's the app.SOURCE). For example
Code:
EQUATE LAST TO 1
EQUATE FIRST TO 3
Then when a record is read from the PERSON file, the individual fields are parsed into their v-dots using the equates.
Code:
V.LAST.NAME = R.PERSON<LAST>
V.FIRST.NAME = R.PERSON<FIRST>
is the same as saying V.LAST.NAME = R.PERSON<3>
An advantage of this approach is that something moves around to another loccation, you only need to change the equate statement and then recompile the code rather than change every place where you typed the literal location <3>.
PRINT versus X.LINE - PRINT is the UniBasic command to send stuff to the output device defined with the SETPTR command (like the printer or _HOLD_ file). PRINT_DETAIL is an Envision thing.
X.LINE is usefull for programmers to build their own line of data to be sent to the printer/output device rather than fighting with Envision's report writer... it's just easier some times
CRT is a UniBasic command to send stuff to the console/screen and is now banned in Envision.
SHOWA is the new GRSS debugger built into Envision by Datatel and would be the new replacement for any CRT statements you are using for debugging. It's really very cool. Look at the GRSS, GRS1 and GRS0 mnemonics and see if they have on-line help. It's the replacement for doing something like
Code:
IF DATATEL.DEBUG THEN
XL.DEBUG.MSG<1> = \"v.last.name =\":V.LAST.NAME
XL.DEBUG.MSG<1> = \"v.first.name =\":V.FIRST.NAME
CRT XL.DEBUG.MSG
END
would be replaced with the single line
SHOWA V.LAST.NAME; V.FIRST.NAME
Of course, to use SHOWA, you must use the Envision generator. If you are hacking generated source code (app.SOURCE), you would still use the UniBasic CRT command to get it to display to the console.
CALL versus CALL_SUBR - this is an Envision thing and has to do with the MIO buffers. CALL_SUBR is used by screens and enables DETAIL screens to cancel the commits to the database if it's parent is cancelled. CALL can be used in screens, but will write the data whether or not the calling screen is cancelled or not. CALL is also used in subroutines.
Source: http://forums.datatel.com/viewtopic.php?f=28&t=12512
In the first question about adding the comments field to an existing LIST.VAR... You were only seeing the first line, because a list inside a list doesn't work. Further down in the thread, you saw where the CONVERT @VM TO \" \" IN ... converted the list for this line into a paragraph (sort of) by replacing the character that made it a list (@VM) with a space. Then, by putting the \"paragaph\" into the window's list variable, you would have been able to see all of it on a single row of the window (albeit, maybe limited by the size of the field, so maybe not see it all
DIM = this is a straight UniBasic command to dimension a matrix in memory. In other languages, this may also be referred to as dimensioned arrays. Referenced in Envision and UniBasic with parenthesis, most other languages with square brackets. Since Envision is an extension of UniBasic, most of the UniBasic commands can be used in Envision. Matrices can be one dimensional or two dimensional. Think of a matrix similar to a spreadsheet.
Code:
DIM XM.TEMP(15) is a single row with 15 columns
DIM XM.TEMP.TWO(15,5) would be an entire page, 15 columns by 5 rows
MAT is the companion to DIM. Where DIM sets aside the size of the matrix, MAT is the command used to initialize the dimensioned array. You usually see it initializing a matrixto null
Code:
MAT XM.TEMP = \"\"
MAT XM.TEMP.TWO = \"\"
Matrices/dimensioned arrays can be very powerful and can be equally complex.
EQUATE is a command used by the compiler. It literally takes what you type and replaces it with the equated value in the object code. Usually equates are used for programmer readability. You see every table in Datatel has an assoicated \"equate table\" in the app.INSERTS (or maybe it's the app.SOURCE). For example
Code:
EQUATE LAST TO 1
EQUATE FIRST TO 3
Then when a record is read from the PERSON file, the individual fields are parsed into their v-dots using the equates.
Code:
V.LAST.NAME = R.PERSON<LAST>
V.FIRST.NAME = R.PERSON<FIRST>
is the same as saying V.LAST.NAME = R.PERSON<3>
An advantage of this approach is that something moves around to another loccation, you only need to change the equate statement and then recompile the code rather than change every place where you typed the literal location <3>.
PRINT versus X.LINE - PRINT is the UniBasic command to send stuff to the output device defined with the SETPTR command (like the printer or _HOLD_ file). PRINT_DETAIL is an Envision thing.
X.LINE is usefull for programmers to build their own line of data to be sent to the printer/output device rather than fighting with Envision's report writer... it's just easier some times
CRT is a UniBasic command to send stuff to the console/screen and is now banned in Envision.
SHOWA is the new GRSS debugger built into Envision by Datatel and would be the new replacement for any CRT statements you are using for debugging. It's really very cool. Look at the GRSS, GRS1 and GRS0 mnemonics and see if they have on-line help. It's the replacement for doing something like
Code:
IF DATATEL.DEBUG THEN
XL.DEBUG.MSG<1> = \"v.last.name =\":V.LAST.NAME
XL.DEBUG.MSG<1> = \"v.first.name =\":V.FIRST.NAME
CRT XL.DEBUG.MSG
END
would be replaced with the single line
SHOWA V.LAST.NAME; V.FIRST.NAME
Of course, to use SHOWA, you must use the Envision generator. If you are hacking generated source code (app.SOURCE), you would still use the UniBasic CRT command to get it to display to the console.
CALL versus CALL_SUBR - this is an Envision thing and has to do with the MIO buffers. CALL_SUBR is used by screens and enables DETAIL screens to cancel the commits to the database if it's parent is cancelled. CALL can be used in screens, but will write the data whether or not the calling screen is cancelled or not. CALL is also used in subroutines.
Labels:
active variable,
CALL,
CALL_SUBR,
Colleague,
Colleague Studio,
datatel,
debug,
development,
DIM,
ellucian,
envision,
EQUATE,
MAT,
unibasic,
unidata
Tuesday, January 6, 2015
Colleague Lookup Resolution
EDIT: I have found that the "+Add" button can only be enabled if the lookup resolution is created in Colleague Studio. To get this to work, I created two lookup resolutions in two places, both with the same name and content (fields). One is created in Colleague studio, one is created in UICD. The names of these two need to be the same. Plug this on the form and it works (for me at least).
I found out today that creating LookUp Resolution in Colleague Studio sometimes doesn't work. When a lookup resolution is defined on the Resolution table field on LookUp Specifications Parameters screen of the file ID field, the default resolution table, which contains only Key and Description fields, is used instead.
To create and use a newly customized lookup resolution table, we have to do it in UICD, and then add the table name in Colleague Studio. First, go to UICD and create a new Context:
After adding the new context, detail into Search Results to define the lookup resolution:
Save out and go to Colleague Studio, enter the name of the resolution table into the Resolution table field.
Do not use the add button, since the new table won't be populated in there. Just enter the name of the table you created.
Save, regenerate the screen and you're done. Log out and log back in to see the new resolution table.
I found out today that creating LookUp Resolution in Colleague Studio sometimes doesn't work. When a lookup resolution is defined on the Resolution table field on LookUp Specifications Parameters screen of the file ID field, the default resolution table, which contains only Key and Description fields, is used instead.
To create and use a newly customized lookup resolution table, we have to do it in UICD, and then add the table name in Colleague Studio. First, go to UICD and create a new Context:
After adding the new context, detail into Search Results to define the lookup resolution:
Save out and go to Colleague Studio, enter the name of the resolution table into the Resolution table field.
Do not use the add button, since the new table won't be populated in there. Just enter the name of the table you created.
Save, regenerate the screen and you're done. Log out and log back in to see the new resolution table.
Friday, November 7, 2014
Bug when running uniquery
The following query failed to run:
X.STMT = "SELECT X.TABLE WITH X.FIELD1 EQ 'someValue' SAVING X.FIELD2"
CALL S.EXECUTE(X.STMT)
CALL S.READLIST(A.OUT, '', A.OUT2)
When using the SAVING keyword, the query has to return some data for it to work. If there is no X.FIELD1 with value equals to "somevalue" in table X.TABLE, the query will fail and return random data. I think it's whatever is in the active list 0. In this case, A.OUT will have random data and A.OUT2 will be '1'.
Tuesday, October 28, 2014
Output text message in batch process
Found this on datatel forum. This is how to display message during a batch process execution.
Put
code like this in the batch process, probably near the front
X.MSG = "Updating OPC Control Records. This could
take several minutes, please wait..."
CALL S.MESSAGES('2',X.MSG)
This
is what you get at run time
Wednesday, October 1, 2014
Check for record lock
XKV.FILE = Table ID / Record Key
XFV.FILE = Table/File Name
XR.FILE = ""
CALL @MIO.READ.RECORD(MIO.READU.EXIT.ON.LOCKED,XFV.FILE,XKV.FILE,XR.FILE)
IF MIO.STATUS = MIO.STAT.LOCKED THEN
// File is locked
END ELSE
// File is not locked
END
RETURN
XFV.FILE = Table/File Name
XR.FILE = ""
CALL @MIO.READ.RECORD(MIO.READU.EXIT.ON.LOCKED,XFV.FILE,XKV.FILE,XR.FILE)
IF MIO.STATUS = MIO.STAT.LOCKED THEN
// File is locked
END ELSE
// File is not locked
END
RETURN
Labels:
Colleague,
datatel,
ellucian,
envision,
lock,
record,
record lock,
subroutine,
unibasic,
unidata
Thursday, July 31, 2014
Check to see if a date is already converted to internal format
IF NOT(NUM(A.DATE)) THEN
* date is not in internal
format, needs converting
A.DATE = ICONV(A.DATE,X.SN.DATE2)
END
Useful for input validation of dates on things like
subroutines and ELF
* from Trevyn Bowden.
Thursday, April 10, 2014
Changing the print option on procedure
To change the print option of a procedure, change/create the printer definition of it in PDEF.
For example, in the UI form Procedure Hook, when a process is called via a procedure statement
PROCEDURE STMT 'SAVE.LIST ':V.JSPARAMS.ID
PROCEDURE OUTPUT 'XPROC123'
PROCEDURE STMT 'GET.LIST ':V.JSPARAMS.ID
Go to PDEF, change/create the printer XPROC123 to set the defaults, such as Output Device, Page Width, Page Length, etc.
For example, in the UI form Procedure Hook, when a process is called via a procedure statement
PROCEDURE STMT 'SAVE.LIST ':V.JSPARAMS.ID
PROCEDURE OUTPUT 'XPROC123'
PROCEDURE STMT 'GET.LIST ':V.JSPARAMS.ID
Go to PDEF, change/create the printer XPROC123 to set the defaults, such as Output Device, Page Width, Page Length, etc.
Thursday, January 16, 2014
How to force a word to be all caps when the field doesn't allow it
Put an equal sign '=' in front of the word.
Wednesday, October 9, 2013
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
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
Labels:
Colleage,
Colleague Studio,
convert,
datatel,
date,
date format,
ellucian,
envision,
oconv,
output,
unidata
Monday, October 7, 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
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
Thursday, September 26, 2013
Subscribe to:
Posts (Atom)










