“Filter Field” Property for Data Views

Labels
AJAX(112) App Studio(7) 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(183) 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
Saturday, January 19, 2013PrintSubscribe
“Filter Field” Property for Data Views

The Filter Field property specifies the foreign key field on the child data view that will be filtered in a master-detail relationship.

Drag & drop techniques can be used in order to automatically configure the relationship. For example, the picture below shows how to configure CustomerID as the filter field between Customers and Orders data views.

Dropping CustomerID field onto 'view1' to create a master-detail relationship.     Master-detail relationship created between view1 and view2.

However, when the master data view has a compound primary key, the relationship must be configured manually.

Let’s add a Notes table to the database that will have a compound primary key that refers to OrderID and ProductID.

Notes table diagram.

The table will then be added to the project. A Notes data view will be added to the default Orders page in order to compose a three-level master-detail relationship.

Creating the Notes Table

Start SQL Server Management Studio and connect to your database. In the Object Explorer, right-click on Databases / Northwind node, and press New Query.

Creating a new query for Northwind database.

Paste in the following script:

create table dbo.Notes(
    IDofOrder int not null,
    IDofProduct int not null,
    Notes ntext not null,
    Created datetime not null,
     constraint PK_Notes primary key clustered
    (
        IDofOrder asc,
        IDofProduct asc
    )
)

alter table dbo.Notes add constraint FK_Notes_OrderDetails_OrderID foreign key (IDofOrder)
references dbo.Orders (OrderID)

alter table dbo.Notes add constraint FK_Notes_OrderDetails_ProductID foreign key (IDofProduct)
references dbo.Products (ProductID)

Click on Execute on the toolbar to run the query.

Adding the Table to the Project

Start the web application generator. Click on the project name, and press Refresh. Check the box next to dbo.Notes table to add it to the project, and press Refresh.

Adding the Notes table to the project.

Adding Notes Data View

Right-click on Notes controller node, and press Copy.

Copying the Notes controller.

Switch back to the Pages tab. Right-click on Customers / Orders page node, and press Paste. The data controller will be instantiated as a data view in a new container.

Pasting the Notes controller onto Orders page.     Notes controller instantiated as a view on the page.

Configuring Master-Detail Relationship

Double-click on Customers /Orders / c101/ view4(Notes) node.

'View4' view on the Orders controller.

Make the following changes:

Property New Value
View grid1
Text Notes
Filter Source view3
Filter Field #1 IDofOrder
Filter Field #2 IDofProduct
Auto Hide Container

Press OK to save.

Viewing the Results

On the Project Designer toolbar, press Browse. Navigate to the Orders page. Select an order from the list, and then select an order detail. The Notes data view will appear underneath, filtered by OrderID and ProductID.

Notes data view is being filtered by OrderID and ProductID of Order Details view.Notes data view is being filtered by OrderID and ProductID of Order Details view.