question about Lookup Table Filter (v12)

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
alex
User
Posts: 266

question about Lookup Table Filter (v12)

Post by alex »

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.


kirondedshem
User
Posts: 642

Post by kirondedshem »

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) ");
}

}

}


alex
User
Posts: 266

Post by alex »

Thank you very much! Your feeling about "easier script" also worked! )


Post Reply