Set Select Object Initial value before nuRefreshObject nuOnLoad

[mvdm]

Hi,

I am trying to apply the initial value for a Select Object that is not associated to the form's table. I can manage to retrieve and apply the value, but I guess its happening too late.

I have a Select Object (t1_id) that is part of a Dependent Dropdown Entry / Edit form to filter the Options List in the consequent Select Object (t2_id) on the same form (frmT3). t2_id is actually read/written in the form's table (tblT3), where as t1_id is recorded on a different table (tblT2).

So, to get an initial value into t1_id, I have to run a query before the form opens.

frmT3 > t1_id > Select (SQL):

Code: Select all

SELECT
 tblT1.tblT1_id,
    CONCAT(t1_name,'|',t2_label)
FROM
    tblT1

frmT3 > t2_id > Select (SQL):

Code: Select all

SELECT
 tblT2.tblT2_id,
    CONCAT(tblT2.t2_name,'|',tblT2.t3_label')
FROM
    tblT2
WHERE
    tblT2.t1_id = '#t1_id#'

frmT3 > Custom Code > Before Edit:

Code: Select all

$s = "SELECT tblT1_id FROM tblT1 JOIN tblT2 ON t1_id = tbT1_id JOIN tblT3 ON t2_id = tblT2_id WHERE tblT3_id = '#RECORD_ID#'";
$t  = nuRunQuery($s, array('#LOOKUP_RECORD_ID#'));

if (db_num_rows($t) == 1) {
    $r  = db_fetch_object($t);
    $j = "nuSetValue('t1_id','$r->tblT1_id');nuRefreshSelectObject('t2_id');";
    nuAddJavascript($j);
}

The problem I am facing is that the t2_id Select Object is blank and there is no options in the list. Also the save button becomes red, so it seems to think something on the form was changed. However if I refresh the page then the t2_id object value appears and there are options in the list.

I have tried nuSetFormValue('t1_id','$r->tblT1_id); but unfortunately it does no work, but I guess it is meant for lookups only.
My guess is that nuAddJavascript is being applied too late, and that I need to apply the initial or selected="selected" from PHP?

Any help or advise would be greatly appreciated.

[kev1n]

How do you refresh t2_id when the selected value of t1_id changes?

[mvdm]

Initially I didn't refresh t2_id, I guess I hoped it was possible to do before each of the objects would initialize.
So I continued to play around with it and ended up with:

Code: Select all

$s = "SELECT tblT1_id FROM tblT1 JOIN tblT2 ON t1_id = tblT1_id JOIN tblT3 ON t2_id = tblT2_id WHERE tblT3_id = '#RECORD_ID#'";
$t  = nuRunQuery($s, array('#LOOKUP_RECORD_ID#'));

if (db_num_rows($t) == 1) {
    $j = "document.getElementById('t1_id').value = '$r->tblT1_id';nuRefreshSelectObject('t2_id');";
    nuAddJavascript($j);
}

This would set the value of t1_id and refresh the Select Object t2_id without the form thinking that changes were made.
So when the form loads now there are Options in Select Object t2_id, but it shows up as blank.

[kev1n]

Is t2_id refreshed if you call nuRefreshSelectObject('t2_id') after setting t1_id's value?

[mvdm]

Yes, in this case it refreshes the option list, but the value is blank.
If I remove the refresh then there are no options and the value is blank.

[kev1n]

Do you have the possibility to upload a minimal reproducible example (MRE) ? Either create an example in another db and upload a dump here or use the cloner do dump your current form's sql here or send it to me by private message

[mvdm]

Hi Kev1n,

Thank you for taking the time to check it. Please see the attached dump file.

[kev1n]

The sql is faulty and truncated at the end. And I would also need a dump of the 3 tables with some sample values in it

[mvdm]

Hi Kev1n,

I have tried to recreate it with data, the behavior is the same. But just incase there is something changed to avoid confusion I exported the form dump files again and uploaded them. I have also uploaded the SQL dumps from phpMyAdmin with data.
Please let me know if there is anything else that I can do.

Thanks again for your help.

[kev1n]

nuDebug Results shows an error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

===SQL===========

SELECT tblT1_id FROM tblT1 JOIN tblT2 ON t1_id = tblT1_id JOIN tblT3 ON t2_id = tblT2_id WHERE tblT3_id = '625ab6ce6f2219d'

===BACK TRACE====

The parameter '#LOOKUP_RECORD_ID#' is not used in your query. Remove it?

Code: Select all

$s = "SELECT tblT1_id FROM tblT1 JOIN tblT2 ON t1_id = tblT1_id JOIN tblT3 ON t2_id = tblT2_id WHERE tblT3_id = '#RECORD_ID#'";
$t  = nuRunQuery($s, array('#LOOKUP_RECORD_ID#'));