Frequently asked Questions about Code Tables

Q: Are code table real database tables?

A: No, code tables are abstract or conceptual definitions only. You define to the Framework the columns that are in the code table and indicate which ones define the unique key for a row in the code table. The table data is frequently stored in a real database table, though it may share the database table with other tables.

Q: Where does the data in a code table come from?

A: It can come from anywhere. By default the Framework is shipped with an RDML function that will store code table data inside a single database table. However you can supply your own data storage function that can sources the code table data from anywhere that you want. This type of function is referred to as a Table Data Handler Function.

Q: How do I create a Data Storage function?

A: A Table Data Handler function is a normal LANSA RDML function that communicates with the Framework using a pre-defined protocol. If you want to create your own Table Data Handler function, see process UF_SYSBR functions UFU0010 – UFU0015 for examples.

Q: Can Data Storage Functions interact with the end-user?

A: No. Data storage functions are designed to act as data retrieval and update routines that can work in many different contexts. For example they can be invoked as a remote procedure by a Windows based Framework application or on a remote server as part of browser based application. This means that they need to be able to operate in contexts where no user interface is available to them.

Q: What are the benefits in using code tables?

A: The main benefits in using the Framework code table system are simply in improved productivity and consistency. By using a standard shipped architecture for code table maintenance your can develop and maintain applications more rapidly and avoid the cost and complexity of developing your own code table system.

Q: How do I interface an external LANSA function with the Framework generic table data table (FPTAB)?

A: The data in FPTAB is unusual in that it contains one record for every non-key cell in the table.

For example, in the Australian States table there are the fields

CODE (a key)

DESCRIPTN

MYSEQ

Each state will be represented as two records in FPTAB: One record for DESCRIPTN and one record for MYSEQ. Both records will contain all the key data (CODE in this case).

Akey1 (FP_EKEY1)	Nkey1(FP_EKEYN1)	Other keys2 - 5	Property Name(FP_EPTNAM)	Alpha property value (FP_EPTVAL)	Numeric property value (FP_EPTNV)
NSW			DESCRIPTN	New South Wales
NSW			MYSEQ		10
QLD			DESCRPTN	Queensland
QLD			MYSEQ		20
...

ExamplesI want to check that a currency code entered by a user is valid

*Use the index (kya) keyed by:

*Table name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ..., field name

CHECK_FOR in_file(FPTABkya) with_key('VF_CURRENCY' #MyCurrencyCodeField)

IF_STATUS *EQUALKEY

ENDIF

I want to display the description of a currency code that I have read from somewhere

*Use the index (nma) keyed by:

*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...

FETCH fields(#FP_EPTVAL) from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN' #MyCurrencyCodeField)

Change #MyDescriptionField #FP_EPTVAL

I want to read through the currency codes and report on all transactions for each currency

*Use the index (nma) keyed by:

*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...

SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')

(assuming that there is always a DESCRIPTN for a CURRENCY)

ENDSELECT

I want to read through the currency codes and report on all transactions for each currency, and I need to know both the exchange rate and the description

*Use the index (nma) keyed by:

*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...

SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')

Change #MyDescriptionField #FP_EPTVAL

* Get a numeric cell value
* Use a second index to avoid confusing the pointer
FETCH fields(#FP_EPTNV) from_file(FPTABn2a) with_key('VF_CURRENCY' 'EXCHRATE' #FP_EKEY1)

Change #MyExchangeRateField #FP_EPTNV

ENDSELECT

I want to report on all combinations of Currency and Department

*Use the index (nma) keyed by:

*Table name, field name, AKey1, Nkey1, AKey2, Nkey 2, Akey3, Nkey3 ...

SELECT *ALL from_file(FPTABnma) with_key('VF_CURRENCY' 'DESCRIPTN')

Change #MyCurrencyCodeField #FP_EKEY1
Change #MyCurrencyDescriptionField #FP_EPTVAL

* Now read through all the departments
* Use another index (n2a) to avoid confusing the pointer
SELECT *ALL from_file(FPTABn2a) with_key('VF_DEPTAB' 'DESCRIPTN')

Change #MyDepartmentCodeField #FP_EKEY1
Change #MyDepartmentDescriptionField #FP_EPTVAL

ENDSELECT
ENDSELECT

Note: It helps coding if for every table type there is a non-key field (e.g. Description) that must exist for every table entry.

I want to decode from a table with a numeric key

Say there is a table VF_POSTCODE keyed by #POSTCODE (numeric)

*Use the index (nmn) keyed by:

*Table name, field name, NKey1, Akey1, NKey2, Akey 2, Nkey3, Akey3 ...

FETCH fields(#FP_EPTVAL) from_file(FPTABnmn) with_key('VF_POSTCODE' 'DESCRIPTN' #MyPostCodeField)

Change #MyDescriptionField #FP_EPTVAL

I want to decode from a table with a mixed key

Say there is a table VF_POSTCODE keyed by #POSTCODE (numeric) and #COUNTRY (alpha)

*Use the index (nmn) keyed by:

*Table name, field name, NKey1, Akey1, NKey2, Akey 2, Nkey3, Akey3 ...

FETCH fields(#FP_EPTVAL) from_file(FPTABLnmn) with_key('VF_POSTCODE' 'DESCRIPTN' #MyPostCodeField *blanks 0 #MyCountryCodeField)

Change #MyDescriptionField #FP_EPTVAL

I want to read departments in description order

*Use the index (val) keyed by:

*Table name, field name, Numeric Property Value, Alpha Property Value.

SELECT *ALL from_file(FPTABval) with_key('VF_DEPTAB' 'DESCRIPTN')

(assuming that there is always a DESCRIPTN for a department)

ENDSELECT