3. Relation Mapping

1. Introduction

In a relational database it is quite common to store references to other tables. For example, in the SalesInvoices table there is only the CustomerNumber which points to a record in the Customers table. To display the CustomerName, you need to look up that value in the Customers table. These relations need to be specified in your Silver Essence Configuration as well.

These relations are defined in the BaseEntity Properties where the Property is mapped to a LookUp. Other than with databases, these relations in the Silver Repository can span multiple data sources. For example, a referenced value for a MS-SQL database column, can be retrieved from an OData webservice. 

Furthermore, the Silver Repository distinguishes between referenced values for displaying data and data entry. Reason for this is that other validations might apply for viewing and entering data. For example, the Customer you want to select when entering a SalesInvoice must be an Active Customer. Now if you would enter a SalesInvoice and the Customer would be Deactivated, the reference from SalesInvoice would be invalid. To make sure data is displayed correctly, you could use a LookUp on Active Customers for data entry, and a LookUp on All Customers for displaying data.

2. Filter by DataType

Referenced values are commonly to be expected for the uniqueidentifier or GUID DataType. These are unique ID's that consist of 32 alphanumeric characters and 4 randomly placed dashed, like 'B62EA271-50B1-40A1-9EBD-45F76D5F4273'. These unique ID's are not to be read by humans and almost always point to referenced data. 

Of course, other DataTypes can point to referenced data as well. For example the string DataType for an ItemCode could also point to referenced data in the Items table. Or the integer CustomerNumber could point to referenced data in the Customers table. DataTypes like date/time and booleans however are not likely to have any references.

To make it easier to check which of your Properties would need a LookUp, you can filter the Properties by DataType:

Silver Editor: BaseEntity QuickEditor Filter by DataType

3. InputType

Once you have selected a LookUp for the Input List, or a Business Controller, you can change the Input Type. If you have no Input List or Controller specified, the option to select the Input Type will not be shown.

By default, the Input Type is set to a browser. This will show in the Silver Portal as a text input with a Browse Icon next to it. Clicking the Browse Icon will open up a list from which the user can select the referenced data. 

You can also select a Drop Down List which is recommended if you expect a limited small set op options to choose from. For example, you would use a Drop Down List for Genders (Male, Female, Unknown) or Project Statuses (Proposed, Active, Blocked, Completed). You would use the Browser for larger sets of options. For example, Customers, Projects, Items etcetera.

Silver Editor: BaseEntity QuickEditor, InputType

4. Label List

The Label List points to a LookUp that is used to show a referenced value when viewing data. For example, that could be the Customer Name according to the currently selected Customer Number. It is recommended to select a LookUp list that has no Query Extensions whatsoever. That way you will be sure that the referenced value can always be found an displayed.

Silver Editor: BaseEntity QuickEditor, Label List

5. Input List

The Input List points to a LookUp that is used to show a selection list when entering or searching data.Typically, the LookUp that is used here has more restrictions (by using Query Extension) than the aforementioned Label List. It is however not mandatory. Both Label List and Input List can use the same LookUp. 

Once you select a LookUp to be used as Input List, the Input Type will be set to Browser by default. You can change this option if applicable.

Silver Editor: BaseEntity QuickEditor Input List

6. Controller

LookUps require little configuration and are simple to use. LookUps typically work fine for lists where securing access is not such an issue like genders, project statuses etcetera. When it comes to a more tighter security and complex filters, using a LookUp for your input might lack the necessary options. For these cases, you can use a Business Controller. The Business Controller has far more options for filtering using Query Extensions and Signatures. Also, the Business Controller has also much more options to configure the Security for your data.

In most cases, using a LookUp will do just fine and this will save you the time to configure the whole Business Controller. In other cases, your options to configure the LookUp are just insufficient and you want to use the Business Controller. The Business Controller will show the main search screen when browsing for data. This will give the user much more options to search for data, more columns can be presented, data can be sorted etctera.

If you select a LookUp for your Input List, you can no longer select a Controller to be used. If you select a controller, this will prevent you from using a LookUp for the Input List. If you want to change from a LookUp to a Business Controller or vice versa, you can do so by first clearing your selection. After that, you will have both options available.

Silver Editor: BaseEntity QuickEditor Input Controller