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
   How do I fix a corrupt Picklist in Infor CRM version 8.1?
I had a user ask how to fix a corrupt picklist. The picklist did not show any values when trying to e
Posted on Feb 06, 2015 by Dale Richter to Infor CRM Questions & Answers
 
   Creating a Searchable & Filterable ComboBox in Infor CRM (Saleslogix) Web Client
I've had a few posts lately covering the ComboBox control in the Infor CRM (Saleslogix) Web Client. T
Posted on Feb 05, 2015 by Ryan Farley to Ryan Farley's Blog
 
   Limiting the Height of the Infor CRM (Saleslogix) ComboBox Popup
In my last post, I discussed how the Infor CRM (Saleslogix) ComboBox is a Dijit Select form widget. This
Posted on Jan 29, 2015 by Ryan Farley to Ryan Farley's Blog
 
   How to Disable the ComboBox Control in the Infor CRM (Saleslogix) Web Client
There's a problem with the ComboBox control in the Infor CRM (Saleslogix) Web Client. It won't di
Posted on Jan 22, 2015 by Ryan Farley to Ryan Farley's Blog
 
   Infor CRM (formerly Saleslogix) Web Client - How Time Zones Work
The Infor CRM web client utilizes  a couple of methods to determine and then present what timezones
Posted on Jan 09, 2015 by Kris Halsrud to Kris Halsrud's Blog
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2015 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