Hi
This related to the solution posted here (http://www.asprunner.com/forums/topic/19820-how-to-limit-the-values-shown-in-the-lookup-wizard-based-on-a-variable-from-the-page/).
I am creating an application with phpr 8.1 and the need is similar:
We have a 'contracts' DB table that store the information about the contracts.
We have a 'invoices' DB table that store the information about the invoices we have sent (the 'invoices' table has a FK 'contract_id': a link to the 'contracts' table)
We have a 'payments' DB table that store the information about the payments that we have received for a contract (the 'payments' table has a FK 'contract_id': a link to the 'contracts' table)
We have a 'map_payment_to_invoice' DB table to assign payments to invoices. This mapping table has 3 FK: 'contract_id' to the 'contracts table', 'invoice_id' to the 'invoices' table and 'payment_id' to the 'payments' table
In phpr I have created a view 'phpr_contract' which uses the DB table 'contracts' with several phpr child views:
- 'phpr_invoices' which uses the DB table 'invoices' and is linked with the 'contract_id' to the master view 'phpr_contract'.
This is where you can create new invoices for a contract.
- 'phpr_payments' which uses the DB table 'payments' and is linked with the 'contract_id' to the master view 'phpr_contract'.
This is where you can record new payments received for a contract.
The view 'phpr_payments' also has a child view 'phpr_map_payment_to_invoice' which uses the DB table 'map_payment_to_invoice' and is linked with the 'contract_id' and 'payment_id' to the master view 'php_payments'.
This is where you can "match" the invoices sent and the payment received (it is possible that a payment is used to pay several invoices or that an invoice was settled with several payments).
In the view 'phpr_map_payment_to_invoice', the fields that are automatically populated are the 'contract_id' and the 'payment_id'.
This view is used to link a payment to an invoice.
We do that by updating the field 'invoice_id'.
To facilitate the update we have created another phpr view 'phpr_list_invoices'
The field 'invoice_id' in the view 'phpr_map_payment_to_invoice' is a lookup wizard that uses the view 'phpr_list_invoices' to display the invoices that exist in the system so we can search/select the right invoice from the list and link it to the payment.
What I want to do is to limit the invoices that are shown in the lookup wizard to the invoices for the correct 'contract_id':
We don't need to see ALL the invoices in the list page with search, we just need the invoices where the 'contract_id' in the view 'phpr_map_payment_to_invoice' is the same as the 'contract_id' in the view 'phpr_list_invoices'.
I have tried the method described in the old post, which is to:
1- In the Events >> After application initialized, Add the code
---
if(isset($_GET['editid1']) && isset($_GET['mastertable']) && !empty($_GET['editid1']) && !empty($_GET['mastertable']) && $current_page == 'phpr_payments_view.php')
{
$_SESSION['contract_id'] = $_GET['editid1'];
}
if(isset($_GET['editid1']) && isset($_GET['mastertable']) && !empty($_GET['editid1']) && !empty($_GET['mastertable']) && $current_page == 'phpr_payments_edit.php')
{
$_SESSION['contract_id'] = $_GET['editid1'];
}
if(isset($_GET['masterkey1']) && isset($_GET['mastertable']) && !empty($_GET['masterkey1']) && !empty($_GET['mastertable']) && $current_page == 'phpr_payments_list.php')
{
$_SESSION['contract_id'] = $_GET['masterkey1'];
}
---
The objective is to create a session variable to store the 'contract_id' when we have it on the page 'phpr_payments_list'
2- in the WHERE section of the lookup wizard for the field 'invoice_id' in the view 'phpr_map_payment_to_invoice' Add the code
---
"contract_id=" .$_SESSION['contract_id']
---
Unfortunately this does not work: the SESSION variable does not seem to be here and php throws a SQL error. This error disappears when I remove the WHERE condition (but then I have a super long list of invoice to choose from which is unpractical).
What am I missing here? Do you have an alternative method to make it work? Can you help?
Thanks
