Missing Physical File Name for the Logical File "INSTITUTIONS"

This error seems to be persistent in our Dev environment and never went away. There was not enough time to spend research to completely solve it. The error was created as follow:

Another file, let's say CUSTOM.FILE, was checked out and modified in ST. For unknown reasons, INSTITUTIONS, which is in CORE, took CUSTOM.FILE's FIELD.FIELDS and made it its own (copied its data). Therefore, a duplication of INSTITUTIONS record exists in ST File Specs. Generating any process that uses fields from INSTITUTIONS will result in the above mentioned error.

I took the following steps to solve the problem. First, delete the duplicated INSTITUTION file.

DELETE FILE_SPECS
WHERE LOGICAL_FILE_NAME = 'INSTITUTIONS~ST'

Second, delete the I_INSTITUTIONS in ST.SOURCE. This will make the error go away.

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

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

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

SQL transaction for safety

In SQL studio, whenever an update to a table is perform, always test it first using TRANSACTION command. This ensures the execution of the query in its entity, and we can also have a preview on how the data will look like after the query execution.

DECLARE @TransactionName varchar(20) = 'Transaction1';
BEGIN TRAN @TransactionName

delete from C70_XTABLE
where C70_XTABLE_ID like '%Colleague%'

select * from C70_XTABLE

ROLLBACK TRAN @TransactionName;

After executing the whole query block, the data will remain unchanged, and we will have a sneak peek of what the query will do.

* Edit: The whole block can be written as:

begin transaction
(sql queries here)
rollback transaction

How to search for text/process/data element using ESEARCH

If you have access to Colleague terminal (through UI desktop, if you still have it), using ESEARCH can give you more accurate and complete results than the function "Find Reference" in Colleague Studio.

After you're in terminal, use this syntax to look for references to subroutine S.MY.TEST.SUB.

ESEARCH ST.SUBROUTINES  <enter>
>>>>S.MY.TEST.SUB  <enter>

SAVE.LIST SOME.SAVEDLIST <enter>

ELE SAVEDLISTS SOME.SAVEDLIST000 <enter> (note the three 0s were added)

P <enter> (P to browse the file, Q to quit)


ESEARCH is fairly fast. The only limitation is has is that you need to go in different modules such as ST.SUBROUTINES or CORE.SUBROUTINES or ST.SOURCE to search for them. Colleague Studio maintains pretty good references, but sometimes it misses customized processes.

Mistakes to watch out for with NULL in lists

The main thing to watch out for is the usage of the <1,-1> code for accessing/updating a list. When you insert a NULL using <1,-1>, it doesn’t really do anything.

In this case, we had three lists that were being used like an association, trying to be retrieve data from a record:

*Stores each field we care about from STUDENT.ACAD.CRED record in lists. The intention is that these lists are associated.

FOR_EACH REFERENCED SECONDARY PST.STUDENT.ACAD.CRED

  XL.STC.CMPL.CRED<1,-1> = V.STC.CMPL.CRED

  XL.STC.STATUS<1,-1> = VL.STC.STATUS<1,1>

  XL.STC.END.DATE<1,-1> = V.STC.END.DATE

END_EACH PST.STUDENT.ACAD.CRED

The issue arises when you consider how assigning NULLs work in UniData. Since a NULL, in Envision, is not actually a character but an empty string, assigning a null to a list doesn’t actually update the list, so when we hit a NULL value in the assignment the positions of the three lists went wrong. If our starting data was like this:

Record #1:

V.STC.CMPL.CRED = NULL

VL.STC.STATUS<1,1> = ‘N’

V.STC.END.DATE = ‘7/11/2014’

Record #2:

V.STC.CMPL.CRED = ‘3.00’

VL.STC.STATUS<1,1> = ‘N’

V.STC.END.DATE = ‘7/12/2014’

Our ending lists would look like this, based on our code above:

	STC.CMPL.CRED	STC.STATUS	STC.END.DATE
1	3.00	N	7/11/2014
2	NULL	N	7/12/2014

In record 1, V.STC.CMPL.CRED was NULL, so it actually did not create a delimiter in the XL.STC.CMPL.CRED in position 1; we merely set the tail of the list equal to NULL, so it didn’t actually do anything. The next record then sticks ‘3.00’ into the tail of the list, which is in position 1. Since position 2 is never set, our three lists are going to look like:

XL.STC.COMPLE.CRED = ‘3.00’

XL.STC.STATUS = ‘N’:@VM:’N’

XL.STC.END.DATE = ‘7/11/2014’:@VM:’ 7/12/2014’

Example of the issue using <1,-1>:

XL.LIST<1,-1> = ‘’

XL.LIST<1,-1> = ‘’

XL.LIST<1,-1> = ‘’

XL.LIST<1,-1> = ‘’

XL.LIST<1,-1> = ‘’

XL.LIST<1,-1> = ‘TEST’

In this case, XL.LIST is now equal to ‘TEST’ with no delimiters. In order to make this work, what you actually need to do is explicitely define the positions:

XL.LIST<1,1> = ‘’

XL.LIST<1,2> = ‘’

XL.LIST<1,3> = ‘’

XL.LIST<1,4> = ‘’

XL.LIST<1,5> = ‘’

XL.LIST<1,6> = ‘TEST’

Now XL.LIST is equal to ‘’:@VM:’’:@VM:’’:@VM:’’:@VM:’’:@VM:’TEST’ with @VM as the delimiter.

In order to fix the original code, I replaced

FOR_EACH REFERENCED SECONDARY PST.STUDENT.ACAD.CRED

  XL.STC.CMPL.CRED<1,-1> = V.STC.CMPL.CRED

  XL.STC.STATUS<1,-1> = VL.STC.STATUS<1,1>

  XL.STC.END.DATE<1,-1> = V.STC.END.DATE

END_EACH PST.STUDENT.ACAD.CRED

With

X.ACAD.COUNTER = ‘1’

FOR_EACH REFERENCED SECONDARY PST.STUDENT.ACAD.CRED

  XL.STC.CMPL.CRED<1,X.ACAD.COUNTER> = V.STC.CMPL.CRED

  XL.STC.STATUS<1,X.ACAD.COUNTER > = VL.STC.STATUS<1,1>

  XL.STC.END.DATE<1,X.ACAD.COUNTER> = V.STC.END.DATE

  X.ACAD.COUNTER = X.ACAD.COUNTER + 1

END_EACH PST.STUDENT.ACAD.CRED

* Cited from Trevyn Bowden.

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.

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.

Display Error Message

Use S.ARG.ERROR.MESSAGE(ARG1, ARG2, ARG3, ARG4)

ARG1: Set this to "1" to display an error message
ARG2: Set this to "1" or to a text strings that will become buttons in the warning message
ARG3: Set this argument equal to a text string or the key identifying a shared error message
ARG4: Set this argument equal to a list of arguments, delimited by values marks, for the error message

Use this with PROCESS.END = 1 and RECORD.CANCEL = 2 to get desired result.

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.