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.

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

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’
• 
• The query design screen is shown, rename the query to qryBillOfMaterialsbyProduct
• 
• Click on ‘Add Filter’, select the Prodcut1.productId field from the combobox.
• 
• Leave next selection as ‘=’, Select ‘@ parameter’ in the next selection
• 
• Select ‘Add new’ in the last combobox.
• 
• In the Parameters section, we have ProductId of type integer.
• 
• 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
• 

Step 4 – Add a autocompletebox and bind to the parameter

• In the design of the new screen, click on Add Data item…
• 
• Select ‘local property’ radio button, select ‘AdventureWorks.vLookupProductAssemblies (Table)’ from the dropdown list. In the Name filed, enter ‘propProductId. then click OK.
• 
• The data item is added in the view on the left
• 
• Add a new Data Item once more.
• 
• 
• 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.
• 
• In the properties window of our property field, in the choices option, select vLookupProductAssemblies. Change the display name to ‘Product assembly’
• 
• 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
• 
• 
• 
• 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).

.