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
   Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix)
I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant
Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Manipulating the DependencyLookup Control in the Infor CRM Web Client (Formerly Saleslogix)
 The Dependency Lookup control in the Saleslogix web client is what is used for the hierarchical Are
Posted on Nov 14, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Infor CRM (formerly Saleslogix) Statement of Direction
Learn more about the future of Infor CRM. Recently I received my login information for the Infor Partn
Posted on Nov 13, 2014 by Brianna Ojard to The Inbox
 
   Adding a report link to the Common Tasks pane in Infor CRM (formerly Saleslogix)
When you are viewing an Entity Detail page, the Common Tasks pane on the right of the page provides a nu
Posted on Nov 12, 2014 by Jason Buss to Jason Buss' Blog
 
   How to Sum a Collection using Hibernate Criteria and Projections
 To sum the results of a criteria collection you can use code such as the following, which gets the
Posted on Nov 07, 2014 by Kris Halsrud to Kris Halsrud's 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