Hope somebody can help, seems like a simple problem.
I have a form based on table 1 with a lookup field based on table 2. Then I have a drop-down list on the same form based on table 4 which is inner joined to an intermediate n:m link table 3.
Everything works fine with existing records, since the drop-down picks up the FK in table 1 in the WHERE clause.
Code: Select all
SELECT t.transcript_id, t.trans_name
FROM transcript AS t
Inner join transcr_geneMMlink tr ON tr.transcr_idFK = t.transcript_id
WHERE tr.gene_idFK = '#pri_gene_idFK#'
So I tried som different sql statements in the function , but can't get the drop down field populated.
Example:
Code: Select all
function fetchGene() {
//build sql statement
$sql = "
SELECT t.transcript_id, t.trans_name
FROM transcript AS t
Inner join transcr_geneMMlink tr ON tr.transcr_idFK = t.transcript_id
Inner join gene g on g.gene_id = p.pri_gene_idFK
WHERE tr.gene_idFK = '#pri_gene_idFK#'
";
//run the query and fetch the results from the database
$q = nuRunQuery($sql);
$r = db_fetch_object($q);
return $r->trans_name;
}
When NuBuilder saves the record the fields are updated accordingly.
Tinka