Extend Microsoft Access Applications to the Cloud: Creating a Blank Web App and Using Templates
- 2/9/2015
- Creating a blank web app
- Adding template tables
- Creating a new table
- Editing the design of a table
- Creating lookups and relationships
- Adding indexing
- Adding validation rules
- Adding calculated fields
- Summary
Creating lookups and relationships
In Chapter 3, I described the rules that are applied when importing lookups and relationships, and I described how lookups and relationships are combined into one feature in the user interface even though behind the scenes real relationships can be created in the back-end Microsoft Azure SQL Database.
Before looking at how to create a new lookup, you’ll find it useful to see how to display existing lookups. View a table in design view, select a field that is a lookup, and then click the Modify Lookups button on the Design contextual ribbon tab. Access opens the Lookup Wizard, as shown in Figure 4-10.
)
FIGURE 4-10 The Modify Lookups selection, displaying the Lookup Wizard.
You will notice that the Lookup Wizard supports both a lookup to data in a table and a list of values you typed in. In the Order Details table, if you choose to modify the lookup for the Status field, you will see an example of a value-based lookup.
Return to the new CustomerContacts table. Using the design view, type in the foreign-key name CustomerID on a new line and change the data type from Short Text to Lookup, as shown in Figure 4-11.
)
FIGURE 4-11 Adding a new lookup field.
In the Lookup Wizard, select the first option, I Want The Lookup Field To Get The Values From Another Table Or Query. Select the Customers table from the list of tables, and then select Company from the drop-down list to be the field displayed. You can also change the sorting order if required, as shown in Figure 4-12.
)
FIGURE 4-12 Completing selections in the Lookup Wizard.
In the lower part of Figure 4-12 are three choices that control how the relationship will operate:
- Prevent Delete If There Is Are Corresponding Records In The “CustomerContacts” Table This option creates a relationship with referential integrity between the two tables.
- Delete Corresponding Records From The “CustomerContacts” Table This option acts similar to the preceding one, but it adds a cascade delete to the relationship.
- Ignore Records In The “CustomerContacts” Table This action might leave some records in “CustomerContacts” without a corresponding record in “Customers.” No relationship will be created.
The web app does not have any point at which you can view all the relationships to see a diagram of how the tables fit together. (There is no equivalent to a desktop database diagram.)
TIP
The information on lookups that enforce referential integrity is stored inside the Microsoft Azure SQL Database. How to extract this information is described in Chapter 9, “Looking under the hood at Microsoft Azure SQL Database.”
After you create this new lookup between Customers and CustomerContacts choosing the first option to enforce referential integrity, if you display the Customers table caption and select the list view, at the bottom of the view you will see that the web app has automatically added a new tab to the related item control (RELIC). It now displays the related CustomerContacts tab (in addition to the existing Orders tab), as shown in Figure 4-13.
)
FIGURE 4-13 Automatic changes to related information for the Customers list view.