Apr 9

Written by:
4/9/2011 11:14 PM 

I’m using LightSwitch as a prototyping tool to get a jump start on a project at work that I took over from a long-exited programmer. The design I inherited has a lot of gaps that need to be filled in. I think the quickest way I can display those to my boss is to create the tables and basic maintenance screens so I can easily point out what’s there and what’s missing. LightSwitch inherently expresses database relationships in the screens it creates, so even if you aren’t going to use LightSwitch in the final application, you can quickly throw together and easily modify the table layout as you create the basic application in LightSwitch. Then you can step through the resulting screens and clearly see the table relationships with errors in design jumping right out at you.

The Requirements

The client has a need to track books as they move between various locations.

• Locations contain multiple Buildings
• Buildings contain multiple Rooms
• Books are always assigned to one of:
  • A particular Room
  • OpenInventory, which has no specified location
• Books can be transferred:
  • From Room to Room across all Buildings at all Locations
  • From any Room to OpenInventory
  • To OpenInventory from any Room
• An AssignedInventory table row records the quantity and is uniquely identified by
  • Book
  • Room
  • Condition
• An OpenInventory table row records quantity and is uniquely identified by
  • Book
  • Condition
• OpenInventoryTransfers and AssignedInventoryTransfers include
  • Book
  • Condition
  • Quantity Sent
  • Received
  • Quantity Received New
  • Quantity Received Used
  • Condition Variance
  • Quantity Variance
  • Source (Open Inventory or Room)
  • Destination (Open Inventory or Room)

The nice thing about LightSwitch is that I can define classes, primarily in the form of table layout, that simultaneously define the underlying tables and the entities that represent them.  For instance, a Location class instance will have a Buildings collection property because I defined the one-to-many relationship between Location and Building. The underlying database will contain a Buildings table where each row holds a foreign key on a Locations table row. But I only see that represented logically, as a collection property in the Location class. And because I defined that relationship, LightSwitch can automatically generate a screen to maintain Location and Building records in a master/detail screen. I don’t have to create GridViews or the ListBoxes that bind to Linq queries written by me, and I don’t have to do it over and over again.

First, create the project:

In Solution Explorer, you can right-click / Open Properties, or double-click

Under Application Type select the Web Radio Button:

Now we start adding tables:

Instead of creating all the tables and then the screens, I’m going to create one table at a time, working from the bottom up, and create the requisite screens at that point. This will probably mean deleting and re-creating some screens as the design plays out, but it’s so easy it doesn’t really matter. And I could use the screen designer to make the changes.

Let’s begin with the Book table. Create the columns as shown and make ISBN a unique index:

You might want to have a separate Authors table, but the design I was given specifies Author name in the Book table.

We want to validate ISBN:

Eventually we’ll want to allow for hyphens and validate that the ISBN is well-formed,  but for now we’ll just check for a length of 10 or 13 and all numeric digits:

partial void ISBN_Validate(EntityValidationResultsBuilder results)
{
    if (this.ISBN.Length != 10 && this.ISBN.Length != 13)
        results.AddPropertyError("ISBN must be either 10 or 13 digits long");
    else
    {
        foreach (char ch in this.ISBN)
            if (ch < '0' || ch > '9')
            {
                results.AddPropertyError("ISBN must be all numeric");
                break;
            }
    }
}

Probably I could have checked for a Long Positive Integer, but In general I only like to treat numeric strings as numbers when they’re actually used as numbers. Anyway, this code will ultimately have to validate an actual ISBN.

Now we’ll generate a screen so we can do CRUD operations on Books.

The Editable Grid Screen (1) is what we want. By selecting Books (2) in the Screen Data dropdown we get the Screen Name (3) of EditableBooksGrid. Let’s stick with that, at least for now.

And that’s it. Hit Ctl-F5 and let’s see what we have.

We see the screen we just added in the menu (1) and it’s the default tab in a tabbed view (2) that we can page through (3). We have controls to do CRUD operations (4) and to save and load the Books table (5). As a bonus, if we’re running in debug mode we can change the screen design at runtime (6).

We’re about to add some definitions of Books, by creating instances of the Books entity, which will be stored in rows in the Books table, but first let’s adjust the column layout a bit. Even though ISBN is the unique key, I’d prefer to see Title in the first column. That’s pretty easy to do. Select the EditableBooksGrid screen in Solution Explorer, then just drag Title so it’s above ISBN:

By selecting the green plus sign Icon we can enter the Book into an individual form. Below I’ve done that. As soon as I enter a value in ISBN and tab away validation finds and displays my error:

So I’ll correct the length, but leave in an alpha character. Below I’ve clicked on the field so you can see how the message is displayed in that case.

 

I’ll enter a ‘valid’ ISBN and leave the required Title and  Name fields blank then click OK. One quirk with this validation is that you can tab away from an empty required field without triggering the error, if it starts out empty. That’s because binding is only triggered when the value changes, and the validation occurs as a result of binding. Leaving an input field empty does not change the value, so these errors of omission are not caught until you click OK and trigger overall form validation.

To show you the difference, I cleared out the first record entirely by clicking on the ‘X’ icon, entered a character in the Title field, tabbed to and entered ISBN, then tabbed back to Title cleared it and tabbed away. I got this message immediately. Notice the error message wording is different, which implies that the business rule is being defined twice in the underlying code. Fortunately LightSwitch handles that, so I don’t have to remember to maintain it in two places:

Now I’ll enter duplicate ISBN numbers. When I click on the Save icon a modal generic error message box appears (not shown) followed by:

One last thing, and then we’ll move on. This form is wasteful of screen space so let’s make some adjustments, but let’s do it at run-time by clicking F5 to run in debug mode, then clicking on the  Design Screen icon in the upper right corner of the screen. Select ISBN and set the Width to 90 pixels and click Save. You can try MaxWidth, but I ended up with padding on the left when I did, and specifying the number of characters doesn’t work any better than it does in HTML:

Do the equivalent for the Name fields and it looks a lot more reasonable:

I entered Mark Twain’s first and last name in the wrong order which gives me an opportunity to show you what you get if you select ‘Tom Sawyer’ and click on the Pencil icon:

I correct the name and press ok, but I still have to click on the Save icon to actually make the changes to the data. If I try to exit the screen without clicking on Save:

And now for something completely different. Not really, we’re just going to the other extreme in the sense that I said I was going to do the tables from the bottom up. But now I’m going to start at the top and define the Location table because Location is the other entity that has no dependencies on other entities / tables, which means I can create the Location screen as soon as I create the table. Then I’m going to create the Buildings table and show you something wonderful.

Actually, I think I’ll go ahead and create the Buildings table. Note the default naming convention in effect. I create a Building class (single tense) and instances of the class, or at least the elements of which must persist, are stored as rows in the Buildings table (multiple tense).

The address fields are optional for Building. It may be that we want to restrict access to the Building can only be reached through the Location’s address so it’s a business rule that the Building address can be null.

Now the magic starts to happen. I said up front that a Location may have many Buildings. We can set that up with just a few mouse-clicks:

Edit the Building class (1). Click on Relationship… (2). A dialog box comes up as shown above. The From table is Building (3) and it’s on the many side of a one-to-many relationship. In the graphic you can see the infinity sign next to the Building icon (4) and a ‘1’ (5) next to the icon representing the still-to-be-designated ‘Master’ table , which will be Locations. We could have started by editing the Location class, but then we would have to switched around the tables in the Add New Relationship dialog box.

As shown below, choose Location for the To table (1). The semantics are a little fuzzy in that the table names are actually Locations and Buildings. The right icon (2) is now labeled Location with the enclosed word Buildings representing the Buildings collection associated with the Location. In Sql Server parlance this would equate to the Building rows that hold foreign key pointers on the Location table. The ‘1’ indicates that Location is the one side of the one-to-many relationship.

After selecting Location, on the left side we have the Building icon (3) with the embedded word Location representing the one and only Location that the Building can be associated with. Again, this is the Location row that the Building row Location foreign key points to. The infinity sign indicates that Building is on the many side of the one-to-many relationship.

You can see the referential rules inferred from the relationship. (4).  Until you’re used to the interface it’s possible you’ll get the relationships backwards sometimes. Looking at these rules when you set up the relationship is your best chance of catching that early. In this case, if they said a Location must have a Building, I’d know I got it backwards

Actually I think if I’d paid closer attention to labels, as in Navigation Property (5) I wouldn’t have had my relationship problems. if you’re not used to the interface and are just zipping along doing your RAD thing it’s easy to take the Navigation Property value in the From column as the From table, when of course it’s just the opposite, so just be aware and maybe you won’t make the same mistake.

In general, if you are editing the class that represents the table on the many side, in this case Building,  when you click on Relationship… then the default settings in the Add New Relationship… dialog will be the ones you intend.

Now when we look at the Building class below we see more symbols to indicate the relationship between Location and Building. You can see the ‘1’ next to the Location icon and the infinity symbol next to the Location column / property in the Building table / class. This indicates there may be many Buildings, but because it’s next to the word Location it’s easy to read incorrectly at a glance. I thinks it’s just a matter of building the visual associations through repetition so it becomes second nature.

I used the terms ‘column / property’ and ‘table / class’ to emphasize that you don’t really have to worry about which is which. How great is it that you can build tables, add the relationships, and those relationships are automatically expressed in the  generated classes that model that data?

So here’s the cool part, we’re going to create a screen that lets us maintain both Locations and Buildings. This is the second place you might notice that you got the relationship backwards. If you don’t see a Location Buildings checkbox then you forgot to set up the relationship or you got it backwards:

Select List and Details Screen and the Locations table in the Screen Data dropdown. Uncheck Location Details and make sure Location Buildings is checked. Select ‘Ok’ and click ctrl-F5 to run the application:

We select the Locations List Detail entry in the menu (1) which brings up the tabbed page (2). The Locations list box (3) will show all the Locations. The Buildings list box (4) will show the collection of Buildings associated with the currently selected Location.

Click on the green plus sign icon under Locations to add one:

Now here’s a test for LightSwitch. We haven’t actually added a Location record yet because we haven’t clicked Save. Will LightSwitch allow us to add a Building to the still to be created Museum of Science Fiction Location?

Here we go:

So far, so good. All that remains is to click on the Save icon, and when I do, it works.

We haven’t done any validation of the input. What do we need? In the case of Location, I have a general rule I’m making up right now is that every entity needs a unique identifier that can be displayed. Location does not exist within the context of any other entity so for Location the Name property will be unique:

We want Building Name to be unique within Location. LightSwitch is up to it:

LightSwitch adds a compound unique index that includes Name and Location.

I can use the same Name at a different Location however:

Now for the Room table:

For now it just has a Name column / property and a many-to-one relationship to Building. Now let’s set up an Editable Grid Screen because I want to make a point about what you will see in the Buildings dropdown.

Not too useful. Remember I said that every entity needs a unique, displayable property. This is why. Fortunately this can be easily done. Edit the Building class and click on <Add Property>:

Name it DisplayName and click on the Is Computed checkbox which will result in:

Click on Edit Method:

namespace LightSwitchApplication
{
    public partial class Building
    {
        partial void DisplayName_Compute(ref string result)
        {
            result = this.Name + " / " + this.Location.Name;
        }
    }
}

Edit the Building class. Click on Building in the Header. Under Properties set Summary Property to DisplayName:

And lo:

One last thing we need to do is make Room Name unique within Building. You can do that by setting the Include in Unique Index  property on for Name and Building in the Room class as we did for Name and Location in the Building class.

Let’s add a Room

Now it’s time to create some inventory.We’ll start with OpenInventory, each instance of which represents some quantity of a particular Book that is not currently assigned to any Location:

Create an OpenInventory class as shown and click on Choice List under Condition Properties; Create the New and Used values that can be assigned to the Condition property. One of the things that differentiates instances of OpenInventory and AssignedInventory  is the Condition of the Books; all the Books within a particular OpenInventory or AssignedInventory instance  are in the same Condition, either New or Used.

Now we establish the relationship between OpenInventory and Book. An OpenInventory can only be linked to one Book, but a Book can be linked to many OpenInventory rows. Actually only two, New or Used, which is just a matter of setting another unique index.  By virtue of the choices I defined above, the property will always be restricted to New or Used. If it’s also unique there can only be two instances.

But that unique index includes both Book and Condition, so let’s add the Book Relationship. OpenInventory is on the many side, so we click on Relationships while editing OpenInventory:

Now we can set the Include in Unique Index property on for Condition and Book.

You have to set them individually. The shot above is setting the property for Book.

Let’s go ahead and create the Editable Grid Screen for OpenInventory.

In this case we have a unique identifier but it’s not very helpful. We’ll need to add a computed property to Book.

partial void DisplayName_Compute(ref string result)
{
  result = this.Title + " (" + this.ISBN + ")";
}

Don’t forget, like I just did, that you have to set the Book class Summary Property value to DisplayName.

I haven’t quite figured out why the Listbox columns sometimes get re-sized automatically and other times not, but you can see that we now have a combination of Title and ISBN displayed for Book.

Let’s run in debug so we can change the width until we get it right. Hit F5 and click on Design Screen when it comes up:

After clicking Save:

Close enough. Afterwards I found it’s a little quirky about when it displays the drop down part full-width and when it clips it at the column width, but still close enough for now.

Let’s test whether the Condition / Book combination is a unique key on OpenInventory:

Sure enough, I cannot create two OpenInventory rows for the same Book and Condition.

I also want to change the column order in the form. It’s as simple as changing this:

to this by dragging and dropping:

I also widened the Book Listbox to 180 pixels.

The other columns are still excessively wide, but let’s move on to the AssignedInventory class, each instance of which is like OpenInventory in that it represents some quantity of a particular Book in a particular Condition but adds on the assignment to a particular Room in a particular Building in a particular Location. Accordingly we’re going to require a computed property to display for Room.

Bur first things first. Here’s the class before we add the relationship to Book. We have to create New and Used choices for Condition again. There may be a way to have AssignedInventory inherit from OpenInventory, layering on the Room property, but trying to do that and reflect the underlying tables at the same time is too much of a distraction. I would have a single BookInventory table with have a nullable Room link to represent both with a single table, but my existing design spec at work calls for separate tables.

AssignedInventory is on the many side of the Book relationship, so click on Relationship…

AssignedInventory is also on the many side of the Room relationship so click on Relationship… again:

And that’s all we need to define AssignedInventory. Let’s see what an Editable Grid Screen looks like:

Not enough information. We don’t know what Building the Room is in. Time for another Summary Property.

partial void DisplayName_Compute(ref string result)
{
    result = this.Name + " / " + this.Building;
}

After also making some adjustments to column order and width:

Notice that I concatenated this.Building to this.Name in DisplayName_Compute; what I get from this.Building is the Summary Property of Building, which includes Location in the Name, so Room is truly uniquely identified in the ListBox.

The combination of Book and Condition also need to be unique, as in OpenInventory, but only within Room.  Therefore we set the Include in Unique Index for Book, Room, and Condition. Notice I’ve also dragged the column names into a more logical order:

Now for Book transfers. We’ll do OpenInventoryTransfers first.

I went ahead and defined the relationships and fields in a logical order. In this case the combination of Book and Condition is not unique, even within Room because you could have multiple transfers in process to or from  a single Room for the same Book and Condition. It’s after they are received that the quantity from each will be deducted from the source inventory and added to the destination inventory uniquely identified by the same Book and Condition within the destination.

Once again it’s necessary to build New and Used choices for Condition, and now we add DestinationType with the choices of OpenInventory and Room. An OpenInventoryTransfer can be in either direction, from OpenInventory to Room or vice-versa. DestinationType identifies which.

ConditionVariance and QuantityVariance will be two computed fields that will indicate a variance in what was received from what was sent. Also we require QuantitySent to be greater than zero:

partial void ConditionVariance_Compute(ref bool result)
{
    result =
        this.Received &&
        ((this.Condition == "New" && this.QuantityReceivedUsed > 0) ||
        (this.Condition == "Used" && this.QuantityReceivedUsed > 0));
}
partial void QuantityVariance_Compute(ref bool result)
{
    result =
        this.Received &&
        (this.QuantityReceivedUsed + this.QuantityReceivedNew != this.QuantitySent);
}
partial void QuantitySent_Validate(EntityValidationResultsBuilder results)
{
    if (this.QuantitySent < 1)
        results.AddPropertyError("QuantitySent must be greater than zero");
}

Let’s go ahead and create the Editable Grid Screen.  I’ll cut to the chase and show you the detail screen as the field sizes need serious pruning.

Notice that Condition Variance and Quantity Variance are displayed as labels rather than text boxes in the screen LightSwitch created by default:

That’s because we converted them to computed fields. If they were actual table columns they would be on the form as text boxes:

Now that Room is on the one side of some one-to-many relationships we should create a List and Details Screen:

We see the AssignedInventory collection for the selected Room…

… and the Open Inventory Transfers Collection

We can create new Rooms, new Assigned Inventories or Open Inventory Transfers to and from the selected Room from this screen. And you always have the luxury of form based entry when row based entry is too cumbersome:

After clicking OK:

Onward to AssignedInventoryTransfer, which is a transfer between two rooms. As you’d expect each AssignedInventoryTransfer has a Source Room and a Destination Room, putting Room on the one side of two one-to-many relationships:

How does this play out in the Room List and Details Screen? We need to re-create it to include the new collections:

Hit F5 to run in debug mode and we’ll modify the displayed collection names using Design Screen:

We can add transfers from this screen:

But first let’s add a business rule that the Source and Destination Rooms cannot be the same:

partial void AssignedInventoryTransfers_Validate(AssignedInventoryTransfer entity, EntitySetValidationResultsBuilder results)
{
    if (entity.Source == entity.Destination)
        results.AddEntityError("Source and Destination must be different Rooms");
}

Now if we try to create a transfer that violates this rule:

We can get this far:

But when we click Save:

We can quickly fix the mistake:

I think we have a complete set of screens to support basic CRUD on all our tables. I want to do some renaming and pruning. Let’s review:

We have the Editable Books Grid which allows us to create and maintain Book table rows.  Book definitions stand on their own so we need a standalone form to edit them. Let’s change the screen title to Book Catalog.

 

We can maintain both Locations and Building through Locations List Detail which we’ll rename Locations And Buildings.

Editable Rooms Grid allows us to maintain Rooms but we can do that in Rooms List Detail, so it’s out.

OpenInventory can only be maintained in Editable Open Inventories Grid so we keep it under the name OpenInventoryScreen, OpenInventory being taken.

Both Editable Assigned Inventories Grid and Editable Open Inventory Transfers Grid can go. Assigned Inventories and Inventory transfers are always linked to a Room and so can be maintained through Rooms List Detail which I’m renaming Rooms And Books Inventory.

All the essential pieces are in place. We can create Book Definitions, Book inventory and Rooms for Book inventory assignment. We can create transfers between OpenInventory and Room and between two Rooms. We can record receipt of those transfers and variances between what was sent and what was received.

This is getting kind of long, so I’m going to post this as Part 1. One glaring necessity is validation of quantities for transfers. Are enough copies available at the source? And what about transfers that haven’t been completed when determining how many copies of a particular Book are available? Also we need security and accountability in the form of users and roles.

The next day…

Another omission just jumped out at me. The OpenInventory entity needs to have an OpenInventoryTransfer collection property and AssignedInventory entity needs to have an AssignedInventoryTransfer collection property – meaning the OpenInventoryTransfers table needs to hold a foreign key on the OpenInventory table, etc. so stay tuned for Part 2.

Ouch, I also duplicated Book and Condition in OpenInventoryTransfer. I can only think that in my head I saw the combination as the foreign key that OpenInventoryTransfer holds on OpenInventory. My only defense is LightSwitch makes it so easy you don’t do some things you normally do without thinking about.