Lookup value from other table in the grid ?

QuestionsLookup value from other table in the grid ?
Davy asked 7 years ago

Hello,

I might have a question about something which is in the commercial version, but I want to make sure it is nothing that is simple to be done.

In my table, I keep a value (ID) of another table. So let's say ID 2. In the other table, there are 2 fields : ID and name. In my grid, the current value of the field is shown, so 2 is shown.

I would like to show the name of the ID 2 instead in the grid…
I found a solution to show the dropdown correctly in my edit form, but not in the grid.

Is this possible or not ?

Greetings,

Davy

6 Answers
varun answered 7 years ago

SELECT distinct ID,company,bhal,blah,blah,ID2 from table1 as t1 INNER JOIN table2 as t2 on t1.ID=t2.ID

remember ID2 is from 2nd table and its foreign key in table1.
use the above code in select_command.

Hope It Helps!!

Abu Ghufran answered 7 years ago

Thanks Varun for your continuous help.
@Davy, i am also sending you sample.

Abu Ghufran answered 7 years ago

Another option is to select id field in sql (invheader.client_id) with JOIN of reference table (INNER JOIN clients)

$g->select_command = "SELECT id, invdate, invheader.client_id, amount, note FROM invheader
INNER JOIN clients on clients.client_id = invheader.client_id
";

And few changes in column definition.

$col = array();
$col["title"] = "Client";
$col["name"] = "client_id";

# this is required as we need to search in name field, not id
$col["dbname"] = "clients.name";

$col["edittype"] = "select"; // render as select

# fetch data from database, with alias k for key, v for value
$str = $g->get_dropdown_values("select distinct client_id as k, name as v from clients");
$col["editoptions"] = array("value"=>$str);

# and set formatter, so that value is shown on grid instead of id
$col["formatter"] = "select"; // display label, not value

$cols[] = $col;

Davy answered 7 years ago

Thanks all,

I received the example and I put the code in the system.
I now indeed see the real value in the grid plus I have it as a dropdown field in my edit form…

But, for a strange reason, from the moment I added this code, when I try to CHANGE a row in the form, the button "Save" does not do anything anymore.
Also, the trashbin (delete) button does not work anymore.

There's no error message… Just return to the grid and nothing changed…

Any help please ?

Greetings,

Davy

Abu Ghufran answered 7 years ago

Most likely, the edit/delete are not working due to missing PK column (as discussed in other ticket).
It will be resolved once you add PK column.

Davy answered 7 years ago

Abu,

it is solved now… I missed indeed the ID field in my selection formula…

1000 Times thanks

Greetings,

Davy

Your Answer

2 + 2 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?