This tutorial will show you how to hyperlink to a record of a pattern.

downloadable files

• Hyperlink and pattern.xlsx: this is the Excel matrix used in this tutorial

Context

Hyperlinks are very useful to guide the user to a specific area of a form and this is particularly valuable in the case of large forms, avoiding the user to browse an entire document in a tedious way. The motifs are a good context for use; indeed, since a motif contains several tens, or even hundreds of lines, which is quite common, Asking the user to manually browse the pattern records looking for a particular data can be particularly painful. The purpose of this tutorial is to show you how to use a hyperlink to point to a particular record in a pattern.

Example provided

The form should allow you to enter a list of contracts and then quickly select those contracts with an amount greater than 100.

The pattern is declared in A2 with the following syntax:

=GTPATTERN("my_pattern";A5:E5;A5:E5;TRUE;TRUE;TRUE). 

Each pattern record contains the following items:

• Ref : Mandatory input box, the contract reference.
• Date : Date, contract date.
• Montant : Input box, the contract amount.
• > 100, a control containing the contract reference if the amount is greater than 100:

=GTCONTROL("Sup100";IF(C5>100;A5;""))

• Column E contains only the formula =GTPATNUM() to determine the row number in the pattern (record number)

In order to select contracts with an amount greater than 100, a dynamic drop-down list is created in D1 with the following statement:

="¤¤ListSup100;L;ITEMSSRC="&GTRANGE($D$5:$D$6). 

As column D contains the references of contracts with an amount greater than 100 or blanks, the drop-down list will be correctly populated. The next step is to create the hyperlink pointing to the record selected in the drop-down list. This hyperlink is declared in D2 with the following syntax:

=GTHYPERLINK("Link";IF(D1="";"";"Aller à "&D1);"qsth://Ref"&"."&(VLOOKUP(D1;$A$5:$E$6;5;FALSE)))

Function VLOOKUP returns the line number of the contract selected in the drop-down list, the IF function being there to display the hyperlink label only if a contract is selected.

Function GTHYPERLINK must point to a component. In this case, it is the “Ref” component that is the target of the hyperlink. Once solved, the formula determining the hyperlink in the screenshot below would read as follows

=GTHYPERLINK("Link"; "Go to b"; "qsth://Ref.2)

Points to watch out for

• Check that the drop-down list is editable to avoid data inconsistency. In the screenshot above, the drop-down list contains item “b” because the value of the corresponding “Amount” field is greater than 100 (here it is 456). However, as the “Amount” field is editable, if the user changes its value to less than 100 after selecting “b” in the drop-down list, they won’t be able to transmit their data if the drop-down list isn’t editable. Indeed, if the new value of the amount is less than 100, then the drop-down list no longer contains the element “b” and an error will be raised when attempting to transmit, indicating that the value in the drop-down list isn’t one of the elements in the list. The only way to avoid this error is to make the drop-down list editable using the “E” switch.