x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 75 subscribers
  • Views 713 views
  • Users 0 members are here
Browse Forums
Announcements
106
topics
32
replies
General Discussion
6k
topics
18k
replies
Michele Gaw

Compare database NULL to Empty Screen Value

SUGGESTED
Posted By

Help!  Please! :-)

I have a requirement where I need to add a record to another table if certain fields on the company record change.  I am using a table-level script to accomplish this.  However, I am completely open to a better way to do this, if any.

I have created a function to handle the record insert (addTKsyncCompRecord()).  Everything appears to be working as expected until I run across a field that has no value in it.  It appears that the system compares nothing to NULL and sees them as different.  Therefore, a record is added even though no change was actually made.

I am not sure how to get around this issue. 

Any suggestions would be greatly appreciated!!

This is an example of my table-level script.  This has been placed in the UpdateRecord area.  To make it easier to read, I have removed some of the fields to compare.

if(CRM.GetContextInfo('company','comp_name') != Values('comp_name'))
{
addTKsyncCompRecord()
}
else if (CRM.GetContextInfo('company','comp_ae1') != Values('comp_ae1'))
{
addTKsyncCompRecord()
}
else if (CRM.GetContextInfo('company','comp_ae2') != Values('comp_ae2'))  <<---failing here because there is no screen value and the database value is NULL
{
addTKsyncCompRecord()
}
else
{
//Do nothing
}

Again, any assistance would be greatly appreciated!

  • 0
    SUGGESTED

    I think I got this.  Posting my resolution in case someone else might find it helpful. 

    There might be a better way, but this is how I solved it...

    Updated the script related to comp_ae2 as follows....

    else if (CRM.GetContextInfo('company','comp_ae2') != Values('comp_ae2'))
    {
    //test for empty or null values
    var ae2data = CRM.GetContextInfo('company','comp_ae2');
    var ae2value = Values('comp_ae2');

    if (isNullv(ae2data) && isNullv(ae2value)) return;

    //if AE2 has been changed add TK record, otherwise, go to next field.
    addTKsyncCompRecord();
    }

    I will also go back and update ae1 with this as well to cover any possible empty or null values.

    Hope someone else might find this helpful!  :-)

  • 0

    Javascript is a bit strange I find. If you create a variable and don't assign a value to it, it will be undefined. For it to be null, it must be set to null. Null doesn't just happen. When I am not sure, what I tend to do is something like this:

    if(('' + myVar) == '')

    This then allows you to check for an empty string as you are prefixing the variable with an empty string. It might not be elegant and I'm sure someone will say it's bad practice, but it's always worked for me. Similarly in SQL, I often do

    if(isnull(myVar, '') <> '')

    when dealing with nulls or empty strings. I convert it to an empty string first then compare. It saves comparing it twice.

*Community Hub is the new name for Sage City