dependent dropdown in a subform

[johan]

Kevin
I just suppose it because my second select stays empty.

Code: Select all

SELECT distinct secties.sec_id, secties.sec_name FROM secties where secties.sec_gevangenis =  '#VALUE_SELECT1#' 

Johan

[kev1n]

Are you also refreshing the 2nd select with nuRefreshSelectObject() ?

[johan]

Kevin
Yes
In custom code of select 1 I've set

Code: Select all

 nuSetProperty('VALUE_SELECT1',nuSubformValue(this, 'res_gevangenis'));
nuRefreshSelectObject('res_sectie');

My second select

Code: Select all

 SELECT distinct secties.sec_id, secties.sec_name FROM secties where secties.sec_gevangenis =  '#VALUE_SELECT1#'
 

Johan

[kev1n]

I noticed that nuRefreshSelectObject() does not work for subforms.

Update the nurefreshselectobject Procedure by running this sql in a db tool like phpMyAdmin:

Code: Select all

REPLACE INTO `zzzzsys_php` (`zzzzsys_php_id`, `sph_code`, `sph_description`, `sph_group`, `sph_php`, `sph_run`, `sph_zzzzsys_form_id`, `sph_system`, `sph_global`, `sph_hide`) VALUES ('nu5ff7efb1ed369a6', 'nurefreshselectobject', 'Refresh a select object', 'nubuilder', 'function nuGetSelectValues($formId, $selectId) {\r\n\r\n $sql = \"\r\n SELECT\r\n sob_select_sql\r\n FROM\r\n `zzzzsys_object`\r\n WHERE\r\n sob_all_zzzzsys_form_id = ? AND sob_all_id = ?\r\n \";\r\n\r\n $t = nuRunQuery($sql, array($formId, $selectId));\r\n\r\n $a = array();\r\n if (db_num_rows($t) == 1) {\r\n\r\n $r = db_fetch_row($t);\r\n if ($r != false) {\r\n \r\n $disS = nuReplaceHashVariables($r[0]);\r\n $t = nuRunQuery($disS);\r\n\r\n while ($row = db_fetch_row($t)) {\r\n $a[] = $row;\r\n }\r\n\r\n return json_encode($a);\r\n }\r\n\r\n }\r\n\r\n return false;\r\n\r\n}\r\n\r\nfunction nuPopulateSelectObject($formId, $selectId, $removeBlank, $prefix) {\r\n\r\n $j = nuGetSelectValues($formId, $selectId);\r\n\r\n $selectId = $prefix.$selectId;\r\n\r\n $cb = \"if (window.nuSelectObjectRefreshed) {\r\n nuSelectObjectRefreshed(\'$formId\', \'$selectId\', count);\r\n }\";\r\n\r\n if ($j == false) {\r\n \r\n return \"var count = -1; \".$cb;\r\n \r\n } else {\r\n \r\n return \"\r\n function nuPopulateSelectObject() {\r\n \r\n var p = $j;\r\n \r\n $(\'#$selectId\').empty();\r\n \r\n if (\'$removeBlank\' == \'0\' ) {\r\n $(\'#$selectId\').append(\'<option value=\\\"\\\"></option>\');\r\n }\r\n \r\n var count = 0;\r\n \r\n if (p != \'\') {\r\n var s = nuIsSaved();\r\n \r\n for (var i = 0; i < p.length; i++) {\r\n $(\'#$selectId\').append(\'<option value=\\\"\' + p[i][0] + \'\\\">\' + p[i][1] + \'</option>\');\r\n count ++;\r\n }\r\n \r\n if (s) { nuHasNotBeenEdited(); }\r\n \r\n }\r\n \r\n return count;\r\n }\r\n \r\n var count = nuPopulateSelectObject();\r\n\r\n \".$cb;\r\n }\r\n\r\n}\r\n\r\nfunction nuRefreshSelectObject($selectId, $formId, $removeBlank, $prefix) {\r\n\r\n if (hashCookieNotSetOrEmpty($formId)) {\r\n $formId = \'#form_id#\';\r\n }\r\n\r\n $prefix = hashCookieNotSetOrEmpty($prefix) ? \'\' : $prefix;\r\n\r\n $js = nuPopulateSelectObject($formId, $selectId, $removeBlank, $prefix);\r\n nuJavascriptCallback($js);\r\n\r\n}\r\n\r\nnuRefreshSelectObject(\'#nurefreshselectobject_selectid#\', \'#nurefreshselectobject_formid#\', \'#nurefreshselectobject_removeblank#\',\'#nurefreshselectobject_prefix#\');', 'hide', '', '1', '1', '')

Next, declare these function in your mainform's custom code:

Code: Select all

function nuSubformRefreshSelectObject(prefix, selectId, formId, removeBlank) {

	if (typeof formId === 'undefined') {
		var formId = '';
	}

	nuSetProperty('nurefreshselectobject_prefix',prefix);
	nuSetProperty('nurefreshselectobject_selectid',selectId);
	nuSetProperty('nurefreshselectobject_formid',formId);
	nuSetProperty('nurefreshselectobject_removeblank',removeBlank === true ? '1' : '0');

	nuRunPHPHidden('nurefreshselectobject', 0);

}

function nuSubformPrefix(t, id){
    return  $(t).attr('data-nu-prefix');
}

To refresh the select, call this code:

Code: Select all

nuSetProperty('VALUE_SELECT1',nuSubformValue(this, 'res_gevangenis'));
nuSubformRefreshSelectObject(nuSubformPrefix(this, 'res_gevangenis'),  'res_sectie', 'ENTER_THE_ID_OF_THE_SUBFORM_HERE'); // <----Replace with the the subform's primary key (15-characters unique id)

[johan]

Kev1n
This works fine. I can set hash value and use it in my 2nd select.
When I reopen my form all sectie are empty
Probably because the hash value isn't set at that moment.
Can I set hashvalue when I open editform?
Johan

[kev1n]

It's going to get a bit more complicated...

In your form's custom code:

Code: Select all

function nuSubformRefreshSelectObject(prefix, selectId, formId, removeBlank) {

   if (typeof formId === 'undefined') {
      var formId = '';
   }

   nuSetProperty('nurefreshselectobject_prefix',prefix);
   nuSetProperty('nurefreshselectobject_selectid',selectId);
   nuSetProperty('nurefreshselectobject_formid',formId);
   nuSetProperty('nurefreshselectobject_removeblank',removeBlank === true ? '1' : '0');

   nuRunPHPHidden('nurefreshselectobject', 0);

}

function nuSubformPrefix(t, id){
    return  $(t).attr('data-nu-prefix');
}

function res_gevangenisOnChanged(t) {
   nuSetProperty('VALUE_SELECT1',nuSubformValue(t, 'res_gevangenis'));
   nuSubformRefreshSelectObject(nuSubformPrefix(t, 'res_gevangenis'),  'res_sectie', '6194a0e97cca406'); // <---- Replace with the the subform's primary key (15-characters unique id)
  
   let res_sectie = t.id.replace('res_gevangenis','res_sectie');
   $('#'+ res_sectie).nuEnable(); 
}

$('[id^=subform][id$=res_sectie]').each(function(i,v){  // <---- replace 'subform' with the object id of your subform
   $(this).attr('data-value', this.value);
});

$('[id^=subform][id $=res_gevangenis]').each(function(i,v){ // <---- replace 'subform' with the object id of your subform
   res_gevangenisOnChanged(this) 
});

function nuBeforeSave() {
  nuSetProperty('VALUE_SELECT1','##');
  return true;
}

function nuSelectObjectRefreshed(formId, selectId, count) {
   let res_sectie = selectId.replace('res_gevangenis','res_sectie');
   $('#'+ res_sectie).val($('#' + res_sectie).attr('data-value'));
}

In res_gevangenis's onchange event:

Code: Select all

res_gevangenisOnChanged(this);

WHERE clause of res_sectie:

Code: Select all

WHERE secties.sec_gevangenis = '#VALUE_SELECT1#' 
   OR '#VALUE_SELECT1#' LIKE '#%';

[johan]

Kev1n

I've tested this but I still don't get gevangenis and sectie in every row when I open an editform.
I only get the first gevangenis and can't choose a section.
Any idea what i'm doing wrong?

[kev1n]

Could you upload the form's sql and used table schema here?

[johan]

Kev1N

THis is the sql of my form - activiteiten

Code: Select all

SELECT
 activiteiten.act_naam,
    activiteiten.act_start

FROM
    activiteiten
inner join frequentie on activiteiten.act_ritme = frequentie.fre_code

Dump of my db in attachment.

Johan

[kev1n]

Form's Custom Code:

I replaced "subform" with "planning" and inserted the correct subform's primary key "60f57210eaaa6d7"

Code: Select all

function nuSubformRefreshSelectObject(prefix, selectId, formId, removeBlank) {

   if (typeof formId === 'undefined') {
      var formId = '';
   }

   nuSetProperty('nurefreshselectobject_prefix',prefix);
   nuSetProperty('nurefreshselectobject_selectid',selectId);
   nuSetProperty('nurefreshselectobject_formid',formId);
   nuSetProperty('nurefreshselectobject_removeblank',removeBlank === true ? '1' : '0');

   nuRunPHPHidden('nurefreshselectobject', 0);

}

function nuSubformPrefix(t, id){
    return  $(t).attr('data-nu-prefix');
}

function res_gevangenisOnChanged(t) {
   nuSetProperty('VALUE_SELECT1',nuSubformValue(t, 'res_gevangenis'));
   nuSubformRefreshSelectObject(nuSubformPrefix(t, 'res_gevangenis'),  'res_sectie', '60f57210eaaa6d7'); // <---- Replace with the the subform's primary key (15-characters unique id)
 
   let res_sectie = t.id.replace('res_gevangenis','res_sectie');
   $('#'+ res_sectie).nuEnable();
}

$('[id^=planning][id$=res_sectie]').each(function(i,v){  // <---- replace 'subform' with the object id of your subform
   $(this).attr('data-value', this.value);
});

$('[id^=planning][id $=res_gevangenis]').each(function(i,v){ // <---- replace 'subform' with the object id of your subform
   res_gevangenisOnChanged(this)
});

function nuBeforeSave() {
  nuSetProperty('VALUE_SELECT1','##');
  return true;
}

function nuSelectObjectRefreshed(formId, selectId, count) {
   let res_sectie = selectId.replace('res_gevangenis','res_sectie');
   $('#'+ res_sectie).val($('#' + res_sectie).attr('data-value'));
}

res_gevangenis: onchange event code should look like this:

2021-11-22_125927.png