Creating a subselect within a subselect using FPDF and Scriptcase? Here is the case I am trying to solve. Each invoice contains within it multiple work orders that have 1 to many line items. I’ve gone as far as I can with modifying my CODE in FPDF even using the Events to load variables but you cannot modify a subselect WHERE criteria based upon the current subselect onRecord event. You can use it within the primary select statement but you cannot modify a subselect SQL statement.
Visual representation:
Customer Invoice
Pagebreak
- Work Order A - use the primary key from the workorder table (pkey: iniid)
— Line Items (1:M) - use the primary key from the workorderdetails table (pkey: woiid).
— woiid: 10547 - Work Order B - use the primary key from the workorder table (pkey: iniid)
— Line Items (1:M) - use the primary key from the workorderdetails table (pkey: woiid).
— woiid: 10548
A subselect cannot be altered using the Scriptcase functions:
$save_where = {sc_where_orig};
$save_current_filter = {sc_where_filter};
$save_current_where = {sc_where_current};
These will only allow you to change the primary SQL select statements.
The trick is how to change the 2nd subselect where clause.
Any tips or feedback on how to change the WHERE clause of subselect SQL statement as it iterates through the first subselect statement? Hence this would change the WHERE clause of the subselect statement within the first subselect statement?