GETCOLUMNVALUE

Returns the value of a column by using the table name and value of the primary key which indicates the specific row from which value is to be derived. An application of this function is on the OrderDetailsTableControl of the AddOrders page. When the user selects the ProductID while adding a new record, the value in the UnitPrice can be updated using this function:

= GETCOLUMNVALUE("Products",Order_DetailsTableControlRow.ProductID.SelectedValue, "UnitPrice")

Syntax

= GETCOLUMNVALUE(tableName, keyValue, fieldName)

Parameters

tableName

A string which represents the name of the table from which the value is to be fetched.  This table must be included in the application in order for GETCOLUMNVALUE to return a value.

The TableName parameter is actually table class name without suffix instead of table name.  Sometime table class name and table name are not the same.  For example, if the table name has a space such as “Order Details”, the table class name will be “Order_DetailsTable”.  Table class name without suffix is “Order_Details”.  The correct value of tableName parameter is Order_Details.  To find the correct table class name, refer to the Business Layer.

As you see, there is a file called “Order_DetailsTable”.   This is how you find the table class name.  For tables, the suffix is “Table”, For views, the suffix is “View”.  For custom queries, the suffix is “Query”.  These suffixes should not be presented on the tableName parameter.

keyValue

The value of the primary key of the table. The key can either be an XML KeyValue structure or a string that is the Id of the record or a decimal value.

fieldName

The name of the field whose value is to be found.

The FieldName parameter is actually a field code name.  Sometimes the field name and field code name are not the same.  For example, if the field name is called “ID”, the code name will be “ID0”.  To find the code name for the fields, first locate the base table class in the business layer.  For example, for the table name called “Users”, go to BaseUsersTable.vb(cs).  If this file is not shown on Application Explorer, please make sure your application has been built and set Application Explorer to display all files by selecting View->Display all source files.

Then look into the Initialize method, you should see code name is defined in this method.

Return Type

Object

Examples

Example

Returns

= GETCOLUMNVALUE(“Customers”, “ALFKI”, “ContactName”)

The name of the customer corresponding to the CustomerID “ALFKI”.

= GETCOLUMNVALUE(“Orders”, URL(“Orders”), “OrderDate”)

The OrderDate for the OrderID passed as a URL value to the page.

= GETCOLUMNVALUE(“UserRoles”, "<key><cv><c>UserID</c><v>" + URL("paramUserID") + "</v></cv><cv><c>RoleID</c><v>" + URL("paramRoleID") + "</v></cv></key>", “Permissions”)

Uses the XML KeyValue structure to pass the UserId and RoleId to retrieve the Permissions field value.  The UserId and the RoleId values are retrieved the URL parameters.