8/18/2025 4:27:43 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss the use of the SalesLogix Web Platform, Client and Customer Portals, and the Application Architect (For version 7.2 and higher only). View the code of conduct for posting guidelines.
|
|
|
|
Business Rule with Custom SQL Query
Posted: 09 Apr 09 2:10 PM
|
Hi, everyone.
I'm trying to do a Business Rule Method to use to filter a grid, but i dont know how to build this query:
Select C.CampaignName as Campaign,
C.Status,
C.StartDate as [Start Date],
C.EndDate as [End Date],
(Select Count(Distinct(EntityId)) From Campaign C Inner Join CampaignTarget ct
On c.CampaignId = ct.CampaignId Inner Join Contact con On con.ContactId = ct.EntityId
Where con.AccountId = 'A6UJ9A000000') as [# Contacts Targeted],
'' as [# Contacts w/Trans],
(Select Count(Distinct(ProductId)) From Campaign C Inner Join CampaignProduct cp
On c.CampaignId = cp.CampaignId Inner Join Contact con On con.ContactId = ct.EntityId
Where con.AccountId = 'A6UJ9A000000') as Products,
'' as [# Transactions],
'' as Sales
From Campaign C Inner Join CampaignTarget ct
On C.CampaignId = ct.CampaignId
Where ct.EntityId = 'A6UJ9A000000'
Any help would be great.
Thanks |
|
|
|
Re: Business Rule with Custom SQL Query
Posted: 13 Apr 09 11:23 AM
|
Anyone? is this posible?
To add more info: what i need is a grid on a tab the show the result of this query.
Is there any other way to do it?
Thanks |
|
|
|
Re: Business Rule with Custom SQL Query
Posted: 14 Apr 09 7:41 AM
|
| Could create a view from that SQL (minus the where entityid = xxxx)? You should be able to base a grid on a view (once you enable it via admin). I don't know if you could then filter it, though. Not sure if this will help or not. |
|
|
|
Re: Business Rule with Custom SQL Query
Posted: 16 Apr 09 9:00 AM
|
OK, this is my method:
#region Usings
using System;
using System.Collections;
using System.Collections.Generic;
using Sage.Platform;
using Sage.Platform.Repository;
using Sage.Entity.Interfaces;
using Sage.Form.Interfaces;
#endregion Usings
namespace Sage.BusinessRules.CodeSnippets
{
public static partial class AccountBusinessRules
{
public static void GetCampaigns(IAccount account, out IList(ICampaign) result)
{
Sage.Platform.Repository.IRepository(ICampaign) rep = EntityFactory.GetRepository(ICampaign)();
Sage.Platform.Repository.IQueryable qry = (IQueryable)rep;
Sage.Platform.Repository.IExpressionFactory ef = qry.GetExpressionFactory();
Sage.Platform.Repository.IProjections proj = qry.GetProjectionsFactory();
IList(ICampaign) campaignList = qry.CreateCriteria().SetProjection(proj.ProjectionList().Add(proj.Property("CampaignName")).Add(proj.Property("StartDate")).Add(proj.Property("EndDate")).Add(proj.Property("Status"))).List(ICampaign)();
result = campaignList;
}
}
}
NOTE: on IList(Type) i have to use () on the post because the other sign desapear.
I've been able to load the tab without errors but now i'm not having any records. I dont know what i'm missing. A good tutorial will be great. Or maybe another CRM :P |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|