Lookup Table filter with dynamic condition

My sample database as below:-DROP TABLE IF EXISTS calcs;
CREATE TABLE calcs (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
refcode varchar(20) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
catid varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO calcs (id, refcode, catid) VALUES
(1, ‘Calc01’, NULL),
(2, ‘Calc02’, ‘2’);DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
catcode varchar(20) COLLATE utf8_unicode_ci NOT NULL UNIQUE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO categories (id, catcode) VALUES
(1, ‘Cat01’),
(2, ‘Cat02’);DROP TABLE IF EXISTS prods;
CREATE TABLE prods (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
prodcode varchar(20) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
catid varchar(20) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;INSERT INTO prods (id, prodcode, catid) VALUES
(1, ‘Prod01’, ‘1’),
(2, ‘Prod02’, ‘1’),
(3, ‘Prod03’, ‘1’),
(4, ‘Prod04’, ‘2’),
(5, ‘Prod05’, ‘2’);My Question is:-
When I select ‘Calc01’, I want ALL Prods (Prod01…Prod05) to show in lookup list.
What if I select ‘Calc02’, only product(s) with catid == 2 (Prod04…Prod05) will shown.Is they any way to do this without creating a view?I did worte a Global Code:-
filterCats($val) {
echo “”. $val .“\n”;
$cond = ‘’;
$tmp = ExecuteScalar("select catid from calcs where catid = ". $val);
if (!is_null($tmp)) {
$cond = "catid = ". $tmp;
}
echo ““. $cond .”\n”;
return $cond;
}& I Placed filterCats($this->calcs->CurrentValue) in Lookup Table → Filter for prods field. But it still show all even with Calc02 selected in Add Mode.But when come to Edit mode, it show only prods with catid = 2.My Question is, what the the right variable to pass in ($this->calcs->CurrentValue) when in ADD & EDIT mode as well?Thanks in advance.

Be reminded that server events are fired on the server side, CurrentValue/EditValue/ViewValue does not have values in Add pages (there is no record yet). While they have values in the Edit page, the values are on the server side (when the page loads). If you want to get the parent field value that the user changes on the client side (after the page is loaded), try get them from $_POST.

scs wrote:
I Placed filterCats($this->calcs->CurrentValue) in Lookup Table → Filter for prods
fieldAre you sure? There is no “calcs” field in your tables above. Double check again your code and your tables schema.

My mistake, it is the id of calcs ($this->id->Currentvalue). Then base on the returned catid, filtering catid in prods file.Seems I can’t get $this->id->Currentvalue value, it is always blank during entry.Btw, what is the actual variable to hold the value for calcs->id->value?

scs wrote:
Seems I can’t get $this->id->Currentvalue value, it is always blank during entry.Because in Add page, there is no data displayed when the form is loaded. That’s why it is always empty.

For such case, then you may try “Example 4 - Auto-Fill asynchronously by custom API action and client script” under “Lookup Table” topic from PHPMaker Help menu.