Data Aquarium Framework

Labels
AJAX(112) App Studio(9) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(178) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(184) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(3) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Data Aquarium Framework
Tuesday, October 11, 2011PrintSubscribe
DotNetNuke Factory, Oracle Data Provider, Wizards

Code On Time has released a new update 5.0.0.72 with major features and enhancements.

Please see update notes below:

  • New code generation project DotNetNuke Factory creates application pages as controls with data views incorporated in a DotNetNuke module. Registered module can be configured by end users. You can integrate a line-of-business database application with a popular open source web content management system in minutes.
     
  • Added support for native Oracle.DataAccess client with full support for sequences on primary key fields. Primary key fields of integer type are expected to have sequences named after the name of the tables. For example , “CUSTOMER_SEQ” sequence is expected to match “CUSTOMER” table.
     
  • Projects generated from Oracle databases now recognize database views.
      Introduced "client-only" virtual Status field to support wizards. Support for new Status and None actions to allow easy implementation of Wizards. The tutorial will be available shortly.
     
  • Ajax Control Toolkit 50731 is now shipping with the code generation library.
        
  • RichEditor has been changed to support new HtmlEditorBehavior from Ajax Control Toolkit. Read more about the new rich text editor at http://stephenwalther.com/blog/archive/2011/08/01/ajax-control-toolkit-july-2011-release-and-the-new-html.aspx.
      
  • Action buttons displayed in forms are now automatically refreshed when data is changed.
    WhenClientScript property of actions can now reference values of fields by enclosing the field name in square brackets. For example [Status]. The same capability already exists in visibility expressions for categories and fields.
     
  • Numerous improvements to the message bar (yellow message at the top of the screen). It now takes up to 15% of the window height and starts scrolling when needed.
     
  • Sidebar boxes are fixed at the top of the screen if there in no horizontal scrolling.
     
  • Automatic resizing in multi-column forms is not performed anymore.
     
  • Adding a data field to a category will remove any bindings of the field that may exist in other categories of a view.
      
  • Designer automatically transfers properties of existing data fields when a field is defined in a new category or view.
     
  • Field validators are now using IDs to identify expressions in Project Designer.
     
  • Data Field visibility rules are now using IDs to identify visibility expressions in Project Designer.
      
  • Category visibility rules are now using IDs instead of header text as primary key. Category description is not mandatory in  the new release. Any characters can be used in the category header text.
     
  • Bread crumbs of many project items in Designer now include extended information.
     
  • Filters and sort order are restored in the grid/data sheet view if you select a record and then return back.
     
  • Filtering is automatically disabled on timestamp fields to prevent errors when users do Quick Find or try to apply a filter.
     
  • Presence of "onDemand" attribute was causing incorrect production of automatic reports. The issue has been resolved.
      
  • Fields with explicitly defined value/text items will correctly render on the search bar. Text of items is displayed instead of actual values.
     
  • If a field has explicitly defined value/text item pairs then the multiple-value filter will correctly display the values. The field will also correctly align as text (to the left) and will print in filter details.
      
  • Reports will print correctly item text when item value/text pairs are explicitly defined on a field level (0-Open, 1-Closed, 2-Pending). This applies only to .NET 4.0 projects.
     
  • Regex validators are invoked when a field value has changed.
     
  • Data access objects based on tables that have a primary key field name matching the table name will not result in compilation error.
     
  • New implementation of DataController.UserIsInRole takes an array of roles. Each value can be specified as a comma-separated list of roles or simple role name.
      
  • Fixed designer error "Can't execute code from a freed script".
     
  • Trimming of long words used in summary boxes has been improved to correctly process HTML tags, which was previously causing incorrect display of history.
     
  • Designer displays Up/Down options on field Items page.
     
  • Values of fields with "Format On Client" set to "False" will print correctly in reports.
     
  • Long standing problem with UTF-8 encoding not being applied generated filed has been resolved.
      
  • "_Mirror" fields supporting server side formatting have been improved to allow editing of field values.
     
  • Hidden primary keys are rendered as "hidden" inputs in insert mode.
     
  • Multiple value selection is passed correctly when action is executed from a modal form.
    Hidden, static, and read-only fields are now passed as "read-only" to updates performed in response to Batch Edit commands. This ensures that there will no exception "Object not found" with "audit" fields processing (ModifiedOn, ModifiedBy).
     
  • Generated *.designer.vb files are now using WithEvents in declarations of control fields.
     
  • .NET 3.5 web apps will display rounded corners in modal views.
     
  • Implemented Web.DataView.goBack() method in the client library to suppress cancelled requests to retrieve the child data sets when data controller URL parameters are used on complex master/detail pages.
     
  • Aliased fields are correctly displayed on automatic search bar if the field's Search Mode property is set to Required or Suggested.
       
  • Exception "Key not present in dictionary" now displays the name of the field that is no longer available in the data controller but referenced in its definition.
        
  • Code generator project file now supports “zip” mode when executing “copy” instruction.
     
  • Universal ProviderFactories variable lists all DB and Membership providers registered in machine.config of standard and 64-bit Microsoft.NET. ProviderFactories variable is now used to configure references to required external assemblies in web.config.
     
  • Button “Next” on the data controller summary now reads “Generate”.
Thursday, August 25, 2011PrintSubscribe
File Upload / Download (External Storage)

Our previous article has covered the subject of File Upload / Download. We have discussed storing and retrieving of files in binary database table columns. Web apps created with Code On Time can greatly benefit from automatic capturing of content type, file name, and length in the specially named table columns that compliment a binary column. This approach requires no programming and works great if the database is expected to store the binary content.

Recent innovations in data storage methods make it highly efficient and cost-effective to externalize binary content from the database. The database becomes much more compact and is easier to backup and manage.

Online storage systems such as Amazon S3 and Microsoft Azure Storage provide a robust and cheap storage for large data files. A web application can also benefit if the binary content does not leave the premises of the data center and is stored in Network Attached Storage.

Creating a Sample Web App

Let’s create a sample web application to illustrate the mechanism of externalizing binary data that remains associated with table rows in a database.

Start Code On Time, select “Create new web application task”, and choose Web Site Factory type for our web application.

Enter UploadDownload in Project Name and make sure to select the programming language that you are comfortable to use. Click Create button.

Leave the namespace and framework unchanged and click Next.

In this tutorial we will be using Microsoft SQL Server 2008. Other types of supported database will involve exactly the same implementation techniques.

Select SQL Server Data Provider and click the button located next to Connection String input field.

Enter the name of your local server or SQL Azure server and specify login credentials if needed.

Enter My Upload Download Demo in Database input field and click Create. Confirm that you want to create a database by press Yes button.

Select Northwind in Sample Tables drop down and click Install button. Wait for the installation script to finish. You will see a popup message indicating a successful installation.

image

The next step is optional. We suggest implementing a built-in membership system in your application. Click Add button shown in the picture. Wait for ASP.NET Membership installation script to finish.

image

Click OK button at the bottom of connection configuration screen to save the database connection string.

Click Next twice to reach Reporting section. Select “Enable dynamic and static reports in my application”.

Click Next button to get to Features page of the project wizard. Enter Upload / Download Demo (External) in Page Header box.

Continue clicking Next until you arrive to the summary page presenting a list of project data controllers. Proceed to generate a project by clicking Next. The web app will start in your default browser shortly thereafter.

Login using admin/admin123% user account. Select Categories tab to activate the list of product categories stored in the sample database.

image

Next we will change the structure of the database and create a virtual binary field that will have its content stored in the file system on your computer.

Preparing the Database

The following picture shows the structure of Categories table.

image

Table column Picture has been automatically processed by web app generator and implemented as a file upload field. The previous screen shot shows the thumbnails of the images stored in the database.

Our intent is to implement a virtual field that will coexist alongside the field Picture but will have its content stored outside of the application database. Think of it as if there in invisible column that looks exactly as Picture but is not actually present in the database table.

Let’s call this field ExternalDoc (short for External Document). The field implementation will require a few utility columns even though the binary field itself will not be present in the database .

These special utility columns must have their name start with the name of the binary column/field.  Execute the following query in SQL Management Studio or SQL Azure Database Manager against My Upload Download Demo database.

alter table dbo.Categories
add ExternalDocFileName nvarchar(150) null
go

alter table dbo.Categories
add ExternalDocLength integer null
go 

alter table dbo.Categories
add ExternalDocContentType nvarchar(150) null
go

The scrip will add three utility columns to the structure of Categories table to allow capturing and storing of the uploaded file name, its length, and content type.

This is how the table structure will look now.

image

Implementing ExternalDoc Field in the Project

Let’s incorporate the new columns and the corresponding virtual field in our project.

Bring forward the web app generator window and click on the project name.

Our database has changed but the code generator is not aware of that. Database metadata (tables, columns, indexes, etc.) are cached in the project files. Click Refresh button to refresh the metadata. The code generator will bring you straight to Data Controller summary page.

Click Start Designer button to activate Project Designer. Select Categories data controller on All Controllers tab.

Enter CategoriesBusinessRules in Handler input and click OK button.

Select Categories data controller one more time. Activate Fields tab and select New | New Field on the action bar.

Enter ExternalDoc as Name. Change the type of the field to Byte[]. Select check box  “Allow null values”.

Our field is virtual. We need to specify a SQL expression that will be evaluated by the database server when the field value is about to be retrieved. Select “The value of this field is computed at run-time by SQL expression” check box and enter NULL in SQL Formula text box.

Our field will store large binary objects (BLOB). This type of field requires special handling by the application. Select “Value is retrieved on demand” check box. On Demand Properties section will become visible in the designer.

Enter the name of the primary key column of the Categories table in the Source Fields input. The name of the column is CategoryID.

Your web application will also need a special construct that will help handling details of basic uploading/downloading just before/after you save the file to the external storage or have it retrieved.

Your application uses the term On Demand Handler to describe this construct in the source code. Any unique name will do. You can use the same on-demand handler name if a binary column is displayed in the views of other data controllers

We will call our handler CategoriesExtenalDoc, which combines the table name and the name of the virtual column. This is the default convention used for all on-demand handlers of your web application.

Set On Demand Style to Thumbnail to have a preview thumbnail displayed when the field is rendered.

Finally enter the field label as External Doc.

The project designer property page will look as follows. Click OK button to save the field.

image

Changing Visual Properties of ExternalDoc and Utility Fields

Select Data Fields tab to activate bindings of data controller fields to presentation views. Data fields allow controlling many presentation aspects of your application.

Let’s bind the virtual field External Doc to editForm1 and grid1 views.

The first binding will insert the field ExternalDoc in the view grid1. Select New | New Data Field on action bar. Also make sure to enter 15 in the columns property of the data field. This will limit the space taken by the field on automatically produced reports if you choose an option to print the list of categories when running the application.

image

Next bind our virtual field to the view editForm1. Make sure to select a category for the field. Category selection is mandatory if the view is a form.

image

Save the second binding and enter External in the Quick Find area on the action bar.

Delete all bindings of utility fields to the view createForm1. Uploading of binary content is only supported for existing records.  Select each binding and click Delete button or select Delete option from the row context menu in the list of bindings.

Change Text Mode property of all utility fields from Text to Static. This will make the field values read-only from the user standpoint but will allow application to make changes to the values when the files are uploaded. Optionally assign n0 as a data format string for both bindings of ExternalDocLength to improve readability of the field value for very long files.

The list of ExternalDocXXX  bindings will looks as follows when you finish making changes.

image

Click Exit button to exit the project designer and proceed to generate the web application.

Navigate to Categories tab. The screen will look similar to the screen shot.

image

Select any category and observe that we now have the virtual field and utility fields presented at the bottom of the form view.

image

Implementing Upload / Download Business Rules

If you try to upload a file in External Doc field then you will see that utility fields are acquiring the properties of the file but the actual data is no saved and an error message is displayed stating “Error: failed to upload categories external doc. Invalid column name \u0027ExternalDoc\u0027.”

You can edit the record again and this time push Clear button instead of performing an upload.

image

The same error message will be displayed at the top of the page but the utility fields will get cleared.

You have probably guessed already that we need to get involved in the process and save the file when the browser has finished uploading the file.

Bring up the code generator and click develop link under Actions column next to the name of your project. Visual Studio or Visual Web Developer will start.

Press Ctrl+Alt+L to activate Solution Explorer and find CategoriesBusinessRules.cs(vb) in the project tree. Double-click the file to open the text editor.

image

The business rules class will require three methods to support an externally stored binary content.

  • The first method will need to intercept the event of uploading, prevent the application framework from trying to save the file, and persist the file contents to the file system.
     
    We accomplish that by implementing a method with ControllerAction attribute. The parameters of the attribute indicate the data controller, the name of the action, and the name of the field.
     
    Method arguments include CategoryID and ExternalDoc stream to let us interact with the uploaded content. You can also include optional arguments with names ExternalDocFileName, ExternalDocLength, and ExternalDocContentType if you need this information to correctly externalize the file.
     
    Our implementation is simply storing the file in the predefined folder in My Documents. We use the category ID and generic “.bin” extension to persist the file
  • The second method will be invoked when the binary content needs to be streamed to the client browser.  Make sure to prevent the default processing logic from being executed by calling PreventDefault method.
     
    Again we are using the category ID to find the location of the previously saved file and this time we write the contents of the file to the stream passed as externalDoc argument.  The application framework will automatically take care of supplying the file name and content type to the browser.
     
  • The purpose of the third method is t0 let the application know if there is a value in a given binary column of a given row.
     
    If an external file exists then the method will update the value of the binary field with the primary key of the record. The client library will use this ID to communicate with the server components of your application and to display a thumbnail if needed.
     
    If the binary field is empty then the method will return the same category ID converted to a string and preceded by the word null in lower case and the symbol “|”. For example, if the category ID is 7491 then the value stored in ExternalDoc will be null|7491
     
    We are not actually probing if the file exists in the designated folder. Instead we make an assumption that if ExternalDocFileName field value is not blank then the file has been previously uploaded without errors. Your own implementation may do whatever it takes including possible communicating with external systems to verify that the file does exist.

Note that method names are absolutely arbitrary and play no role in application execution. ControllerAction and RowBuilder attributes turn these methods in the special sauce of your business rules class.

Before you proceed any further make sure to fire up Windows Explorer. Go to Documents folder and create My External Doc Files folder referenced in the implementation below. The folder must exist by the time you try uploading the content.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [ControllerAction("Categories", "UploadFile", "ExternalDoc")]
        protected void SaveExternalDocToFileSystem(int categoryID, Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream output = File.Create(fileName);
            externalDoc.CopyTo(output);
            output.Close();
        }

        [ControllerAction("Categories", "DownloadFile", "ExternalDoc")]
        protected void LoadExternalDocFromFileSystem(int categoryID, Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream input = File.OpenRead(fileName);
            input.CopyTo(externalDoc);
            input.Close();
        }

        [RowBuilder("Categories", RowKind.Existing)]
        public void VerifyPresenceOfExternalFile()
        {
            int categoryID = Convert.ToInt32(SelectFieldValue("CategoryID"));
            string externalDocFileName = Convert.ToString(
                SelectFieldValue("ExternalDocFileName"));
            // update ExternalDoc field to reflect existence of a file
            if (!String.IsNullOrEmpty(externalDocFileName))
                UpdateFieldValue("ExternalDoc", categoryID);
            else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <ControllerAction("Categories", "UploadFile", "ExternalDoc")> _
        Protected Sub SaveExternalDocToFileSystem(ByVal categoryID As Int32, _
                                                  ByRef externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim output As Stream = File.Create(fileName)
            externalDoc.CopyTo(output)
            output.Close()
        End Sub

        <ControllerAction("Categories", "DownloadFile", "ExternalDoc")> _
        Protected Sub LoadExternalDocFromFileSystem(ByVal categoryID As Int32, _
                                                  ByRef externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim input As Stream = File.OpenRead(fileName)
            input.CopyTo(externalDoc)
            input.Close()
        End Sub

        <RowBuilder("Categories", RowKind.Existing)> _
        Protected Sub VerifyPresenceOfExternalFile()
            Dim categoryId As Integer = Convert.ToInt32(SelectFieldValue("CategoryID"))
            Dim externalDocFileName As String = Convert.ToString( _
                SelectFieldValue("ExternalDocFileName"))
            ' update ExternalDoc field to reflect existence of a file
            If Not String.IsNullOrEmpty(externalDocFileName) Then
                UpdateFieldValue("ExternalDoc", categoryId)
            Else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryId))
            End If

        End Sub
    End Class
End Namespace

Upload a few files to the ExternalDoc column. You should be able to see the file thumbnail if the upload was successful or the file type displayed in a white box if the file is not an image.

image

Here is how the content of the folder that we use to store the uploaded content may look like.

image

Your externalized content will also print on reports. For example, select Categories tab and choose Report|PDF Document option on the action bar.

image

A prompt to download a PDF document will be displayed. Open the file and you should be able to see the uploaded content on it.

image

Conclusion

Code On Time offers an excellent mechanism of implementing storage of a binary content outside of the database.

We expect that future updates to the framework will allow file uploading not only when users are editing existing records but also when a new record is being created.

Upcoming releases will also support code-free uploading of binary content through built-in business rules to external file system and Microsoft Azure Storage.

Project Azure Factory available with Code On Time will support popular annotation feature as well.

Thursday, May 19, 2011PrintSubscribe
Cascading Lookups

ASP.NET web applications created with Code On Time application generator offer impressive lookup capabilities. Numerous lookup styles allow configuring sophisticated data lookup scenarios including cascading lookups. Cascading lookups is a data lookup scenario involving more than one lookup field.

Example of Cascading Lookups

Consider the fragment of Adventure Works LT database presented below.

Table SalesOrderHeader has three lookup fields. Field CustomerID references table Customer, fields ShipToAddressID and BillToAddressID are referencing the same table Address. Shipping and billing addresses may be represented by the same or different rows in the table Address. The many-to-many table CustomerAddress links a Customer and an Address.

If a new Sales Order Header is created and a customer is identified and associated with the header then it will be logical to assist end users of your application in selecting a customer address by limiting the list of all addresses to those that are matching the selected customer. Selection of one lookup value (CustomerID) in a row must cascade as a filter to other dependent lookups (ShipToAddressID and BillToAddressID).

Configuring Cascading Lookups

If you generate a new Web Site Factory application from the database and start creating a new Sales Order Header then you will see a page similar to the one presented below. The screen shot shows that a customer has been selected already and shipping and billing addresses are blank.

image

Click on (select) link in Ship To Address Line1 or Bill To Address Line1 and you will be presented with lookup window that allows selecting an address. The entire collection of addresses stored in the database is available for selection. The screen shot shows that the application user has activated the search bar to find the right address and is trying to find an address using predictive input.

image

Let’s make the task of selecting an address easier and limit the list of addresses to those that are associated with the user account.

First we will examine lookup configuration of ShipToAddressID and BillToAddressID fields.

Run the web application generator, select the project name and click Design to activate the project Designer.

Select “All Controllers” tab and locate SalesOrderHeader data controller.

Click on the controller name and select Fields tab.

Select ShipToAddressID field and click Edit button.

Scroll to Lookup section of the field properties. The configuration of lookup properties is presented below.

Property Items Data Controller is set to Address, which instructs the generated web application to show the first grid view of Address data controller when a user click on (select) link of the lookup field.

image

If you inspect configuration of BillToAddressID field then you will find that it is exactly the same. This type of configuration is automatically performed by Code On Time web application generator when a baseline application is constructed straight from your database.

Table Address does not offer any information that will help us to identify the addresses that belong to a specific customer.

Table CustomerAddress has a pointer to a Customer and provides a perfect replacement for Address data controller.

By default only the first mandatory field from master table Address is included by the application generator in the data controller CustomerAddress. We suggest defining the following de-normalization map for your project to expand available fields borrowed from master tables in tables CustomerAddress and SalesOrderHeader.

SalesLT.SalesOrderHeader => SalesLT.Customer
LastName
FirstName
CompanyName

SalesLT.CustomerAddress => SalesLT.Customer
CompanyName
LastName
FirstName

SalesLT.CustomerAddress => SalesLT.Address
AddressLine1
AddressLine2
City
StateProvince
CountryRegion
PostalCode

The application in the screen shots was generated with this de-normalization map.

Let’s change the configuration of field ShipToAddressID as follows:

  1. Set Data Controller property to “CustomerAddress”.
  2. Set Data Value Field property to “AddressID”.
  3. Set Data Text Field property to “AddressAddressLine1”.
  4. Set Content Fields under Dynamic Properties section to “CustomerID=CustomerID”.

image

Step (4) will instruct the data controller CustomerAddress to filter data by field CustomerID (field on the left hand side of the equal sign) with the value stored in field CustomerID (field on right hand side of the equal sign) in the current row of Sales Order Header data controller.

This change does not effect the command text of the data controller SalesOrderHeader in any practical way. The lookup configuration will be taken into account only when a user selects a value for ShipToAddressID or BillToAddressID field.

Save the changes and modify the field BillToAddressID by following exactly the same steps.

Generate your project, start creating a new Sales Order Header record, select “Family’s Favorite Bike Shop” customer in Customer Company Name field. Try select a shipping or billing address. Only two records will be available for selection. Both records match the selected customer.

image

Try changing a customer and observe that a different selection of addresses is presented each time.

Lookup Styles

You can change the style of lookups using the Items Style property. If you change the style of CustomerID, ShipToAddressID, and BillToAddressID then the cascading lookup behavior will still take place.

Lookup field CustomerID is presented  in Lookup and  two address reference fields are presented in Auto-Complete styles below.

image

A combination of Auto-CompleteList Box and Radio Button List lookup styles is shown next.

image

Clearing Dependent Lookup Fields

Most scenarios of cascading lookups will require  a particular combination of master and detail selections in the lookup fields. If a master values has changed then a cascading clearing of dependent lookup fields may be called for. In fact inconsistent selection of master and detail lookup fields may cause data integrity issues.

An extra step is involved in clearing dependent cascading lookup field values.

Select CustomerID master field in Designer and set its “Copy” property as follows:

ShipToAddressID=null
ShipToAddressAddressLine1=null
BillToAddressID=null
BillToAddressAddressLine1=null

The screen shot shows the “Copy” property of CustomerID when opened in Designer.

image

The primary purpose of “Copy” property is to allow specifying multi-field copy instructions executed upon lookup selection. Read about multi-field lookup at /blog/2010/02/multi-field-lookup.html.

If you enter “null” on the right-hand side of the equal sign then the field value will be cleared when the value of the lookup field has changed. In this particular case any changes to CustomerID field must cause clearing of fields ShipToAddressID and BillToAddressID. Both fields are aliased with ShipToAddressAddressLine1 and BillToAddressAddressLine1 fields accordingly. If you configure only the foreign key fields to be reset upon changes of the master field CustomerID then fields will get cleared but their visual representations will remain intact. Therefore we require clearing of alias fields as well.

Property “Copy” provides surgical level of control over resetting of dependent fields in a cascading lookup field groups. If you have more than one level of dependency then make sure to configure clearing of fields on all levels.