2. PHPRunner General questions
  3. Hide items from dropdown box with lookup wizard
This topic is locked

Hide items from dropdown box with lookup wizard

 		2/25/2010 7:40:15 PM
PHPRunner General questions
N
 nico282 author

Hi.
I have two tables - say "Courses" and "Teachers". "Couses" have the fields CourseName and TeacherID, "Teachers" have the TeacherID, the TeacherName and the RetirementDate.

The Courses view have a dropdown menu set with link field TeacherID and display field TeacherName.
My need is to show in the dropdown menu only the teachers in service, not showing the retired ones (RetirementDate < Today).
If I set the Where condition, the dropdown shows the correct values, but for thecourses linked to the retired teachers the list and the detail view shows the TeacherID instead of the TeacherName, as if the disabled values where deleted.

Obviously, if i don't set the Where codition of the dropdown the list is correct, but the menu shows all the values.
I'm using PHPR 5.1, and I couldn't think of any other simple wai to achieve what I need.
Thanks for the help,
Nicholas
A
 		alang 3/1/2010

A good way to handle this is to create a "View" in the database (not a PHPR view). You need to use a database admin tool to create this view. In the view you include the where condition (teachers in service), the teacherID and the name of the teacher that you want to view in the dropdown. In the lookup wizard, you then select the view rather than the table as the lookup target and then the text name as the field you want to display. Using a view in this way gives a lot more flexibility on what you can display in the dropdown - concatenation of fields across multiple tables etc.
Sergey Kornilov admin 3/1/2010

Nicholas,
I can suggest the following workaround.
Add an alias of TeacherID field to SQL Query:

select ...

TeacherID,

TeacherID as TeacherID2

from ...
Set 'Edit as' type of TeacherID2 field to 'Lookup wizard' without adding a WHERE clause.
Make TeacherID2 field appear on List/View/Print/Search pages (remove it from Add/Edit).

TeacherID field (setup as Lookup Wizard with WHERE clause) should appear on Add/Edit pages only.
This is it.
N
 		nino82 3/23/2010

Hello.
My problem is identical to that of Nicholas, but I use inline edit and inline add.
How can I fix it?
Thanks for the help.
Nino
Sergey Kornilov admin 3/23/2010

Nino,
it works the same way with inline add/edit.
N
 		nino82 3/24/2010

Sergey'll explain my problem better.
I want to implement your solution. But in my case, with your solution, I will see two TeacherID fields in Courses page (type List with Inline Add\Edit):

  • TeacherID2, without WHERE clause, to view anyway (In service or Retired case) the correct teacher's name
  • TeacherID, with WHERE clause, to add or modify the teacher's value ONLY with valid values
    My goal is to see, by default, TeacherID2 in Courses page and to see TeacherID during Add/Edit Inline steps.
    Thanks for the help,
    Nino
J
 		Jane 3/25/2010

Hi,
unfortunately it's not possible to replace TeacherID2 with TeacherID field on the list page when you turn on inline edit.