x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 2 replies
  • Subscribers 77 subscribers
  • Views 156 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Matthew Shaw

Using a SQL Search in a Variable

Posted By

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";
}

  • 0

    Hi Mattew

    Try This. Putting this on the create script would show yo an error message on the top of the screen.

    var Pid = CRM.GetContextInfo("Cases","case_primarypersonid");
    if (Pid != 'undefined' && Pid !='' && Pid !='0'){ //Check PId has been defined
       Query= CRM.CreateQueryObj("SELECT isnull(pers_emailaddress, 'NoEmail') as pers_email FROM vPersonPE WHERE pers_personid ="+Pid");
       Query.SelectSql();
       if (!Query.eof){ //person found
          if (Query.pers_email == 'NoEmail'){
             ErrorStr="Note: Person is missing an email address";
          }
       }
    }

  • 0

    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;
}

*Community Hub is the new name for Sage City