Wednesday, April 04, 2012

Using an autocompletebox to filter screen data

Problem

When you create  a LS query with filter and parameters, there is no automated way to add a lookup combobox to the parameter values. when the user has a list of values, preferably with names to choose from, the application is a lot easier to use.

We can use an autocompletebox based on an entity with an Id and a Name to accomplish this.

Solution

Example

In the AdventureWorks there is a one to many relationship between the primary key field of the Product table and the ProductAssemblyID field in the BillOfMaterial table. We can use an autocompletebox with product id and name from the Product table as a filter on ProductAssemblyId in the BillOfMaterial screen.

Product
 
ProductId PK, int, not null
Name nvarchar(50), not null

-

BillOfMaterials
 
BillOfMaterialsID PK, int, not null
ProductAssemblyID FK, int, null
StartDate…  

Step 1 - create a project and select the tables

Create a new LS project based on the AdventureWorks database (which can be downloaded from codeplex for version 2008 r2. Add a datasource that connects to this database and use the tables BillOfMaterials and Product.

The BillofMaterials entity has 2 relations to the Products entity.

image

To find out which relation we need, click on the line that connects the 2 entities, this will show the FK (foreing key) names. When I click on the link to the Properties1 entity , the properties window shows the name of the FK that is used

image

Step 2 – create a query on BillOfMaterials that filters by ProductAssemblyID.

  • In Visual Studio in the LS project rightclick on the BillOfMaterials entity
  • In the popup menu select ‘add query’
  • image
  • The query design screen is shown, rename the query to qryBillOfMaterialsbyProduct
  • image
  • Click on ‘Add Filter’, select the Prodcut1.productId field from the combobox.
  • image
  • Leave next selection as ‘=’, Select ‘@ parameter’ in the next selection
  • image
  • Select ‘Add new’ in the last combobox.
  • image
  • In the Parameters section, we have ProductId of type integer.
  • image
  • Save the changes

Step 3 – Add the lookup table or view to your Datasource

For this I will use the view that has been created in my previous articleUsing a view in LS and a way to set your primary key

There are 2 reasons: 1st reason, the Product entity has too many columns to use in an autocompletebox, 2nd reason : the view allows me to eliminate products that are not even used in the Bill of materials.

Step 3 – Add a new screen base on the query

  • In the solution explorere window right click on the ‘Screens’ folder and select ‘Add screen’. use editable Grid Screen and for Screen Data select the qryBillOfMaterialsbyProduct. clikc OK
  • image

Step 4 – Add a autocompletebox and bind to the parameter

  • In the design of the new screen, click on Add Data item…
  • image
  • Select ‘local property’ radio button, select ‘AdventureWorks.vLookupProductAssemblies (Table)’ from the dropdown list. In the Name filed, enter ‘propProductId. then click OK.
  • image
  • The data item is added in the view on the left
  • image
  • Add a new Data Item once more.
  • image
  • image
  • Drag propProductID to the designer surface and drop it above the Data grid Section, under the ‘Bill Of Material Product ID’. Delete the ‘Bill Of Material Product ID’ textbox. It will be replaced by our property.
  • image
  • In the properties window of our property field, in the choices option, select vLookupProductAssemblies. Change the display name to ‘Product assembly’
  • image
  • change the parameter binding of the Query parameter to the  Id field of propProductId.ProductId. Select the ProductId query parameters and make the change in Parameter Binding of the properties window. The values will be suggested, but do not always show up the first time you click in the test box
  • image
  • image
  • image
  • Save your work

Test the result

run the app, open the screen and select a product from the Product Asembly list. the grid shows a list of for BOM records that have this assembly (column Product1).

image.

3 comments:

Unknown said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

Unknown said...

lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses

Unknown said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose