nuLookup and null

[alf1976]

i Have a field that uses a nulookup object. In the database this field can be null.

If i don't alter this field on a form and save, the value in the database stays as null.

If i select a value and save the database, it updates correctly.

But if then go into the nulookup and delete all the text from the lookup object, so it is blank and then save, the value in the database is 0 and not null.

is there anyway i can alter this. i have tried adding a before save JavaScript event to try and catch it

Code: Select all

function nuBeforeSave()
{
    if ($('#CompanyMainContactID').val()=='')
    {
        $('#CompanyMainContactID').val(null);
        $('#CompanyMainContactID').change();
    }
    
    return true;
    
}

But it still saves 0 into the database

[admin]

Andrew,

Have you tried this with other Lookups?

Is it being saved to a varchar (not a number)?

It doesn't happen for me.

See if you can tell me how to duplicate it.

Steven

[alf1976]

Hi Steven,

On the database it is an int field which can be stored as null

database.jpg

i have tried the same with a couple of other lookups. They all behave the same way.

screen.jpg

So if i highlight the text in the lookup field, delete it out, the lookup goes blank, i then click save and it stores a value of zero into the table. i want it store to store null as effectively there is no contact is selected.

If it helps any...
When a new record is added CompanyMainContactID and CompanyMainSiteID store Null in the database (but these cannot be accessed on the form when it is a new unsaved record as i use nuDisable on them).


Andrew

[admin]

Andrew,

I'm not sure why you feel this is a problem for a foreign field.

Unless there is a record in the foreign table that is 0. (which is unlikely if the primary key of the foreign table has an auto-incrementing id)

It should just ignore any joins you make. (the same way a null foreign key would)

Steven

[toms]

Andrew,

As Steven has already pointed out, it should normally not cause any issues if the value 0 is stored.
However, if for some reason you would like the value not to be saved as 0, you can run a query in "After Save" (PHP) to set its value to null if the field is empty.

(untested but should work)

Code: Select all

$fieldID = "#CompanyMainContactID#"; // lookup field to update
$table = "company_table";   // <---- change the table name
$pk = "CompanyID"; // primary key
$recordID = "#RECORD_ID#"; // record id
		
if ("#RECORD_ID#" != "-1" && $fieldID == "") { // if field id is empty
	$sql = "UPDATE $table SET $fieldID = null WHERE $pk = '$recordID' ";
	nuRunQuery($sql);
}

[alf1976]

Thanks for the responses.
Its just a personal preference i prefer to use null in these circumstances.
I used Toms suggestion.

[admin]

Cool.