The Pattern is a compartment that allows you to organise the components attached to it like a table:

• It defines the structure of a record
• It can have records added or subtracted.

The pattern is used in documents for which the number of information to be presented and/or entered varies according to the entities, whether this number is centrally defined or whether the entities add or subtract records.

Syntax

The pattern must be declared in the import field of the form matrix with the following syntax:

GTMOTIF (Name ; Base_Pattern ; [Flags ; [GTCLE (Duplicate Key Message ; Invalid Key Message ; Cell1 ; ... ; CellN) ; [Initial_range]])

• Name: the name of the pattern. This name must be unique for the whole form, it cannot be empty and must start with [A-Za-z]. Accented characters are not allowed.
• Base_Pattern: the cell range that will be duplicated. This range can have as many rows as desired
• Flags is a string that contains the pattern options via flags (separated by semicolons):
  • “ALL” corresponds to all options being TRUE
  • “NONE” corresponds to all options being FALSE
  • “A” the recipient can add rows.
  • “S” the recipient can delete rows
  • “H” boolean indicating whether row heights are preserved (below) and duplicated (inside) (default FALSE)
  • “T” the recipient can sort the data (default FALSE)
  • “F” the recipient can filter data (FALSE by default)
  • “V” the recipient can allow a single empty row despite the mandatory constraints (FALSE by default)
• GTKEY: a range of cells whose concatenated values must be unique for a pattern element. The key is empty by default. In the GTKEY macro, messages can be formulas. The cells must necessarily contain a component.
• Initial_Range: the initial range, which must be a multiple of the Base_Pattern. If the pattern is not populated with data, it will display as many records as the initial range. The initial range can be equal to the base pattern but having at least two rows of initial range allows to define a different border inside the cells. Note that the initial range also defines the minimum number of rows in the response.

Notes :

• The base pattern and the initial range must match (both in size and with respect to the merged cells, other styles are ignored, only those of the base pattern are important).
• The pattern domain (i.e. the cells below it) must not cross any merged cells below it (because of the cell offset).
• If a pattern is below another pattern; the right and left bounds of the latter must be contained within those of the pattern above (due to the cell offset).
• Only the base pattern can contain components and it must contain less than one.
• It is not possible to create the pattern in Design

Example

• The range B3:E3 is the basic pattern.
• The field “Subsidiary” in B3 serves as a key
• Cell E3 contains a formula with C3*D3, hence the error #VALUE in Excel.
• note that row 4 has a different height from row 3.
• Line 5, which is empty, is mandatory if you want to calculate totals. Here, cell C6 contains the formula =SUM(C3:C5) which will give the sum of column C of the pattern. This row can be hidden if the pattern option “Adjust row heights” is TRUE (it is the case here).

Adding/Removing Records at Runtime

In GTAnswer, an information button “i” displays the pop-up menu for adding/removing pattern lines:

Note that it is not possible to enter data in line 4, as it does not contain any components.

After insertion, the result obtained is as follows:

When inserting a record, the document will:

• Shift the cells downwards, which implies the translation of all the formulas referring to cells located below the insertion (i.e. all the cells that “move”): This is where the row 5 left empty in the Excel template comes in. The formula that was in C6 is now in C7 and is =SUM(C3:C6). For details on formulas in patterns, see the relevant paragraph.
• Copy all components and styles of the basic pattern into the new cells created (cell format, number format, conditional formatting, comment, etc.).
• Extend the formulas of the basic pattern into the new cells (identical to Excel’s “drag formulas”).
• If the pattern has the AdjustRowsHeights property set to TRUE, the row heights below are shifted and the height of the first row of the pattern is copied into the current row. If AdjustRowsHeights is FALSE, the line heights are not changed.

Bear in mind that the pattern works like the downward shift of cells in Excel. When adding a “pattern row”, there is no insertion of new rows in the grid. And so, everything below the pattern will be shifted down. For instance:

Will give, after insertion :

You can see the effect of the offset on the borders below.

As a result, a grid can contain several patterns with the restriction that a pattern below another cannot “cut the edges” of the pattern above it. Similarly, a merged cell below the pattern cannot “cut off the edges” of the pattern.

Sorting and filtering

Sorting

Sorting is only done by a single column. The complete rows of the pattern are sorted by this column. To sort by a column, you must right-click on a cell in that column:

The sorting criteria are dynamic depending on the data: in the example above, there are only numbers, so “from smallest to largest”. If the column contained at least one string we would have “A to Z”. If it contained only dates, “oldest to newest”.

Remarks :

• The sorting can be cancelled.
• The response sent by GTAnswer will also be sorted
• Errors (#N/A, …) are always considered higher than other values
• Empty cells are always considered lower than other values (they are always at the end).

Filtering

The principle is similar to that for sorting. The user must right-click within the pattern on the column he wishes to filter:

The filtering is done dynamically as the values are selected. However, it will only be applied definitively when the user has clicked on the “OK” button.

Remarks :

• The filtering can be cancelled
• Activating filtering requires the “Adjust row heights” option.Unlike sorting, filtering is multi-column.
• The filtering is done via the displayed values (i.e. taking into account the number format), as in Excel.
• The response sent to GtServer does not contain any filter information but if the document is saved, the filtering is saved.
• The “Clear” button clears the filter for the current column. To clear all filtering, use the “Clear All Filters” button in the pop-up menu.
• The filter is not compatible with hidden rows
• It is possible to search for items to be filtered using the input box above the list of items. In this example, entering “ar” in this box would filter the list, which would only display the items “Paris” and “Marseille”.

Key

A pattern key consists of the following elements:

• A list of components (given by cells during import)
• A message to be displayed in case of duplication.
• A message to be displayed for invalid keys (containing an error value)

The components forming the key must have the “Mandatory” option. The Excel import will add this option to the components if it is not already present. The key is checked :

• when a complete valid line is entered
• When an attempt is made to transmit the answers
• When the user clicks on the “View constraints” button.

The key values are case insensitive (2 identical labels with different case are not allowed) . A key containing errors (#N/A…) is considered invalid and will be displayed as such in the list of unverified constraints. The construction of the message formulas containing the values use the following function:

GTCONCATLIST (Separator; Text1; ...; TextN)

This function concatenates the values Text1 to TextN in the same way as the CONCATENATE Excel function by separating each text with Separator. If called with only one argument, it sends back the corresponding text.

“Empty” pattern and mandatory constraint on components

The option “Allow a single empty row despite mandatory constraints” allows you to override mandatory constraints and keys on components in the following cases:

• The pattern contains only one row
• All editable cells (regardless of locks) of the pattern are empty.

Importing Excel in GTAnswer

Only the area corresponding to the basic pattern in the input matrix requires named fields. However, GTAnswer needs to know the last row. To do this, you need to name a cell just below the last row of the pattern as follows:

Cell B9 is here named MyPattern.FOOTER. The footer is automatically created in the file exported. What is important is the end row and not the cell.

Since version 2021, the Excel import uses the pattern’s key to position the values at the right place. This is why it is impossible to import values into a pattern without a key.

Formulas behaviour in patterns

Let’s say the following pattern ( the header/footer in orange, the pattern in yellow , with the grid option “Formulas” = TRUE, the column headers are in text format and recall the original formula in the pattern).

After inserting several rows (not necessarily at the beginning or end of the pattern), we obtain :

Note the presence of an empty row before the footer: the footer aggregation formulas use this row so that the extension is done correctly as seen in the first paragraph.

Analysis by column :

• Column C: we have the desired result
• Column D: the range of the formula is contained in the row of the pattern and we have the desired result.
• Column E: the range starts in A3 which is not part of the pattern, so we get anything
• Column F: same as column E
• Column G: same as F but with absolute references, you get the desired result.

In all cases, the footers are correct.

Conclusion:

• If you need a footer (for example to make a total), you need an empty row before it and include this row in the formulas
• A range in a pattern that refers to the inside of the pattern must :
  • Either refer to a part of the row of the pattern (case D) relatively.
  • Either refer to the whole column (as in the footer) absolutely (case G)
• A range in a pattern that does not contain any pattern cells must be absolute (see below).

To understand how inserting a row affects the formulas, you need to know the two steps of inserting a row:

1. The cells below the insertion are shifted downwards. At this point all formulas in the form that need to be adjusted are adjusted, whether the references are absolute or relative. This is what Excel does. So, in the example above, when you first insert a row, cell B4 is changed to B5. The formula in the footer of column B is therefore transformed from SUM(B3:B4) to SUM(B3:B5). The formula in F3 which contains SUM(B3;B4) becomes SUM(B3:B5). Similarly, the formula in which SUM(B$3 ;B$4) is found becomes SUM(B$3 :B$5).
2. In the new cells, the formula from the first row is drawn ( according to Excel). This is where the absolute/relative difference comes in.

Example: In the previous case, the formula for E3 is taken from E4, so SUM(A3:C3) becomes SUM(A4:C4). For column F, we have seen that step 1 transformed the formula of F3 into SUM(B3:B5). This formula is taken from F4 which therefore contains SUM(B4:B6)! For column G, we have SUM(B$3 :B$5) which becomes SUM(B$3 :B$5), the row references being absolute.

The above example gives :

Column E seems to work as expected (A3:C3 then A4:C4). However, if you insert a row below the first one, you get :

In this, the GT calculation engine mimics that of Excel, which would provide exactly the same behaviour.

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