Code On Time: Database Lookups

Monday, June 4, 2012Print Subscribe

The Advanced Search Bar automatically determines multiple search options for each search field, based on the type of the field.

It is possible to limit the amount of options displayed to the user.

Let’s require users to search by the Birth Date field on the Employees grid view, and only permit between, equals, and includes search options.

Start the Project Designer. In the Project Explorer, switch to Controllers tab, and double-click on Employees / Views / grid1 / BirthDate data field node.

Make the following changes:

Property	New Value
Search Mode	Required
Search Options	$between,=,$in

Press OK to save the data field. On the toolbar, click Browse to regenerate the web application.

Navigate to the Employees page, and expand the advanced search bar.

The Birth Date search field will appear first, with a limited subset of search options. The first search option will be activated when the search bar is opened for the first time.

For the Last Name field, instead of having the default first search option of equals, let’s change it to begins with, and preserve the rest of the search options.

Go back to the Project Designer. In the Project Explorer, double-click on Employees / Views / grid1 / LastName data field node.

Make the following changes:

Property	New Value
Search Mode	Suggested
Search Options	$beginswith,*

Press OK to save the data field, and click Browse on the toolbar.

Activate the search bar on the Employees page, and you will see that Last Name shows up by default. The begins with search option will show up first, but the user can change it to any other option.

Labels: Database Lookups, User Interface, Web Application Generator

Monday, June 4, 2012Print Subscribe

Data Lookup Capabilities

Code On Time web applications offer rich data lookup capabilities.

Fields with an Items Style of Lookup render as link within a textbox. The Eraser icon to the right of the link will clear the field. The New Record icon to the right of the textbox will create a new lookup item.

When the link is clicked on, a lookup window will open. Users can perform Quick Find or use Advanced Search Bar.

A lookup field can be configured as an Auto Complete text box. When a user types in a value, the list of lookup items will be filtered. If an item is selected, it is inserted in the field. If no item is selected, the field value will revert.

Both Lookup and Auto Complete styles do not prefetch data items when the form view is loaded.

A lookup field can also be configured as a Check Box List. The values can come from a static list or a many-to-many table.

Data lookup fields can be rendered as a Radio Button List.

Consult the following list of properties when configuring a lookup field.

Property	Description
Items Style	Select the type of Items Style for the lookup. The options include: Auto Complete, Check Box, Check Box List, Drop Down List, List Box, Lookup, Radio Button List, User ID Lookup, User Name Lookup.
Items Data Controller	Select the controller data source.
Items Data View	Select the data view that will be displayed for the lookup.
Data Value Field	Select the value that the field will save to the database.
Data Text Field	Select the text that the field will display to the end user.
New Data View	If the user is allowed to create new lookup items, this will specify the data view that will be used. A blank value will hide the New Record icon.
Copy	You can copy field values from the selected lookup item into the current record.
Search On Start	Enabling this property will open the lookup in search mode.
Activate If Blank	Enabling this property will open the lookup if the field is blank when a user opens the form.
Lookup Window Description	Specify the text that will be displayed above the lookup.
Page Size	Specify how many lookup items will be displayed at one time in the lookup modal window.
Search By First Letter	Allows you to search for a lookup item by the first letter.

Labels: Database Lookups, Web App Generator

Monday, June 4, 2012Print Subscribe

Items Style User Id Lookup

ASP.NET Membership offers a prefabricated registry of users and roles. There are situations in which the membership User ID needs to be referenced in the database. You can reference a User ID with the User Id Lookup items style.

Create an Owner field in the Orders table.

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Orders node, and select Design option.

Add a column with the following properties:

Column Name	Data Type	Allow Nulls
OwnerId	nvarchar(50)	True

Save the table design modifications.

Note that the data type of the field may be configured as uniqueidentifier if you are working with SQL Server. If your database engine is MySQL, then int type can be used instead.

Start Code On Time web application generator. Click on the project name, and refresh the Orders data controller.

Press Generate to regenerate the web application. When it opens in a browser window, navigate to Orders page and edit a record. The OwnerId field is just a simple text box. It needs to be converted into a User Id Lookup.

Start the Project Designer. Switch to the Controllers tab, and double-click on Orders/ Fields / OwnerId node.

Give this field the following properties:

Property	Value
Label	Owner
Items Style	User ID Lookup

Press OK to save the field. On the toolbar, press Browse, and wait for the application to load.

Navigate to the Orders page, and edit a record. The Owner field will now be a lookup.

Click on the lookup link, and it will show a list of users.

When you select a user, the User Id will be inserted into the field.

Labels: ASP.NET Membership, Business Rules/Logic, Database Lookups, Web Application Generator