Using a SQL Search in a Variable

Posted By Matthew Shaw 1 month ago

Hi All,

I'm trying to create a dynamic prompt for users. That when going to a Case record it would show if the contact doesn't have an email address. I'm trying to use something like the following on the Person field on the Cases screen.

I know how to get a value from the current context, I just don't know how to use that in a SQL query (or any query) and return back another value for checking. The below, whilst horrible wrong gives you an idea of what I'm trying.

Does anyone know how to correct this?

var Pid = CRM.GetContextInfo("Cases","case_primarypersonid");
var PEmail = SearchSQL("SELECT pers_emailaddress FROM vPersonPE WHERE pers_personid ="+Pid);

if(!PEmail);
{
ErrorStr="Note: Person is missing an email address";
}

Parents

0 James Hill 1 month ago

I ended up with the following - slightly different ways of going about the same thing:

var Pid = CRM.GetContextInfo("Cases", "case_primarypersonid");
 
if (!isNaN(parseInt(Pid, 10)) && parseInt(Pid, 10) > 0)
{
	var PEmail = CRM.CreateQueryObj("SELECT pers_emailaddress FROM vPersonPE WHERE pers_personid = " + Pid);
 
	PEmail.SelectSql();
 
	if (!PEmail.Eof)
	{
		if(String(PEmail.FieldValue("pers_emailaddress")) === "undefined")
		{
			ErrorStr = "Note: Person is missing an email address";
		}
	}
 
	PEmail = null;
}

Reply

0 James Hill 1 month ago

I ended up with the following - slightly different ways of going about the same thing:

var Pid = CRM.GetContextInfo("Cases", "case_primarypersonid");
 
if (!isNaN(parseInt(Pid, 10)) && parseInt(Pid, 10) > 0)
{
	var PEmail = CRM.CreateQueryObj("SELECT pers_emailaddress FROM vPersonPE WHERE pers_personid = " + Pid);
 
	PEmail.SelectSql();
 
	if (!PEmail.Eof)
	{
		if(String(PEmail.FieldValue("pers_emailaddress")) === "undefined")
		{
			ErrorStr = "Note: Person is missing an email address";
		}
	}
 
	PEmail = null;
}

Children

No Data

Sage CRM

Using a SQL Search in a Variable

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING