AX1374

Querying a hyperlink column

If hyperlink data is stored in a designated hyperlink column, an Axiom query can return this data using clickable hyperlinks.

For example, you might have a Parts table where each part is associated with a hyperlink to a specifications sheet. Or you might have a table that holds detailed transactional data, where each record is associated with a hyperlink to an invoice. When an Axiom query includes this Specification column or Invoice column, the contents of the column are automatically converted to clickable hyperlinks (rather than requiring you to manually convert the document data into a hyperlink).

For information on how to set up a hyperlink column in a table, see About hyperlink columns .

Using the hyperlink column in an Axiom query

When a hyperlink column is included in an Axiom query field definition, its values are returned as follows:

• If the column value starts with http, https, or file, the value is automatically wrapped in a Hyperlink function. For example:

  =HYPERLINK("http://partserver/part1spec.pdf", "Part Specification")

  Where http://partserver/part1spec.pdf is the value stored in the column and Part Specification is the text defined as the Hyperlink Label for the column. The equivalent file example would look like the following:

  =HYPERLINK("file://\\partserver\partspecs\part1spec.pdf", "Part Specification")

• Otherwise, the column value is automatically wrapped in a GetDocument function. Axiom assumes that any value that does not fall into the first category is a valid Axiom file path. For example:

  =GetDocument("Part Specification", "\Axiom\Reports Library\PartSpecs\part18spec.pdf")

  Where Part Specification is the text defined as the Hyperlink Label for the column, and \Axiom\Reports Library\PartSpecs\part18spec.pdf is the value stored in the column.

The following screenshot shows an example Axiom query. The PartSpec column is a designated hyperlink column. When the query is run, the column value is automatically converted to a hyperlink as shown in the formula bar. It is not necessary to manually convert the data using the in-sheet calc method.

Note the following design considerations:

• Microsoft Excel automatically formats hyperlink cells as blue font with underline text. The Windows Client does not. If a hyperlink is generated and saved in one client, then that hyperlink will continue to display as it was saved regardless of which client it is subsequently viewed in. Because of this, you may want to manually format the cell using the in-sheet calc method so that the hyperlink always displays in the same format.

• No formatting is automatically applied to the GetDocument functions. If you want these functions to display as if they were hyperlinks (blue font with underline text) then you must manually format the cell using the in-sheet calc method.

• Hyperlinks are launched with a single click, but GetDocument functions require double-clicking. If you are using GetDocument functions, you may want to indicate this behavior in the Hyperlink Label for the column (i.e. "Double-click to open specification"), or as user instruction within the file.

• Hyperlink functions using the file protocol are not supported for use in the Windows Client. These functions can only be used in the Excel Client.

Alternate query syntax to return the raw value

You can optionally override the default query behavior and instead return the raw value stored in the hyperlink column. You might do this if:

• You are creating a Save Type 1 report to populate and maintain the values in the hyperlink column. In this case, you must be sure to return the raw value from the column and save back a raw value.

• You are creating a formatted grid for use in an Axiom form. In this case you must manually return the raw value so that you can reference it in an HREF tag. See the following section for more information.

To return the raw value using an Axiom query, enter the following syntax in the field definition:

For example, if the column is named PartSpec then you would enter the following:

You can also enter the column name as just PartSpec if the primary table of the Axiom query is the Part table, as shown in the following example:

NOTE: You can also use GetData to query a hyperlink column. In this case the raw value is always returned. You can wrap this result in a Hyperlink function or a GetDocument function to provide a clickable hyperlink.

Using hyperlink column values in Axiom forms

The Hyperlink and GetDocument functions generated by the Axiom query can only be used within the Excel Client and the Windows Client. Axiom forms do not render these functions as clickable hyperlinks.

If the hyperlink column contents are web URLs, then you can still use this data in Axiom forms. To do this, you must return the raw value instead of the converted value as described in the previous section. You can then reference the raw value in an HREF tag (or in a Hyperlink component) to display a clickable hyperlink for use in the form.

However, there is no equivalent option if the hyperlink column holds Axiom file paths. GetDocument functions are not usable in Axiom forms, and there is no way to convert the file path to a URL. A document ID is required to generate a URL using GetDocument hyperlink. You can generate hyperlinks for these documents in different ways (such as by querying Axiom.FileSystemInfo), but not by querying the hyperlink column.

AX1374