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
%>
| 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"| Replace Text |
See Example E31
X.dbMagicCell="FirstName,,{ar+*A*R*+n+N}; LastName ,,(#LastName#|SW|F|BGCOLOR=YELLOW|#LastName#); Title ,,{<+<+>+>}; 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. |
| 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 replace 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: |
| Math Formula Operators |
| 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]);_ |
| #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 |
|
<% |
 |
| Example - Extended MagicCell |
Create a new computed field
having the same value of (student_score) and call it CompuDate
Hide the gain_loss field.
Position CompuDate in the
place of gain_loss column using SELECT.
Use NameMap to map the
CompuDate as Gain_Loss.
Use the extended MagicCell to
change the appearance - X.dbMagicCell="CompuDate,,(|GT|365|bgcolor=Yellow~|GT|547|bgcolor=lightblue)"
| Example - Extended MagicCell |
Create a new computed field
having the same value of (exam_score) and call it Grade
Optionally, position Grade in
the place of exam_score column using SELECT .
Use the extended MagicCell to
change the appearance -
X.dbMagicCell="Grade,,(|GT|95||A~GT|90||B~|EQ|0||Incomplete~|EL|||C)"
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 |