Update drop-down field from lookup result on the same form

[Tinka]

Hi

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#'

But when creating a new record, I would like to populate the drow-down field with the result from the lookup. I have put a php function on the "update other fields" -like you explain in the documentation on lookups -, but the problem is that the FK in table 1 first is created when the record is saved.

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;
}

Somehow I need to pick up the result from the lookup, is that done in another hash variable or can I use some of the fields from the lookup table 2.
When NuBuilder saves the record the fields are updated accordingly.

Tinka

[admin]

Tinka,

Are you able to use Update Other Fields to do what you want?

Capture.PNG (25.21 KiB) Viewed 3698 times

Steven

[Tinka]

Yes, this is where I put the php function.
The problem lies in that the foreign key variable (#pri_gene_idFK#) is not created in the record before the new record is saved. So how can I pick up the value of it before save and populate the drop-down?

When using the "Update other fields" with values from the table 1 (which the form is based on) on e.g. text fields it works fine. So maybe it is harder to update a drop-down field?

[Tinka]

Well, I got one step further:
I found out that the Lookup PHP function only accepts #RECORD_ID# as a hash variable, and now my function updates a text field with the right value after the lookup field has been populated.

But it won't update/filter the drop-down field. How can I do that?

The PHP function looks like that:

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 = tr.gene_idFK
		
	
	WHERE tr.gene_idFK = '#RECORD_ID#'
    ";

    //run the query and fetch the results from the database
    $q = nuRunQuery($sql);
    $r = db_fetch_object($q);
    return $r->trans_name;
}

[Tinka]

Well, in this post http://forums.nubuilder.cloud/viewtopic.p ... wns#p10089 Steven points out, that a drop-down field is static once an Edit Form is loaded.

So there is no chance of updating a drop-down field after a lookup selection by Javascript or another method? I would rather use a drop-down than a Lookup-field since I will only have few options in the list.

Tinka

[Tinka]

I made it work by using a second look-up field instead of a drop down.

If anybody is interested in details, I can post them here. The short is:

The default value of the second lookup is filtered by the field name of the first lookup. The form for the second lookup is also filtered (in sql) for this hash variable. I made a second browse form to do so.

Tinka

[admin]

Moved by Admin