Frequently asked Questions about Code Tables

Visual LANSA Framework

Frequently asked Questions about Code Tables

Q: Are code table real database files?

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 file, though it may share the database file 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 (or LANSA for the web function) with the Framework generic table data file (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 logical view (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 logical view (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 logical view (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 logical view (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 logical view 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 logical view (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 logical view (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 logical view (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 logical view (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 logical view (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