Hiding Shell Toolbar Items

From time to time I would need to hide different shell toolbar items.  Typically I did so based on the user's permissions or by what window they were in.


   oWindow:=LimsApp("Shell");
   oToolBar:=oWindow : TOOLBAR;

   Dummy:=oToolBar : HIDEITEM(102);     /*Database Connection;
   Dummy:=oToolBar : HIDEITEM(104);     /*User Action;


Item 101 = Closed Starlims
Item 102 = Database Connection
Item 103 = Printer
Item 104 = User Action
Item 105 = NOTHING
Item 106 = Search
Item 107 = Sort
Item 108 = Change View
Item 109 = Copy to Clipboard
Item 110 = First Record
Item 111 =NOTHING
Item 112 =Previous Record
Item 113 = Next Record
Item 115 = Last Record
Item 116 = Mark All
Item 117 = Unmark All
Item 118 = Refresh
Item 119 = Reload
Item 120 = Timesheet (could be local for my system)


You can also ShowItem(), DisableItem() or EnableItem() as well using the same format as above.

PrmCount()

It goes without saying that if you employ ExecFunction() statements in your code you are going to pass parameters.  Knowing that the parameters you expect were passed becomes important and so is the PrmCount().

Not only will it check and ensure the correct number of parameters were passed, it can be used to create stems of programming logic based off that value as well.

Usually this was something I wrapped in a Case statement.  for example:

:BEGINCASE;
     :CASE PrmCount() = 1;

     :CASE PrmCount() = 2

etc., etc.

This let me count the number of parameters passed and perform logic based on that number.  Very useful to catch errors and make sure you are using the correct passed values.

The order, by the way, is the same order you declared the parameters.  So, if PrmCount() = 1 then it matches the first parameter declared after the :PARAMETERS label.

LIMSTime()

Like I mentioned with DOW() in a previous post, I had to figure out what day, time, and day of the week it was to send out different emails.

I used LIMSTime() to figure out time frames and then did simple checks.  For example:

SetAMPM(.T.); // this shows the AM/PM ending on time (or not) if desired.


Useful if I wanted to do things like:

time := LimsTime()
date := Today();
IF RIGHT(time,2)='AM';
     :IF LEFT(time,2) <'8';
etc., etc.

This would tell me if it was before 8 am, something very useful if I needed to fire off an email report.

or, perhaps:

:IF  LimsTime() > '4:30:00' .AND. LimsTime() < '0830:00' .AND. DOW(Today())=2;





.etc, etc.

This let me play with a time frame and say if it was before 8:30 and 4:30, do...

Playing with DOW() in V9

At one point I was called upon to determine the day of the week and fire off different emails from starlims depending on the day.  That led me to some doodling with the Dow() function that I figured I would share.

:DECLARE myary, strDate;

:BEGINCASE;
  :CASE DoW(Today())=1; /*SUNDAY;
         strDate := Today() + 1;
  :EXITCASE;
  :CASE DoW(Today())=2;
         strDate := Today() +7;
  :EXITCASE;
  :CASE DoW(Today())=3;
         strDate := Today() +6;
  :EXITCASE;
  :CASE DoW(Today())=4;
         strDate := Today() +5;
  :EXITCASE;
  :CASE DoW(Today())=5;
         strDate := Today() +4;
  :EXITCASE;
  :CASE DoW(Today())=6;
         strDate := Today() +3;
  :EXITCASE;
  :CASE DoW(Today())=7;
         strDate := Today() +2;
  :EXITCASE;
:ENDCASE;

Str() vs. LTransform() vs. LIMSString()

Each of one of these beauties has their benefits and uses.  Knowing which one is best to use in a given situation can make the difference in your code you might be looking for.

Str() simply converts a numeric expression to a string and is very similar to LTransform(), but uses length and decimal specifications instead of mask like LTransform().  Its very useful when all you are juggling is numbers that need to be formatted as strings.  It looks like this:

Str(Numeric Expression, Length, Decimals)

I'm not going to explain what Numeric Expression means.  It should be obvious.  Length, though isth e length of the string to return, including the decimal point, digits and sign.  A value of -1 specifies that only the significant whole digits to the left of the decimal point are returned and suppresses any right padding.  Decimal places, however, are still returned as specified in Decimals.  If Length is not specified, the length returned is the actual length of the Numeric Expression.

Decimals is the number of decimal places in the return value.  A value of -1 specifies that only the significant digits to the right of the decimal point are returned.  The number of whole digits in the return value, however, are still determined by the Length argument.  If Decimals is not specified, the decimals returned is the actual decimals (if used) of the Numeric Expression.

The Str() function employs rounding.   If Length is less than the number of decimal digits required for the decimal portion of the returned string, the return value is rounded to the available number of decimal places.If Length is specified, but Decimals is omitted (no decimal places), the return value is rounded to an integer.


LTransform() is a little different.  It too can be used to just handle numbers but its more flexible than that.  It can convert any value into a formatted string.  It can format character, date, logical, and numeric values and   format the data for output to the screen or the printer.  The formatting is defined in the parameters.  It looks like this:

Ltransform(Value, Picture)

The usefulness of LTransform() is its ability to present data more cleanly or in a format without using clumsy lengths of concatenation or to perform transformations on data for presentation, like uppercasing all characters, specifying a comma position, or just enclosing negative numbers in a parenthesis.  The Picture parameter is formatted as follows:

Function string:  A picture function string that specifies formatting rules for the return value as a whole, rather than to particular character positions.  The function string consists of the @ character, followed by one or more additional characters as listed below.  If a function string is present, the @ character must be the leftmost character of the picture string, and the function string must not contain spaces.  A function string can be specified alone or with a template string.  If both are present, the function string must precede the template string, and the two must be separated by a single space.
Function                 Action
B                     Displays numbers left-justified
C                     Displays CR after positive numbers
D                     Displays date in SET DATE format
E                      Displays date in British format
R                     Non-template characters are inserted
X                     Displays DB after negative numbers
Z                     Displays zeros as blanks
(                      Encloses negative numbers in parentheses
!                      Converts alphabetic characters to uppercase

Template string:  A picture template string specifies formatting rules on a character by character basis.  The template string consists of a series of characters, some with a special meaning, as shown below.  Each position in the template string corresponds to a position in the Value.  Because Ltransform() uses a template, it can insert formatting characters such commas, dollar signs, and parentheses.Characters in the template string that have no assigned meaning are copied into the return value.  If the @R picture function is used, these characters are inserted between characters of the return value; otherwise, they overwrite the corresponding characters of the return value.  A template string can be specified alone or with a function string.  If both are present, the function string must precede the template string, and the two must be separated by a single space.

Template                     Action
A,N,X,9,#                      Displays digits for any data type
L                                    Displays logicals as "T" or "F"
Y                                   Displays logicals as "Y" or "N"
!                                     Converts an alphabetic character to uppercase
$                                    Displays a dollar sign in place of a leading space in a numeric
*                                    Displays an asterisk in place of a leading space in a numeric
.                                     Specifies a decimal point position
,                                     Specifies a comma position


LIMSString() is probably the most flexible of the three.  It can return any input except an array or object as a string.  Arrays can be handled if you break them down to values, such as Array[1] or Array[1,1].

Pointers on posting text via scripts

Its often useful to check the size of the column before you post text to it.  For varchar columns, use Len().  For text, use Datalength().

So:  SELECT LEN(mytextfield) AS varcharsize

or

SELECT DATALENGTH(mytextfield) AS textsize

Of course this only gives you the length of what's been put in there and not its max ALLOWABLE size.  To do that, use a query like the following:

SELECT     COLUMNPROPERTY(OBJECT_ID('dbo.yourtablename'), 'yourcolumnname', 'precision') AS colLength

This can be really, really useful when you are posting notes back to your database, for example, to make sure you do not overflow the allowed size.  Here I check against my NOTES table.

checklen := SqlExecute("SELECT     COLUMNPROPERTY(OBJECT_ID('dbo.NOTES'), 'NOTES', 'precision') AS col


:IF  val(checklen[1,1]) < texttoupdate;
     //commit the text data
:ENDIF;

Personally, I just split the data into chunks allowed by the column and enter it into the table.  I use a management field as the id to track the split note so it appears as a single note to the user and to other scripts that need an unique value.

The most undervalued StsMes() function

I found the ability to display small messages in the status bar to be very useful and employed it as a means of providing quick feedback to users.  Its something that requires a bit of training at first if they are not used to it but most people who have been surfing the web seemed to understand it.

Showing a count of items in the Console with SQL

At one point I needed to show a count of items in the console link provided.  To get there I used a format similar to the following and then mapped it accordingly:

"SELECT 'NAME', '(' + CONVERT(VARCHAR(4), COUNT(F.ITEMS)) + ') MY ITEMS' as Due,'',1 as Sort
   FROM TABLE
 UNION 
SELECT 'NAME', '(' + CONVERT(VARCHAR(4), COUNT(F.ITEMS)) + ') MY ITEM LIST TWO' as Due,'',2 as Sort
   FROM TABLE
Order by Sort"

'NAME' is the name of the console branch.
Obviously the convert to varchar is how I'm turning the numerical count into something textual to display.  The actual display with be (count) Name of branch item.  In this case, (#) My Items followed by (#) My Item List Two.

The Union adds the two queries together.  Order seems pretty self explanatory.  So it would look like this in the v9 console:

NAME
   (#) My Items
   (#) My Item List Two

Good for knowing something at a quick glance in the console, such as a number of records, pending tests, and so on.

StopAction()

An old favorite in v9, this allowed for halting action expressions, including returning a value when stated criteria was met.

The help file uses it in relation to a Confirm function, which is nice.  I typically use it with those, and custom forms to act like a return keyword in most languages since it could return a value as well.

So you could call a StopAction(My Value) and halt a flow of logic and return a value.  Useful in a lot of situations. 

Everything is good with examples so let's look at one:


arrGotOne := SqlExecute("Select ORIGREC from HDHISTORY where MBNO = ?strMBNO?");

:IF len(arrGotOne) > 0;
    UsrMes("Can not Delete","This MB Number has been used.  We can not delete it from the list.  Retire it");
    stopaction();  
:ELSE;
    SqlExecute("Delete from HDDRIVES where ORIGREC = ?intORIGREC?");
:ENDIF;

Building a simple search form

Code for a very, and I do mean, very, simple search form.


:DECLARE oUserForm, sTXT, sSoftware,sOKButtonClick,sButton4, oCSFac;

X := LimsAPICall("GetSystemMetrics","LONG",,0);
Y := LimsAPICall("GetSystemMetrics","LONG",,1);

oUserForm := UserForm{(X-400)/2,(Y-190)/2, 400, 190, "Search Catalog"};
oUserForm:AddControl({"sTXT", 15, 90, 100,25, "TEXT","Number (including zeros):" });
oUserForm:AddControl({"sSoftware",110, 90, 100, 20, "SE","",""});
oUserForm:AddControl({"sOKButtonClick", 60, 20, 80, 25, "PB", "OK"});
oUserForm:AddControl({"sButton4", 180, 20, 80, 25, "PB", "Cancel"});
oUserForm:EnableMinBox(.F.);
oUserForm:EnableMaxBox(.F.);
oUserForm:Display(.T.);

The LimsAPICall to GetSystemMetrics is a nice way to get positioning information.

Otherwise the rest of the form is built on the fly (easy to adjust that way) and then displayed.

Export Coordinates workaround

I wrote the following as a kludge to get around individuals changing the coordinates of the starlims window.  Its neither pretty or the best example of coding but its functional.  Just put in in an action and call it where ever you need it.

It saves the previous form and browser locations to alternate fields (ALTFLD, ALTLINK: pre-existing fields reutilized).  It loads these values into memory, validates them against the exiting values and reapplies the original values.  The Process begins in the Preload event and ends in the ONclose event.  Pretty much required for all child windows as well as the container window or child windows lose their locations as well;

:DECLARE WindowID, nSize, check, ncheck, InitSize,chkName;

/*Allows changes made by admins but otherwise tosses input;
chkName := '<<USERNAME>>';

:IF (.NOT.chkName = 'sysadm');
/*Add more admins however necessary;

/*Originally used GetAPPID() but the function was not always consistent in returning the correct window when child windows are involved;
   WindowID:='SHELL';

    nsize := sqlexecute("Select FORMSIZE, wndorigin FROM WNDMAINT WHERE windowid = ?WindowID?", "DICTIONARY");
    InitSize := sqlexecute("Select ALTFLD, ALTLINK FROM WNDMAINT WHERE windowid = ?WindowID?", "DICTIONARY");

    :IF .NOT.comparray(InitSize, nSize);
        :DECLARE updateSize, UpdateOrig;
        UpdateSize := Initsize[1,1];
        UpdateOrig := InitSize[1,2];
            sqlexecute("Update WNDMAINT SET FORMSIZE = ?UpdateSize?, WNDORIGIN = ?UpdateOrig? Where WINDOWID=?WindowID?", "DICTIONARY");        
    :ENDIF;
:ENDIF;