dbMagicCell | dbFormMagicCell | dbAggMagicCell

MagicCell is one of the highlight of ASP-db. It transforms the cell value according to a variety of conditions and formulas (Macro). Note that a lookup is a standard way of transforming a cell value. MagicCell provides means to transform values according to a macro or logical conditions or a math formula. These transformations combinations are essentially limitless. Some examples that we learn from user are - Text and Language translation, one line image DB by converting filename to a full <IMG..>. <CR> to <P> translation.. etc.. The nested translation capabilities have done wonders for some users. The new addition of the math formula evaluator adds fuel to the fire. Now, you can perform uniform calculated fields across different databases by using the equation evaluator in ASP-db without using SQL queries. "Divided by zero" condition is also arrested and you are allowed to replace this condition with your own text.

Property Syntax

Obj.dbMagicCell="(;,[]#|~/+{})Field,TDtag,(ValueMacro | OPER | Value | LGTDTag | DisplayMacro:Length:Format ~ … Repeat), Index, indexanchor, multiplier; …repeat"
Obj.dbFormMagicCell="(;,[]#|~/)Field,TDtag,(ValueMacro | OPER | Value | LGTDTag | DisplayMacro:Length:Format ~ … Repeat), Index, indexanchor, multiplier; …repeat"
Obj.dbAggMagicCell="(;,[]#|~/)Field,TDtag,(ValueMacro | OPER | Value | LGTDTag | DisplayMacro:Length:Format ~ … Repeat), Index, indexanchor, multiplier; …repeat"

Replace Text

See Example E31 

X.dbMagicCell="FirstName,,{ar+*A*R*+n+N}; LastName ,,(#LastName#|SW|F|BGCOLOR=YELLOW|#LastName#); Title ,,{<+&lt+>+&gt}; Notes ,,{the +<B>THE </B>+<CR>+<HR SIZE=4 COLOR=RED>}"

Parameters

Parameters Description
(;,[]#|~/+{}) Default delimiters. (;)=Group (,)=Field ([])=Format brackets (#)=MagicMacro Wrap (|)=Extended Field (~)=XMagicGroup (/)=In Set Delimiter (+)=Replace delimiter ({})=Wrapper of Replace Text like {T1+W1+T2+W2} -> replace text (T1) with Text (W1) and text (T2) with (W2) etc...

Field

Field name or number. 
TDTag TD tags of the cell
MagicMacro Basic magic filter macro
Length Truncate the length to Length. Will round off to the nearest space and add "...". Mostly for Memo fields.
Format Apply local format to each of the fields. See example.
Index Use this field as Index in lieu of std index. 
IndexAnchor Include anchor when Index is activated
Multiplier If value is numeric, multiplier it by this multiplier if not blank.
ValueMacro Magic type macro to generate a value.. Be careful with this value as it is generated by combining fields and the result is compared with "Compare Value". Think in this way, When displaying the field of Name, look at the field of Money. If Money > 100000 then Display "#Name# is Rich and has $#Money# otherwise, display "#Name# is Poor. See MagicMacro Tags table for special tags. When skipped, this will default to Field.
OPER Logical operator. See OPER Tags table.
Compare Value Value compared to the result produced by ValueMacro using OPER.
LGTGTag Logical TD Tag (essential for BGcolor). Overrides the Global Tdtags.
DisplayMacro Regular magic cell macro. If left blank that mean the original cell value will be used and only the LGTDTag will apply. This is a common case of changing the TD’s Bgcolor according to the different values.
A new addition to the function is a Replace function where the to-be-replaced values and the replace-values pairs are to be wrapped within a delimiters - default is {..+..+..+..}. The following example replace all the "BA " in he memo field to a bolded "Bachelor of Arts ". Note that the trailing spaces are honored. Note that if more than one replacement is desired, just keep going  like {R1+W1+R2+W2+R3+W3} where R? is replace this text and W? is with this text. If one of the paired item is blank then the replacing action will terminate. To repl
ace with a 'blank' use a double single quote [''] and not a blank.

The #me# tag will enable recursive replace. That means, replace myself (field content) according to the macro. This #me# is not the same as the SCRIPT_NAME.

The <<equation::Fmt>> format is a special case where the #fields# inside the <<...>> macro will be mapped first and then the content will go through a Math equation evaluator. For example <<(!Budget! - !Spent!)/ !Budget! * 100::###.##>> will cause the formula inside the <<..>> to be evaluated and formatted first. If !Budget! = 0 then a Divided-by-Zero condition will occur. This condition will be arrested and suppressed and the text dbOptions="DBZ=Divide-by-zero Text" will be displayed. See table below for supported operators. If you use <<....::Fmt>>, then most likely you cannot use # as the tag wrapper. The Fmt is there because this is the only way to format the equation results.

Example:
x.dbMagicCell = "ReportsTo,,(#reportsto#|LEN|3||#ReportsTo::currency# ~ EL||#ReportsTo#)"

 

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) 

 
Macro Tags

Tags Description

#AspdbIndex#

Current record pointer.
<%
Set X=Server.CreateObject("AspDB.EP")
X.dbQuickProps="1;NWIND;employees;Dual-horiz;4;;;ACCESS;5;1;1"
X.dbGridDisplayFlds="0,1,2"
X.dbMagicCell="1,,Last Name of Record - #aspdbIndex#"
X.
ASPDB
%>

Format=[Fmt_String]

Fmt _String  = All valid VB Format function strings.

X.dbSQL="SELECT ProductID, UnitPrice, Quantity, Discount, UnitPrice * Quantity * (1-Discount) as ExtPrice FROM [Order Details] WHERE OrderID=123
X.dbMagicCell="UnitPrice,align=right,format=[currency]; _
Quantity,align=right; Discount,align=right,format=[##0%],,,100;ExtPrice"
This tag will become obsolete and replaced by the local format like #field:length:Format#
Format=[Fraction:+/-:Base:LPF] Returns the fraction representation of the numeric cell value. 
+/- = Prefix with (-) when negative.
Base = Base resolution (e.g. 64 = ?/64) Numerator
LCF Lowest Prime Factor (e.g. 1 = 1/64) Denominator

Text.dbMagicCell="(;!)1!align=right!format=[fraction]; _2!align=right!(#2#|LT|0||<font color=red>format=[fraction:+/-]</font>~|EL|||format=[fraction]);_
3!align=right!format=[fraction];_
4!align=right!format=[fraction];_
5!align=right!format=[###,###]"
This item will go away in 2002

#Filename.# If Right(String1)="." 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.

#Me#

ServerVariables("SCRIPT_NAME")
#SQL# Current SQL.
#(SQL)# Current SQL URL encoded.
<CR> Chr(13)
<LF> Chr(10)

Macro Operator Tags

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?

Contains text. e.g. CT | abc

CX?

Not contain text. e.g. CX | abc

IT?

In text. e.g. IT | abc

IX?

Not in text e.g. IX | abc

RA?

Within numeric range. (low – high). Must use "- as delimiter. e.g. RA | 10000-20000

RX?

Not within numeric range. (low - high). Must use "- as delimiter. e.g. RX | 30000-40000

ST?

In set. e.g. ST | GM/Ford/Toyota

SX? Not in set. e.g. SX | Ford/Toyota

SW?

Starts with character(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) 08.05.01 NN Not Numeric - Not IsNumeric(field) Logical Test (L) 08.05.01
IN IsNull(Field) Logical Test (L) 08.05.01 EV Is Even (logical) (2002)
NV Not Even (logical) (2002) IB Is Blank (2002)
NB Not Blank (2002)    

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

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

For example: The following tag implies a range of Dates. Data types does not apply to all operators. Use it with a logical mind.

Example

RAD Within a Range (Date) RAD|7/15/93-8/20/93

Example - Regular MagicCell

<%
Set X=Server.CreateObject("AspDB.EP")
X.dbQuickProps="1;NWIND;employees;Grid;4;;;ACCESS;5;1;1"
X.dbGridDisplayFlds="0,1,2"
X.dbMagicCell="1,,Last Name of Record - #aspdbIndex#"
X.
ASPDB
%>

 

Example - Navigate w/o using grid's index
 

Use the grid field as an index and not the system # (leftmost column) as an index to navigate and sync with the form
Set the last parameter of the MagicCell's group to "index", and the cell's "converted" content will be hot linked to the form's record. For example -
X.dbMagicCell="fieldnamenumber, table tag, MagicCell,index,indexanchor"
If the field after the index field is "indexanchor" then the url generated will include an anchor tag.
In this example, field "0" is used as the hot link index. You should also disable the GridIndex by issuing the dbGridIndex=False to make this looks better. 

<%
session("ASPDB_2_employeeID")=2
Set Order=Server.CreateObject("Asp.DB")
Order.dbQuickProps="3;NWIND;;dual-horiz;;;;ACCESS;5"
Order.dbSQL="SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE employeeID = " & session("ASPDB_2_employeeID")
Order.dbDisplayLookUpList="(;|)CustomerID||SELECT CustomerID, CompanyName FROM Customers"
Order.dbGridIndex=false
Order.dbMagicCell="0,,,index"
Order.ASPdb
%>

Example - MagicCell applies to Grid and Form separately

MagicCell and Name Mapping can be done separately in Grid and Form

<%
Set X=Server.CreateObject("ASP.DB")
X.dbQuickProps="1;NWIND;employees;Dual-horiz;4;;;ACCESS;5;1;1"
X.dbGridDisplayFlds="0"
X.dbMagicCell="0,,#2# #1#"
X.dbFormMagicCell="1,,#1# #2#"
X.dbNameMap="1,Name"
X.dbFormNameMap="0,ID"
X.ASPDB
%>

 

Example - Extended MagicCell

If date in cell > 365 then value of gain_loss cell will change color to yellow and if date in cell > 547 then value of gain_loss cell will change color to lightblue ?
  1. Create a new computed field having the same value of (student_score) and call it CompuDate

  2. Hide the gain_loss field.

  3. Position CompuDate in the place of gain_loss column using SELECT.

  4. Use NameMap to map the CompuDate as Gain_Loss.

  5. Use the extended MagicCell to change the appearance - X.dbMagicCell="CompuDate,,(|GT|365|bgcolor=Yellow~|GT|547|bgcolor=lightblue)"

  * Skipping the First "Field" parameter defaults to the current Magic Field

Example - Extended MagicCell

If exam_score > 95 then Grade=”A”; if exam_score > 90 then Grade=”B”. The rest are all “C” grade except when exam_score=0 then grade=”Incomplete”. 
  1. Create a new computed field having the same value of (exam_score) and call it Grade

  2. Optionally, position Grade in the place of exam_score column using SELECT .

  3. Use the extended MagicCell to change the appearance -
    X.dbMagicCell="Grade,,(|GT|95||A~GT|90||B~|EQ|0||Incomplete~|EL|||C)"

  * Skipping the First "Field" parameter defaults to the current Magic Field

The scan sequence of these examples are important. You must scan from high to low unless RA (range) is used. In the grade example, if you scan from low to high, the entire class will get the lowest grade. EL (Else) should always at the very end.

Example - Even/Odd ZigZag Example (2002)

This example uses the ASPdbIndex to detect the odd/even row and then swap the picture and memo field. Note that The memo field is truncated and rounded off the the nearest space. A new local format tag is added to enable local formatting like #car# - #price::currency#. will format the price to currency.

<!-- #include Virtual=/ASPDB/ASPdb.Inc -->
<%
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = "999"
X.dbMode = "Grid"
X.dbOptions = "HoverButton=True"
X.dbGridHideFlds = "Car, Price"
X.dbGridIndex = False
X.dbHeader = False
X.dbMemoTextSize = -1
X.dbDSN = "DSN=cardb"
X.dbMagicCell = "(;,[]#|~/+{})Picture,,(#aspdbindex#|NVL||align=right|<A HREF=""http://localhost/aspdb/images-car/#Picture#""><IMG WIDTH=150 BORDER=0 VSPACE=0 HSPACE=0 SRC=""http://localhost/aspdb/images-car/#Picture#"" ALT=""Click here to see a larger photo""></A>~#aspdbindex#|EVL|||<center><b>#car# - #price::currency#</b> </center>#Comment:160#);Comment,,(#aspdbindex#|EVL|||<A HREF=""http://localhost/aspdb/images-car/#Picture#""><IMG WIDTH=150 BORDER=0 VSPACE=0 HSPACE=0 SRC=""http://localhost/aspdb/images-car/#Picture#"" ALT=""Click here to see a larger photo""></A>~#aspdbindex#|NVL|||<center><b>#car# - #price::currency#</b></center>#Comment:160#)"
X.dbNavigationIcon = "Std"
X.dbSQL = "SELECT Car,Price,Picture,Comment FROM CarSpecs"
X.dbUserLocalText = zHead & ",<Center><h3>Zig Zag Grid Demo</h3></Center>;"
X.dbImageDir = "http://localhost/aspdb/images-2002/"
X.ASPdb
%>

 

Notes
  • How are Null sand blanks handled in MagicCell operations - See Xmagic_Null.html
  • (3) Logical tests are added (08.05.01) to allow the test of field data -
    - Not a date value
    - Not a number
    - Is a Null
  • The order of MagicCell groups is very important. Always thik of it as -> if... elseif ... elseif.....