The REMOTE function returns the value of a remote object. Usually this means the value of a cell in a spreadsheet or a field in a document. REMOTE is the "grandfather" of all linking functions - every other remote function can be expressed using just REMOTE.
document_name is the name of a remote document and must include the full path if not in the same directory as the open document. The document can be a spreadsheet, database or word processing document.
reference_name is the name of the object whose value is sought. An object can be a named field in a write or spreadsheet document, a cell or range reference in a spreadsheet, or a table, query, relation or SQL statement in a database.
Note that there are easier-to-use functions that will achieve the same result, as follows:
for linking to Write | |
for linking to Spreadsheet | |
for linking to Database |
Here are some simple examples, using REMOTE to link to spreadsheets and write documents.
REMOTE("c:\ability\samples\letter.aww", "myfield")
returns the value of the field myfield from the Write document letter.aww.
REMOTE("c:\ability\samples\income.aws", "A1")
returns the value of the cell A1 from the spreadsheet income.aws.
REMOTE("spread1", "C1..E17"))
returns the range C1..E17 from an untitled spreadsheet, Spread1. Note that since Spread1 has not yet been saved it does not have an extension.
Using REMOTE for database linking is usually unnecessary, as there is a full range of specific database linking functions.
The full syntax for REMOTE, including database links, is as follows:
Application |
reference_name |
Write |
reference_name is a field, usually inserted into a write document in order to perform calculations, or to establish a link to a spreadsheet cell or database field. For example, to get the value of a field called "daysales" in a write document called "report.aww", in the current directory, use the formula: REMOTE("report.aww", "daysales") |
Spreadsheet |
reference_name is either: cell reference ("A1") range reference ("A1..C3") range name ("myrange") Spreadsheet documents are made up of cells. Groups of these are called ranges and can be named. It is possible to refer to a cell, a range of cells or a named range. Spreadsheet also allows fields, which enable links to be established with other Ability applications, for example, when using mail merge, or with other fields in the same spreadsheet. All these are spreadsheet objects that can be referred to by REMOTE. For example, to get the value of the cell reference B21 in a spreadsheet called "manager", in the current directory, use the formula: REMOTE("manager.aws", "B21") |
Database |
reference_name is either: table query relation SQL statement In addition, a filter and/or sort order can be applied to the object and it is possible to extract a specific field or record. The syntax for tables is: "table !sort_order !filter !field_name !record_number" The syntax for queries is: "query !!!field_name !record_number" The syntax for relations is: "relation !sort_order !filter !field_name !record_number" The syntax for SQL statements is: "SQL_statement !!!field_name !record_number" Note that reference_name must consist of exactly one database object, namely a table, query, relation or SQL statement. This is optionally followed by a combination of database query components (sort order, filter) and specific locations (field name, record number). Each query component and location following the database object must be prefixed by an exclamation mark - !. Tables and relations can have a sort_order and filter applied to them, and refer to a field_name and record_number; queries and SQL statements can refer to a field_name and record_number, but are always empty for sort_order and filter. The arguments of reference_name are always entered in the same order, namely sort_order, filter, field_name and record_number. If any of these are empty, the ! must still be entered in the reference, but only up to the last argument used in the reference. For instance, if the reference is to a field_name in a table, but no sort_order, filter or record_number is specified, then reference_name contains table and field_name, which is prefixed by !, and !! to stand for the two empty query components between these. No ! is needed, however, for the missing record_number, since this comes after field_name. The full reference_name is "table!!!field_name". If no query components or locations are specified, REMOTE will return the database object, for example, a table, but display only the first field of the first record. Likewise, if a reference is made to a field_name or record_number, then the field (column) or record (row) is returned, but only the first cell in the field or record is displayed. Of course, if a reference is made to a field_name and a record_number, there is only one cell that can be returned and displayed. You can use REMOTE in conjunction with other functions to perform operations on rows and columns of database objects. For example, the formula =SUM(REMOTE("Company.adb", "Salary!!!GrossPay") might sum the gross pay of all the employees in a company. |
Examples:
REMOTE("c:\ability\samples\mydata.adb", "Employee")
returns the table Employee from the database mydata.adb.
REMOTE("c:\ability\samples\mydata.adb", "Customer!ByCity")
returns the table Customer from the database mydata.adb with saved sort order ByCity applied.
REMOTE("c:\ability\samples\mydata.adb", "Customer!ByCity!UpperHalf")
returns the table Customer from the database mydata.adb with saved filter UpperHalf and sort order ByCity applied.
REMOTE("c:\ability\samples\mydata.adb", "Customer!ByCity!UpperHalf!Company")
returns the field Company from table Customer from the database mydata.adb with saved filter UpperHalf and sort order ByCity applied.
REMOTE("c:\ability\samples\mydata.adb", "Customer!ByCity!UpperHalf!Company!12")
returns the 12th record from field Company from table Customer from the database mydata.adb, with saved filter UpperHalf and sort order ByCity applied.
REMOTE("c:\ability\samples\mydata.adb", "Employee!!!LAST_NAME!3")
returns the third last name from table Employee from the database mydata.adb.
REMOTE("c:\ability\samples\mydata.adb", "QCalc!!!!2")
returns the second record from the query QCalc from the database mydata.adb.
REMOTE("c:\ability\samples\mydata.adb", "JoinOnCustID!!HANOP")
returns the relation JoinOnCustID from the database mydata.adb with applied filter HANOP.
REMOTE("c:\ability\samples\mydata.adb", "SELECT Quantity FROM Orders WHERE CustID = BERGS")
returns the range of quantities from Orders table from the database mydata.adb where CustID = BERGS.
See also: