SQL queries

Status
Not open for further replies.
P

piri314

Member
Hi,

Could you, please, help me to configure an SQL query in an element :
I would like to use a drop down on an ID which value is in another table

1 > table SVE_annu
id_sve
id_annu (drop down from table Manifs)

2 > table Manifs (this table contains some datas from table Annu)
id_annu (drop down from table Annu)

3 > table Annu
id_annu
value : structure

So, how to obtain the value structure in the 1st drop down (table SVE_annu) ?

Thank you for your advices.

Pirina
 
Not quite sure I understand what you are trying to do. You should just be able to do a simple join on table Annu, using 'id_annu' as the key and 'structure' as the value. I'm not sure where the Manifs table comes in to it.

-- hugh
 
Hi Hugh,

The
2 > table Manifs
contains only 25 organisations with specific information comming by data joint drop-down list from the
3> table Annu (which have 374 items). The link is the id_annu and the text > the name of the organisation (structure).

In my
1 > table SVE_annu
if I do a simple data join drop down to table Manif (because the link concerns only the organisations in table Manifs), I have the id_annu number, not the name of the organisation (structure) which is in the table Annu.

Thanks
PIRINA
 
OK, I think I understand now. You only want the dropdown to contain items from manifs, using the structure name from annu.

You'll have to use a DIY join, something like this:

Code: 
SELECT manifs.id_annu as value, annu.structure as text FROM annu INNER JOIN manifs ON (manifs.id_annu = annu.id_annu)

-- hugh
 
Hi, one more time about this tread.
In the form the sql query works, BUT the asked value is displayed in the table view (and the form sent to the admin) like id_number, not like text value.

How it's possible to display all the time the text value (in red) from the query ?

SELECT manifs.id_annu as value, annu.structure as text FROM annu INNER JOIN manifs ON (manifs.id_annu = annu.id_annu)

Thank you
PIRINA
 
We're looking in to that one. Being a join element, the table view should show the 'text' rather than the 'value' by default. But the "DIY" join type is a new feature, so we may have moofed something.

-- hugh
 
i just tested this with the latest code form the svn and it correctly shows the 'text' part of the query in the tabl view:

to test:

create an diy database join element:
query = select id as value, username as text from jos_users

add a record via the table's form, select "admin" from the diy join list
view table - the data correctly shows "admin" and not "62"
 
Interesting.

Could you PM me the usual backend login details, and an ftp account? I need to check what exact version you are running, and if necessary upgrade you.

Remember to include a link back to this thread in your PM.

-- hugh
 
This does seem to be a bug. I suspect it's something to do with this element belonging to a joined group.

I'll have to get Rob to take a look at this one, so I'm creating a ticket on it.

-- hugh
 
Pirina,

We just went to take a look, and it seems that you have changed your table / element structure ... I no longer see that element ...

-- hugh
 
Hi,

Sorry, you can see the problem now, the tables, forms, elements are on the site.

I re-did the connexions one more time, without succes.

Thanks
PIRINA
 
We're still working on it. We've spent about 4 or 5 hours on it so far ... hope to have a resolution soon.

-- hugh
 
I tell a lie, it looks like Rob has fixed this in the latest SVN. I just checked my test case with this mornings SVN build, and it renders properly on the table now.

Try updating and let me know.

-- hugh
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 220 (members: 2, guests: 218)
    Back
    Top