2. PHPRunner General questions
  3. [SOLVED]  How to limit the values shown in the Lookup wizard based on a variable from the page?
This topic is locked
[SOLVED]

 How to limit the values shown in the Lookup wizard based on a variable from the page?

 		7/6/2012 08:18:31
PHPRunner General questions
L
 lmb_hs_sp author

Hi,
I'm struggling with this issue and I'm sure I can get some help from you guys...
The context:
The application we're building is to manage flats that we rent to customers.
When a customer moves in, we need to organize a handover of the flat.

For each customer, I have a list of occupants.
From a MySQL point of view I have 2 tables:

customer table (primary key: id_customer)

occupant table (primary key: id_occupant; customer_id)
Now I need to organize handover so I have another table where I record handover information:

handover table (primary key: id_handover, foreign keys: customer_id and occupant_id)
I have created a phprunner view to organize handover.

In that handover view I have 2 fields where I store

the customer_id

*the occupant_id
This view to organize handover is a detail view of the master view 'customer' so when the user clicks on the 'handover' link, the customer_id is there in the add/edit handover page. The Master/Child relationship between these 2 php runner views is based on the customer_id.
What I want to do:

On that add/edit handover view I need the drop down for occupants to be limited to the list of occupants where the customer_id is the currently selected customer_id (no point in having the list of all the occupants for all the customers).

So I should be able to do this in theory with the WHERE option in the lookup wizard.
My problem is I can't seem to figure out the syntax need to use to make this work...
As a summary:
Variable name in the Add/Edit handover view:

customer_id (no need to enter this one: it is inherited because handover view is a detail view).

occupant_id (which I need to enter/select from a drop-down list)
variable for the Table that populate the lookup wizard are:

occupant_id

customer_id

occupant_details
What I did:

In the Editor, change the properties of the 'occupant_id' field to Lookup wizard

In the Link field I select 'occupant_id'

In the Display field I select 'occupants_details'
But what is the syntax I should use in the WHERE field to limit the list of occupant in the drop down to the occupants where the customer_id is equal to the customer_id of the current page?

I tried a lot of things and I believe that the closest to the solution should be something like

-----

"customer_id=".$values["customer_id"]

-----

But it does not work: error message is

Undefined variable: values
Any suggestion?
Thanks
Franck
C
 		cgphp 7/6/2012

Set a session var when the current customer_id is selected:

"customer_id=".$_SESSION["customer_id"]
L
 		lmb_hs_sp author 7/6/2012

Hi Cristian and thanks for the quick reply.
I have a couple of question:
Where exactly shall I set this var (which event type?, on which page do you recommend I set this event up?)
I'm assuming this is a var that will be valid during the whole user session. So I need to make this variable very unique to this context by setting a name for this var in such a way that there is no possible confusion with some other session variable that might be required in another context right?
Once I have set the session var then the syntax in the WHERE part of the lookup wizard will be:

----

"customer_id=".$_SESSION["the_very_specific_name_for_this_session_variable"]

----
Is this correct?
Sorry if this sounds obvious to you but I'm quite new at these things...
Thanks again for the reply
Franck
L
 		lmb_hs_sp author 7/6/2012

Another question:
I'am assuming there that the session variables are valid during the whole life of a user session (from login to logout) so if I go with this session variable logic, what happens if a user wants to enter several handovers for different customers during the same session?
Thanks
Franck
C
 		cgphp 7/6/2012

Can you post some screenshots of your app?
L
 		lmb_hs_sp author 7/6/2012

Hi Cristian,
The Screenshots are visible here
Master View

https://picasaweb.google.com/103992959312630936663/Limit_dd_views?authkey=Gv1sRgCPjTuaqllejlmAE#5762246989885693394

Child view (inline add)

https://picasaweb.google.com/103992959312630936663/Limit_dd_views?authkey=Gv1sRgCPjTuaqllejlmAE#5762246990000963234

Child view (add)

https://picasaweb.google.com/103992959312630936663/Limit_dd_views?authkey=Gv1sRgCPjTuaqllejlmAE#5762246991107739826
Let me know if you need any more info.
Thanks
Franck
C
 		cgphp 7/9/2012

Limit the "Lookup wizard" by the masterkey:

"contract_id=".$_GET["masterkey1"]
L
 		lmb_hs_sp author 7/9/2012

Hi Cristian,
I tried your suggestion.

---

"contract_id=".$_GET["masterkey1"]

---
Unfortunately it still gives me an error message (albeit different from before):
----

Error type 256

Error description You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

URL localhost/cust_ho_comments_add.php?

Error file C:\...\output\include\dbconnection.my.mysqli.php

Error line 42

SQL query SELECT id_occupant, contract_id, details_on_occupant FROM ipi_opt_occupant_details WHERE (contract_id=)

----
Any idea why this happens?

FYI I'm running phprunner 6.1 build 12231
Franck
C
 		cgphp 7/9/2012

I was assuming you were using inline mode and not popup mode for master-details relationship. Does it work if the inline mode for master-details relationship is enabled?
L
 		lmb_hs_sp author 7/9/2012

YES!

it works if I'm in inline mode and if I do an inline Add.
Does this means that it would not work in the popup mode then?
Thanks
Franck
C
 		cgphp 7/9/2012

Here it is a solution for the popup mode. In the "After application initialized" event, enter the following code:

$current_page = basename($_SERVER['SCRIPT_NAME']);
if(isset($_GET['masterkey1']) && isset($_GET['mastertable']) && !empty($_GET['masterkey1']) && !empty($_GET['mastertable']) && $current_page == 'cust_ho_out_comments_list.php')

{

$_SESSION['contract_id'] = $_GET['masterkey1'];

}


Limit the "lookup wizard" by

"contract_id=".$_SESSION['contract_id']


Make sure cust_ho_out_comments_list.php, is the real name of the details page.
L
 		lmb_hs_sp author 7/9/2012

Great! this sounds good.
Now if I understand correctly, your code assign the value of the variable 'masterkey1' to the session variable 'contract_id' if:

-we're on the right page

-we have a master table variable

-we have a master key variable
Is there a risk that the value is this session variable is not properly updated?

In other word is there a risk that the value stored in the session variable 'contract_id' is set only once and used every time a user wants to create a new handover for a different customer (which would not be the intended behavior)?
I just want to make sure that this code re-write the session variable 'contract_id' every time the 'masterkey1' changes during the session.
Once again sorry if this is a stupid question, I'm a total noob with php...
Thanks a lot for all your help so far
Franck
C
 		cgphp 7/9/2012

The code above is executed each time a new request is made.
L
 		lmb_hs_sp author 7/9/2012

Legen...

(wait for it)

...dary!
Thanks a tom!
Franck