Macros and Events:

Macros:
A macro is a script containing a sequence of commands that can be initiated with a single
command button or event.
Macros are useful for customizing your forms in any number of ways. For instance, you can save
time and effort for your users by defining a series of steps that execute automatically with a
single click. You can use a macro to automatically validate user input before it is entered in the
table. You can automate complex surveys with many branches using conditional (if-then-else)
logic. You can even make your forms dynamic by having macros execute automatically when a
given event occurs, such as when the form opens or the user changes a field value.

Components of a Macro:
A macro consists of a sequence of commands that you want performed in a given order.
Cellica Database offers a large library of commands that you can use as steps in a macro.
Most commands have parameters you must specify, such as values read into or written out by
the command. For these values, you can supply a constant or an expression. Expressions can
read values stored in a table column for the current record as well as apply mathematical
operations, if-then-else logic, and functions to return a desired value.
The script commands can execute in a straight linear sequence, or you can incorporate skips,
branches and loops based on conditional logic using Skip commands. You can also have
SubMacros within a Macro. SubMacros are a sequence of commands that you write once,
and can then call from the main script as many times as needed. Simply write the SubMacros as
you would any other, then call it from the main script using a "Run macro" command.
One Cellica Database form can have any number of macros. However, each macro is "built
into" a single Cellica Database form; it is not possible to call an external macro.

Create a Macro:
Create a new form or open an existing form using Edit Form option on File Manager.
Click on Macros present in the FormsForm and Tab Properties control.

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig 1: Macros Property in Form and Tab Properties Control

You will be presented with following Macro Builder Dialog

Fig 2: Macro Builder

_____________________________________________________________________________________
Cellica Database Macros and Events

Using various options on Macro Builder

a) Add New Macro:
Use this option to add new macro to the current form.
b) Remove Macro:
Use this option to remove selected macro in Macro Name list from current form.

a) Add New Macro:

To create new macro, click Add New Macro Button and provide Macro Name.

Fig 3: Enter Name for Macro

Please Note: You cant use same macro name twice in the current form.

_____________________________________________________________________________________
Cellica Database Macros and Events

Now we will design our newly added macro,

Fig. 4: Add New Macro

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig. 4: Add command in New Macro

1) Add Command:
Click on Add Command button to add command to the macro and select the command
you want to use in macro from Command Name drop down list.By default it is added to last.
2) Insert Before:
It used to insert the command before to the selected command in macro steps.
3) Insert After:
It used to insert the command after the selected command in macro steps.
4) Remove selected:
It will remove the selected command from macro steps.

_____________________________________________________________________________________
Cellica Database Macros and Events

5) Remove All:
It will remove all the command from selected macro.

Commands:
Command is a action executed on current form.
For Example: With Set Property command you can set the property of control.
There are two types of commands depending on arguments they needed,
a) Commands with arguments
b) Commands without arguments
For Example: Set property command has three arguments:
1) Property:
List of properties used with control.(Caption, Show/hide Control, Fore Color etc)
2) Control:
List of controls as per property selected in Property.
3) Value:
Required Value to assign to Property of selected Control.
Following commands are supported in Cellica Database Macro builder.
1) Set Property:
Arguments list:
a. Property: Property to set.
(Set Caption, Read Only, Show/Hide Control, Set Font Name, Set Font Size, Set
Forecolor, Set Backcolor, Set Mandatory.)
b. Control Name: Control ID
c. Value: Value for selected property.
Caption, Read Only, Show/Hide Control, Font Name, Font Size, Fore Color, Back
Color, Mandatory.
Use to set a property for a control on a form.
2) Set New List:
Arguments list:
a. Control Name: Control ID
b. List Type :Constant List or Combo Box list
c. Values: Depending on List Type, List of items or set list from another combo
box.
Use this with conjunction with combo box control on form. You can set new list of items or
set list from another combo box to specified combobox control.
_____________________________________________________________________________________
Cellica Database Macros and Events

3) Set Focus: Sets focus to specified control on form

Arguments list :
a. Control Name: Control ID
4) Assign:
Argument List :
a. Destination Value Type: Either Control or Column.
b. Destination Value: Select Value depending on Destination Value Type. Either
Control ID or Column Name.
c. Source Value Type: Control or Column or Expression or Constant
d. Source Value: Enter value according to Source Value Type.
Assign the Control/Column/constant/expression value to specified control or column.
5) Macro Step Label:
Argument List :
a. Step Label
Use "Step Label" to jump to specific command using Macro Step Label. This "Step Label" need
to be use in "GoTo" label in skip command. It is used for identify destination for "GoTo" clause
in "Skip" command expression.
6) Run Macro: Runs a specified Macro
Argument List:
a. Macro Name: Name of macro to run.

7) Stop Macro
You can use the "Stop Macro" action to stop the current macro. If included in a
submacro, processing does not return to the main macro. Typically use it when an error
condition is detected.
8) Return From Macro:
Stop the execution of the current macro and return to control process which called it. if
this statement is included in a submacro, processing returns to the main macro. if included in
main macro, processing returns to the form. Recommended as the last step in every macro.
9) Record|First:
Go to first record in the table, based on the current sort order.
_____________________________________________________________________________________
Cellica Database Macros and Events

10) Record|Previous:
Go to previous record in the table, based on the current sort order.
11) Record|Next:
Go to next record in the table, based on the current sort order.
12) Record|Last:
Go to last record in the table, based on the current sort order.
13) Record|Insert: Insert a new record.
14) Record|Delete: Removes current record.
15) Record|Duplicate:
Create a new record by creating a duplicate copy of the current record.
16) Record|Search:
Finds the first record (based on the current sort order) whose value in a specified table
column matches a search value.

17) Record|Save: Save the current record.

18) Record|Close: Close current record.
19) Select Tab: Selects a specific tab.
Argument:
a. Number: Tab Number
20) Skip:
Argument List:
a. Skip Type : Number, GoTo Label and If-Then-Else
- For Number: provide the Number of statements to be skip.
- For GoTo Label: provide Label Name.
- For If-Then-Else: If part, then part and else part.
Skip forward in the macro sequence, it can be used to create conditional branches and
loops.
21) Message Box:
Argument List :
a. Message Text : Message to display in message box.
b. Skip Message: (True or False):
_____________________________________________________________________________________
Cellica Database Macros and Events

- If set to True, Message box will show Yes and No buttons.

If user press Yes button then command next to Message Box command will
be skipped. If No button is pressed the execution of macro will continue from
next command.
-

If set to False, Message box will show only OK button. When OK button is
pressed the execution of macro will continue from next command.

Displays a message box containing a warning or information message. A Common use is

a message that appears when validation fails.
22) Open Form:
Argument List :
a. Form Name: Form to open.
b. Close Form: (True or False): If True then the parent form will be closed
before opening new form. If False, opened form will be stacked over the
Parent form.
Open a specific form.

Expression:
You can assign result by executing expression in command. Expression can be used with three
commands:
1) Assign
2) Select Tab
3) Skip
For Example:
1) $ColumnName*2/10
Column name is prefixed with $ in expression.
2) [TextBox]*2/10
Form control is used In [Control] open and close brackets.
3) 10 * 100/100
Numeric values can be used in expression.

_____________________________________________________________________________________
Cellica Database Macros and Events

Date Time functions for expression

Formats of fixed date value used in date time functions:

Fixed Format : (Time 24 hour)

Date : "dd-MM-yyyy"
Time : "HH:mm:ss"
DateTime : "dd-MM-yyyy HH:mm:ss"

User can use "date/datetime/Time" values in these functions by using Constant date in given
format/Column containing datetime value/ControlID of control mapped wit date time type column

1) Now(Flag)
Parameter:
Flag : 0 - Date, 1 - Time, 2 - DateTime
Return current date/time/datetime.

2) DateDiff(date1, date2)
Return the date difference in number of days. (date2-date1)

Add(date, Flag, Cnt)

Parameter:
Flag: 0 - Day, 1 - Month, 2 Year
Cnt : Number of Day/Month/Year
Return date calculated by adding no of Day/Month/Year in given date.

3) Substract (date, Flag, Cnt)

Parameter:
Flag: 0 - Day, 1 - Month, 2 Year
_____________________________________________________________________________________
Cellica Database Macros and Events

Cnt : Number of Day/Month/Year

Return date calculated by subtracting no of Day/Month/Year from given date.

4) Year (date)
Return the year component of given date.

5) Month(date)
Return the Month component of given date ( integer in the range 1-12).

6) MonthName(date)
Returns the name of the month in given date.
For example, MonthName (14/02/2012) returns February.

7) DayNameOfWeek(date)
Returns the name of the day for datetime.
For example, DayNameOfWeek(14/02/2002) returns Thursday.

8) DayOfMonth(date)
Returns the day of the month for given date (integer in the range 1-31).

9) DayOfWeek(date)
Returns the day of the week for given date as an integer in the range 1-7(where 1 represents
Sunday).

10) DayOfYear(date)
Returns the day of the year for given date as an integer in the range 1-366.
For example, DayOfYear(02/02/2012)returns 33.

11) TimeDiff(Time1, Time2)

Returns difference in number of milliseconds (Time2-Time1)

12) Time Add(Time, Flag, Cnt)

Parameter :

Flag : Hour, Minute, Second

Returns Time type value by adding Hour/Minute/Second in given Time.

_____________________________________________________________________________________
Cellica Database Macros and Events

13) Time Substract(Time, Flag, Cnt)

Parameter :
Flag : Hour, Minute, Second
Returns Time type value by subtracting Hour/Minute/Second from given Time

14) Hour(datetime)
Returns the hour in datetime as an integer in the range 0-23.

15) Minute(datetime)
Returns the minute in datetime as an integer in the range 0-59.

16) Second(datetime)
Returns the second in datetime as an integer in the range 0-59.

17) String ConvertToTimeString(Flag)

Parameter :
Flag:milisecond/Second/minute
Returns Time String As "HH:MM:SS"

18) Date StringToDate(string)

Returns Date from String. String Date Format "DD/MM/YYYY"

19) Time StringToTime(String)

Returns Time from String. String Time Format "HH:MM:SS"

20) DateTime StringToDateTime(String)

Returns DateTime from String. String DateTime Format "DD/MM/YYYY HH:MM:SS"

_____________________________________________________________________________________
Cellica Database Macros and Events

String functions

1) Concat ( text1, text2)

Returns: String value by appending Text2 at the end of Text1.
Parameters: ControlID/ Column Name/ Constant String value/ String functions.
For example,
- Concat(ConvertToString([ControlID]), left($ColumnName))
- Concat([ControlID], $ColumnName)
- Concat("XYZ", "Abc")

2) Substring( text, start, length)

Returns: Characters in text, beginning at the character position specified by start (1- indicates
the first character position), through the number of characters specified by length.
Parameters:
First parameter :ControlID/ Column Name/ Constant String value/ String functions.Second and
Third parameter: Number constant or length() function.
For example,
- substring(ConvertToString([ControlID]), 0, length($ColumnName))
- substring([ControlID], 0, $ColumnName)
- substring("XYZ", 0, "Abc")

_____________________________________________________________________________________
Cellica Database Macros and Events

3) Insert(text, start, length, insert_text)

Returns: a string, where number of characters specified by length have been deleted from text,
beginning with the character position indicated by the value of start, and where insert_text has
been inserted into text, beginning at start.

Parameters:
First and Fourth parameter: ControlID/ Column Name/ Constant String value/ String
functions.Second and Third parameter: Number constant or length() function.
For example,
- insert(ConvertToString([ControlID]), 0, length($ColumnName), "Abc")
- insert([ControlID], 0, length($ColumnName), $ColumnName)
- insert("XYZ", 0, length("XYZ"), "Abc")

4) Replace(text, search_string, replacement_text)

Returns: a string, Searches text for search_string, and replace with replacement_text in all
occurrences.
Parameters: ControlID/ Column Name/ Constant String value/ String functions.
For example,
- replace(ConvertToString([ControlID]), "XYZ", "Abc")
- replace([ControlID], "XYZ", $ColumnName)
- replace("XYZ", "YZ", "Abc")

_____________________________________________________________________________________
Cellica Database Macros and Events

5) Length(text)
Returns: the number of characters in text, excluding trailing blanks.
Parameters: ControlID/ Column Name/ Constant String value/ String functions.
For example,
- length(ConvertToString([ControlID]))
- length($ColumnName)
- length([ControlID])
- length("XYZ")

6) Left(text, count )
Returns: a string, with the leftmost count number of characters in text.
Parameters:
First parameter: ControlID/ Column Name/ Constant String value/ String functions.Second
parameter: Number constant or length() function.
For example,
- Left(ConvertToString([ControlID]), 10)
- Left($ColumnName, 5)
- Left([ControlID], 3)
- Left("XYZ", 2)

7) Right(text, count )
Returns: a string, with the rightmost count number of characters in text.
Parameters:
First parameter: ControlID/ Column Name/ Constant String value/ String functions.Second
parameter: Number constant or length() function.

_____________________________________________________________________________________
Cellica Database Macros and Events

For example,
- right(ConvertToString([ControlID]), 10)
- right($ColumnName, 5)
- right([ControlID], 3)
- right("XYZ", 2)

8) Locate(search_string, text, start)

Returns: Returns the starting position of the first occurrence of search_string within text. The
search begins with the character position indicated by the value of start (1 indicates the first
character position). Returns 0, in case of search_string do not found.
Parameters:
First and Second parameter: ControlID/ Column Name/ Constant String value/ String
functions.Third parameter: Number constant or length() function.
For example,
- locate("abc", ConvertToString([ControlID]), 10)
- locate("abc", $ColumnName, 5)
- locate("abc", [ControlID], 3)
- locate("abc", "XYZabc", 2)

9) Ltrim(text)
Returns: a string, with characters in the text, without leading blanks.
Parameters : ControlID/ Column Name/ Constant String value/ String functions.
For example,
- ltrim(ConvertToString([ControlID]))
- ltrim($ColumnName)
- ltrim([ControlID])
- ltrim(" XYZ")
_____________________________________________________________________________________
Cellica Database Macros and Events

10) Rtrim(text)
Returns: a string, with characters in the text, without trailing blanks.
Parameters : ControlID/ Column Name/ Constant String value/ String functions.
For example,
- rtrim(ConvertToString([ControlID]))
- rtrim($ColumnName)
- rtrim([ControlID])
- rtrim(" XYZ")

11) Repeat(text , search_string)

Returns: a number, the repeated count times search_text is found in text.
Parameters:
First and Second parameter: ControlID/ Column Name/ Constant String value/ String functions.
For example,
- repeat(ConvertToString([ControlID]), "aa")
- repeat($ColumnName, "aa")
- repeat([ControlID], "ab")
- repeat("XYZAbc", "ab")

12) Lcase(text)
Returns: a string, with text as all characters in lowercase
Parameters : ControlID/ Column Name/ Constant String value/ String functions.
For example,
- lcase(ConvertToString([ControlID]))

_____________________________________________________________________________________
Cellica Database Macros and Events

- lcase($ColumnName)
- lcase([ControlID])
- lcase("XYZ ")

13) Ucase(text)
Returns: a string, with text as all characters in uppercase
Parameters: ControlID/ Column Name/ Constant String value/ String functions.
For example,
- ucase(ConvertToString([ControlID]))
- ucase($ColumnName)
- ucase([ControlID])
- ucase("XYZ ")

Conditional branches and Loops:

You can use condition branches and loops with skip command.
Skip command have 2 argument.
1) Skip type : There are three skip types
i) Number:
Skip the specified number of step from current step.
ii) Goto Label:
Jumps directly to specified macro step label.
iii) If-then-else:
Using this you can implement conditional branching and looping. if you select
this option then it will be ask for three argument.
A) if part:
In this part you need to provide the condition.
For Example:
$Age > 10 OR [TextBox] > 10
Here $Age is the Column Name and [TextBox] is the control name.
If you want to check multiple condition then you can use (OR, AND and NOT)
operator.
B) then part :
_____________________________________________________________________________________
Cellica Database Macros and Events

If if part is evaluated to true then this part will be

processed
Here you can use constant value or expression or goto clause.
For Example:
1) 2
2) $Age+10
3) goto First- First is the label value assigned to the macro step label command.
C) else part :
If if condition is evaluated to false then this part will be processed
Here you can use constant value or expression or goto clause.
For Example:
1) 0
2) $Age+10
3) goto First - First is the label value assigned to the macro step label command.

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig. 5:Conditional branches and Loops.

_____________________________________________________________________________________
Cellica Database Macros and Events

Events:
You can initiate/execute a macro on particular event. You need to select the macro for
particular event.
There are three types of event groups:
a) Form level events
b) Control level events
c) Radio group events
To execute macro on particular event you need to use Event property on one of the above
mentioned event groups.
a) Form Level Event

Fig.6: Form Event Property

Supports following events,

1) OnRecordDisplay: When navigated to another record( First/Last/Previous/Next)

2) OnLeavingRecord: When record is moved in navigation, this event is called up for old
record.
3) OnStartup: When form is load.
4) OnRotate: When orientation changed.

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig. 7:Set macros for Form Level Event.

b) Control Level Events

Fig 8: Control Event Property

i) TextBox/Location/Formula Field control event

Supports following events,

1) Get Focus: When focus is set in control.

2) Lost Focus: When focus is moved from control.

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig. 9:Set macros for Control Level Event.

ii) Combobox/CheckBox/RadioButton(Group) control event

Supports following events,

1) Get Focus: When focus is set in control.

2) Lost Focus:When focus is moved from control.
3) Changed: Value of control is changed

Fig. 10:Set macros for Radio or Combo box or Checkbox Event

iii) Button control event:

Supports following events,

Clicked: Clicked on button.

_____________________________________________________________________________________
Cellica Database Macros and Events

Fig. 11:Set macros for button Event.

c) Radio Group Event:

Fig. 12: Radio Group Event Property

Supports following events,

1) Get Focus: When focus is set in control.

2) Lost Focus: When focus is moved from control.
3) Changed: Selection in Radio Group is changed.

Fig. 13: Set macros for Radio Event

_____________________________________________________________________________________
Cellica Database Macros and Events