Default Value nuBuilder 3 -> nuBuilder 4.5

[mikevb]

I try to migrate an application from nuBuilder 3 to nuBuilder 4.5.
In nuBuilder 3 every object hat a field "Default Value" where I was using various select statements to get information from various tables from the db and use it as a default value in the edit form when adding a new record.

In nuBuilder 4.5: how would I be able to use a complex SQL select statement with data from the database and use the result as a default value for a field in an edit form? It was very easy in nuBuilder 3, but I didn't find an example for nuBuilder 4.5
I would appreciate any help. Thank you!

[kev1n]

Hi,

In nuBuilder 4.5 you'd use the PHP BE (Before Edit) event to run SQL statements and to pass their results to the form/objects.

Example:

Code: Select all

function getDBValue($sql) {
    $t = nuRunQuery($sql);
    if (db_num_rows($t) == 1) {
        $r = db_fetch_row($t);
        return base64_encode(json_encode($r[0]));
    }
    else {
        return '';
    }
}

$field1Value = getDBValue('SELECT * FROM your_table WHERE ....');
$field2Value = getDBValue('SELECT * FROM your_table WHERE ....');

$js = " 

		nuSetValue('field1', JSON.parse(atob('$field1Value')));
		nuSetValue('field2', JSON.parse(atob('$field2Value')));
		nuHasBeenEdited();
   
	";

nuAddJavascript($js);

Replace field1, field2 with the Object Ids.

[mikevb]

Hi Kevin
Thank you. This script works partly, but not as expected.

The fields in the form are populated with the default values from the select statements. That part is perfect.
If I save the form, the values in the fields are not saved to the database and nuHasBeenEdited() keeps the “Save” button red.
When I try to manually overwrite a default value, after saving the form the value is reset to the default value
When I edit a saved form, the default values are shown, but the corresponding fields in the database are empty and changed values are reset to default values and are not saved.

Somehow this PHP code is not sticking to “Before Edit”. It’s changing values after edit, too and prevents from saving these values to the database.

Values of other fields that are not mentioned in the PHP are saved as expected.

[kev1n]

Are you on the latest 4.5. version?
Otherwise, call the change() method of the object

Code: Select all

$('#field1').change();

after setting the value with nuSetValue()

[mikevb]

Thank you Kevin, that helps a bit further.

Now default values are populated, I can change these values and save them into the database successfully.
Unfortunately right after clicking the save button, the changed and saved values are immediately overwritten by the default values and the save-button turns red again.
Also when I browse and open an existing record: these fields always show the default values and not what's saved in the database.

I think I saw somewhere else in the forum that I have to check first if the field is empty, before I set the default value. Once I'll get it to work, I'll post the final solution below.

Does the latest version have some new functions built in that would help with that? I'm on V.4.5-2021.04.15.00 (Files and DB). Before I can upgrade I have to test and make sure I don't break things.
Cheers Mike

[kev1n]

Only call nuAddJavascript($js) if it's a new record:

https://wiki.nubuilder.cloud/ ... NoRecordID

[mikevb]

YESS that does the trick. Thank you Kevin for your help!

If someone else needs default values generated from SQL select statements: the following PHP code works fine for me in nuBuilder 4.5.
Replace it with your select statements and your corresponding object ID's and copy it into Form Properties -> Custom Code -> BE Before Edit.

Code: Select all

if(nuHasNoRecordID()){

	function getDBValue($sql) {
		$t = nuRunQuery($sql);
		if (db_num_rows($t) == 1) {
			$r = db_fetch_row($t);
			return base64_encode(json_encode($r[0]));
		}
		else {
			return '';
		}
	}

	$field1Value = getDBValue('SELECT some_field1 FROM some_table1');
	$field2Value = getDBValue('SELECT some_field2 FROM some_table2');

	$js = " 
		  nuSetValue('your_object_id1', JSON.parse(atob('$field1Value')));
		  $('#your_object_id1').change();
		  nuSetValue('your_object_id2', JSON.parse(atob('$field2Value')));
		  $('#your_object_id2').change();     
	   ";

    nuAddJavascript($js);
}

[kev1n]

As of V.4.5-2021.07.06.00, nuSetValue() already calls the change() method. Therefore $('#your_object_id1').change(); shouldn't be necessary in your code.