Data Mapping

From Lianjapedia
Jump to: navigation, search

Overview

In the context of relational databases, a foreign key is a field in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key or a unique key in the first table. For example, a table called Employee has a primary key called employee_id. Another table called EmployeeDetails has a foreign key which references employee_id in order to uniquely identify the relationship between both tables.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Bm-noteicon.png
Quick Start Guide

Data mapping is the process of performing a two way mapping between the fields in the parent and child tables.
This is accomplished by setting the "Choices", "Get Data Mapping" and "Set Data Mapping" attributes for fields or grid columns.

See Also

Autosuggestions, Choices Column Constraint, Constraints, Data Files Tab, FIELDCHOICES() GETDATAMAPPING(), Getdatamapping Column Constraint, Help Attributes, INDEX, KEYLOOKUP(), Pages Files Tab, SETDATAMAPPING(), Setdatamapping Column Constraint, SQL SELECT, SQLLOOKUP()

Example Data Mapping App

Example Data Mapping App: example_datamapping


The Example Data Mapping App (example_datamapping) illustrates the use of Data Mapping to display a 'mapped' value of a field in a Grid Section cell. As demonstrated here, where a product's name is displayed, rather than its numeric product ID, it is frequently used to present more user-friendly data pulled from a lookup table.

Data Mapping is also supported in Form and Canvas Section fields.

Example Data Mapping App: example_datamapping


Note that from Lianja v4.1, Data Mapping is also supported in the Web/Mobile client and the example_datamapping App has been updated to show this. It can now be found in the Demo Web/Mobile Apps category.

Choice list


In the Grid Section (southwind!order_details table), the users see the product name in place of the product ID that is actually stored in the table.

They can select a product name from the provided choice list and if they make a change, the correct product ID will be written back to the table.

The relevant Grid Column Attributes here are Choices, Get data mapping and Set data mapping.

Field Attributes



To use Data Mapping in the Web/Mobile client (from v4.1), the Choices, Get data mapping and Set data mapping data dictionary field attributes must be set.

Setting the Inherit dictionary rules Section Attribute for the Grid Section to True causes the data dictionary settings to be applied to the Grid Column Attributes automatically.

Remember the database needs to be deployed after making any data dictionary changes, before these will be available in the desktop App Center or Web/Mobile client.

Bm-noteicon.png
Inherit dictionary rules

With the Section Inherit dictionary rules Attribute set to True, changes you make to Grid Column Attributes
(or Formitem Attributes in Form or Canvas Sections) will be overwritten by their data dictionary equivalents,
if set, when the App is saved and reloaded.

Choices

The Choices choice list is based on a SQL SELECT statement, returning the product names from the products table. It can also include an ORDER BY clause to sort the names.

select productname from products order by productname

KEYLOOKUP()

Both the Get data mapping and the Set data mapping in this example use the KEYLOOKUP() function.

The KEYLOOKUP() function searches the index of a table and can return values from the corresponding record if the key is found or an alternative expression if there is no match.

KEYLOOKUP(<workarea | alias | table>, <"indextagname">, <keyExpr>, <valueExpr>
    [, <notfoundExpr>])

Get data mapping

The Get data mapping determines the value that will be displayed in the cell.

keylookup("products", "productid", {}, productname)
Argument Description
"products" The name of the lookup table (in quotes).
Alternatively, the alias name (in quotes) or workarea number (no quotes) can be specified if the lookup table is open.
"productid" The name of the index tag (in quotes) for the lookup table to search. Index tags can be created using the index on command.
{} The key value expression to search for in the index. In this case, we are looking for the numeric value of the order_details.productid field in the current row of the Grid Section, so we can use {}. For a character value, quotes are required "{}".
productname The value expression to return if the key value is found in the index. So, in this case, if the value of the current order_details.productid is found in the products table productid index, the products.productname value will be returned for the corresponding record.
- An optional 'not found' expression can also be specified. If the key value is not found in the index, this will be returned. This should be the same data type as the 'value' expression, e.g. "Product not found" for the character products.productname here.

Set data mapping

The Set data mapping determines the data that will be written back to the table when the cell's value is updated.

keylookup("products", "productname", "{}", productid)
Argument Description
"products" The name of the lookup table (in quotes).
Alternatively, the alias name (in quotes) or workarea number (no quotes) can be specified if the lookup table is open.
"productname" The name of the index tag (in quotes) for the lookup table to search. Index tags can be created using the index on command.
{} The key value expression to search for in the index. In this case, we are looking for the character value of the mapped cell contents in the current row of the Grid Section, so we can use "{}". For a numeric value, no quotes are required {}.
productid The value expression to return if the key value is found in the index. So, in this case, if the value of the current cell is found in the products table productname index, the products.productid value will be returned for the corresponding record.
- An optional 'not found' expression can also be specified. If the key value is not found in the index, this will be returned. This should be the same data type as the 'value' expression, e.g. 0 for the numeric products.producid here.

Notes on Client Support

Attribute Notes
Choices -
Get data mapping Supported on all clients from v4.1; previously on the Desktop client only. For Web/Mobile client, must be defined in the data dictionary field attributes.
Set data mapping Supported on all clients from v4.1; previously on the Desktop client only. For Web/Mobile client, must be defined in the data dictionary field attributes.