User Interface

Labels
AJAX(112) App Studio(8) 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(177) 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(2) 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
User Interface
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.

Tuesday, May 3, 2011PrintSubscribe
SQL Anywhere Web Applications in Minutes

Code On Time announces support for SQL Anywhere 12.  Generate powerful user-friendly line-of-business web applications straight from SQL Anywhere 12 databases with Microsoft Office look and feel. The following instructions are written with an assumption that you have installed SQL Anywhere software on your computer.

image

INSTALLATION

Download the code generator at http://codeontime.com/download.aspx. The installation program will also install Microsoft.NET 4.0, IIS Express 7.5, and Microsoft Report Viewer 2010. These components are available at no cost and fully supported by Microsoft.

The code generator will use IIS Express to run generated web applications on your computer. It can be installed alongside the production version of IIS available in several versions of Microsoft Windows.

The report viewer component will render will render dynamically created reports in PDF, Word, Excel, and TIFF formats.

Follow installation instructions and click on Code OnTime Generator shortcut created on the desktop after installation.

GENERATING AN APPLICATION

Start new Web Site Factory project and enter SQL Anywhere Demo as the project name.

Click Next until your reach Data Connection page in the project wizard.

Select SQL Anywhere option in data provider and enter the following connection string.

userid=dba;password=sql;

image

Enable dynamic and static reporting in your project.

image

Click next and you will see Authentication and Membership page of the project wizard.

Follow instructions at /blog/2011/05/sql-anywhere-membership-configuration.html to configure ASP.NET Membership in your database.

Select the check box titled “Enable support for ASP.NET Membership with membership bar user manager”.

Select the check box “Membership will use a standalone database that already exist”. Select SQL Anywhere option in Provider Name.

Enter the same connection string

userid=dba;password=sql;

You can also point the connection string to another SQL Anywhere database to store ASP.NET membership data that can be shared between multiple projects. Make sure that configuration of the database has been performed prior to generating the application.

Click Next several times until your see a list of data controllers created from your database.

image

Now you are ready to generate an application and see it in action.

Click next and wait for the application to be displayed in your web browser. If the browser page comes out blank then simply give it a few moments and hit Refresh button. This may happen if your computer is busy and IIS Express is still getting ready to start the generated web application.

You should see the screen similar to the one in the picture.

image

USING GENERATED WEB APPLICATION

Make sure to keep the code generator running. Code On Time will automatically shut down the started IIS Express instances if you close the web application generator window.

Sign-in using one of the user accounts automatically created by application.  We suggest that you sign in as admin / admin123% . This user account is authorized to see the membership manager.

Click around and play with the generated pages.

Here is the screenshot of the Customers page if you select a customer record. Master record is shown in edit mode with the detail records tabbed at the bottom. Several other standard page layout are available. You can use the project Designer to create custom layouts. Click on the link to see an example of a custom Order Form.

image

Note that detail records can be edited in modal forms if you are using a commercial edition of the web application generator.

image

Here is the screen shot of the Products page display in Data Sheet view that allows spreadsheet-style editing of data. Data Sheet view does not require any external components and is available in Premium and Unlimited editions of Code On Time.

image

Select Report | Adobe PDF option from the action bar of any grid or data sheet view and take a look at PDF printout of your data with custom filters displayed in the report header.

image

Select Actions | View RSS Feed and subscribe to the feed to be notified by your RSS feed reader when new movies with the filtering criteria become available. How needs email anymore?

image

Try some cool data analysis features that allow extending your application and data to the end-user desktops enabling safe and efficient data delivery to business users.

Select Actions | Export to Spreadsheet to export data for analysis.

image

A prompt will show up to warn you that some data is being downloaded. Internet Explorer 9 and other major browser display a prompt at the bottom of the screen.

image

Press Open button to open the file. This will start Microsoft Excel. You will see a warning about potential security concern.

image

We are downloading data from our own application – there is no risk involved. Press Enable button to continue.

The data will be downloaded into a new Worksheet. You may be asked to identify yourself. Enter admin/admin123% or any other valid user account registered in the application membership database.

image

Select Insert on the ribbon and click PivotTable button.

image

Confirm the creation of a data range.

image

Now you are good to go. Here is the pivot view of the sales order items that shows distribution of product sales by employees. With a few clicks add a Pivot Chart for added impact.

image

Within minutes business users can make sense of their data and have amazing dashboards built in the tool they know best –Microsoft Excel.

The data feed embedded into the spreadsheet is live. Users can save the spreadsheet on the hard drive.

To refresh the data users can open the spreadsheet and select Refresh button on the Data tab of the ribbon.

image

Users will be prompted to re-enter the user name and password. The user’s identity will be verified against the database and the data feed will be refreshed.

Conclusion

Within minutes you can turn your SQL Anywhere 12 database into interactive treasure trove of data that can be placed on Internet or Intranet server.

Other available database connectivity options are MySQL, Microsoft SQL Server, and Oracle.

Review code customization techniques at http://codeontime.com/tutorials.aspx to learn how to use application designer to enhance the application and how to write custom business rules if the standard features of the generated application need to be enhanced.

Wednesday, April 27, 2011PrintSubscribe
Data Sheet Enhancements

The following enhancements and bug fixes were introduced in the latest update.

Miscellaneous Improvements

  • Search bar is correctly displayed if activated on start in browsers other than IE.
  • Firefox bug preventing creation of lookup items in-place has been fixed.
  • Core CSS enhancements to improve support for IE 9 and enhance quality of the data sheet rendering
  • Several new resource constants in Web.DataViewResources.js to support new functionality in the data sheet view.

Data Sheet

Summary of data sheet enhancements.

  • Menu “Actions” in the action bar will feature “Show in Data Sheet” or “Show in Standard View” option if your application has been produced by Premium or Unlimited edition. These new options are executing actions with Data Sheet and Grid command names, which causes switching between data sheet and standard grid view.   
     
    See the Data Sheet view in action at http://codeontime.com/demo.aspx.
     
    image
    image
     
  • Data Sheet view now display an arrow pointer in the gap column to indicate the row that is currently selected in the view. The focused cell may be positioned in any row. The selected row is designated by the arrow. Any linked child data views are filtered to match the primary key of the selected row.
     
    image
     
  • Several bugs related to keyboard handling were fixed. Pressing Enter key while working in the child data sheet with the master form would have caused erratic selection of buttons in the master form.
  • User can start creating a new row in the data sheet by pressing Insert key or navigating past the last row in the data sheet view.
  • User can end editing an existing or entering a new row by pressing Tab or Enter key while in the last column of the row. Tab key will cause automatic creation of a new row if the current row has been persisted successfully. Enter key will leave the selection focus on the last focused column.
  • Empty data sheet view offers a link to enable instant creation of new rows. Users can click on a link to start creating a new row.
     
    image
    image
  • User can press Ctrl+Delete keys to delete the row with the current focus in the data sheet.
  • User can press Ctrl+Space to select a a row if multiple-row selection is enabled in the data sheet.
  • Pressing Tab or Shift+Tab key in edit mode will skip the read-only cells.

Next Update

The upcoming update will introduce the long awaited enhanced file upload/download processing to allow capturing file name, size, and content type. 

Continue to Date and Time