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.

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.