Finding shared records in CRM

To find all the records(say lead)  shared with any particular user we can make use of following query

It will return us all records(lead) shared with the user directly( i.e. through Action–>Sharing and user)

select  fl.subject

from
PrincipalObjectAccess poa , FilteredLead fl, FilteredSystemUser fsu
where
poa.ObjectTypeCode = 4
and poa.ObjectId = fl.leadid
and poa.PrincipalId = fsu.systemuserid
and fsu.domainname=SYSTEM_USER

// fsu.fullname=’name of user’

And to get the records shared with the user indirectly (i.e. through Action–>Sharing and Team (user belongs to that team)

select  fl.subject
from 
FilteredLead fl,  PrincipalObjectAccess poa,  FilteredTeam ft
where
poa.ObjectTypeCode = 4 

and poa.ObjectId =fl.leadid
and poa.PrincipalId = ft.teamid and 

fl.owneridname not in (select fullname from filteredsystemuser
where domainname=SYSTEM_USER)
and
ft.teamid  in (select ft.teamid from filteredteammembership fm
, filteredsystemuser fsu, filteredteam ft
where fm.systemuserid=fsu.systemuserid and
ft.teamid=fm.teamid
and fsu.domainname=SYSTEM_USER
)

Bye

Making value in one lookup depended on value of another lookup.

Making value in one lookup depended on value of another lookup in Microsoft CRM. Say we have created 2 custom entities Categories and Skill. Skill entity has category lookup field which relates it to that category.(Added a relationship between them)Than created few records for both the entities. After that we have added that entities to the third entity where they’ll appear as LookUp.

Now we want the value of Skill lookup to be dependent value selected on Resource(Category) LookUp.

untitled1.JPG

Resource can have following values

untitled2.JPG

Now if Datawarehousing is selected than

we want to show Analysis and SSAS in the Skill lookup

untitled3.JPG

To accomplish this we will write the following code in the OnChange event of Resource Lookup

// Making the skill id lookup null whenver a new values is selected for reesource

crmForm.all.new_skillid.DataValue=null;

crmForm.all.new_skillid.disabled = false;

// getting the value of selected resource

var ar=new Array();

ar=crmForm.all.new_resourceid.DataValue;

var rID = ar[0].id;

// Changing the display of skillid lookup so that only skill belonging to particular rID should appear

crmForm.all.new_skillid.lookupbrowse=1;

// in the additionalparams for the skill id attribute we will specify the fetch xml query // e.g. select * from new_categoryskill (name of our custom skill entity) where new_categoryofskillid = rid

crmForm.all.new_skillid.additionalparams = “fetchXml=<fetch mapping=’logical’ ><entity name=’new_categoryskill’><all-attributes/><filter type=’and’><condition attribute=’new_categoryofskillid’ operator=’eq’ value='” + rID + “‘/></filter></entity></fetch>”;

To get the fetchXml query what we can do over here is

http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

We will go to our skill entity form and run an advancedfind query against it (giving the appropriate condition for which we want the fetch xml)

Now pressing CTRL+N open it in a new window Now in URl replace everything with the following line

javascript:prompt(“my%20query:”,%20resultRender.FetchXml.value);

Press Ok to the dialog that appears and it opens a prompt window from where we can copy our fetch xml and modify it accordingly.


Using Left and CharIndex in Oracle

Well i was given the task to get the email id’s of the user which was stored in one of our oracle db table.

But the problem was that we wanted that part of emailid which  appears before ‘@’ .

Well coming from SQL Server background i thought it could be acheived using CharIndex and Left Function.

Let’s see what they do

Select Left(‘abcdef’,3)

-> abc

and

select charindex(‘c’,’abcde’)

-> 3

But than as expected there were no functions like charindex and left in Oracle.

After searching i finally managed to found the solution

Inplace of CharIndex we have instr function

select instr(‘ab’,’b’) from dual;

->2

and for left and also right we have

SUBSTR (`ABCDEF’,-5); //Right(..)

SUBSTR (`ABCDEF’,1,5); // Left(…

So finally the query was

substr(emailid,1,Instr(Emailid,’@’)-1)

-1 is used otherwise @ will also come along

And one more thing, to extract username portion from login name i.e  nishantr1 from

abccompany\nishantr1 we could write something as following

SUBSTRING(loginname ,charindex(‘\’,loginname)+1, len(loginname)) for sql server.


Bye

Using ISV.config to access field values in CRM

First of all open your isv.config file ( found at <installation path\_Resources\isv.config e.g. C:\Program Files\Microsoft CRM\CRMWeb\_Resources”

Here we are adding buttons in the form toolbar for opportunity, inside the isv.config file (Plzz take it’s backup)

Add the following

<Entity name=”opportunity”>

<ToolBar ValidForCreate=”0″ ValidForUpdate=”1″>

<Button Title=”MyBtn1″ Icon=”/_imgs/ico_18_debug.gif”

Url=”http://default.aspx” PassParams=”1″ WinParams=”” WinMode=”0″ AvailableOffline=”false” />

<Button Title=”my button” ToolTip=”Advanced Mail Merge” Icon=”url” Client=”Web, Outlook”

JavaScript=

var n=crmForm.all.name.DataValue;

var objectID=crmForm.ObjectId;

window.open(‘http://Default.aspx?name=’+n+’&oId=’+objectID)”

/>

</ToolBar>

</Entity>

In the above code we have created two button.

First button makes use of URL and passParam attribute.

When we set passParam as 1 then objectid( id of the record), object name and object type code are passed to url specified as query string.

The thing here is that in our custom web page we can only get the id and the type of entity.

What if we want values of other form fields.

Well in this case we can make use of JavaScript attribute.This attribute will override the Url attribute if in case both of them are defined.In the above code we are attaching the name value of opportunity in the query string.

One more thing to get the current record’s primary key i.e. say opportunity id for opportunity we’ll use crmForm.ObjectId

We can even user window.opener object in javascript of our isv custom page.

Chk this

https://nishantrana.wordpress.com/2007/10/13/refresh-crm-form-from-third-party-applications/

Bye

Finding Lead records that have an associated activity with them

Here in the query one more condition is added which is when the Rating is Hot. 

select leadqualitycodename,* from filteredlead As fl inner join dbo.FilteredActivityParty as fap
ON fl.leadid=fap.partyid
and fl.leadid=‘yourleadid’ and leadqualitycodename=‘Hot’ 

Events in C#

We’ll take a simple and useful scenario to make our understanding of Events as clear as possible.
First of all copy and paste the code below. We’ll try understanding the code than.

using System;
public class ResultDeclarationEventArgs : EventArgs
{
private string message;
public ResultDeclarationEventArgs(string message)
{
this.message = message;
}
public string Messsage
{
get
{
return message;
}
}
}
public delegate void ResultHandler(object sender, ResultDeclarationEventArgs e);
public class Kid
{
private int percentage;
public event ResultHandler onResultDeclaration;
public int Percentage
{
set
{
percentage = value;
if(percentage > 80)
{
ResultDeclarationEventArgs myREA = new ResultDeclarationEventArgs( ” I got Distinction”);
onResultDeclaration(this, myREA);
}
if(percentage > 60 && percentage < 80)
{
ResultDeclarationEventArgs myREA = new ResultDeclarationEventArgs(” I got FirstClass”);
onResultDeclaration(this, myREA);
}
if(percentage > 40 && percentage < 60)
{
ResultDeclarationEventArgs myREA = new ResultDeclarationEventArgs(” I got SecondClass”);
onResultDeclaration(this, myREA);
}
if(percentage < 40)
{
ResultDeclarationEventArgs myREA = new ResultDeclarationEventArgs(” Sorry I failed “);
onResultDeclaration(this, myREA);
}
}
}
}
public class Mom
{
public Mom(Kid myKid)
{
myKid.onResultDeclaration += new ResultHandler(GiveMsgToMom);
}
void GiveMsgToMom(object sender, ResultDeclarationEventArgs e)
{
Console.WriteLine(e.Messsage);
}
}

public class Dad
{
public Dad(Kid myKid)
{
myKid.onResultDeclaration += new ResultHandler(GiveMsgToDad);
}
void GiveMsgToDad(object sender, ResultDeclarationEventArgs e)
{
Console.WriteLine(e.Messsage);
}
}

class myDemo
{
public static void Main()
{
Kid kid = new Kid();
Mom mom = new Mom(kid);
Dad dad = new Dad(kid);
Console.WriteLine(“Please enter the percentage of the Kid”);
string percentage = Console.ReadLine();
kid.Percentage = Convert.ToInt32(percentage);
}
}

The scenario is something like this First there is a class named Kid. It has a field called Percentage and declares an event OnResultDeclaration. It is of type ResultHandler. ResultHandler is a delegate. public delegate void ResultHandler(object sender, ResultDeclarationEventArgs e);
It can point or refer to any method which has return type void and takes as parameter an object and ResultDeclarationEventArgs.
Object will give the name of the sender who has called this eventhandler and ResultDeclarationEventArgs is to pass any information regarding the event.

ResultDeclarationEventArgs is a class that derives from EventArgs class. It has a message field which will contain the message the Kid class, which has the event OnResultDeclaration, would like to convey to the class (e.g. Mom, Dad) which are interested in that event.
For this reason, we have our event handlers(method) inside Mom and Dad class which have the same signature as defined by the event.
void GiveMsgToMom(object sender, ResultDeclarationEventArgs e)
void GiveMsgToDad(object sender, ResultDeclarationEventArgs e)
Than we register these eventHandler to the events.
myKid.onResultDeclaration += new ResultHandler(GiveMsgToDad);
myKid.onResultDeclaration += new ResultHandler(GiveMsgToDad);

Kid class will check for the percentage passed in and accordingly set’s the message.
Whatever message is set is than displayed in the Event handler inside Mom and Dad class using e.Message