How can we help?

Search for answers or browse our knowledge base

< All Topics
Print

Components creation syntax (XlCode)

The creation of components is done using the following syntax:

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

Remark: If Value contains a character “;”, the Flag=Value set must be enclosed with double quotes “”

TYPES

TypeComponentRemarks
BEdit Box
LDropdown list
RRadiobuttons group
KCheckbox
CControl
TEdit box formatted as textEquivalent to B ;C=S ;TFMT
PDDescending attachment
PAAscending attachmentNot customizable by default (equivalent to PJ + NP)
PJAttachmentCustomizable by default
DDate
HHour
HLHyperlink
SScrollbar

SWITCHES

SwitchMeaningApplicable components
MMandatoryAll but checkbox, Control, Descending Attachment and Hyperlink
NPNot customizableAll but Descending attachment and hyperlink
NRThe value is not trasnmittedControl
EEditableDropdown list
TFMTPut a text format (@) on the componentMust be used with any component not containing a number, a date or a formula
NOSAVEThe save icon is not displayedAttachment
IMGDisplay image if the attachment is an imageAttachment
STRETCHEnlarges the image to be displayed to the size of the cellAttachment
PROPKeep image proportionsAttachment with IMG
NINot importable via Excel importAll editable components
NENot exportable via Excel exportAll editable components

FLAGS

FlagMeaningValueComponentsExamples
ITEMSDataPipe ("|") separated listL, 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
ITEMSSRCDynamic data sourceThe header must be a pattern or and Excel rangeL¤¤LIST;L;ITEMSSRC=PatternHeader
="¤¤"&"LIST;L;ITEMSSRC="&GTRANGE(RANGE)
CConstraintS :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
MAXMAX value (inf or equal to)Float ou Date ou HourB, 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
MINMAX value (sup or equal to)Float ou Date ou HourB, 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
LMax lengthInteger >= 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
CAPTIONTitleStringK ¤¤CHECK;K;CAPTION=MyTitle
Notice : the cell musn't be horizontally centered for thetitle to appear
COLSColumns settingH : 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
MSIZEMax sizeFloat >= 0PA, PJ¤¤ATTACH;PA;MSIZE=1
FILTERFilter description followed by allowed extensions ex:
Excel, Word 2007+|*.xlsx;*.docx|Zip Files|*.zip
StringPA, 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
LINKLinkStringHL ¤¤LINK;HL;Link=qstp://Page n°2
¤¤LINK;HL;Link=qsth://Header
DISPLAYDisplay NameStringTous ¤¤BEDIT;B;DISPLAY=DisplayName
INCRIncrementInteger > 1S ¤¤SCROLLBAR;S;INCR=2
DIRScrollbar orientation* H : Horizontal
* V : Vertical
S ¤¤SCROLLBAR;S;DIR=H
¤¤SCROLLBAR;S;DIR=V
DEFDefault 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
TSort in listsASC = Ascending
DESC = Descending
L ¤¤List;L;ITEMS=H|A|D;T=ASC
¤¤List;L;ITEMS=H|A|D;T=DESC

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
How can we improve this article?
How Can We Improve This Article?
Table of Contents