LightSwitch – Simple table with a couple of referenced support tables

Background

One of the common “Beginner go to” Applications is a simple table that has Foreign Key relationships to a couple of Lookup tables.

Let us see whether Microsoft Lightspeed makes short walk of it or whether it stumbles.

 

Data Model

Diagram

Admittedly, it is an awkward looking Diagram.

DataModel

 

But, hopefully it is easy enough to follow with four entities.

 

Entities

Entity Use Content
listofServers lists all of our servers HRDB, HRWeb, mail gateway
listofApplications List the few applications that we support HR, Sales, Marketing
listofServerEnvironment Dev, QA, Test, Performance, and Prod
listofApplicationServers Joins the aforementioned entities

 

Foreign Key

Let us use sp_fkeys to get the foreign keys defined on our table.

Syntax


exec sp_fkeys
         @fktable_name = [table-name]
       , @fktable_owner = [schema-name]

Sample


exec sp_fkeys
         @fktable_name = 'listofApplicationServers'
       , @fktable_owner = 'dbo'

Output

sp_fkeys__listofApplicationServers-20161117-0940PM

Explanation

  1. We have declared three foreign key relationships
  2. The first one listed is FK_listofApplicationServers_listofApplications
    • The join is on ApplicationID
  3. The second one listed is FK_listofApplicationServers_listofServerEnvironment
    • The join is on serverRole (listofApplicationServers) to EnvironmentID ( listofServerEnvironment )
  4. The third one listed is FK_listofApplicationServers_listofServers
    • The join is on serverName
    • But, there is an important different between this join and on our other joins
    • This join does not target our Primary Key
      • Please take a look at the last column ( PK_NAME )
      • The first two joins targets PK_listofApplications and PK_listofServerEnvironment
      • And, the last targets IX_listofServers_ServerName
        • The reason is that the PK on dbo.listofServers is a serverID and not serverName
        • We chose to introduce serverID as a firewall against server name changes

 

 

LightSpeed

Once we are in light speed we will quickly go to work.

 

Data Source

Attach Data Source Wizard

Choose your Database Objects

 

AddDataSource-20161017-0703PM

 

 

 

LSML Files

listofServerEnvironments

Diagraming

Here is what our lsml display of the listofApplicationServer entity looks like

listofApplicationServer-lsml-20161117-0651AM

 

Explanation
  1. Our Primarky key is a composite key
    1. And, it has ApplicationID, serverName, and serverRole has the columns in the composite
  2. There are two additional properties
    • And, those are listofApplication and listofServerEnvironment
    • They allow us to map many-to-one  to our support tables ( listofApplication & listofServerEnvironment )
  3. A careful observer will not that our join to listofServers is missing. Why?

 

Add Relationship

By clicking on an empty spot in the panel, we can initiate the “Add Relationship”  option

    Add New Relationship – Begin

AddNewRelationship-20161118-0622AM

 

 

    Add New Relationship – In Process

Here we attempt to tie the listofApplicationServer table to the listofServer table

AddNewRelationship-InProgres-20161118-0720AM

 

But, we get an eror message that reads:

Image

RelationMustBeMappedOnTheKeyFields

 

Textual

The relationship must be mapped on the key fields and only the key fields of the primary side of the relationship.

 

Screens

Next thing to do is to setup the Screens.

Doing so involves deciding which screens are needed and customizing the default layout design of each screen.

 

Add New Screen

Let us Add New Screen …

 

AddNewScreen-20161119-0809AM

 

 

Explanation:

  1. Screen Sheet Name
    • ManageApplicationServer
  2. Screen Data
    • Syntax
      • DataSourceName.DataSetName
    • Sample
      • DBDiagData.listofApplicationServer

 

 

Layout – Default

 

Browse

Here is the layout of the Browse Screen

BrowseManageApplicationServers=-20161119-0811AM

 

Explanation
  1. Our three primary columns are displayed in the Browse Screen
  2. And, so for each each record we have our key columns display
  3. This makes it easy for specificity in row selection
  4. BTW, the key columns are Server Name, ApplicationID, and Server Role

 

 

 

View

Here is the layout of the View Screen

ViewManageApplicationServer-20161119-0811AM

 

Explanation
  1. The layout is two rows displayed horizontally
  2. And, all columns are displayed

 

Add Edit

AddEditManageApplicationServer-20161119-0812AM

 

Explanation
  1. This is same the screen design as the View Layout
    • The layout is two rows displayed horizontally
    • And, all columns are displayed

 

 

HTML Client

When we run the uncustomized application, here are the screens generated.

Browse

HTML Client display of the Browse Screen

Browse-20161119-0815AM (brushed up)

 

Explanation
  1. Our three primary columns are displayed in the Browse Screen
  2. And, so for each each record we have our key columns display
  3. This makes it easy for specificity in row selection
  4. BTW, the key columns are Server Name, ApplicationID, and Server Role

 

 

 

View

HTML Client display of the View Screen

View-20161119-0816AM ( Brushed up)

Explanation
  1. The layout is two rows displayed horizontally
  2. And, all columns are displayed
  3. Both the id and literal columns are displayed for columns involved in Foreign Key relationships

 

Add Edit

Here is what is rendered in the Add/Edd HTML Client

AddEdit-Edit-20161119-0819AM (Brushed up)

 

Explanation
  1. All columns are displayed
  2. For the columns involved in Foreign Key relationships both the id and the literal columns are displayed
    • For example
      • The Application ID ( 1 ) and the Application Name ( –PAS ) are displayed
      • The server Role ( 1 ) and Server Environment ( Production ) are displayed

 

 

 

Layout – Revised

 

Browse

Here is the layout of the Browse Screen

BrowseManageApplicationServers-20161119-0825AM

 

 

Explanation
  1. As stated earlier, we kept the identifying parameters
  2. To make them easier to read and identify, we replaced the system ids with their actual literal companions
  3. For record keeping, we did not remove the original system identifiers, we are merely hidden them

 

View

Here is the layout of the View Screen

ViewManageApplicationServer-20161119-0831AM (brushed up)

 

Explanation
  1. We kept the same layout and data has the Browse screen
  2. We could have added more data here, but for this particular entity the other  columns are timestamp tracking columns

Add Edit

 

AddEditManageApplicationServer-20161119-0834AM

Explanation
  1. Again, we kept the same data
  2. The entity we are addressing is a join table and not a user table

 

HTML Client

Now we will run the applicaton post touch up…

Browse

HTML Client display of the Browse Screen

Browse-20161119-0823AM (brushed up)

 

 

Explanation
  1. Again, our three primary columns are displayed in the Browse Screen
  2. But now not just the system ID, but the descriptive literals

 

 

View

Here is the HTML Client display of the View Screen.

We declutter it so that it only has data that we need.

 

View-20161119-0840AM (Brushed Up)

 

Add Edit

Here is what is rendered in the Add/Edd HTML Client

AddEdit-Edit-20161119-0839AM (Brushed up)

 

Explanation
  1. Again, we only have the columns that are needed
  2. And, those the Server Name, Application, and Environment

 

Summary

LightSpeed allows us create simple screens that supports entity and referenced columns.

Unfortunately, though it supports referenced Primary keys, it does not support candidate keys.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s