Tornado Class Library

Special Topic - MagicCell

MagicCell is one of the most exciting feature of ASP-db.Net. It provides a powerful filter for the data going to the web page. One line of MagicCell code usually is equivalent to an application. MagicCell applies to (Grid) dbMagicCell, (EmbeddedGrid) dbEmbeddedGridMagicCell, (Form) dbFormMagicCell  and (Edit) dbEditMagicCell.

Some examples of the transformation are -

 

Delimiters

ASPdb.net employs a pipe like format for random data entry. That menus data can be entered as field=1| macro=xxx or Macro=xxx| field=1 or fi=1| mac=xxx. All the keywords of the properties use an minimum abbreviated format. That is, you can use the minimum characters of the keyword within the same property w/o conflict with another property. If the property is dbMagicCell = "field | Tag | Macro". You can actually use dbMagicCell = "f=1| t=class=red| m=Hello #1#". There is no two fields with the same first character. If there is one then you can go to the next character for the field as fi=.. | ta=..| ma=... On the opposite side of the abbreviation, you can go long hand and as long as you want. You can use fieldname=1| Tag=..| Macro=.... The delimiter used in this example is a vertical bar. A standard pipe delimiter. Only in some rare cases that you need to change delimiters. In MagicCell, many times you have to do that. The delimiter format is explained in the dbMagicCell property, but we'll provide examples for every one of them here.

 

Basic MagicCell

Format - Mydb.dbMagicCell="(;,[]#|~/+{})Field | tag | Macro ~ Repeat"

There are two types of MagicCell operations - Basic and Extended. Basic MagicCell is used under normal conditions as well as in Embedded conditions. There are (3) Keywords - Field | tag | Macro. Field is the field's name or ordinal. Tag is the table <TD> tag and Macro is the transformation macro. For Example -

  Dim CS As New Tornado.z
  With CS
    .dbQP = "U=1| D=nwind | Q=employees| gdf=0"
    .dbSQL = "Select employeeid, lastname, firstname from employees"
    .dbMagicCell = "fi=0 | mac=Employee name is #2# #lastname# and id is #0#"
    .ASPdbNET()
  End With

Here we select 3 fields from table - employeeid(0), lastname(1), firstname(2) where (/0 is the field's zero based ordinal.

Transformation format is as follows - Field 0 = Transformation Macro

 

Extended MagicCell

Format - Mydb.dbMagicCell="(;,[]#|~/+{})Field | tag | (Extended Macro ~ Repeat)"

Extended MagicCell has the macro wrapped in (...). An Extended MagicCell macro can use the magic operators as well as pre-programmed functions.  Example -

Dim XM As New Tornado.z
With XM
.dbQP = "U=2|D=NWIND|Q=Select FirstName,LastName FROM Employees"
.dbGridMagicCell = "fi=FirstName|Mac=(#FirstName#|NE|Andrew||#FirstName:3:>#)"
.ASPdbNET()
End With
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim MF As New Tornado.Z()
With MF
   .dbQP = "S=1|DSN=cars| Bookmark=carspecs;0| TextHolder=Title=Magic Master and Thumbnail Images"
   .dbSQL = "Select car, manufacturer, picture from carspecs "
   .dbGridMagicCell = "(;![]#|~/+{})fi=picture| Macro=#picture:_tn=I=/tornado/db/images-car/~T=width=128~W=200,200~ N=_nocar.gif#"
   .dbOptions="MagicTNInitFrame=380,232"
   .ASPdbNET()
End With
End Sub
</script>
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim EditAll As New tornado.z
   With EditAll
      .dbQP = "U=7|s=12|Sm=false|ps=-1|D=Nwind|ni=none|gdf=lastname,firstname,region|Q=employees"
      .dbGridMagicCell = "fi=region| Macro=(#region#|NB|||<a href='filtertest2.aspx?region=#region#'>result</a>~#region#|EL|||)"
      .ASPdbNET()
   End With
End Sub
</script>
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim x As New Tornado.z
   x.dbQP = "u=1| D=NWIND| Q=Select orderid, freight from orders"
   '...x.dbMagicCell = "(~?[]!|~/+{})fi=freight| tag=align=right class=red"
   '...x.dbMagicCell = "(~?[]!|~/+{})fi=1| tag=align=right| mac=!1:#,###.0;(#,###.0)!"
   x.dbMagicCell = "(~?[]!|~/+{})fi=1| mac=(!1!|LTN|0|align=right|<font color=red>!1:#,###.0;(#,###.0)!</font>~|EL||align=right)"
   x.ASPdbNET()
End Sub
</script>

 

Format within #Field# Macro

The are two formats within the #Field# macro -

   Value:Length:Format

Value = Raw value of field

Length = Length of the field. Apply mostly to String

Format = The VB format of the value.

Exanples -

MagicCell Macro

The Macro can be the following -

Tag Description

#AspdbIndex#

Current record pointer.
X.dbMagicCell="fi=1| Mac=Last Name of Record - #aspdbIndex#"

#Field:Length:FormatString#

FormatString  = All valid VB Format function strings.

Example -

X.dbSQL="SELECT ProductID, UnitPrice, Quantity, Discount, 
          UnitPrice * Quantity * (1-Discount) as ExtPrice 
           FROM [Order Details] WHERE OrderID=123
X.dbMagicCell="fi=UnitPrice| macro=#unitPrice:currency#, fi=Quantity| tag=right" 
#Filename.# If Filename EndsWith "." then it is assumed a filename is the value. File extension will be discarded. 
#FieldName or Number# Value of field after going through magic filter.
#(FieldName or Number)# URL Encoded value of field after going through magic filter.
#[FieldName or Number]# HTML Encoded value of field after going through magic filter.

#Me#

ServerVariables("SCRIPT_NAME")
#SQL# Current SQL.
#(SQL)# Current SQL URL encoded. Any single quotes will be converted to double and original doubles will be preserved as it is assumed that URL encoded strings is begin passed to the next operation.
<CR> Chr(13)
<LF> Chr(10)
#PopText# This is the same as the Memo poptext except that the Text field is popped. Use the same dbPoptext property.

Magic Operators

Macro Oper
 Tags
Description Macro Oper
 Tags
Description
EQ? Equals to. If numeric, convert to Double. e.g. EQ|1234 or EQ|ABCD  NE? Not equals to. If numeric, convert to Double. e.g. NE|1234 or NE|ABCD 
GT? Greater than. Numeric only. e.g. GT | 123.45 GE? Greater than or Equal to. Numeric only. e.g. GE | 1234
LT? Less than. Numeric only. e.g. LT | 1234 LE? Less than or Equal to. Numeric only. e.g. LE | 1234
CT?, CX? Contains text. e.g. CT | abc. CT=True; CX=False IT?, IX? In text. e.g. IT | abc. IT=True; IX=False
RA?, RX? Within numeric range. (low � high). Must use "- as delimiter. e.g. RA | 10000-20000. RA=True; RX=False ST?, SX In set. e.g. ST | GM/Ford/Toyota  ST=True; SX+False
SW? Starts with char(s). e.g. SW|A EL? Else (the rest). e.g. EL| | |C. Compare value will be ignored.
ND Not a date - Not IsDdate(field) Logical Test (L) NN Not Numeric - Not IsNumeric(field) Logical Test (L)
IN IsNull(Field) Logical Test (L) EV, NV Is Even (logical). EV=True; NV=False
IB, NB Is Blank. IB=True; NB=False
NB test is true if the value is a 'blank' or a 'null'.
EP, EX Regular Expression (Pre-programmed and User Specified)
EP=true; EX=False
FE, FX File exist. FE=True; FX=False LI, LX Like (Pattern matching) LI=True; LX=False
 

?=Data Type (S,N,D or B) where the values are converted using the functions as indicated.

S=String Cstr() (default) 
N=Numeric  Cdbl()
D=Date     CDate()
B=Boolean  CBool()     
L=Logical (Type or Null Test only - No conversion)  

 

EP, EX - Pre-Programmed Regular Expression

Use regular RE or the following Pre-Programmed Expressions. Same rules for Validator and MagicValidate applies here. Pre-programmed masks are -

Data type is ignored.

 

Pre-Programmed MagicCell Functions

In additional of using standard cell formatting, logical and calculation to obtain sophisticated cell value transformation, a new feature of MagicCell is added and known as Preprogrammed Functions. The syntax is #field:_FunctionName=Parameters#. When the macro is prefixed with an '_' then the preprogrammed functions will kick in. The functions are -
 

<body onLoad="self.focus()">
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim MF As New Tornado.Z()
  With MF
    .dbQP = "S=1|DSN=cars| Bookmark=carspecs;0| TextHolder=Title=Magic Master and Thumbnail Images"
    .dbSQL = "Select car, manufacturer, picture from carspecs "
    .dbGridMagicCell = "(;![]#|~/+{})fi=picture| Macro=#picture:_tn=I=/tornado/db/images-car/~T=width=128~W=200,200~ N=_nocar.gif#"
    .dbOptions="MagicTNInitFrame=380,232"
    .ASPdbNET()
  End With
End Sub
</script>

Math Formula Operators

+ - * / \ ^ MOD = < > <= >= <> AND OR XOR NOT PI ASB INT FIX SGN SQR LOG EXP SIN COS TAN ATN MIN(a,b) MAX(a,b) IIF(Test,True,False)

FE, FX - File

The macro is expected to be a full virtual path to be tested for file existence.

LI, LX - Like Operator (Pattern Matching)

If string matches pattern, result is True; if there is no match, result is False. If both string and pattern are an empty string. the result is True. Otherwise, if either string or pattern is an empty string, the result is False.

The behavior of the Like operator depends on the Option Compare statement. The default string-comparison method for each module is Option Compare Binary.

Built-in pattern matching provides a versatile tool for string comparisons. The pattern-matching features allow you to match strings using wildcard characters, character lists, or character ranges, in any combination. The following table shows the characters allowed in pattern and what they match:

Characters in pattern Matches in
? Any single character
* Zero or more characters
# Any single digit (0-9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist

A group of one or more characters (charlist) enclosed in brackets ([ ]) can be used to match any single character in string and can include almost any character code, including digits.

Note   To match the special characters left bracket ([), question mark (?), number sign (#), and asterisk (*), enclose them in brackets. The right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

By using a hyphen (-) to separate the upper and lower bounds of the range, charlist can specify a range of characters. For example, [A-Z] results in a match if the corresponding character position in string contains any uppercase letters in the range A-Z. Multiple ranges are included within the brackets without delimiters.

The meaning of a specified range depends on the character ordering valid at run time (as determined by Option Compare and the locale setting of the system the code is running on). Using Option Compare Binary, the range [A�E] matches A, B, C, D, and E. With Option Compare Text, [A-E] matches A, a, , B, b, C, c, D, d, E, and e. The range does not match or   because accented characters fall after unaccented characters in the sort order.

Other important rules for pattern matching include the following:

In some languages, there are special characters in the alphabet that represent two separate characters. For example, several languages use the character to represent the characters a and e when they appear together. The Like operator recognizes that the single special character and the two individual characters are equivalent.

When a language that uses a special character is specified in the system locale settings, an occurrence of the single special character in either pattern or string matches the equivalent two-character sequence in the other string. Similarly, a single special character in pattern enclosed in brackets (by itself, in a list, or in a range) matches the equivalent two-character sequence in string.

 

Example

This example uses the Like operator to compare a string to a pattern. The result is a Boolean value representing whether the string fits the pattern.

"F" Like "F"   ' Does "F" match "F"? Returns True.
"F" Like "f"   ' Does "F" match "f"? Returns False.
"F" Like "FFF"   ' Does "F" match "FFF"? Returns False.
"aBBBa" Like "a*a"   ' Does "aBBBa" have a "a" at the 
   ' beginning, an "a" at the end, and any number of characters in 
   ' between? Returns True.
"F" Like "[A-Z]"   ' Does "F" occur in the set of
   ' characters from A to Z? Returns True.
"F" Like "[!A-Z]"     ' Does "F" NOT occur in the set of
   ' characters from A to Z? Returns False.
"a2a" Like "a#a"     ' Does "a2a" begin and end with an
   ' "a" and have any single-digit number in between? Returns True.
"aM5b" Like "a[L-P]#[!c-e]" ' Does "aM5b" fit the following 
   ' pattern: Begins with "a", has and character from the set L through
   ' P, followed byb any single-digit number, and finally contains any
   ' character excluded from the character set c through e. Returns True.
"BAT123khg" Like "B?T*"  ' Does "BAT123khg" fit the 
   ' following pattern: Begins with "B", followed by any single
   ' character, followed by a "T" and finally zero or more characters
   ' of any type. Returns True.
"CAT123khg" Like "B?T*"  ' Does "CAT123khg" fit the 
   ' following pattern: Begins with "B", followed by any single
   ' character, followed by a "T" and finally zero or more characters
   ' of any type. Returns False.

 

 

Color styles you can use to in the magic cell -

 Aqua, Beige, Black, Blue, Brown, Chocolate, Crimson, DarkBlue, DarkCyan, DarkKhaki, DarkViolet, DeepPink, DeepSkyBlue, Gold, GoldenRod, Gray, Green, GreenYellow, Khaki, LightBlue, LightCoral, LightGrey, LightGreen, LightSalmon, LightSeaGreen, Lime, Magenta, Olive, Orange, OrangeRed, PaleVioletRed, PeachPuff, Pink, Purple, Red, RoyalBlue, SaddleBrown, Salmon, SandyBrown, SeaGreen, Silver, SkyBlue, SlateGray, SteelBlue, Teal, Tomato, Violet, White, Yellow

 

Set cell color styles according the the values of two fields
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
    Dim Mag As
  New  Tornado.zMag.dbQP="u=1|s=1|d=nwind|q= select ProductName,UnitsInStock,UnitsOnOrder fromProducts|th=ti= 
  MagicCell  Compare"Mag.dbMagicCell="fi=1|mac=(#1#|GTN|#2#|class=green~|EL||class=red)"
  Mag.ASPdbNET()
End Sub
</Script>

 

EditMagicCell

Format - Obj.dbEditMagicCell ="(;,[]#|~/+{})Field | Tag| Macro"

EditMagicCell applies to RONOUPDATE edit fields and can only use the 'self' value as macro as the other edit fields will be in HTML format. An example application of this property is to use this RONOUPDATE field to create a link in a grid edit setup. Edit default values are not supported in EditMagicCell as Edit works with "Raw" value only. When Tag is defined like (Tag=Class=Yellow) or the tag contains </td>, then the system TD class will be disabled. See the example below -

 

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
'... works on NOUPDATE & NOADD fields
Dim M As New tornado.z
  M.dbQP= "U=1| D=NWIND| Q=Select employeeid, Firstname from employees| ni=b5,update| bm=employees;0"
  M.dbEditUpdateFlds = "fi=0| Ty=RONOUPDATE, 1"
  M.dbEditMagicCell = "(;,[]!|~/+{})fi=0|Tag=class=Yellow|Mac=format=!0::##! http://www.aspdb.com'>Link - !0!"
  M.dbMagicCell = "(;,[]!|~/+{})fi=0| Tag=class=green|Mac=format=!0::##!"
  M.ASPdbNET()
End Sub
</Script>

 

Embedded GridMagicCell

Format - Obj.dbEmbeddedGridMagicCell ="(;,[]#|~/+{})Field | tag | Macro"

Normal macro is only for embedded MagicCell - Basic only, No Extended or Preprogrammed.

* (2008 version) When the embedded magic grid returns no data then dbBlankText will be used (def = 'No Data').

Dim pop As String = "<AHREF='/tornado/Work/eg_MagicCellAdvancedDetail.aspx?Details=^0^' Target='Details' onClick=""window.open('','Details', 'width=500, height=500, left=400, top=400, directories=no, menubar=no, toolbar=no, status=no, resizable=yes')"">^0^</A>"

.dbEmbeddedGridMagicCell = "(;![]^|~/+{})fi=OrderID ! mac=" & pop

 

<body onLoad="self.focus()">
<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
   Dim myself = Request.ServerVariables("SCRIPT_NAME")
   If Request.QueryString("details") = "" Then
      Dim pop As String = "<A HREF='" & myself & "?Details=#0#' Target='Details' onClick=""window.open('','Details', 'width=500, height=500, left=400, top=400, directories=no, menubar=no, toolbar=no, status=no, resizable=yes')"">#0#</A>"
      Dim E63 As New tornado.z()
         With E63
         .dbQP = "U=60| S=USA| M=G| D=Nwind| GDF=CustomerID,CompanyName,ContactName"
         .dbEmbeddedGridMagicCell = "(;![]#|~/+{})fi=OrderID|mac=" & pop
         .dbGridMagicCell = "(;![]#|~/+{})field=CompanyName|macro=#1#<br>#4#<br>#5#, #7#<p>#2#<br>#4#<br>#9#! field=ContactName|Macro=SQL:SELECT OrderID,OrderDate,RequiredDate,ShippedDate from orders where customerID= '#0#'"
         .dbNameMap = "field=CustomerID|alias=ID,field=CompanyName|alias=Customer,field=ContactName|alias=Orders"
         .dbEmbeddedGridNameMap = "field=OrderID|alias=ID,field=OrderDate|alias=Ordered,field=RequiredDate|alias=Required,field=ShippedDate|alias=Shipped"
         .dbSQL = "SELECT CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode, Country,Phone,Fax FROM Customers"
         .dbTextHolder = "Title=Tornado Demo - Advanced Master with embedded Charting Details|subtitle=Can you image you can do this with 57 lines of code without knowing too much about ASP?"
         .ASPdbNET()
         End With
   Else
   Dim ID As String = Request.QueryString("Details")
   Dim E61 As New tornado.z()
      With E61
      .dbQP = "U=61| S=tyairgate!bgpic=pagebg2.gif| M=GC| PS=-1| Ni=None| D=Nwind.mdb"
      .dbGridMagicCell = "field=0|tag=align=middle,field=2|tag=align=right,field=4|tag=align=right,field=SubTotal|tag=align=right|macro=#SubTotal:currency#,field=Quantity|tag=align=right|macro=#quantity#"
      .dbCommonTables = "index=PID,PDesc|sql=Select ProductID,ProductName from Products"
      .dbLookUpFlds = "field=1|keyindex=PID|Lookupindex=PDesc"
      .dbSQL = "SELECT ProductId,ProductId as Description,UnitPrice,Quantity,Discount,UnitPrice*Quantity*(1 -Discount) as SubTotal from [order details] where OrderID=" & ID
      .dbAggregate = "(~^:)Fields=3^5|AggType=SUM|BottomTemp=<tr bgcolor=yellow align=right><td ColSPAN=3><b>Total </b></td><td>[[3:###,###]]<td><td Colspan=2 align=right>[[5:Currency]]</td>|CellFormat=Val"
      .dbTextHolder = "Title=Order Details for Order " & ID
      .dbChartData = "dataset0yValues=#(5)#| dataset0Labels=#1#"
      .dbChartParams = "Type=26| TitleString='Description vs SubTotal Spinning Plot'| titleFont='Arial,14,1'| Archieve=/tornado/Jars/|Width=350| Height=350| 3D=True| textLabelsOn=True| pieWidth=.40| pieHeight=.40| valueLabelsOn=True"
      .dbGridTemplate = "<center>[[Grid]]<P>[[Chart]]</center>"
      .ASPdbNET()
      End With
   End If
End Sub
</script>

The above very advanced example includes a master module, a slave module with a chart module in the slave.

 

Magic Expression (Formula)

Magic Expression/Formula is enclosed by a pair of <<....>>. You can use the Math Operators listed above.

<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
Dim SP1 As New Tornado.z
   With SP1
      .dbQP = "U=1| S=7| Ps=10| D=Nwind| Th=ti=Magic Formula| gdf=0,1,5,6"
      .dbSQL = "Select * From Products"
      .dbNameMap = "fi=6|al=Unit Price + $10"
      .dbMagicCell = "fi=6|Mac=<<#5# + 10.0:c>>"
      .ASPdbNET()
   End With
End Sub 
</script>

Execute the above code and you'll see field #6 is used as a dummy and has the value of #5# + $10. The :c is to display in currency format.

Note: If your format has ### in it then you must change the default #field# wrapper to some other characters like '!'.
 

MagicCell & JavaScript

You can easily mix JS with MagicCell to build a very advanced application

<script language="vb" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
'------------------- User defined Javascript code -----------------
Dim Js As String = vbCrLf & "<!-- Beginning of Java Functions -->" & vbCrLf
Js &= "<" & "script language=""javascript"">" & vbCrLf
Js &= "function formatANumber(pval) {" & vbCrLf
Js &= "var val = new String(pval);" & vbCrLf
Js &= "if (isNaN(pval)) {" & vbCrLf
Js &= " document.write(""#Error#"");" & vbCrLf
Js &= " } else {" & vbCrLf
Js &= " var pos=val.indexOf(""."");" & vbCrLf
Js &= " var dec=((pos>-1)?""""+val.substring(pos,pos+3):"".00"");" & vbCrLf
Js &= " var sign=(val.charAt(0)==""-"")?""- "":"""";" & vbCrLf
Js &= " var val=""""+Math.abs(parseInt(val));" & vbCrLf
Js &= " var len=val.length;" & vbCrLf
Js &= " var temp="""";" & vbCrLf
Js &= " if (dec.length==2) dec+=""0"";" & vbCrLf
Js &= " if (dec.length==1) dec+=""00"";" & vbCrLf
Js &= " if (dec=="""") dec += "".00"";" & vbCrLf
Js &= " for (var k = 1; k <= len; k++) {" & vbCrLf
Js &= " temp=((k%3)==0)&&(k!=len)?("" ""+val.charAt(len-k)+temp):(val.charAt(len-k)+temp)" & vbCrLf
Js &= " }" & vbCrLf
Js &= " document.write(sign + temp + dec);" & vbCrLf
Js &= "}}" & vbCrLf
Js &= "</" & "script>" & vbCrLf
Js &= "<!-- End of Java Functions -->" & vbCrLf
'------------------------ ASPdb.Net Code ------------------
Dim Jsc As New Tornado.Z()
  With Jsc
    .dbUnit = 10632
    .dbSkin = 13
    .dbDSN = "NWIND.mdb"
    .dbMode = "grid"
    .dbSQL = "Select ProductName,UnitPrice,UnitsInStock,'extra' FROM Products"
    .dbNameMap = "field=3|alias=Show 2 <BR> formatted <BR> fields"
    Dim MC As String = "field=3|MAcro=Price = <" & "script language=""JavaScript"">formatANumber('#1#')</" & "script><BR>"
    MC &= "Stock = <" & "script language=""JavaScript"">formatANumber('#2#')</" & "script>"
    .dbGridMagicCell = MC
    .dbSendHead=Js
    .dbTextHolder="Title=Tornado Demo - MagicCell with embedded Javascript"
    .ASPdbNet()
  End With
End Sub
</script>

 

Another MagicCell and JS application in editing.

<SCRIPT LANGUAGE="JavaScript">
function MyJS() {
answer = confirm("U sure?");
if (answer == "true") {
return true; // accept the submit
}else{
return false; // go back to the form
}
}
</SCRIPT>
</HEAD>
<script language='vb' runat='server'>
Sub Page_Load(Source as Object, E as EventArgs)
  Dim JS As New tornado.z()
  With JS
    .dbQP = "U=1| D=NWIND| S=8| GDF=0,1,2,3| Ni=b5,update| euf=0,1,2| eaf=0,1,2| bm=customers;0| evn=MyJS()| TH=title=JS"
    .dbSQL = "Select * From customers where customerID = 'FRANK'"
    .ASPdbNET()
  End With
End Sub
</Script>

Send comments on this topic.
Copyright 1998-2006 ASP-db