2. PHPRunner General questions
  3. [SOLVED]  Dynamically Filter dropdown in Add page
This topic is locked
[SOLVED]

 Dynamically Filter dropdown in Add page

 		8/7/2020 11:57:03 AM
PHPRunner General questions
A
 AlphaBase author

When adding a new payment record (i.e. payment was received), i want to allocate that to one or more invoices previously sent. This I can do no problem using an ADD page in a Master-Detail setup. I look up the invoice in a dropdown from the invoice table and select the correct one(s) to include in the detail "Payment_Allocations" table. Great that works.
BUT, what I really want is to filterthe dropdown on the Client_Idwhich is in the ADD page (but not yet submitted). IOW's I only want to see the invoices for that client in the dropdown on the ADD page. How can I grab the Client_Id and filter the dropdown on that before submitting?
Sergey Kornilov admin 8/7/2020

Very easy, use SQL variables:

https://xlinesoft.com/phprunner/docs/sql_variables.htm
A
 		AlphaBase author 8/9/2020

Doesn't work, no way, no how. Maybe I'm doing it wrong?

CLIENT_ID = ':master.CLIENT_ID2' is the filter in the dropdown.
Sergey Kornilov admin 8/10/2020

The idea is correct but you might be doing it wrong.
Every single piece should be correct i.e. you need to enter WHERE clause surrounded by double quotes, Client_Id and CLIENT_ID are two different things, CLIENT_ID must be a text field, otherwise, you need to remove single quotes from your WHERE clause etc.
It will work once you get everything right.
A
 		AlphaBase author 8/10/2020



The idea is correct but you might be doing it wrong.
Every single piece should be correct i.e. you need to enter WHERE clause surrounded by double quotes, Client_Id and CLIENT_ID are two different things, CLIENT_ID must be a text field, otherwise, you need to remove single quotes from your WHERE clause etc.
It will work once you get everything right.


Thanks but no dice. I tried removing the single quotes previously and again just now. Doesn't work. Yes the fields are CAPS, so that too is correct. And putting the entire where clause in double quotes causes data entry to be completely disabled in the detail section.

Just to be clear and maybe I should have said this before. This is a master-detail ADD page with the detail on the Add page, if that matters.
A
 		AlphaBase author 8/10/2020

Here is an image.....
Sergey Kornilov admin 8/10/2020

One more thing to consider. While SQL variables will work with current field values on Add/Edit pages it may not apply when you add master and details together. In this case, you can implement a Field Event for CLIENT ID2 field, save its value in the session variable and then reference that session variable in Lookup Wizard WHERE clause using session variable like :session.clientid2
A
 		AlphaBase author 8/29/2020

Finally got back to this. Thanks it works.

While SQL variables will work with current field values on Add/Edit pages it may not apply when you add master and details together. In this case, you can implement a Field Event for CLIENT ID2 field, save its value in the s

It would be great if this functionality as added to Master_Detail forms.