Lookups - Cells with a List of Options to Choose a Value from

Note

This tutorial presupposes what done in Customizing the Application's Appearance tutorial. If you have missed or skipped the previous tutorial or you want to experiment and go back, don't worry! You can import this .eap file to sync or restore with what you see in this documentation. Learn about resetting by using .eap files. If you don't use the .eap, what you see might differ slightly from the screenshots.

Lookups enable you to configure table cells with a default lists of values to choose from. You will learn the basics of Lookups and how to design them.

After Building Your First App: Personal Budget and Customizing the Application's Appearance, you have a fully functioning application to record your income and payments. But you can improve an eLegere application in multiple ways. One of these ways are Lookups.

Lookups are drop-down lists of values to fill a table cell. The user clicks on the row's cell with the Lookup and can choose one the values from the list as record.

Lookups are useful if you want to provide a limited set of values for a field and unnecessary data repeat.

The tutorial teaches you how to create Lookups by guiding you through some step-by-step examples.

You will learn about what constitutes any Lookup: Relations between Storages. During the explanation, you will acquire some good practices and rules to manage Lookups and Relations as well.

Note

The tutorial explains first the steps to create a Lookup and its Relation. The tutorial will require to repeat the same pattern of operations many times. If you prefer to understand the theory first before practice, go to Rules for Relations and Lookups. Then go back to the beginning and follow the tutorial. Otherwise, continue to read the sections in order.

Create a Lookup for the Expense or Income Type Column

Learn to build your first Lookup for Personal Budget: differentiate the choice between expenses and incomes.

At this stage, you have only the Storage Transactions. (The picture displays two example fields.)

The first thing you need is a Storage. Every Lookup requires a Storage with the list of fixed values to choose from.

Suppose that for each entry of Personal Budget you need a Lookup field specifying if the entry is an income or an expense.

To do the above, you need to:

• Create and Configure the Storage for the Lookup
• Establish a Relation in Transactions
• Enable the Lookup Relation on Personal Budget
• Configure the Column for the Lookup Relation.

Create and Configure the Storage for the Lookup

In this stage, you are going to create a Storage to record the options of the drop-down list.

1. Create a new Blank Storage and call it Income Expense Type by following the procedure in Building Your First App: Personal Budget.

2. Add the following fields to Income Expense Type Storage.

   • IdIncomeExpenseType Identity The Identity for the entries of Income Expense Type Storage.
   • TypeName String The field storing the name of the option.
   • Icon Icon The field storing the icon representing the option.
   • Color Color The field storing the color to highlight the option.

   

   Tip

   Lookups don't require Color and Icon type fields to work: they're optional. Color and Icon fields help to assign a distinctive icon and color to the option. Assigning a distinctive icon and color helps identifying the options in the list.

3. Save the Storage and create a new application called Income Expense Type. Income Expense Type must use the Storage of the same name (i.e. Income Expense Type).

   

4. In Design Mode of Income Expense Type, make visible the Identity field IdIncomeExpenseType. In the Master sub-panel, click on Is Visible on the Grid button () on the IdIncomeExpenseType's row.

   

   The next steps and the next tutorials require making visible IdIncomeExpenseType to refer to specific elements through their IDs. You will understand later why is useful to see the IDs of Storage's entries in Further Study: Default Value for Lookups and when you will restrict data visibility.

   Info

   Users can't edit Identity type fields. Applications assign the ID to the field automatically. Each numeric ID is unique.

5. Change the name of the fields as follows and save the changes in Design Mode.

   

   • Name
   • Icon
   • Color
   • ID

6. Insert and save the following entries in the Master table of Income Expense Type as they appear in the image below. (The Application assigns the IDs to the ID field automatically. No further action is required.)

   

   Tip

   Defining an icon and a color is optional. The Lookup will work despite Color and Icon fields exist or are empty. But defining an icon and a color helps identifying the option in the Lookup more easily.

Establish a Relation in Transactions

In the previous step, you have created the storage recording the options for the drop-down list. Now, you must create a Relation between the data of Income Expense Type Storage and Transactions Storage. Since the Lookup will appear in Personal Budget, you will establish the Relation in Transactions (the Storage used by the Personal Budget Application).

1. Add to the Transactions Storage a Numeric field type called IncomeExpensesTypeId.

   

2. In the Storage editor of Transactions, click on the Relations sub-panel. The click on Add Relation button on the upper-right corner.

   

3. Choose the Income Expense Type Storage for the Relation.

   

4. In the window that appears, you must choose the fields to be related. Choose for Transactions the field IncomeExpensesTypeId. The field for Income Expense Type Storage is already set on the Identity field IdIncomeExpensesType. Name the Relation as Income_Expense_Type_Lookup.

   

   Introduction to a few concept that the later Rules for Relations and Lookups discusses. Understand the following key points before continuing with the tutorial.

   • Relations are relationships between one field of a Storage and the field of another Storage.
   • A Relation constituting a Lookup is a Lookup Relation.
   • A Lookup Relation is a relation between a Numeric field and an Identity field.
     • The Numeric field must be in the Storage of the application that will display the Lookup drop-down list. I.e. Transactions
     • The Identity field is the Identity field of the Storage containing the options that must appear in the drop-down list. I.e. Income Expense Type
   • In an application you can display the Relation as a list of fixed options to scroll and select in the Application: a Lookup.

   Tip

   Convention: The fields related in a Relation must have unique, clear, and unambiguous names. It's recommended to call the Numeric fields FieldNameId in a Lookup Relation. In this way, it's clear to what Identity field IdFieldName they relate in a Relation. Note: There are no mandatory rules to choose a field name. The suggestion above helps to identify fields and relations without confusion in Storages with multiple Lookup relations.

   Tip

   You can establish a Relation with multiple sorts of field between two Storages. The most common is between a Numeric field type and Identity type field.

5. Click on the Confirm button () to create the Lookup Relation.

   

   The tile of the relation will appear in the sub-panel Relations.

   

Enable the Lookup Relation on Personal Budget

You must now enable the Lookup Relation on Personal Budget.

1. Access Personal Budget's Design Mode and click on the Create the Lookup Relation button () corresponding to IncomeExpensesTypeId.

   

2. The Lookup Relation window will appear. (The window's settings with the optional tag are optional. They are not required for the Lookup to function.)

   

   	Property	Description
   1	Related Table	The Relation to the table containing the options for the drop-down list. (The tables of Storages with a Relation established with the field will appear in this list.)
   2	Show Field	The field containing the names of the options for the drop-down list.
   3	Icon Field	optional The field containing the icons for the options.
   4	Color Field	optional The field containing the colors assigned to each entry of the drop-down list.
   5	Order Field	The field to use as reference to order the list of the options of the drop-down list.
   6	Order Type	Option to set Ascendant or Descendent order for the Order Field.
   7	Formula	optional Adds a formula to set a behavior to the Lookup. (You will see its use in one of the following tutorials.)
   8	Restriction Formula	optional Adds a formula to display certain options of the list under certain conditions. (You will see its use in one of the following tutorials.)

   Choose the Lookup relation existing with Income Expense Type storage. Fill the rest of the settings with the Income Expense Type storage's fields.

   

3. Click on the Confirm button to save the Lookup settings.

Configure the Column for the Lookup Relation

At this stage, you must configure the column of the Lookup in Personal Budget's Design Mode.

1. Makes the following changes in the Master sub-panel to the IncomeExpensesTypeId row:

   • Change the Name from IncomeExpensesTypeId to Income/Expense.
   • Make the field Visible.

   

2. Go to Layout. Arrange Income/Expense in the table as you prefer.

3. Remove the Cell Bg Color formula you have setup in the previous tutorial Customizing the Application's Appearance. The formula is outdated since you have a Lookup and has no use anymore. The Lookup entry will highlight the same kind of information.

   

   Click on the Cell Bg Color, then on the Clear Formula button. Close the Color Settings by clicking on the Confirm button.

   

4. Remove from Field Descriptions the description of the Value column. You don't need to add the the minus symbol (-) anymore for the expenses to the BudgetValue field.

5. Save the changes in Personal Budget's Design Mode (Save ).

6. Remove the minus symbol (-) from the Value column's cell (field BudgetValue). You don't need the symbol to classify an entry as an expense thanks to the Lookup.

   

You have finished to configure the Lookup Relation for Income/Expense.

Rules for Relations and Lookups

Before proceeding, you must understand the fundamentals behind Relations and Lookups. It's a lot of theory but necessary to avoid issues and mistakes.

Warning

It's essential to understand the following key points. Spend as much time as needed to understand Relations and Lookups.

Key Points to Remember

1. Relations are connections between the data of two Storages.
2. Relations are independent from their representation in an eLegere Application. You can determine in an application different ways to represent the same Relation. (You will see another way in the next tutorial on Details)
3. A Relation has two components:
   • A source field that stores a reference pointing to an entry of the other storage
   • A target field that constitutes the entry of the second storage that the source field references.
4. You can create multiple relations having the same source field.
5. Lookups are one way to represent a Relation as a drop-down list.
6. When you represent a Relation as a Lookup, you have a Lookup Relation.
   • Lookup Relations require a source field recording the pointer to the other Storage. Most of the time, a Numeric type field covers this role because Identity fields assign unique numbers as IDs.

In short, Relations are ways to connect entries on a Storage to entries of other Storages.

Why do you need Identity fields as target field?

When the source field in the Relation points to an entry in the other Storage, the source field stores one value of the target field.

To be sure that the reference field points to exactly that specific entry among many, you must choose as target field a field with unique and unambiguous data. Identity fields are perfect for this: each Identity field assigns a unique number to a Storage's entry automatically. You are sure that the target field value will be unambiguous. Using Identity avoids to have two identical entries since only one entry can have that ID value.

Warning

In theory, you could use as target field a field different from an Identity field. But using a field different from an Identity isn't recommended (unless you know what you are doing).

Why? Consider the following cases.

Bad Case 1

Source Field	Target Field
Numeric type	Numeric type

Suppose that you establish a Relation like the Case 1. Also, imagine that there are multiple entries with the same value for the target field. You have two non-unique fields and the reference is ambiguous.

Table Income Expense Type

ID	Name
1	Expense
1	Income

Table Transactions

Name	Value	Income/Expense
Car Fuel	40	1 (income or expense?)
Shared Account	5	1 (income or expense?)

If multiple entries of target field have the same value, how the source field knows what value to record as reference? The Lookup breaks because it's not clear what entry and value the source field must point to and record.

Bad Case 2

Grounding a Relation between Fields with values that might change is not recommended.

Source Field	Target Field
String type	String type

Suppose that you establish a Relation between two String type fields. In this case, if one of the fields changes value, you lose the reference of either the source field or the target field.

Before ID value change

Table Income Expense Type

ID	Name
out	Expense
in	Income

Table Transactions

Name	Value	Income/Expense
Car Fuel	40	out (expense)
Shared Account	5	in (income)

After ID value change

Table Income Expense Type

ID	Name
outcome	Expense
income	Income

Table Transaction

Name	Value	Income/Expense
Car Fuel	40	out (no reference)
Shared Account	5	in (no reference)

It's not a good practice because any change causes a loss of the reference.

Bad Case 3

Another case to avoid is between two Identity fields (or two Unique fields.)

Source Field	Target field
Identity type	Identity type

Bad Case 3 causes the opposite problem of Bad Case 1: there could be only one entry with that value. This happens because Identity fields generate unique numbers as IDs. When you have two unique fields, you can't assign the same value to multiple entries.a

Table Income Expense Type

ID	Name
1	Expense
2	Income

Table Transaction

Name	Value	Income/Expense
Car Fuel	40	1
Lunch	15	1 (not allowed: there is another row with value 1 for the unique column Income/Expense)

If Bad Case 3 occurs, you can assign that value of the Lookup only to one entry. eLegere will consider an error to assign the same Lookup value to another entry.

Good Case

Source Field	Target field
Numeric type	Identity type

Good Case is an example of a Relation built in a good way. You have a non-unique field that points to and references a unique field.

The Identity as target field will have only unique numbers as values to identify an entry.

Numeric type fields works well as Source fields because the values of the target field Identity are numbers.

The relation between a Numeric and an Identity field is always unique. So, a case like Bad Case 1 never happens. The Lookup will list all the options without issues because each entry is referenced without ambiguity.

Also, changes to the entry won't affect the reference: the source field points to a unique value that indexes that particular entry in the Storage. Any change won't affect the ID of the entry and the reference remains intact.

Table Income Expense Type

ID	Name
1	Expense
2	Income

Table Transaction

Name	Value	Income/Expense
Shared Account	5	1 (expense)
Car Fuel	40	1 (expense)
Cash back	20	2 (income)
Gym Subscription	80	1 (expense)

Table Transactions with Lookup

Name	Value	Income/Expense
Shared Account	5	Expense
Car Fuel	40	Expense
Cash back	20	Income
Gym Subscription	80	Expense

The Rules

A list of rules to avoid the issues described in the above section exist. Follow them whenever you can:

1. Always build Relations between a Numeric type field as Source field and an Identity type field as Target field (unless there is a very good reason).
2. In the Storage, always use the following convention to identify if the Relation is established for a Lookup or other kinds of representations: Storage_Name_TypeOfRelation. For example, label a Relation that will become a Lookup Relation as Product_List_Lookup.
3. Always establish a Relation between a unique and a non-unique field. Never establish a Relation between two unique fields or two non-unique fields.

Create a Lookup for a Payment Methods Column

Suppose that you need to specify the payment method besides the amount of money and the type of entry. To do so, you have to create a new Blank Storage and must establish a Lookup Relation to introduce a drop-down list with the payment methods' list.

Tip

Before proceeding, be sure to have understood what are Relations, Lookups, and how they work. Take your time and revise Rules for Relations and Lookups in case.

Note

You will do the same operations made for the Income/Expense Lookup, but for the payment methods.

You must create a Lookup Relation between the Storages Transactions and Payment Methods and configure the relation in Personal Budget.

A Lookup for Payment Methods in Personal Budget requires:

• A Storage Payment Methods to record the data of the payment methods.
• An application of the same name (Payment Methods) to insert the payment methods in the Storage.
• A Relation between Transactions and Payment Methods.
• The Lookup configured in Personal Budget with the Payment Method options.

Create the Payment Methods Storage

You need at this stage to build a Storage containing the Payment Method options.

1. In the Storages sub-panel in Home, create a Storage called Payment Methods. (See Building Your First App: Personal Budget to know how to build Storages.)

2. Set up the following Fields in Payment Methods:

   • IdPaymentMethod Identity The Identity field for the entries of Payment Methods.
   • Name String The name of the payment method.
   • Icon Icon The Icon field type for the icon associated to the payment method's entry.

   

3. If you like, customize the Payment Methods Storage's tile and save all the changes.

Create the Payment Methods Application

1. Go back to the Home, Applications sub-panel, and create an application Payment Methods based on the Storage Payment Methods.

   

2. You do not need to customize the application. Payment Methods is ready to record the payment methods.

   

   Add the payment methods that you could use in Personal Budget.

   

Create the Relation from Transactions to Payment Methods

You must now establish a relation between the two Storages. Afterwards, you will configure the relation as a Lookup Relation in the application.

1. Add to the Storage Transactions a new Numeric type field called PaymentMethodId.

   

2. Establish a Relation between PaymentMethodId and IdPaymentMethod of the Payment Methods Storage. Call the relation Payment_Methods_Lookup.

   

   

3. Save all the changes to the Transactions Storage and the new Relation.

   

Configure the Payment Methods Lookup

1. Enter the Design Mode of Personal Budget Application.

   

   Configure the Lookup Relation of PaymentMethodId as follows:

   

2. Make the field PaymentMethodId visible and rename its header's label as Payment Method.

   

3. Arrange the Payment Method column's position and customize the appearance in the Layout sub-panel.

   

4. Save all the changes in Personal Budget's Design Mode (Save ).

You will be able to register the Payment Method for the entries of Personal Budget.

Extending Payment Methods: Add the Type to a Payment Method through a Lookup

At this point, introduce in Payment Methods a column that specifies the type of the payment method. For example: bank account, credit card or debit card.

1. Create a new Blank Storage called Payment Methods Type and add the following fields.

   • IdPaymentMethodType Identity The Identity field for the entries of Payment Methods Type.
   • Name String The name of the category of payment method. (e.g. bank transfer, cash, credit card, debit card)
   • Icon Icon The Icon field type for the icon associated to the payment method category.

   

2. If you like, customize the Payment Methods Type Storage's tile and save all the changes. The situation will be like the picture below.

   

3. Go back to the Storage editor of Payment Methods.

   

   • Add a Numeric type field PaymentMethodTypeId.
   • Create the relation Payment_Methods_Type_Lookup between the field PaymentMethodTypeId and IdPaymentMethodType from Payment Methods Type.

   

4. Save all the changes on the Payment Methods Storage.

5. Create an application called Payment Methods Type based on the Storage Payment Methods Type.

   

6. Add the following Payment Methods Type in the application and choose an icon:

   

   • Cash
   • Check
   • Credit Card
   • Debit Card
   • Bank Transfer

7. Save all the entries in the Payment Methods Type application.

8. In Payment Methods' Design Mode, configure as follows the Lookup Relation corresponding to PaymentMethodTypeId.

   

   The result will be like the picture below.

   

9. Change the header's name of the field PaymentMethodTypeId in Method Type, make the column visible, and customize the column in Layout if you like.

10. Save all the changes in Payment Methods' Design Mode (Save ) and enter the application.

Now, the Lookup between Payment Methods and Payment Methods Type will work. You have obtained a more functional application to manage your payment methods by using Lookups.

Exercise: Create a Lookup with a Products and Services Category List for Personal Budget

Try by yourself to add a Lookup for the different sorts of products and services paid or received. (Here you find the solution.)

At the end of this tutorial, the map of the Lookups should correspond to the picture below.

Further Study: Default Value for Lookups

Note

This step is optional.

The majority of the entries in Personal Budget will be expenses. It would be useful to set up "Expense" as default value for the Lookup Income/Expense.

You can use the Default Value column in the Formula sub-panel of Design Mode.

1. Access the Design Mode of the Personal Budget application.

2. Click on Formula sub-panel button on the upper-right corner of the Master sub-panel.

   

3. Click on the textbox under the Default Value column corresponding to Income/Expense.

   

4. Insert the value 1 in the textbox. The ID of the entry corresponding to the "Expense" option in Income Expense Type Storage is 1.

   

   

5. Save all the changes in Design Mode (Save ).

By default, the Personal Budget application will fill the Income/Expense Lookup with the "Expense" option automatically since the option has the ID 1 in Income Expense Type Storage.

Congratulations, you have learnt about Lookups and how they work.

Success

The next step is the section Details: showing secondary data related to the row. You will learn what Details are, how do you create them, and how you can apply data editing restrictions on them.