How see fields coming from three connected table

Applications Forms
#1

Dear all,

I’m going crazy to understand this problem.

I’ve got three table that are connected on cascade.
TABLE 1 (movmag)
– id
– article_id

TABLE 2 (article)
– id
– description
– mat_type_id

TABLE 3 (material_type)
– id
– description

I have a (movmag) TABLE1 form with article_id and
within this integer field I’ve inserted this lookup

SELECT descri
FROM article
WHERE id = {article_id}
ORDER BY descri

OK - correct description of article done

I have two new_fields text added where I would like to show material type id and description

NEW_FIELD1 text (I would like to take mat_type_id from article table TABLE 2)

SELECT mat_type_id
FROM article
WHERE id = ‘{article_id}’
ORDER BY id, descri

OK - correct id of material type done

NEW_FIELD2 text (I would like to show material type description TABLE 3)

SELECT id, descri
FROM material_type
WHERE id = ‘{NEW_FIELD1}’
ORDER BY descri

NOTHING DISPLAYED
Where is the mistake I did on last select? Is it not possible to have a variable reference in an added new_field ?
Many thanks
Giovannino

see image–> http://img814.imageshack.us/img814/6103/selectm.png

#2

Re: How see fields coming from three connected table

Your form / Fields / NEW_FIELD1 / Ajax Processing / Check “Use Ajax …”

#3

Re: How see fields coming from three connected table

If I go through records using forward arrow wich Ajax event I have to choose ?

On Change, On Blur, On Click, On focus ? I’m just looking records and I do not change or focus any field.

may I use "sc_exec_sql "

I’ve tried using it but probably it’s too complex for me.
Still nothing appears

#4

Re: How see fields coming from three connected table

If id in material_type table is integer then you should remove the quotes:

SELECT id, descri
FROM material_type
WHERE id = ‘{NEW_FIELD1}’ ----> should be: WHERE id = {NEW_FIELD1}
ORDER BY descri

#5

Re: How see fields coming from three connected table

Try:

$sql="SELECT mat_type_id
FROM article
WHERE id = '".{article_id}."'
ORDER BY id, descri";
sc_lookup(dataset,$sql);
if(!empty({dataset})){
  $mat_type_id={dataset}[0][0];
}else{
  something
}
$sql="SELECT id, descri
FROM material_type
WHERE id = '".$mat_type_id."'
ORDER BY descri";
sc_lookup(dataset,$sql);
#6

Re: How see fields coming from three connected table

Many thanks for support
Giovannino