how to reference a form element in an Sql statement ?

[nettemor]

Hi,
I am trying to reference a form element in the 'Joins where and/or order by statement (SQL)' field. But it throws an error. I have referenced it like this {ac_salg___postnr.value} but seems to be wrong. Whats the correct reference to use (hope it is possible) ?

Here is the statement:
WHERE {thistable}.`id`
IN ( SELECT montorid FROM `xx_montorer`, `xx_montpostnr` where xx_montorer.id=xx_montpostnr.montorid and xx_montpostnr.montorpostnr= {xx_salg___postnr.value} )

(Background: user enters a postalnumber in a postalno field. Based on this input i need to fill a dropdown with all the monters that are doing montings in the areas with the entered postalcode. )

 

[troester]

The where and/or ... is only used on form load so it won't help in your case.

I think you are looking for the cascading dropdown element which will watch the postalno field.
http://fabrikar.com/forums/index.php?wiki/cascading-dropdown-element/

 

[nettemor]

Ah.... And i can use an input field and let the user enter the postalno in this. And based on the value/changes in this field i can fill the dropdown with monter names for the right postalno ? This is the way i'd like do it, and its possible ?

 

[nettemor]

Did not have the cascading plugin installed, and that cleared things up.

So, i now have a field called postalno. User enters value in this and the cascading dropdown filles up.
This is all good. But here is the challenge; the cascading dropdown is picking up the id's from table2 and displays these. But i would like to display the names. But the names connected to the id' is in another table. Any suggestion how i can solve this so that the names are displayed and not only the id's?

 

[cheesegrits]

You'll need to use a CONCAT for the label, which does a sub-query. One of those little tricks that takeas advantage of CONCAT()'ing the label.

If you can give me the exact structure, i.e. actual table and field names and how 'table2' relates to 'another table'. Like, is it another_table.table2_id is an FK pointing to the PK of table2 (table2.id, or whateve).

The subquery will probably look like this ...

(SELECT name FROM another_table WHERE another_table.table2_id = '{table___table2_id}')

... where {table___tbale2-id} is the full element name of your CDD. So it uses the selected value of that (which presumably points to the PK of table2), and looks up the row/field in another_table which has the same FK for table2, and pulls the name from it.

-- hugh