Page 1 of 1
nuLookup and null
Posted: Sat Feb 03, 2018 12:56 pm
by 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
Re: nuLookup and null
Posted: Sat Feb 03, 2018 3:11 pm
by 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
Re: nuLookup and null
Posted: Sat Feb 03, 2018 10:59 pm
by 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
Re: nuLookup and null
Posted: Sun Feb 04, 2018 1:55 am
by 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
Re: nuLookup and null
Posted: Sun Feb 04, 2018 10:32 am
by 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);
}
Re: nuLookup and null
Posted: Sun Feb 04, 2018 12:25 pm
by alf1976
Thanks for the responses.
Its just a personal preference i prefer to use null in these circumstances.
I used Toms suggestion.
Re: nuLookup and null
Posted: Mon Feb 05, 2018 12:03 am
by admin
Cool.