In the SalesLogix web entity model you are able to define an entity based off a underlying database table.  You are also able to build relationships from one entity to another entity.  These relationships can either be 1 to many or many to 1.  This is a really cool feature as it allows something like an “Account” entity with a one to many relationship to the “Contact” entity.  With this being defined the Account entity gets a property like “Account.Contacts”  This property represents the collection of all Contacts tied to the Account.  With this relationship thus established you could do something like Account.Contacts.Count to find out how many contact records exist under an account, or any number of useful features.

When you think about what this relationship would mean in a database syntax you can see that it would represent a query like

select c.* from account a inner join contact c on a.accountid=c.accountid

If you were to examine database activity of the SalesLogix entity model in action you would likely see similar queries being used. 

The important point about this in the scope of my conversation is that the joins established are inner joins (Show only those contacts that exist under the account).  Here in lies a problem.  And a serious one at that.

The problem lies not in the working of the entity model itself, that works as described above.  The problem lies in how the web interface design also relies on the entity model for displaying data (and not just data selection).  Lets take a look at a very simple scenario that illustrates the problem.

Tickets have a relationship to Contacts via the TICKET.CONTACTID to CONTACT.CONTACTID table join.  The Ticket entity has a Ticket.Contact relationship that represents information about the contact that may be tied to the ticket.  I stress “may” because a ticket does not need to have a contact.  Using the entity model you can do something like Ticket.Contact.Workphone and it would get the Workphone field of the Contact entity tied to the Ticket.

Accounts have a relationship to Tickets.  An Account can have many tickets.  The Account entity has a Tickets property that represents the collection of all the tickets assigned to the Account.

Here we can see a sample Account showing multiple tickets.

Now for the problem

Lets say I want to add a more information on the grid to show the name of the contact and the contact’s phone number.

I open the Account quick from “AccountTickets” in the Application Architect. 

Here I can see the SalesLogix grid view and the data source which the grid is based.  If I examine the properties of the data source:

I can see the Entity Type = “Ticket”.  This means the data displaying in the grid is from the Ticket entity as the top layer.

I can see the Source = “Account” this is because this is an Account view and this defines that the current source of the data is based on the Account the record is displaying.

I can see the GetByProperty is “Tickets”.  This is the name of the relationship entity property on the Account entity which represents the collection of tickets tied to the Account.

Now I will add a couple of new columns to display the Contact name and Phone numbers in the grid.  To do that I bring up the Grid control’s Properties and select the Columns collection:

I notice that right now there is a column already on the grid that kind of does what I want.  The Fourth column from the top shows the Ticket’s Urgency Description field.  Now the Ticket stores the UrgencyID, not a text description .  The “Urgency” property on the Ticket is a relationship to the Urgency entity.  You can see that the Data field of this column is bound to Urgency.Description.  This represents the Description property of the Urgency entity, by way of the ticket’s relationship to the urgency entity.  Put another way it is like “Ticket.Urgency.Description”.

If you use the Data Field drop down you will notice you can not get down into the second layer entity properties like Urgency.Description, instead you only see primary relationship property “Urgency”:

That is fine.  When you select urgency you can then simply type “.Description” at the end of the Data Field to tell which property within the Urgency entity you actually want to display.

OK, hopefully that is clear lets now add the Contact fields I want.  I am not going to show a step by step but just a couple of screen shots:

The name column

The Phone column

OK so now I build and deploy and lets take a look:

Cool. I see the columns.

Wait a second I only see 7 tickets even though the count is showing the correct 17.  What is going on?

Well if you look at the query run in the background it looks like:

SELECT
TOP 20 
child1_.TICKETID as y0_,
child1_.TicketNumber as y1_,
child1_.RECEIVEDDATE as y2_,
child1_.STATUSCODE as y3_,
urgency2_.DESCRIPTION as y4_,
child1_.NEEDEDBYDATE as y5_,
ticketprob3_.NOTES as y6_,
ticketsolu4_.NOTES as y7_,
contact5_.NameLF as y8_,
contact5_.WORKPHONE as y9_
FROM
ACCOUNT this_
inner join
TICKET child1_
    on this_.ACCOUNTID=child1_.ACCOUNTID
inner join
URGENCY urgency2_
    on child1_.URGENCYID=urgency2_.URGENCYID
inner join
CONTACT contact5_
    on child1_.CONTACTID=contact5_.CONTACTID
inner join
TICKETPROBLEM ticketprob3_
    on child1_.TICKETID=ticketprob3_.TICKETID
inner join
TICKETSOLUTION ticketsolu4_
    on child1_.TICKETID=ticketsolu4_.TICKETID
WHERE this_.ACCOUNTID = 'AEKPXA003P5M'

Ignoring the confusing alias names, the important thing the From portion of the clause where we can see all inner joins being used.  Remember what I said above about how the entity model works?  There it is.  Unfortunately for us it carries down to how data is displayed.

In my database only 7 tickets have a TICKET.CONTACTID value.  The other 10 Tickets have a null value in the field.

At this point I hope you can see what the problem is.  NO GRID IN THE SALESLOGIX WEB CLIENT CAN DISPLAY DATA FROM ANYTHING MORE THAN THE FIRST LEVEL OF THE ENTITY MODEL ON WHICH IT IS BOUND.  And that, dear reader, sucks.

How do you get around it?

1 Don’t use quick forms to develop any forms with grids.  Develop your own, from scratch, way of displaying data in custom ASP.NET forms and bypass the entity model to show related data.

2 Redundantly create custom entity properties on the first layer entity that show data from the related entity.  For instance create a custom entity property like Ticket.ContactName which would return a string value from code to represent what you should be able to get out of the entity model, like this:

        public static void GetContactNameStep( ITicket ticket, out System.String result)
        {
            string name = "";
            if(ticket.Contact != null) name = ticket.Contact.NameLF;
            result = name;
        }

Neither of these is particularly palatable but that is what you are left with.

This seriously needs to be fixed.