Skip to main content
Solved

Lobby Filter - NO value !%

  • Oct 7, 2022 4:22 AM UTC
  • 4 replies
  • 295 views
Hero (Customer)
Forum|alt.badge.img+10

Hi ,

I have Project ID as a parameter in my lobby .

When I leave it blank , it populate ALL records .

If I enter a Project ID, it will populate records related to this project. This is all fine until now as I am using this condition   project_id like nvl('$Project$', '%')

but now I want to populate all records where Project_id is blank  so I tried to enter !% in my lobby filter but it didn’t return any records.

How can I update my condition to return records with project_id =  no value .

 

Thanks

Best answer by Tomas Ruderfelt

Something like this:

('$Project$' IS NULL OR project_id LIKE '$Project$' OR ('$Project$' = '!%' AND project_id IS NULL))

 

Explanation:

  • When user enter Project Param value ==> POs related to this Project must be listed => project_id LIKE '$Project$'
  • When user leave Project Param to blank ==> All records listed => '$Project$' IS NULL
  • When user enter  !% in Project Param ==> only POs with Project_Id is Null must be listed. => ('$Project$' = '!%' AND project_id IS NULL)
View original

    4 replies

    Superhero (Employee)
    Forum|alt.badge.img+21

    This will show all records, regardless of project_id in the view if you leave parameter blank, (Use all parentheses due to the OR statement.).

    ('$Project$' IS NULL OR project_id LIKE '$Project$')

    If you enter % in the parameter you will get all lines with a project id.

      Hero (Customer)
      Forum|alt.badge.img+10
      • NMALKI
      • Author
      • Hero (Customer)
      • Oct 20, 2022 4:43 AM UTC

      @Tomas Ruderfelt Thanks for your reply but with your condition how can I get records with Project_id Null (not entered).

      I am trying to add a Case statement in my condition with no luck!

      Condition is below based on the Project Parameter value entered :

      • When user enter Project Param value ==> POs related to this Project must be listed
      • When user leave Project Param to blank ==> All records listed
      • When user enter  !% in Project Param ==> only POs with Project_Id is Null must be listed.

      Much appreciated

       

      Superhero (Employee)
      Forum|alt.badge.img+21
      • Tomas Ruderfelt
      • Superhero (Employee)
      • Answer
      • Oct 20, 2022 4:51 AM UTC

      Something like this:

      ('$Project$' IS NULL OR project_id LIKE '$Project$' OR ('$Project$' = '!%' AND project_id IS NULL))

       

      Explanation:

      • When user enter Project Param value ==> POs related to this Project must be listed => project_id LIKE '$Project$'
      • When user leave Project Param to blank ==> All records listed => '$Project$' IS NULL
      • When user enter  !% in Project Param ==> only POs with Project_Id is Null must be listed. => ('$Project$' = '!%' AND project_id IS NULL)
      Hero (Customer)
      Forum|alt.badge.img+10
      • NMALKI
      • Author
      • Hero (Customer)
      • Oct 20, 2022 9:26 PM UTC

      @Tomas Ruderfelt it worked, Thanks a lot for your help :) 

       

      Terms & ConditionsAccessibility statement