O$ $sendall for complex grid

Doug Easterbrook doug at artsman.com
Mon Jun 6 14:15:16 UTC 2022

hi Paul…

Similar to what Rudolph suggests … we get postgres to return the text string containing icons, colours, and what have you as part of the sql call.

We came up with that idea late one night at Euromnis in Valkenburg (… many many years ago).    and it significantly improves performance retrieving and populating columns in omnis lists.


A key performance issue in an omnis list is the changing of length of field contents.   if you add a single character to a character string, omnis, unpacks the list line and repacks it.   if you do that to a list with many many lines in it, it unpacks the list, replaces the string with the new string, shuffles all memory for the remaining lines down and then repeats.    each memory shuffle adds processing time and it will hurt the performance of the application.

effectively, we define a string fields (we call it ‘gIcons’) that we replace in our SQL with either a stored procedure or a postgres case statement so that we can examine any data in and field to determine the content of the icon/colour/style etc. that we display in a column.    We mostly use it for headed lists, but the concept is the same for complex grids where we also use this.

for example (let use postges and a table class)

we defined two store procedures called ’getcolorstyle’ and ‘getIcon’ which I’ve put at the end.  getIcons works with all omnis built in icons and those you put into #ICONS yourself.

then we include ‘gIcons’ in the schema.

when building the sql in the table class to fetch data, we might replace gIcons with

Begin Statement
sta: case when balance<0 then getColorStyle(‘red’)||getIcon(1052)
sta: when balance=0 then getColorStyle(‘blue’)
sta: when balance>0 and last_name is not null then getColorStyle(‘darkgreen’)
sta: end as gIcons
End statement
get statement query
calc sqlStatement as replace(‘gIcons’,query)

so, if gIcons is in the statement, it returns a bunch of colours or icons or what have you that are defined on data fields in your join.     Needless to say, you can make these as complicated as you want, and/or, you can use other stored procedures to make something really useful.     I’ll send you an image privately so you can see it.

advantage of a store procedure for figuring out what to display — its in the database.    that means we on’y have to change the stored procedure, rather than having to change code in every list where we want to display the variable.

it also means we can join two functions that create icons and dedup the icons if we are joining two tables or more tables to give an indication on the records.

This concept is so pervasive in our app, that we just assume every database return is fast..    which it is.

you can also use this same idea in returning clauses if you use that.

update table1 set field1=‘a',field 2=‘b’ returning  getIconsForThisTable (Table) as gIcons.

so, every database update we do, automatically updates the icons on return

hope that makes sense.

-- Get Omnis Compatible Icon Escapes
CREATE OR REPLACE FUNCTION getIcon(IN icon_number integer) RETURNS varchar AS
	return_string varchar;
  --	V80803 rewritten to be generic and allow all icons by constructing the hex string for Omnis icons
	return_string := chr(27)||chr(8)||to_hex(2415919104+icon_number)||'#';
	RETURN return_string;

-- Get Color Style Codes From Omnis
CREATE OR REPLACE FUNCTION getColorStyle(IN color_name varchar)
RETURNS varchar AS
	color		  integer;
	return_string varchar;
	IF	   color_name	= 'gray' THEN		color := 44;
	ELSEIF color_name	= 'yellow' THEN		color := 43;
	ELSEIF color_name	= 'magenta' THEN	color := 42;
	ELSEIF color_name	= 'red' THEN		color := 41;
	ELSEIF color_name	= 'cyan' THEN		color := 40;
	ELSEIF color_name	= 'green' THEN		color := 39;
	ELSEIF color_name	= 'blue' THEN		color := 38;
	ELSEIF color_name	= 'darkgray' THEN	color := 37;
	ELSEIF color_name	= 'darkyellow' THEN color := 36;
	ELSEIF color_name	= 'darkmagenta' THEN color := 35;
	ELSEIF color_name	= 'darkred' THEN	color := 34;
	ELSEIF color_name	= 'darkcyan' THEN	color := 33;
	ELSEIF color_name	= 'darkgreen' THEN	color := 32;
	ELSEIF color_name	= 'darkblue' THEN	color := 31;
	ELSEIF color_name	= 'black' THEN		color := 30;
		RETURN '';
	return_string := chr(27) || chr(1) || upper(to_hex(2147483648+color)) || '#';
	RETURN return_string;

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
Phone (403) 650-1978

> On Jun 6, 2022, at 1:56 AM, Rudolf Bargholz <rudolf at bargholz.ch> wrote:
> Hi Paul,
> Why not adapt your data fetching method, probably SQL, to fetch the data for the icon display in your fetching method. If performance is really an issue, then why not have a column in your complex grid, in which you place a text background object, and then use the following as the text value
> [style(kEscBmp,List.IconId)]
> where List.IconId is the icon id value of the icon you want displayed, and 0 if no icon is to be displayed.
> This will then display the icon dynamically as the complex grid is being drawn on the screen and avoid having to go over each line in the drawn complex grid to assign an icon to a field in a post processing step.
> Another way of having this draw faster is setting the object you are trying to redraw to invisible before starting the redraw process, and setting it to visible after the redraw has finished, or even better
> Calculate $cwind.$norefresh as kTrue
> Now do your drawing
> Calculate $cwind.$norefresh as kFalse
> This will prevent Omnis from refreshing changes to your complex grid as these are being made, and only display changes when the processing has completed.
> Regards
> Rudolf Bargholz
> -----Ursprüngliche Nachricht-----
> Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Paul Mulroney via omnisdev-en
> Gesendet: Montag, 6. Juni 2022 05:00
> An: Omnis-dev list <omnisdev-en at lists.omnis-dev.com>
> Cc: Paul Mulroney <pmulroney at logicaldevelopments.com.au>
> Betreff: O$ $sendall for complex grid
> Hi $all,
> I have a complex grid, and I want to show an icon on certain lines and not others.
> I have a test window that has this code in the $construct:
> Do ilData.$define(ibSelected,inStatus,isText)
> Do ilData.$add(kFalse,1,'Line 1')
> Do ilData.$add(kFalse,2,'Line 2')
> Do ilData.$add(kTrue,0,'Line 3')
> Do ilData.$add(kTrue,0,'Line 4')
> I want to do something like this, to set the icon based on the status.  If the status is zero, don't show an icon.  If it's 1, show a warning, if it's 2 show a stop sign.   However, this doesn't work:
> Do $cwind.$objs.ilData.$sendall($ref.$objs.btnIcon.$iconid.$assign(pick($sendallref.inStatus,0,1117+k16x16,1152+k16x16)))
> This will set the icon to a warning, but for all lines.  I can't figure out how to set it for specific lines:
> Do $cwind.$objs.ilData.$objs.$sendall($sendallref.$iconid.$assign(1117+k16x16),$ref.$objtype=kPushbutton)
> Any help much appreciated!
> Regards,
> Paul.
> A man rushed into the doctor’s office and shouted, “Doctor! I think I’m shrinking!”
> The doctor calmly responded, “Now, settle down. You’ll just have to be a little patient.”
> -- 
> Paul W. Mulroney                                            We Don't Do Simple Pty Ltd 
> pmulroney at logicaldevelopments.com.au       Trading as Logical Developments
> www.logicaldevelopments.com.au                   ACN 161 009 374 
> Ph: +61 8 9458 3889                                       86 Coolgardie Street
>                                                                         BENTLEY  WA  6102
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 

More information about the omnisdev-en mailing list