Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Summary and GroupBy of data using IRepository and projections

In the SalesLogix web client, you don't really have the same old options regarding Queries and Datasets.  However, by using IRepository you can create similar functionality by using projections to define groups and other calculations.

In this case, I needed to present a dialog displaying a breakdown of completed ticket activity rates for a ticket.  This was previously set up in the LAN client using this query:

select 
    b.typedesc, 
    sum(a.elapsedunits) as hourtime 
from 
    ticketactivity a 
left join 
    ticketactivityrate b 
on 
    a.ratetypedesc=b.ticketactivityrateid 
where 
    a.ticketid='123456789012' 
group by b.typedesc

Basically, this query was used to create a recordset which was looped through to build a string that was then displayed in a Messagebox.

 

In order to implement this in the web, I added a button to the Ticket Detail quickform, and added a C# Code Snippet to the OnClick event of that button.

First of all, I created a reference to the ITicket interface for the current record.  I also used the repository helper to create a repository for Ticket Activity

 Sage.Entity.Interfaces.ITicket ticket = this.BindingSource.Current as Sage.Entity.Interfaces.ITicket;
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.ITicketActivity> repository = Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.ITicketActivity>();

Next, after setting a couple variables holding the TicketNumber and TicketID, I started building my Criteria for IRepository.    Using a Projectionlist, I added a sum of the ElapsedUnits property, and grouped by the RateTypeDesc property from the TicketActivity entity. (These properties need to be visible in the entity model, otherwise this will not work.  Out of the box, RateTypeDesc is not visible, so I had to modify the entity)

string sTicketNumber;
string sTicketID;
string sMessage;
sTicketNumber = ticket.TicketNumber.ToString();
sTicketID = ticket.Id.ToString();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.Eq("Ticket.Id", sTicketID));
criteria.SetProjection(repository.PF.ProjectionList().Add(repository.PF.Sum("ElapsedUnits")).Add(repository.PF.GroupProperty("RateTypeDesc")));
System.Collections.IList result = criteria.List();
if (result.Count == 0) 
{
    sMessage = "No Time logged against ticket " + sTicketNumber;
}
else
{
    sMessage = "Time summary for ticket " + sTicketNumber + "<br><br>";
}

As you can see, I get my result and check the count to see what message I want to display.  Finally, I loop through the IList to build my string.  Since RateTypeDesc holds an ID for the rate found in the TicketActivityRate entity, I use the GetByID function in the EntityFactory to return the actual Rate description.

foreach (IList i in result)
{
    if (Convert.ToDouble(i[0])!=0) 
    {
    Sage.Entity.Interfaces.ITicketActivityRate rate = Sage.Platform.EntityFactory.GetById<Sage.Entity.Interfaces.ITicketActivityRate>(i[1]);
    sMessage = sMessage + (rate == null ? "None Defined": rate.TypeDescription.ToString()) + " - " + Math.Round(Convert.ToDouble(i[0]), 2).ToString() + " hours<br>";
    }
}

Once I have my string,  I simply raise a ValidationException and pass the sMessage string variable.  I ended up using the ValidationException because it allows you to include HTML code, which I used to include breaks after each rate.

 throw new Sage.Platform.Application.ValidationException(sMessage);

 

That's all there was to it.  Ultimately, it ended up not being that difficult to accomplish once I started to understand Projections in IRepository. 

Thanks for reading!

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Editing Filters in Saleslogix Web Client 8.0
Is it possible to add or remove Saleslogix filters from the List view in Accounts?
Posted on Apr 08, 2014 by SalesLogix Support to SalesLogix Questions & Answers
 
   Bug in the Saleslogix Web LiveGrid Lookup dialog
I recently ran into an issue with the Opportunity Products grid in the Saleslogix web client.  I cli
Posted on Apr 04, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Web Client Controls are Missing
Every so often we hear that a user can no longer add products to an opportunity whether it is a new oppor
Posted on Mar 24, 2014 by Mark Duthoy to SalesLogix Support
 
   Major Problem in Saleslogix v8.1 Cloud Instances
I have been working with a new client who is on the hosted cloud solution for Saleslogix version 8.1.&nbs
Posted on Mar 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Setting the Defaults on the Insert Account/Contact Duplicate Record View
When creating a new Account/Contact record in Saleslogix web, you have the ability to check for duplicat
Posted on Mar 10, 2014 by Jason Buss to Jason Buss' Blog
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register