The creation of components is done using the following syntax:

¤¤HEADER;TYPE;[SWITCH;Flag=Value];...

TYPES

Type	Component	Remarks
B	Edit Box
L	Dropdown list
R	Radiobuttons group
K	Checkbox
C	Control
T	Edit box formatted as text	Equivalent to B ;C=S ;TFMT
PD	Descending attachment
PA	Ascending attachment	Not customizable by default (equivalent to PJ + NP)
PJ	Attachment	Customizable by default
D	Date
H	Hour
HL	Hyperlink
S	Scrollbar

SWITCHES

Switch	Meaning	Applicable components
M	Mandatory	All but checkbox, Control, Descending Attachment and Hyperlink
NP	Not customizable	All but Descending attachment and hyperlink
NR	The value is not trasnmitted	Control
E	Editable	Dropdown list
TFMT	Put a text format (@) on the component	Must be used with any component not containing a number, a date or a formula
NOSAVE	The save icon is not displayed	Attachment
IMG	Display image if the attachment is an image	Attachment
STRETCH	Enlarges the image to be displayed to the size of the cell	Attachment
PROP	Keep image proportions	Attachment with IMG
NI	Not importable via Excel import	All editable components
NE	Not exportable via Excel export	All editable components

FLAGS

Flag	Meaning	Value	Components	Examples
ITEMS	Data	Pipe ("|") separated list	L, R	¤¤List;L;ITEMS=01|02|03 ¤¤List;L;ITEMS=A|B|C ¤¤RADIO;R;ITEMS=1|2|3|4|5|6 ¤¤RADIO;R;ITEMS=A|B|C|D|E|F
ITEMSSRC	Dynamic data source	The header must be a pattern or and Excel range	L	¤¤LIST;L;ITEMSSRC=PatternHeader ="¤¤"&"LIST;L;ITEMSSRC="&GTRANGE(RANGE)
C	Constraint	S :String I : Integer R : Real	B, L	¤¤BEDIT;B;C=S ¤¤List;L;E;C=S ¤¤BEDIT;B;C=I ¤¤List;L;E;C=I ¤¤BEDIT;B;C=R ¤¤List;L;E;C=R
MAX	MAX value (inf or equal to)	Float ou Date ou Hour	B, L, D, H, S	¤¤BEDIT;B;C=R;MAX=10,25 ¤¤List;L;E;C=I;MAX=20 ¤¤DATE;D;MAX=23/01/2015 ¤¤HOUR;H;MAX=08:25:46 ¤¤SCROLLBAR;S;MAX=20
MIN	MAX value (sup or equal to)	Float ou Date ou Hour	B, L, D, H, S	¤¤BEDIT;B;C=I;MIN=5 ¤¤List;L;E;C=I;MIN=12 ¤¤DATE;D;MIN=01/12/2002 ¤¤HOUR;H;MIN=02:15:26 ¤¤SCROLLBAR;S;MIN=-10
L	Max length	Integer >= 0 0 = no limit	B, L, C	¤¤BEDIT_C20;B;L=20 ¤¤BEDIT_Comment;B;L=0 ¤¤CONTROL_LONG;C;L=0 ¤¤List;L;E;L=0
CAPTION	Title	String	K	¤¤CHECK;K;CAPTION=MyTitle Notice : the cell musn't be horizontally centered for thetitle to appear
COLS	Columns setting	H : Horizontal V : Vertical Integer > 1 : Number of columns	R	¤¤RADIO;R;COLS=H;ITEMS=1|2|3|4|5|6 ¤¤RADIO;R;COLS=V;ITEMS=1|2|3|4|5|6 ¤¤RADIO;R;COLS=6;ITEMS=1|2|3|4|5|6
MSIZE	Max size	Float >= 0	PA, PJ	¤¤ATTACH;PA;MSIZE=1
FILTER	Filter description followed by allowed extensions ex: Excel, Word 2007+|*.xlsx;*.docx|Zip Files|*.zip	String	PA, PJ	¤¤ATTACH;PA;"FILTER=Zip file|*.zip" ¤¤ATTACH;PA;"FILTER=Excel, Word 2007+|*.xlsx;*.docx|Zip files|*.zip" Remarks: - "FILTER=xxx" must be delimited by double quotes - The list of extensions (such as *.xlsx;*.docx) must always be separated by semicolumns ";",no matter the machine locale
LINK	Link	String	HL	¤¤LINK;HL;Link=qstp://Page n°2 ¤¤LINK;HL;Link=qsth://Header
DISPLAY	Display Name	String	Tous	¤¤BEDIT;B;DISPLAY=DisplayName
INCR	Increment	Integer > 1	S	¤¤SCROLLBAR;S;INCR=2
DIR	Scrollbar orientation	* H : Horizontal * V : Vertical	S	¤¤SCROLLBAR;S;DIR=H ¤¤SCROLLBAR;S;DIR=V
DEF	Default value	* MIN : minimum * MAX : maximum * AVG : Average * Integer value : Custom value	S	¤¤SCROLLBAR;S;MIN=-10;DEF=MIN ¤¤SCROLLBAR;S;MAX=20;DEF=MAX ¤¤SCROLLBAR;S;DEF=AVG ¤¤SCROLLBAR;S;DEF=6
T	Sort in lists	ASC = Ascending DESC = Descending	L	¤¤List;L;ITEMS=H|A|D;T=ASC ¤¤List;L;ITEMS=H|A|D;T=DESC

Points of Vigilance:

• If Value contains a “;” character, the entire Flag=Value must be enclosed with double quotes ” “.
• It is possible – and very useful – to construct the syntax of a component using an Excel formula. However, the formula will only be evaluated at the time of import into Design. In other words, it is not possible to use a formula to dynamically modify the definition of a component during the execution of the document in GTAnswer. For example, if cell A1 contains “MyInputBox” and cell A2 contains the number 30, then the formula ="¤¤"&A1&";B;C=I;MAX="&A2 will be correctly evaluated during the import as ¤¤MyInputBox;B;C=I;MAX=30 and it will generate an input box whose header will be “MyInputBox” and whose maximum value will be 30. However, if cell A2 itself contains an input box, then the import into Design will generate an error. This limit notably helps to avoid type inconsistencies. Moreover, GT implements functions that allow for dynamic control of documents and components. Thus, preventing a user from entering a dynamically defined value can be achieved using a GTCONSTRAINT function. Similarly, allowing or preventing a user from entering data based on a variable is possible using the GTLOCK function, dynamically modifying the items of a dropdown list is possible using the GTLIST function, etc.

Examples

¤¤TOTO;B;M;C=I;MAX=12;MIN=10;NP

Mandatory integer input box not customizable between 10 and 12

¤¤TOTO;B;M;TFMT;L=50

Mandatory input box for a string of up to 50 characters

¤¤Comment;B;M;TFMT;L=0

Mandatory input box for a string of unlimited length

¤¤TRUC;L;M;TFMT;ITEMS=01|02|03

Editable drop-down list, mandatory,

(list items 01,02 and 03) with a text format

¤¤TRUC;L;E;M;C=R;ITEMS=1|2|3

Editable drop-down list, mandatory,

accepting only real numbers, (list items 1,2 and 3)

¤¤CTRL;C;TFMT

String-hosted control (Excel-like text format)

¤¤CTRL;C

Control

¤¤PIECE;PA;"FILTER=Office|*.doc;*.xls;*.ppt"

Ascending attachment with filter

¤¤MyDate;D;

Date component with calendar

¤¤MyDate;B;

Alternative to a date component by adding a GTCONTRAINTE with rule condition and(no(esttexte(cell));no(esterreur(year(cell))))

Object declaration functions

Functions for creating objects when importing Excel-Design. These functions are only known by GT Design.

GTPATTERN

GTPATTERN(Name;Base_Pattern;Flags;[GTKEY (Msg_duplicate;Msg_invalid;Cell1;…;CellN);[Initial_range]])

Creates a pattern called ” Name “ ( pattern identifier), for base range Base_Pattern (re-copied or deleted cells), for initial range Initial_range (range for which the base range is recopied at the opening of the document)

“Flags” is a string that contains the pattern options via flags (separated by semicolons):

• “ALL”: all options to TRUE
• “NONE”: all options to FALSE
• “A”: User can add records
• “S”: User can delete records
• “H”: User can adjust row heights (FALSE by default)
• “T”: User can sort data (FALSE by default)
• “F”: User can filter data (FALSE by default)
• “V”: allow a single empty line despite mandatory constraints (FALSE by default)

GTKEY (Msg_duplicate;Msg_invalid;Cell1;…;CellN) indicates the pattern key (empty by default):

• Mg_duplicate: message to be displayed in case of duplicate
• Msg_invalid: message to be displayed in case of invalid value for an element of the key (e.g. #N/A)
• Cell1;..;CellN: list of individual cells in the pattern forming the key

Examples

Standard

  Example 1: =GTPATTERN("MyPattern_1";A3:K3;"ALL")

Does not allow empty rows

  Example 2: =GTPATTERN("MyPattern_2";A6:K6;"A;S;H;T;F")

For the data/reference list

  Example 3: =GTPATTERN("MyPattern_2";A6:K6;"NONE")

For the data/reference list while keeping the row heights

  Example 4: =GTPATTERN("MyPattern_2";A6:K6;"H")

With a key

  Example 5: =GTPATTERN("MyPattern_1";A3:K3;"ALL";

    GTKEY("The value"&A3&"is duplicated";A3&"invalid";A3))

GTCONTROL

GTCONTROL(header;formula)

Creates a control (component that cannot be entered by the correspondent) with the header “header” and containing the formula expressed as the second argument.

When integrating data from the questionnaire, the result value of the formula will be retrieved in the “header” field in the client database.

GTLIST

GTLIST (header_and_flags_list; formula_of_the_range_of_list_items)

Creates a drop-down list with the header “header” and the flags associated with the header.

The content of the list is defined by the second argument which can be a formula and must send back the range of items in the list to Answer.

Example 1: =GTLIST("MyList";B1:B3)

Example 2: =GTLIST("MyList2";SI(A1=1;B1:B3;C1:C4))

Example 3: =GTLIST("MyList3;M;E";OFFSET(C1;0;0;nb.si(B1:B4;A1);1))

Particularly useful for linked lists

WARNING: All ranges used as data sources pointing to another sheet or pointing outside the pattern (if the list is in a pattern) must be declared as ABSOLUTE.

GTHYPERLINK

GTHYPERLINK(header ;display label or formula ;url of the hyperlink or formula)

Creates a calame hyperlink with:

• a header
• a display label
• a destination url

Both the display label and the destination url can be the result of a formula.

Note: the specified header must be identical to the header of the targeted component, respecting the case.

See the article Hyperlink Syntax for hyperlinks within the questionnaire.

GTLOCK

GTLOCK(range;formula;[Extend_Formula])

Places a conditional lock (prevents user input) on the range (1st argument), if the formula condition returns true

The Extend_Formula option allows (false by default) to specify if the formula is adapted (shifted) for each cell of the starting range, should be specified to TRUE when the locked area is a range.

Note 1: The range must be on the same tab as the GTLOCK function

Note 2: For each cell of a sheet, only one GTLOCK declaration is allowed.

Note 3 : Components declared as mandatory and which are locked are no longer considered as mandatory when the answer is submitted

Note 4: GTConstraints must be checked even if the component is locked

Example 1 =GTLOCK(A1;B1>0)

the component in A1 will be locked if B1>0

Example 2 =GTLOCK(A1:A3;B1>0;VRAI)

The component in A1 will be locked if B1>0, the component in A2 will be locked if B2>0, etc…

Example 3 (not recommended) =GTLOCK(A1:A3;B1>0)

The components in A1, A2 and A3 will be locked if B1>0,

Expression equivalent to =GTLOCK(A1:A3;$B$1>0;TRUE)

GTCONSTRAINT

GTCONSTRAINT(Anchor cell;GTRULE(cond° formula to be respected;msg formula if constraint not checked[;Target1[;Target2;...]]) 

              [;GTRULE (...)] )

              [;GTRULE (...)] )

Allows you to place a constraint which, if not checked, prevents the transmission of the response

Cell designates the anchor cell for the constraint

This cell must be inside a pattern if the constraint is to be repeated on each line of the pattern.

GTRULE(…) is used to define one of the rules of the constraint

Multiple GTRULEs can follow each other in the GTCONSTRAINT statement, the first rule condition (formula) not met will trigger the display of that rule’s message when the response is transmitted

If no target is specified, the anchor cell will be considered the target

Note: All cell references must be on the same tab as the GTCONSTRAINT statement

  Example 1 :

=gtconstraint(A3;gtrule(A3>0;"The value must be positive:"&&A3))

  Example 2 :

=gtconstraint(A3;gtrule(A3>A4;"The current value("&A3&")must be greater than"&A4;A3;A4))

  Example 3 :

=gtconstraint(A3;gtrule(A3>B3;"too small");gtrule(A3<C3;"too large"))

GTCONDITIONING

GTROWCONDITIONING (Cell_anchor;formula;rows to be conditioned)

GTCOLUMNCONDITIONING (anchor_cell;formula;columns to be conditioned)

GTSHEETCONDITIONING (anchor_cell;formula)

Allows to dynamically hide rows, columns or sheets, when the formula returns true (second parameter of the declaration)

Note: The anchor cell and the rows/columns/sheets to be conditioned must be on the same sheet as the GTCONDITIONINGxxx statement

Note 2: In the case of conditioning for each row of a pattern, the anchor cell of the GTCONDITIONINGROW declaration must be in the pattern

Note 3: Mandatory components lose their mandatory character if they are conditioned.

However, the rules of GTCONSTRAINT must be respected (modify the formulas of GTRULE to take account of the conditioning).

   Example 1: GTCONDITIONROW (B3;B3>0;3:3)

Hides line 3 if the cell in B3 has a value > 0

The conditioning will be copied to each row of the pattern if B3 is in a pattern.

The formula for each pattern line will then be B3>0, B4>0, etc… (same formula copying modes as for a formula in a pattern cell)

Example 2: GTCONDITIONCOLUMN(C4;C4>0;B:C)

Hides column B if cell C4 has a value >0

Hides column C if cell D4 has a value >0

  Example 3: GTCONDITIONINGSHEET(Sheet1!A1;D5>0)

Hides the sheet Sheet1 if cell D5 has a value >0

the conditioning is written on Sheet1

  Example 4: GTCONDITIONINGCOLUMN(C4;$C4>0;B:C)

Hides columns B and C if cell C4 has a value >0

See the Conditioning article for more details

GTRANGE

GTRANGE (reference)

  Used to reference a range of cells (mainly used with the ITEMSSRC flag for drop-down lists)

 

  Example 1 : ="¤¤List1;L;M;ITEMSSRC="&GTRANGE(B2:B12)

 WARNING: All ranges used as data sources pointing to another sheet

             or pointing outside the pattern (if the list is in a pattern)

             must be declared in ABSOLUTE

Excel Design Support Macros

GTVALUEORCOMP

GTVALUEORCOMP(value_to_send back_in_Excel,declaration_of_GT_component)

Allows to send back to Excel the value_to_send back_in_Excel (which can be a formula) and to declare a component during the import in Design.

This makes formulas based on the cell containing the component operational in Excel, facilitates the testing of formulas and the design of graphs (avoids the #value from formulas in Excel)

The following Excel macro command must be declared in a VBA module

Function GTVALUEORCOMP(v,c)

GTVALUEORCOMP = v

End Function

Note: In the same way as GTVALUEORCOMP, it may be useful to create a VBA function GTCONTROL in the design workbook. The developer will then see the result of the function in Excel.

Note that declaring two VBA functions in this way does not allow the declaration of components to be seen as quickly unless a graphic chart for the input cells is used which is visible in Excel.

Calculation functions

Functions to be used in the questionnaire to perform various calculations.

These functions can be entered into the Excel workbook for import into Design.

These functions do not have an equivalent in Excel and therefore do not send back a meaningful value in Excel.

This article describes in more detail the Excel calculation functions that can be used in a questionnaire:  Grid Calculation Engine.

GTPATNUM

GTPATNUM()

Sends back the number of the “line” in the pattern. The “line” being the base range or duplicated range of the pattern. Here is  the article dedicated to the function.

GTVALIDMAILADDRESS

GTVALIDMAILADDRESS()

Sends back the email address validated in Answer (validation by sending an email with a .checkmail attachment) or the login of the connected user. See Validation of repliers mail address.

Warning, this function is volatile.

GTADMAILADDRESS

GTADMAILADDRESS()

Sends back the email address validated in Answer by the authentication in the local Active Directory.

Warning, this function is volatile.

GTISUSERCONNECTED

GTISUSERCONNECTED()

Sends a boolean depending of the connexion state of the user. The returned value is TRUE if the user opens a questionnaire from the discussion thread or from the “Answer” button in the sandwich menu.

Warning, this function is volatile.

GTMS.function_agreg

GTMS.SUM(Expression_to_at_least_one_multitab)

GTMS.NB(Expression_to_at_least_one_multitab)

GTMS.NBVAL(Expression_to_at_least_one_multitab)

GTMS.MAX(Expression_to_at_least_one_multitab)

GTMS.MIN(Expression_to_at_least_one_multitab)

GTMS.AVERAGE(Expression_to_at_least_one_multitab)

GTMS.CONCATENATE(Expression_to_at_least_one_multitab)

 Expression_to_at_least_one_multitab being a formula using one or more references to a multi-tab sheet

In the context of a multi-tab, GTMS.function_agreg sends back the function_agreg of the Expression_to_at_least_one_multitab declined according to all items of the multi-tab.

cf  Multi-tab data synthesis and
a sample file GTTut-SynthGTMS-Qst.xlsx.

Examples :

=GTMS.SUM(SheetMO!B1)

will calculate (and when exported be converted to)

=SUM(Item1!B1;Item2!B1;Item3!B1)

GTMOGETSHEETNAME

GTMOGETSHEETNAME(item[,Axis[,tab_index]])

Sends back the name of the multi-tab sheet for a specific multi-tab item, for a multi-tab axis (name of the multi-tab axis) and for the index (base 0) of the concerned tab in the multi-tab group (if the multi-tab multiplies several base tabs).Generally used with indirect() to retrieve values from a multi-tab outside the multi-tab group.

Be careful, after an Excel export, to keep the formulas operational in Excel and to be compliant with the restricted tab names of Excel, you have to isolate the call to the GTMOGETSHEETNAME(“Item of multi-tab”) function in a cell without any other function transforming the result of GTMOGETSHEETNAME

Generally used with indirect() to retrieve values from a multi-tab outside the multi-tab group.

Be careful, after an Excel export, to keep the formulas operational in Excel and to be compliant with the restrictive

For example, instead of writing =INDIRECT(“‘”&GTMOGETSHEETNAME(“Multi-tab item”)&”‘!B2″), it is better to write =INDIRECT(“‘”&C4&”‘!B2”), with  =GTMOGETSHEETNAME(“Multi-tab item”) in cell C4

cf  Multi-tab data synthesis

GTMOAXISVAL

GTMOAXISVAL([offset]) 

Sends back the current item (if offset absent or =0) or next/previous item (offset!=0) of the current multi-tab group

GTPJSIZE

GTPJSIZE(cell_reference) 

Sends back the file size in bytes saved in the attachment component located in the “cell ”

GTMOITEMNUM

GTMOITEMNUM() 

Sends back the file size in bytes saved in the attachment component located in the “cell 1

GTDISPLAYTEXT

GTDISPLAYTEXT(cell) 

Sends back the text displayed by the cell (takes into account the numeric format of the source cell)

Was this article helpful?

0 out Of 5 Stars

5 Stars		0%
4 Stars		0%
3 Stars		0%
2 Stars		0%
1 Stars		0%

5 Submit

How can we improve this article?

How Can We Improve This Article?

Name

Email

Submit