Altering Custom look-up lists in Edit Forms 

Orixa uses relationships between different data-tables in an App to link the App's logic together. When records are added to the database the exact details of which records are allowed to be linked together may need to be customized. The following article shows how to alter these linkages, without reprogramming your App.

Altering a "Dependent" lookup on an Edit Form

Lookup list for Products in a Sales Invoice Item record  

In the image above a typical Orixa system is shown with the user in the process of picking a product to add to a sales invoice item. Marked at "1" we can see some products which are present in the list.

How is the content of this list defined?

Without any programming, by default, Orixa will add a "full list" to every drop-down list. If it is a list of Customers, all Customers would be included. The list would be generated using the DisplayScript for the Customers "BusinessObjects" record.

If you want to customize this list, the first thing to do is to add a "decoration" to the database-table.

Data-table definition with look-up list decoration  

1. Definition for the "Sales Invoice Items" data-table.
2. Field "ProductsID" links to the "Products" data-table, allowing the user to add any product to an invoice.
3. An additional decoration has been added to the ProductsID DESCRIPTION for the data-table. This alters the list of records the user will see when it drops-down

Types of decoration for lookup lists

Custom Lookup Lists

A decoration in the form: 

DESCRIPTION 'Custom, [name-of-linked table], [name-of-resource]'

Will substitute the SQL used to generate the list. The BusinessObject display script will no longer be used. Instead the SQL in the Resource with the name given in "name-of-resource" will be used.

As this is a simple replacement, the replacement SQL should always create a simple list of data containing "Name" and "ID", as shown in the example below.

Dependent Lookup Lists

A decoration in the form: 

DESCRIPTION 'Dependent, [name-of-resource], [name-of-linked-field]'

Will substitute the SQL used to generate the list, but with the additional control that the replacement SQL will take as a parameter the ID contained in the [name-of-linked-field]. This is explained in the example below.

Example Dependent Lookup SQL Resource  

1. A record has been added to the "Resources" data-table. Note that the name used is the same as the name used in the data-table decoration shown in the previous image.
2. Note that in this look up list the records returned is a limited number based on the "ProductsTypeID".

Products-types  

When new Product-Types are added, these new products will not  appear in the drop-down list until the new ID is added the the SQL Resource.