2. PHPRunner General questions
  3. Lookup with where clause
This topic is locked

Lookup with where clause

 		4/5/2008 12:14:58 PM
PHPRunner General questions
A
 acpan author

a. MAIN Table
NameID, Name, Tel, GroupID
b. GROUP Table
GroupID

GroupName

NameID
When user clicks on inline edit on the MAIN Table List page,

He can click the drop down on GroupID which present a lookup

table to GROUP table and filtered on current NameID of MAIN Table.
How to add the where clause to lookup field in the MAIN table ?
i tried in visual editor for MAIN table:
Lookup table: Group
and in where clause:

' GroupID ='.$data["NameID"]
but failed.
It seems that i can't reference the current record's NameID as $data["NameID"],

and pass it to the lookup table.
Thanks

Dex
J
 		Jane 4/7/2008

Dex,
use can't use $data array in the where clause in this case.
As workaround save NameID value in the $_SESSION variable in the Edit page: Before Process event on the Events tab and then use this $_SESSION variable in the WHERE clause.
C
 		ckranichDevClub member 4/16/2008

Dex,

use can't use $data array in the where clause in this case.
As workaround save NameID value in the $_SESSION variable in the Edit page: Before Process event on the Events tab and then use this $_SESSION variable in the WHERE clause.


Hi Jane,
I have a similar problem (maybe because of a bogus database design...)

I want to use the WHERE clause in the lookup wizard to limit the possible selections

(Unfortunately there are TWO fields to be taken into account)
I have a productionline table which

holds:

DIMID ..... points to a dimensions.UID

PROGID ... points to programme.UID

PROGAM ... should be filled by dropdown selection
according to your suggestions above I did

  1. Before Process Edit

    // save DIMID and PROGID into $session for later retrieving in lookup wizard where clause

    $var = $smarty->get_template_vars("value_PROGID"); // get PROGID

    $_SESSION["PROGID"]= $var;
    $var = $smarty->get_template_vars("value_DIMID"); // get DIMID

    $_SESSION["DIMID"]= $var;
  2. used $_SESSION in WHERE clause

    "DIMID =".$_SESSION["DIMID"]

    (only used one field to get the simple case running first)
    But still the 'Test it' function says it cannot evaluate the WHERE clause and therefore delivers all values
    What have I done wrong?
    Thank you in advance for your help!
    Christian
J
 		Jane 4/17/2008

Hi,
there is no $smarty array in the Before process event.

You need to select PROGID and DIMID fields from database and then save it in the $_SESSION variables.

Here is a sample:

global $conn,$strTableName;

$str = "select PROGID, DIMID from ".$strTableName." where RecordID=".$_REQUEST["editid1"];

$rs = db_query($str,$conn);

$data = db_fetch_array($rs);
$_SESSION["PROGID"] = $data["PROGID"];

$_SESSION["DIMID"] = $data["DIMID"];



where RecordID is your actual field name where primary key is stored.