Retrieve value from master table to use in Recordset_Selecting

[thalassa3003]

I am using PHPmaker v2024.8.
I have table structure as follows:

--- table1
--- table 2
--- table 3

These are related as master/detail tables (Add/Edit/View). So I can expand the different rows from the detail tables.

Now I would like to add a filter in the function 'Recordset_Selecting(&$filter)' on table 3.
For this I need to capture the value of table1.categoryCode.

The filter is AddFilter($filter, "category = $table1Category");

Is there a function to capture the value from the column table1.category to use as a filter on table3?

The other scenario is to get a value from table2.categoryId and use a MySql to retrieve table1.categoryCode (SELECT categoryCode FROM table1 WHERE id = $table2.categoryId)
How do I retrieve the $table2.categoryId from table2 (mastertable) from the table3 (detailtable).

[arbei]

1. Master/Detail is two level only, you cannot use data from level 1 to filter records in level 3.
2. Detail table only know the foreign key, if categoryId is the foreign key linked to the master table, then you can use it. Otherwise you need to query the database to find the categoryId according to the foreign key value.

[thalassa3003]

For the second option:
How can I retrieve a value (categoryId) from table2 (mastertable) from table3 (detailtable)?

That way I can indeed

• query the database in the code of table3 to get the value from table1
• use this value in 'Recordset_Selecting(&$filter)' on table 3.

[arbei]

arbei wrote:

you need to query the database to find the categoryId according to the foreign key value

You may use ExecuteScalar() (to get a value from the master record) or ExecuteRow() (to get the whole master record).

[thalassa3003]

Thanks for the feedback!

I am indeed familiar how to do this query. However I am not able to retrieve the value 'categoryId' from table2 (mastertable) that I need for the query.

In the Recordset Selecting event from table 3 (detail table) I tried:

$masterTable = $this->getCurrentMasterTable();
$categoryId= Container($masterTable)->categoryId->CurrentValue;

But the $categoryId is empty, however the value is present in table2 (masterTable).

Am I doing something wrong?

[arbei]

You need to use the current page object of the master table (e.g. List page of master table), not the master table object (which is a fresh object with no data if you get if from container).