Table Joins

Lookup 1- From one table to another, with filters

The next step up is using the lookup column type to find information to bring into a table.
A simple example would be in a house rental business. You have a list of properties, and a list of tenants including prospective tenants. When people move into a house, you want to have a link between the house and the tenants.
When you create the table tabProperty, add a column colTenantSurname as a type Lookup. Select the table tabTenants. This will establish a link between the tabProperty and tabTenants tables so that you are able to select a tenant for a property. Adding additional lookup columns, means that those columns will be automatically filled.

tabTenants
1
Search
colSurname
colFirst Name
colPhone number
colTownDesired
colType
1
Smith
John
555 666 7777
Marietta
Prospect
2
Jones
Peter
555 777 6666
Atlanta
Prospect
3
Williams
Jenny
555 776 6677
Johannesburg
Prospect
4
Williams
Anthony
999 333 7676
Pretoria
Tenant
There are no rows in this table
.

Tenants from Town:
Atlanta
+4
tabProperty
1
Search
colStreetNo
colTown
colPostCode
colTenantSurname
colFirstName
colPhoneNumber
1
440 Oriole Drive
Marietta
30067
Smith
John
555 666 7777
2
460 Cardinal Ave
Atlanta
30307
Williams
Anthony
999 333 7676
3
There are no rows in this table
Once you have decided which of the tenants to rent the house to, you simply select a tenant from the dropdown list in the colTenantSurname. CODA will then automatically fill the colFirstName and colPhoneNumber fields with the information in the respective columns in the tabTenants table.
Enhancement 1 - Filter on tabTenant
Once your lists of tenants and properties gets large, it becomes difficult to find the name of the correct tenant. To make the Lookup list smaller, we can use the colType column to show only prospective tenants.
image.png
Lookup options provides one with several ways to influence the Lookup.
The first shows that the Tenants will be shown in the order in which they were entered in the tabTenants table.
Click on the Item Settings to add a filter to the lookup. Select a customer filter, and in the next edit box, select the colType column, and then select “Prospects”. Now when the dropdown in column colTenantSurname is selected. only prospective tenants are shown, people that are already tenants will be excluded.


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.