Tejana

Microsoft Lightswitch Sharing a Unique Key

Posted in LightSwitch, Microsoft, Software, SQL by tejana on February 18, 2012

Applications with several master tables may have  tables where they can share a primary unique key. We will see how to set up Microsoft Lightswitch tables to share a key. And we will show a modal window picker with an add button. With this approach the index of the Customer table and the index of the Vendor table can be the same value for a customer that is also a vendor. Alternatively, one could use a single master table for multiple concepts, Vendor, Customer and so on, but our approach is based on Fowler’s Party pattern, as described in www.cs.fsu.edu/~stoeckli/PublicationsVitae/JavaDevJournal.docSimilar :

3.0 Fowler’s First Pattern – The Party Pattern

The first analysis pattern described by Fowler in his book Analysis Patterns, is the Party pattern. A Party, according to Fowler, is an abstraction to define persons or organizations. He models a Party class with sub-classes of Person and Organization. The Party could have different roles such as a person could be a employer or employee, a doctor, a mother, etc. An organization could be a business entity, a shelter, a hospital, etc. Figure 1 depicts the class representation of the Party pattern.

image

 

The model also shows the influence of Paul Patterson’s Maintaining a Primary Child Entity and Simple Stakeholder Management  Take a look at the Party entity:

party

 

Paul Patterson’s Blog show how to artfully exploit the Address, Phone, Email, and Contact with standard non required 1 to many relationships:

 

partyEmail

 

It is the relationships to Vendor, Employee and Customer that I draw your attention to:

partyCustomer

 

The resulting table structure will require a Party for a Customer and the Customer table will use the Party index. Further if a Vendor or Employee are associated with this Party, they will also use the same index. The primary key of the master tables is a foreign key of the Party table:

 

TableCustomer

 

Of course now this Party represents a challenge to the data entry screen (The add customer method opens the top right of the screen instead of the add dialogue):

 

customerList2

 

The user gets one chance to enter in a Party (labeled "Account:”). Once the record is saved a Customer must be deleted to change its Account/Party. To help, the screen validation logic has been leveraged to display an Add button for any Name entered if it does not exist in Parties. A modal window picker is used to display any programmatically set  party. This allows the user to select an existing party from a large dataset including all Vendors and Employees that are not yet Customers.  If a party record is added, any changes to the name, change the party name unless of course the name is changed to an existing Party. If the added party is not used it is removed. A screen PropertyParty is used to store the new party and then possibly delete it.

 

customerList3

 

       partial void CustomerListAddAndEditNew_Execute()
       {
           // Add new customer on screen right
           Customer _customer = new Customer();
           _customer.IsCompany = false;
           _customer.IsPerson = false;
           Customers.SelectedItem = _customer;
           this.FindControl("CompanyName3").Focus();
       }

       partial void AddNewPartyFromCustomer_Execute()
       {
           if (PropertyParty == null)
           {
               Party p = new Party();
               p.PartyName = Customers.SelectedItem.CustomerName;
               Customers.SelectedItem.Party = p;
               PropertyParty = p;
           }
           else
           {
               PropertyParty.PartyName = Customers.SelectedItem.CustomerName;
           }           
       }     

 

       partial void Customers_Validate(ScreenValidationResultsBuilder results)
        {

              // Set Party if available
                if ((!String.IsNullOrEmpty(Customers.SelectedItem.CustomerName)) &&
                   (Customers.SelectedItem.Details.EntityState == EntityState.Added))
                {
                    if (Customers.SelectedItem.Party == null)
                    {
                        var _v = DataWorkspace.TejanaTest2Data.Parties
                                   .Where(p =>
                                   p.PartyName.Equals(Customers.SelectedItem.CompanyName, StringComparison.Ordinal))
                                   .SingleOrDefault();
                        if (_v == null)
                        {
                            this.FindControl("AddParty").DisplayName = "Add: " + Customers.SelectedItem.CustomerName;
                            this.FindControl("AddParty").IsVisible = true;
                        }
                        else
                        {
                            Customers.SelectedItem.Party = _v;
                            this.FindControl("AddParty").IsVisible = false;
                        }
                    }
                    else
                    {
                        this.FindControl("AddParty").IsVisible = false;
                        if (Customers.SelectedItem.Party.Details.EntityState == EntityState.Added)
                        {
                            var _v = DataWorkspace.TejanaTest2Data.Parties
                                   .Where(p =>
                                   p.PartyName.Equals(Customers.SelectedItem.CompanyName, StringComparison.Ordinal))
                                   .SingleOrDefault();
                            if (_v == null)
                            {
                                Customers.SelectedItem.Party.PartyName = Customers.SelectedItem.CustomerName;
                               
                            }
                            else
                            {
                                Customers.SelectedItem.Party = _v;
                            }
                        }
                    }
                }
                if ((Customers.SelectedItem.Party != null) &&
                    (PropertyParty != null) &&
                    (Customers.SelectedItem.Party != PropertyParty))
                {
                    if (PropertyParty.Details.EntityState != EntityState.Discarded)
                    {PropertyParty.Delete();}

                 }

}

I have not had time to be blogging, it is nice to have work. Before going back to work I thought I would post a LightSwitch blog entry.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: