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 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
 
   Index of Posts for Starting with Infor CRM (Saleslogix) Development
The blogs on customerfx.com go all the way back to 2003. There's years and years worth of information
Posted on Dec 30, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Bug with Binding a Picklist Control in the Infor CRM (formerly Saleslogix) Web Client v8.1 Core Update 3b
I just recently discovered a bug when using a picklist control with the  StorageMode set to "ID
Posted on Dec 29, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Adding Filters to the Lookup Control at Runtime in the Infor CRM (formerly Saleslogix) Web Client
To add a new Lookup Pre-filter you can add code such as this on a quick form:LookupPreFilter preFilter =
Posted on Dec 22, 2014 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