Code On Time: Database Lookups

Blog

Database Lookups

Monday, June 11, 2012Print Subscribe

Property Context Fields can pass values from the current record to the lookup data view. The value is passed as a map in the format LookupFieldName=LocalFieldName.

If LookupFieldName is an actual field in the lookup data view, then an automatic “equals” filter is applied to the view. The value of LocalFieldName data field is copied from the current record to the filter expression.

If the lookup view does not have a data field that matches LookupFieldName, then automatic filtering will not occur. Developers can specify a view Filter Expression or create a business rule to use the value to implement filtering.

Let’s create a Filter Expression for a lookup view that takes advantage of values passed in the Context Fields property.

Navigate to the Orders page and select an order. Create a new Order Details record, and activate the ProductID lookup.

A complete list of products (77 in total) will be displayed by default.

Let’s limit the set of products displayed to only those that are not already associated with the relevant order. If there is an OrderDetails record in the current order with a specific ProductID, then the product should not be displayed in the lookup.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on OrderDetails / Fields / ProductID field node.

Change the Context Field property:

Property	New Value
Context Fields	ExistingOrderID=OrderID

Press OK to save the field. Double-click on Products / Views / grid1 view node.

Notice that the ExistingOrderID field does not exist in the Products data controller. The application framework will not perform automatic filtering.

Change the Filter Expression:

Property	New Value
Filter Expression	@ContextFields_ExistingOrderID is null or ProductID not in ( select "OrderDetails"."ProductID" from "Order Details" OrderDetails where OrderDetails.OrderID = @ContextFields_ExistingOrderID )

The ExistingOrderID field that was mapped in in the Context Fields property of OrderDetails.ProductID is referenced as @ContextFields_ExistingOrderID in the filter expression above. The application framework will automatically bind the parameter value.

Press OK to save the view. On the toolbar, press Browse.

Navigate to the Orders page, and select an order. Create a new order detail, and activate the ProductID lookup. The set of products available for selection will be limited to those that don’t exist for the order. As more OrderDetails records are added, the total number of products available for selection will gradually decrease.

Labels: Database Lookups, Web Application Generator

Monday, June 11, 2012Print Subscribe

Static Filter Values in Context Fields

Property Context Fields can list static values that can be used as lookup field filters.

For example, the CategoryID lookup field on the Products edit form displays eight categories available for selection.

Let’s specify static filter values for the CategoryID lookup data view.

Start the Project Designer. In the Project Explorer, switch to Controllers tab. Double-click on Products / Fields / CategoryID field node.

Change the Context Fields property:

Property	New Value
Context Fields	CategoryID=1, CategoryID=5

Press OK to save the field.

CategoryID refers to the primary key of Categories controller. Any other data field in grid1 can be referenced as well.

On the toolbar, press Browse.

Navigate to the Products page, and edit a record. Activate the CategoryID lookup – only two items will be displayed.

If the lookup filter fields are text based, then the value must be surrounded by single quotes. For example, let’s use the field CategoryName as a context filter.

Change the Context Fields property of Products / Fields / CategoryID as shown next:

Property	New Value
Context Fields	CategoryName='Seafood', CategoryName='Dairy Products'

Press OK to save the field. On the toolbar, select Browse.

Navigate to Products page, and edit a record. Activate the CategoryID lookup – only two categories will be available for selection. Notice that the CategoryName field is hidden, since it is explicitly defined in the grid view.

Labels: Database Lookups, Web Application Generator

Thursday, June 7, 2012Print Subscribe

Lookup of User Identity

Both name and id of a user selected in a lookup window can be captured in a database table when necessary. Let’s set up a lookup field to capture both properties of the user identity.

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

Add two columns with the following settings:

Column Name	Data Type	Allow Nulls
OwnerID	uniqueidentifier	True
OwnerName	nvarchar(50)	True

Save the table modification. Refresh the Orders controller.

Regenerate the project. Navigate to the Orders page, and edit a record. The OwnerID and OwnerName fields will be visible at the bottom of the form, but are currently only simple text boxes.

Let’s configure the OwnerID field as a User Id Lookup and have it copy the user name into OwnerName field. The OwnerName will become an alias of OwnerID data field. Only Administrators will be allowed to modify the owner of the record.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on Orders / Fields / OwnerID field node.

Make the following changes:

Property	New Value
Items Style	User Id Lookup
Copy	OwnerName=UserName
“Write” Roles	Administrators

Press OK to save the field. In the Project Explorer, double-click on Orders / Views / editForm1 / c1 – Orders / OwnerID data field node.

Assign an alias for the data field:

Property	New Value
Alias	OwnerName

Press OK to save the data field. In the Project Explorer, right-click on Orders / Views / editForm1 / c1 – Orders / OwnerName data field node, and select Delete option.

Confirm the operation. On the toolbar, select Browse option to regenerate the web application.

Navigate to the Orders page, and edit a record. If your user account belongs to the Administrators role, then the field is rendered as a lookup.

Activate the lookup, and a lookup window will open and display a list of users.

Select a user from the lookup, and the name of the user will be copied into the field Owner Name.

Save the field. If you look at the record in the database, both UserId and UserName fields have been populated.

Labels: ASP.NET Membership, Database Lookups, Designer, Security, Tips and Tricks, Web Application Generator