2. PHPRunner General questions
  3. [SOLVED]  Filter lookup values by current field value
This topic is locked
[SOLVED]

 Filter lookup values by current field value

 		1/8/2014 18:04:09
PHPRunner General questions
G
 grin968 author

Hi there,

I'm trying to find a way to limit choices in dropdown (lookup) by the current value in that dropdown.

For example, if I have status field with all possible values:

  • draft
  • submitted
  • in progress
  • completed
  • cancelled.
    From draft status user can select submitted or cancelled;

    from submitted - in progress or cancelled or draft;

    from in progress - cancelled or completed.
    I have table defining statuses and separate table defining allowed status changes (from_status_id, to_status_id).

    PhpRunner Ent 6.2
    Any idea how it can be done?
    Thanks,

    Greg
G
 		grin968 author 1/9/2014

Found solution myself. It doesn't shine with simplicity but it works. Here it is.
Let's say there are tables

  • requests (I'm with MySQL, so change syntax accordingly) :

    request_id, name, whatever_column, status_id
  • statuses:

    status_id, name
  • status_rules:

    status_id, can_change_to_id
    The later one specifies:
  • for existing status (status_id) how it can change (can_change_to_id) and to itself (for display purpose)
  • for display purpose add all statuses (can_change_to_id) with status_id = 0. This entries are necessary to display status name when records are in read mode
    Add view joining all statuses with their rules:

    create view vw_status_rules

    SELECT

    r.status_id,

    r.can_change_to_id,

    s.Name AS Name

    FROM (status_rules r

    LEFT JOIN statuses s

    ON ((r.can_change_to_id = s.status_id)))
    Now it's a PHPRunner turn:
  • Sync you project.
  • Go to Editor, select status_id field, got to Edit as:, Lookup Wizard
  • Select vw_status_rules in Tables
  • Select can_change_to_id as Link field
  • Display Name an d Order by - Name
  • in WHERE type in " status_id = " . $_SESSION["status_id"]
  • submit changes
  • Go to Events
  • Select List page -> List page: Before process. Add $_SESSION["status_id"] = '0'; to function BeforeProcessList
  • Select Edit page -> Before Display. Add $_SESSION["status_id"] = $values['status_id']; to function BeforeShowEdit
  • Select Edit page -> After record updated. Add $_SESSION["status_id"] = '0'; to function AfterEdit
    That's it.
    Would be glad to have more elegant solution. Let me know if you have one. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=73603&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

    Cheers,

    Greg
Sergey Kornilov admin 1/9/2014

I was about to suggest the same idea: WHERE clause based on session variable that is getting populated in BeforeShowEdit event.