Database 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
Database Lookups
Friday, March 23, 2012PrintSubscribe
Search by First Letter in Lookup Windows

Search by First Letter can be enabled in the lookup windows.

The following screen shot shows lookup properties of the field Products / Fields / Suppliers. Toggle the “Search by First Letter” check box to enable the feature.

Property 'Search by First Letter' in the 'Lookup' section of a field

This is how the list of suppliers is rendered when a lookup window is displayed.

Feature 'Search by First Letter' enabled in a lookup window

Users can remove the selected “first letter” filter by clicking on the filter element in the filter details area.

Filter element can be removed from the list of active filters via a single mouse-click

Friday, March 23, 2012PrintSubscribe
Page Size in Lookup Windows

The standard data lookup window displays 10 records per page by default. You can change that by entering a value in the Page Size property under the Lookup section of the lookup data field.

The following screen shot shows the custom page size configured for the data controller field Products / Fields/ SupplierID.

Custom value of the 'Page Size' property of the lookup window in Project Designer

This is the lookup window activated at runtime. The custom page size is set to “5”.

The lookup window with the custom 'Page Size' property set to '5'

Saturday, February 25, 2012PrintSubscribe
Denormalization Field Map

A normalized database allows for efficient and economical data storage and retrieval. Business applications require displaying denormalized information to the users. The structure of the database is hidden from the user. End users can only relate to the business entities that they are accustomed to in the real world.

For example, consider the Order Details table in the Northwind sample database. The diagram below shows the normalized database schema. Normalization increases the performance and decrease the footprint of database records. On the other hand, this means that related data is stored in multiple tables, increasing the complexity of data presentation.

Code On Time automatically performs denormalization. It inserts several reference fields from master tables into each business object presented to the user. For example, in the “Reference Information" category on the Order Details screen, it includes Order Customer Company Name, Order Employee Last Name, and etc. These fields are not explicitly available in the Order Details table according to the database diagram.

DenormalizationProcess

The application generator detected direct and indirect foreign key relationships between Order Details and the rest of the tables in the above diagram, and has included reference fields from the linked master tables up to 3 levels. Only one master field is borrowed for inclusion into Order Details for each relationship. Usually, these fields are found either as 1) aliases for foreign key lookups of direct outgoing master relationships, such as Product Name or 2) read-only fields placed in the Reference Information category, such Order Ship Via Company Name.

image

Suppose we need to add the parent Employee’s FirstName and HomePhone to the Order Details forms.

Denormalization

Start the web application generator, click on the project name, and choose the Business Logic Layer option.

image

Switch to the Denormalization Field Map section, and enter the following field map:

dbo.Orders => dbo.Employees
FirstName
HomePhone

image

Press Finish to save your changes.

Click on the Refresh button. Select the Orders controller to ensure that it will reflect the new denormalization field map, and press Refresh.

image

Finally, press the Generate button.

When the website comes up, navigate to the Orders page. You will see that the fields have been added to the Reference Information category of this screen. Now you can find out the first name of the employee made the order and call them without having to look them up. All other pages that use the Orders data controller will also reflect these changes.

image

However, if you check the Order Details screen, the new reference fields are not there. You need to Refresh every relevant data controller in order for them to include the fields referenced in the Denormalization Field Map.

Click on the project name again, and press Refresh. Select the OrderDetails controller, and press Refresh.

image

When the website comes up again, you can see that the new reference fields FirstName and HomePhone have been added under Reference Information on the Order Details page.

image

Continue to Cascading Lookups