Dealing with Duplicates in Lookups

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
Thursday, October 6, 2016PrintSubscribe
Dealing with Duplicates in Lookups

Fields configured with Lookup, Auto Complete, and Drop Down List item styles allow the user to type in a value and display a list of results. The text displayed in the results is configured via the Data Text Field property. Users can quickly enter a search term and find the field value they are looking for using lookups.

However, it is possible that the text displayed in the results can be non-unique. For example, there may be multiple customers with the same name, but from different locations. How can the user determine which value to select?

If multiple fields are borrowed from the lookup controller, the user interface will display these borrowed values in order to help the user determine the correct option. The example below shows duplicate customers displayed in a lookup of type “DropDownList”.

Duplicate customers will show the city and country of the customer in the drop down list.

Let’s borrow the City and Country fields from the Customers table into Orders form in the sample Northwind project.

Start the app generator, click on the project name, and press “Model”. Select “Orders” model from the list.

Editing the Orders data model.

Check the box next to “City” and “Country” columns in the Customers table.

Including City and Country fields in the Orders model.

Press “Save” to save the model. Then, press “Finish”, and confirm the popup to refresh the project. Then, press “Generate” to regenerate the app.

Navigate to the Orders page, and create a new record. Notice that the two new fields are displayed as read-only in the form. Enter a duplicate customer name, and the City and Country will be displayed next to the duplicate values.

Duplicate customers will show the city and country of the customer in the lookup results.

Selecting one of the customers will populate the Company Name in the lookup input, and copy the City and Country in the following borrowed fields.

Selecting a customer in New Orders form will copy the City and Country values.

This feature is automatically enabled when duplicate values are detected. The fields defined in the Copy property of the lookup field will be used. If duplicates are a common occurrence on a particular lookup, it is recommended to use a different display value, or create a calculated field in the lookup controller that can be specified as the Data Text Field. The full lookup grid can also be viewed by activating the arrow icon next to the field.