question about Lookup Table Filter (v12)

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

question about Lookup Table Filter (v12)

Postby alex » Sat Jan 06, 2018 12:16 pm

I try to place a code into Lookup Table -> Filter (I wish select data based on data from another table):

The logic I need is something like this but it doesn't work because it selects more than one record: "`product_id` = '" .ew_ExecuteScalar("select product_id from product_description where product_id in (select product_id from product where status = 1) "). "' "

This one for instance is working: "`product_id` = '" .ew_ExecuteScalar("select product_id from product_description where product_id = '85' "). "'"

I have read the manual that I would probably need a MyLookupFilterFunction() but I have no idea what kind of script and where to put it to make it working (never did):

I think I need something like I use in Recordset_Selecting but as I said - no idea how to...:

$lookuptblfilter = "`product_id` = '" .
$rs = ew_Execute("select product_id from product_description where product_id in (select product_id from product where status = 1) ");
if ($rs && $rs->RecordCount() > 0) {
$sFilter = "";
while (!$rs->EOF) {
$sFilter .= " `To` = '".$rs->fields("product_id")."' OR";
$rs->MoveNext();
}
$sFilter = rtrim($sFilter, "OR");
ew_AddFilter($filter, $sFilter);
$rs->Close();
}

Please advice.
alex
 
Posts: 113
Joined: Wed Mar 12, 2014 11:15 pm

Re: question about Lookup Table Filter (v12)

Postby kirondedshem » Sat Jan 06, 2018 1:43 pm

>The logic I need is something like this but it doesn't work because it selects more than one record: "`product_id` = '" .ew_ExecuteScalar("select product_id from product_description where >product_id in (select product_id from product where status = 1) "). "' "

If am underatnding this correct,you want lookup to pick only products whose product_d from description is active in product table.
This means the filter you apply should be able to pass an array of product_ids not only one product_id
NOTE:ew_ExecuteScalar returns a single value so it obviously wont work for you.
NOTE:you dont have to loop through the products ids either, although you are not doing it right either.
Possible solution:
Using sql rules if i want to check if a product_id(single value) is equal to any values ina given set, I can use "in" operator. So do this instead, the sql would be something like
-select product_id from my_table where product_id in (select product_id from product_description where product_id in (select product_id from product where status = 1)

>I try to place a code into Lookup Table -> Filter (I wish select data based on data from another table)
-go to the table->Lookup_Selecting and put this (you can read about it in help menu)
NOTE: I assume the filed having the lookup table setting is called my_field

// Lookup Selecting event
function Lookup_Selecting($fld, &$filter) {
//var_dump($fld->FldName, $fld->LookupFilters, $filter); // Uncomment to view the filter
// Enter your code here

//fAPPLY THE FILTER ONLY ON ADD and EDIT pages
if ((CurrentPageID() == "add" || CurrentPageID() == "edit"))
{
//APPLY FILTER ONLY WHEN LOOKING UP ON THIS FIELD
if ($fld->FldName == "my_field")
{
ew_AddFilter($filter, " product_id in (select product_id from product_description where product_id in (select product_id from product where status = 1) )");
//NOTE:I feel It can be made easier by using the one below
// ew_AddFilter($filter, " product_id in (select product_id from product where status = 1) ");
}

}

}
kirondedshem
 
Posts: 313
Joined: Sat Jan 28, 2017 7:13 pm

Re: question about Lookup Table Filter (v12)

Postby alex » Sun Jan 07, 2018 5:28 am

Thank you very much! Your feeling about "easier script" also worked! )
alex
 
Posts: 113
Joined: Wed Mar 12, 2014 11:15 pm


Return to General Discussion (PHPMaker)